This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Yesterday, I was solving a performance issue of a client, and I found that their in-house DBAs scheduled to restart a SQL Server at every Sunday.
I simply stopped my all tuning work and immediately set a meeting with DBA team.
To restart a SQL Server, again and again, is a very bad practice and it leads to the biggest performance issue.
You must be aware of the following points, before restarting a SQL Server.
A Plan Cache of SQL Server stores the precompiled execution plan for frequently executed of queries.
It improves the query performance by reducing a cost of creating and compiling execution plan again and again.Apparently, most of our queries are frequently executing.
Once you restart the SQL Server, it clears the Plan Cache area, and all your queries require to create a new query plan which degrades the performance.
A Buffer Pool of SQL Server, very capable of storing the result of your frequently executed queries.
It reduces the disk/io and improves the query performance by sending a result directly from the memory area.
If a result is not available in the buffer pool, it fetches from the disk.When you restart the SQL Server, it clears the Buffer Pool area, and it takes more time to fill it again.
The Dynamic Management Views and Functions of SQL Server, return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
The DMVs and DMFs are the first tool for SQL DBA to monitor the health of SQL Server. The DMVs and DMFs store its data into the process space of sqlservr.exe.
Once you restart your SQL Server, it clears all DMVs and DMFs statistics by destroying the process of sqlservr.exe.
The rollback of uncommitted transactions of SQL Server, this is a part of SQL Server crash recovery which rollback all uncommitted transactions.
Now imagine that you are going to restart your SQL Server and few of queries are still running.
In this situation, SQL Server rollbacks all uncommitted transactions which degrade the performance and restart process might take more time to complete the action.
