SQL Server gets slower until I restart

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