SQL Server gets slower until I restart

16 June,2015 by Jack Vamvas

Question: My SQL Server Instance gets slower over a few days. When I restart the SQL Server Instance – the response times improve. The cycle repeats itself ad infinitum.

How can I troubleshoot ?

Answer: This is a very common situation.  Based on the same workload a SQL Server slowly starts grinding . It’s not a satisfactory situation for anyone.

Firstly , the DBA needs to negotiate an agreed time for restart – disrupting server access.

Secondly, a regular SQL Server restart suggests there are some underlying issues. Identifying the issues starts with asking the right question.

What is happening when a SQL Server restarts? There is a ton of tasks completing when a SQL Server restarts. There are two tasks which are immediately relevant

1)    Rebuild of TempDB. Everytime SQL Server restarts the TempDB is rebuilt. The rebuild parameters are based on the default sizes configured. Check the TempDB default sizes and the growth rates.   Applying   inefficient growth rates can lead to crazy fragmentation, leading to performance downgrade.

Read more on  TempDB performance and strategy checklist

2)     SQL Server Memory is released. If MaxMemory is set , SQL Server memory usage will grow up to the Max Memory level. If the level is to high ,  swapping may occur on the server.

Check to see if free memory is at least over 500 MB. In my experience, when a server begins to drop below 500 MB , it is an indicator that it’s about to  start using the page file. Basically – operations that normally were running to memory , begin to read \ write to disk.

Read More on other SQL Server Performance Killers

3)    There are plenty of things to consider,

Hhave you patched the server ?

Is there an inefficient query plan ? 

What other tasks are running?

 

Read More on SQL Server performance

SQL Server Transaction Log files - performance myth

SQL Server Performance ,memory pressure and memory usage

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server gets slower until I restart


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer