General performance of the server and sql services restart

Question

Hi All,

This is little off topic and going against proper performance troubleshooting. My question is, assuming I have a sub-prod environment be at DEV, QA, or any UAT Server, where we don't have same hardware in terms of Memory and CPU's.

Now lets say, the prod has 16 CPUs and 512 GB RAM dedicated for prod database but the sub-prod has very less configuration with 8 CPU's and 256 GB RAM. Also, on sub-prod environments are mostly shared server and atleast they host 40-50 databases on the same SQL Instances. And when multiple application users are performing UAT testing on their respective databases, we see some slowness. Queries competing for resources. Even if the sub-prod env has dedicated db server , sometimes SQL Server restart will help in resolving the performance issue. Why?

And another question is, if we have to restart sql instance (lets say, standalone sub-prod, users are doing UAT testing and experiencing slowness in the workload,no blocking but its a shared instance and the team is okay for sql restart) then in that case, which of the following restart methods is better.

Assuming that they are okay to run maintenance as well.

Option1::

Rebuild the indexes 

update the statistics on all tables with FULLSCAN

restart SQL Server 

Option2:

restart SQL Server 

Rebuild indexes 

update the statistics on all tables with FULLSCAN

Again, I am saying this is not the ideal method we should be doing instead of checking whats currently running on the server, check for blocking, check for waits etc..... but there is limited time and users are okay for sql restart then which of the above options is better than the other.

Thanks,

Sam

Answer 1

Hi Samantha,

you are correct that this is not the optimal solution, you should troubleshoot why there is that performance impact and what is causing it.

Just regarding your question which option might be better... just let me ask you a question... and maybe you find your answer yourself ;-)

If you have performance issues and everything is performaning slow and you have to restart the server in order to reset everything back to high performance... why would you start a cost intensive activity like index maintenance on this slow server?

So option 2 will the better option, as (technically) it doesn't matter if you do index/stats maintenance before or after a restart.

Hope that helps.

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

The reason that restarting SQL Server may help to alleviate performance issues, is that since the plan cached, parameter-sensitive plans will be sniffed for new parameter values, and with some luck you now get better plans.

Since this is a shared server there can be other reasons too. Maybe some application does not handle its connection pool properly, and is leaving lots of open connections behind. Or there are applications that submit tons of unparameterised queries and these plans takes up the major bulk of the plan cache.

The latter situation can be alleviated by these commands:

EXEC sp_configure, 'optimize for ad hoc workloads', 1
RECONFIGURE

For a more accurate answer, you would need to analyse that server in more detail. Restarting the instance works, but it is a bit of heavy artillery.

Answer 3

Hi @Samantha r,

Restarting SQL Server may help with certain issues such as memory leaks or running out of resources after a long run. Restarting SQL Server can clear memory and cache and free up system resources, which may improve SQL Server performance problems.

However, restarting SQL Server will not help if the performance problem is caused by poor performance of query statements, index failures, lack of disk space, or other non-system-related reasons. In this case, you need to analyze and optimize the query statements, redesign the indexes, or increase disk space, etc.

Please read this blog:

https://www.linkedin.com/pulse/frequent-sql-server-restart-bad-practice-vitaly-bruk-1e#:~:text=When%20we%20restart%20SQL%20server,data%20back%20into%20the%20memory.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".