Thursday, December 29, 2016

Setting a higher Priority Base value to SQL Server - are we optimizing the process or taking a risk?

There are set of server level configurations related to optimizing SQL Server but not all guarantee that the result will be as expected and friendly. The Boost SQL Server Priority is one of such settings and though we have been given it for setting it to a higher value, prioritizing SQL Server processes in the Operating System, it is not recommended to change it.

I was asked about this last week, whether we can optimize SQL Server processes by setting the property to true. Theoretically, it should optimize but it can result unexpected result as well.

In simple term, this setting allows us to set the Priority Level (or Priority Base) to 13. The default is 7, not 0 or a higher value. I am sure that the value 7 has been set for this for many reasons, hence it should not be changed. Assume that we changed this and now it is 13. This makes Operating System to give SQL Server processes to high priority than other requested processes. Experts say that this might bring you to a situation where you see something like no response from keyboard or no response from mouse

Therefore, remember, this is something you need to think twice if you are going to change it.

No comments: