SQL performance probs

[Thom Shannon]

Hi, I'm having some problems with our db server, it's SQL Express 2005.
We've got a couple of dozen databases running different apps and every
few days or so it just slows right down till its taking something like
20-30secs to render pages.

Restarting SQL sorts it out again, restarting the web app pools doesn't
seem to help. Activity monitor and profiler really aren't helping me
with it, it's really intermittent and when it happens I can't afford to
spend much time investigating because it's affecting all our clients.

Any advice much appreciated!

--
*** OPEN COFFEE - http://upcoming.yahoo.com/group/3263/ ***

Terms & Conditions Apply: http://www.glow-internet.com/home/terms.aspx

Glow New Media
t: 0151 707 9770
m: 07730 987 574
www.glow-internet.com

Suite 712 Gostins Building
32-36 Hanover Street
Liverpool
L1 4LN

Map: http://tinyurl.com/2f5nxd

[Derek Fowler]

Have you tried tracking the number of open connections in Perfmon? Maybe something is leaving connections open so the server is running out and everyone is having to go though the few remaining ones.

Derek

--
Derek Fowler
m. 07966 512 369
e. dezf...@gmail.com

[Thom Shannon]

I'm still not getting very far with this. I can't see any unusual load on the server that starts this intermittent slow down, I've tried starting up a bunch of sites and watching the connections build up in activity monitor, that seemed to trigger the issue, but then I tried it again and more than double the connections didn't do anything!

I'm suspecting a LinqToSql app that went live roughly around the time this started, I'm using a DataContextFactory to manage creating instances of the context within the scope of the request only, on other things I've used a static DataContext, I'm not sure which is best though.

Any ideas?

[Derek Fowler]

Check the docs for DataContext - it should give a prescribed use and at least say whether it's thread safe or not. Maybe it'd be worth getting reflector on it at seeing whether the DataContext is holding a pool of connections internally.

 

How about locks, have you checked that? Could be that LINQ is being overly aggressive and locking tables.

 

Derek

[Paul Kinlan]

Are you disposing your data context?

2008/10/16 Derek Fowler <dezf...@gmail.com>

[Thom Shannon]

I'm letting the bin men do their work. I could probably dispose it on an end request event or something.

I've been watching the db locks, don't see anything out of the ordinary.

[Paul Kinlan]

I belive it should be disposed, potentially after each query you make in asp.net, I say this, because if you have connection pooling on (which it is by default) you shouldn't notice connections be created too much and you don't have a single thread of execution which you might do if it was a winforms app.  The problem you get is how you want to deal with calling SubmitChanges if there are multiple queries and steps made to change the data.

Paul.

2008/10/16 Thom Shannon <th...@glow-internet.com>

[Thom Shannon]

Yeah the issue is the change tracking, a select query is fine but when you have something more complicated going on you need to know that you're working within the same datacontext.

This is the factory I used, http://www.west-wind.com/weblog/posts/246222.aspx

Thom