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:

Search This Blog

About Me

Dinesh Priyankara (MSc IT) is an MVP – Data Platform (Microsoft Most Valuable Professional) in Sri Lanka with 16 years’ experience in various aspects of database technologies including business intelligence. He has done many local and foreign business intelligence implementations and has worked as a subject matter expert on various database and business intelligence related projects. He is the Founder/Principal Architect of dinesQL (Pvt) Ltd and he consults, teaches and runs training programs on data related solutions and subjects.