Sql server her yavaşladığında restart etmek gerek diye düşünenler için ezber bozan çok güzel bir yazı. Eğer gerçekten önemli bir sebebi yoksa sistemi restart etmek sql server a yapılacak en büyük kötülüklerden biri.

Orijinal link
https://www.linkedin.com/pulse/frequent-…y-bruk-1e/

Frequent SQL Server restart, is it a bad practice?
In theory, Microsoft SQL Server never requires a reboot, just like a Windows server, and most of the configuration can take effect immediately.

However, in a few cases, we are obliged to do so:

[*]Windows maintenances;

[*]Enabling/disabling, or changing the configuration of network protocols (using SQL Server Configuration Manager);

[*]Applying of Hotfixes/Patches/Service packs;

[*]tempdb issues which are fixed by the recreation of the file;

[*]Applying startup parameters such as trace flags;

[*]Server authentication mode changes.

Except for the above reasons, a good maintained and healthy SQL Server could happily stay online forever.

In my opinion, restart of the server is probably one of the most damaging things for performance! When we restart SQL server, we get all the memory back to the server OS, completely clean plan cache and wipe out all tempdb. That mean:

• To take back the memory from Windows OS will take a while. During that period, SQL server will read a lot of data from the disc and upload data back into the memory.
• 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 repeatedly compiling of execution plans.
• All cached query plans are lost and SQL server will need to compile it again. Depending on the workload of the server, it will take a while for SQL server to generate and load back to the cache all execution plans. “New Plan Cache” probably will not contain rarely executed procedures, because all plans will be generated “on the fly” since service is up. Additionally, we risking “to get” bad execution plan.
• All collected information used to output the Dynamic Management Views (DMV’s) and Functions, about the state of SQL Server, will be lost and cannot be used to monitor the health of a server instance, diagnose problems, and tune performance.
• Part of SQL Server crash recovery it’s a rollback of uncommitted transactions. SQL Server rollbacks all uncommitted transactions, which degrade the performance and restart process, might take more time to complete the action.
• In case of a busy server with the high workload, SQL Server restart will perform rollbacks for many queries are start their running before the restart and this process may take a lot of time.
• Let’s be honest, not every Windows restart finished as we want – happy and fixed all our issues.

[*]

Seems, we have many things to think about, before we will push on the reboot button.
I am not saying that reboot of SQL Server is a bad practice, but often reboot or reboot without good reason, it’s a bad practice.

For the summary, I will use Paul Randal’s quote:
“if you’re regularly rebooting Windows/SQL Server, make sure it’s for a good reason and not just because someone thinks it’s a good thing to do or it’s the chosen way to fix a problem that should be fixed in some other way.”