SQL time outs

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)

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)

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)

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)

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)

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

SQL time outs

queries keep timing out

giggle

LifeinAsia

giggle

LifeinAsia

giggle

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week