In a perfect world, DBA installs SQL Server and clicks “Make my SQL Server run faster“, and everyone’s happy. But until Microsoft builds that switch, there are different configurations both at the server level and at the database level that might help turbocharge your SQL Server. Let us look into some of those settings.
MAXDOP and Cost Threshold for Parallelism
When a query is run against SQL Server on a NUMA computer, SQL Server finds the optimum number of processors to run a single statement for each parallel plan. You can help SQL Server choose the maximum degree of parallelism using the MAXDOP option which can be set at the server level, database level, or even at the query level. Just remember setting MAXDOP with a query hint overrides the database value and the database MAXDOP value will override the server-level configuration value. What is really cool? No server restart is required.
The default value for MAXDOP is 0 which means if a query gets a parallel execution plan, it will use ALL processors. Queries get drunk and CXPACKET waits will test your patience. Also, do not set the value to 1 either as that will seriously impact the performance because of course, you just suppressed parallel plan generation.
For NUMA machines, best practice recommends setting MAXDOP to the number of cores per NUMA node or 8, whichever is less. (To find how many NUMA nodes you have on a computer, simply use Task Manager and go to the performance tab)
The Cost Threshold for Parallelism is set at the server level. We do not want every query that has a parallel plan to go parallel. We can prevent smaller queries from going parallel and free up processors for expensive larger queries. The default value is 5 and I recommend testing and going higher between 20-50 to suit your environment.
Power Plan
In a Windows machine, there is a configuration that balances the power and delivers better performance. This is neither a SQL Server nor a database setting but I believe configuring Power Plan can become a turbocharger for your SQL Server.
From the control panel through power options change the ‘Balanced‘ option to ‘High Performance‘.
Trace Flags
Trace flags help while troubleshooting certain issues and Microsoft has done a good job documenting all of them (which was not the case back in those days of SQL Server 2005). However, leaving a trace flag ON/OFF can impact performance as it will alter behavior.
Trace flags can be turned on for different scopes.
Global – These are configured on the server. Enable them at startup to make sure they stay active after a restart. The below example enables the trace flag 3226 globally.DBCC TRACEON (3226, -1)
Session – Flags are active and limited to a connection. The below example turns on the trace for a connectionDBCC TRACEON (3226)
Query – Remains valid for the context of a specific query. Use QUERYTRACEON hintOPTION (QUERYTRACEON 4199)
Use DBCC TRACESTATUS which flags are turned on in your server.
Evaluate all the active flags and understand the need for them. You might find hidden flags that should not have been turned ON. Find the person who turned it on and take him for a long hard walk. Starting from SQL Server 2016, there are several traces that have no effect as they are controlled by the DB engine or can be set with ALTER DATABASE script. Query Optimizer Fixes, Parameter sniffing, Legacy Cardinality Estimation, and more can be set at the database level.
