homepage Welcome to WebmasterWorld Guest from 54.196.201.253
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
SQL time outs
queries keep timing out
giggle




msg:4544291
 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




msg:4544621
 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




msg:4544802
 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




msg:4544982
 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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved