Welcome to WebmasterWorld Guest from 54.167.85.221

Forum Moderators: open

Message Too Old, No Replies

SQL time outs

queries keep timing out

     

giggle

11:23 am on Feb 10, 2013 (gmt 0)

10+ Year Member



Hi

We just got a new Windows 2008 server running SQL Server 2008 R2.

Our system is written in 'classic' ASP (I want to retire).

After a reboot the server works great and all aspects of the website function quickly, then, after a few hours, all updates start to generate timeouts and all sorts of screen start to fall over. Then my life becomes hell. At the moment I just reboot the server to give the others another few hours of work time.

Does anyone have any idea what would cause these timeouts to start happening after a few hours of operation?

Thanks in desperation...

Mick

LifeinAsia

5:44 pm on Feb 11, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Time to start digging around on the server...
What do the following potential bottlenecks look like?
- memory
- disk usage
- deadlocks
- long-running queries
- index fragmentation levels

My first guess is that memory isn't being released properly and my first suspect would be ASP.

giggle

3:46 am on Feb 12, 2013 (gmt 0)

10+ Year Member



Thanks LifeinAsia (I'm in Bangkok - fancy calling around and sorting out the problem yourself! :) )

We have 4gb of memory when I look at the task manager it sits at about 2.7GB usage constantly (don't know if this is normal)

SQLSERVR.exe is top of the list in the Resource Monitor. In the Commit column it's sitting at 1,514,912 - is this figure too high?

Is there a way I can test for long-running queries?

How can I check index fragmentation levels (I run a rebuild indexes process every Wednesday)

Scratching my head!

Thanks

Mick

LifeinAsia

5:28 pm on Feb 12, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



If it's actually memory related, I would expect SQL to be maxing out the memory.

Is there a way I can test for long-running queries?
Depending on which version of SQL you have, fire up Profiler.

How can I check index fragmentation levels
In SSMS, right click on an index, select Properties, and go to Fragmentation. To look at all the indexes on a table, use DBCC SHOWCONTIG (although it's a bit more difficult to decipher):
DBCC SHOWCONTIG (TableName) WITH ALL_INDEXES

giggle

10:23 am on Feb 13, 2013 (gmt 0)

10+ Year Member



Yea, Profiler is great, thanks for pointing that out.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month