Microsoft SQL Server Best Practices - Part 1
In IT a Best Practice is “defined by Gartner as a group of tasks that optimizes the efficiency (cost and risk) or effectiveness (service level) of the business discipline or process to which it contributes.” -1 In layman's terms it is the generally accepted, best way to do things. It is important to make this clarification because a Best Practice is by no means the only way to do things. In IT there are always multiple ways to do things and for this reason it is necessary to have “standards” defined for how things are done in an environment so that all the disparate and/or duplicate systems can be run the same way. Best Practices go beyond a single environment and are industry wide standards for doing things. They can be defined by the vendor of a product or by the community of engineers that support the product.
Thus, with that caveat, I am presenting this article on Microsoft SQL Server Best Practices based on what Microsoft says, what my peers say and of course, what I say based on my years of experience supporting MS SQL Server.
Part 1 – CPU Best Practices
CPU Cores vs CPU Clock Speed
When running SQL Server, the most basic tasks are run by the physical processors. When sizing your server for supporting a SQL Server workload, whether that server is physical or IaaS, it is a best practice to utilize processors with fewer cores and faster clock speed versus a higher core count. A faster clock speed will complete tasks faster than a slower clock speed and will be cheaper once SQL Server per core licensing costs are considered. There is also a diminishing return on the number of worker threads available to SQL Server as more cores are added. -2
Hyper-threading
On physical servers, Hyper-threading is a feature that “for each processor core that is physically present, the operating system addresses two virtual (logical) cores and shares the workload between them when possible.” -3 It is a best practice to disable Hyper-threading on servers running SQL Server workloads. The primary reasoning behind this is that SQL Server per core licensing considers a hyper-threaded logical core the same as a non-hyper-threaded physical core and enabling this feature doubles the licensing cost of SQL Server. The feature is not a true doubling of CPU performance but an enhancement that only boosts CPU performance by 10 – 20%. -4
NUMA
Non-uniform memory access (NUMA) is a multi-processor server architecture where memory is assigned to a physical processor and “that processor can access its own local memory faster than non-local memory.” -5 All modern multi-processor servers utilize NUMA and this is important to know about when configuring the SQL Server parallelism.
Soft-NUMA
Software-based NUMA is a feature of SQL Server to automatically create logical NUMA nodes that contain 8 cores when SQL detects that the physical processors have more than 8 cores each. This is a newer feature of SQL starting in SQL 2014 SP2 and SQL 2016 and had to be manually configured prior to those versions. This is also important to know about when configuring the SQL Server parallelism. -6
MaxDOP
The maximum degree of parallelism (MaxDOP) “sets the number of processors employed to run a single statement, for each parallel plan execution.”7 This means that on a server with multiple processors, SQL can split a query up and run it across multiple processors simultaneously (in parallel) to complete the work faster. There are a bunch of rules to follow when setting MaxDOP based on the version of SQL, the NUMA architecture and the number of cores which you can read all about in the Microsoft Documentation.7 My personal best practice is to go to the SQL Server properties, navigate to the Processors page and review the number of CPUs configured per NUMA node in SQL and then I set MaxDOP to half this number. In the example below I would utilize a MaxDOP of 4.
领英推荐
The reasoning behind this is so that each parallel query does not consume an entire NUMA node and that multiple parallel queries can be run simultaneously without interfering with each other. In the screenshot example above the guidance from Microsoft is to “keep MAXDOP at or below # of logical processors.”7 Based on that guidance, what if I set MaxDOP to 5? That is technically at or below # of logical processors but if multiple queries are started in parallel then each one will want to utilize 5 cores for a total of 10 cores when there are only 8 cores thus the queries will be competing for CPU time.
SQL 2019 introduces the ability to configure MaxDOP during installation based on detected CPU and NUMA configuration. My best practice for this is to use half the number of CPUs recommended during installation and come back after and use the method discussed above to confirm.
Another reason for this recommended best practice is because you have to pay attention to the NUMA configuration. Remember back when I explained “that a processor can access its own local memory faster than non-local memory?”5 If the server had 6 physical cores per CPU socket and 2 CPU sockets and we configured MaxDOP to be 8 then queries would be spread out in parallel across multiple CPU sockets and thus multiple NUMA nodes. This would most likely result in a slower query than if we used a MaxDOP of 3 or 6 because of the slower access to data in buffer pages when crossing NUMA nodes. The guidance on this example is to “keep MaxDOP at or below # of logical processors per NUMA node.” -7
Cost Threshold for Parallelism
“The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time.” -8
There is a lot to unpack in that documentation explanation. Basically every query that SQL runs, based on its Execution Plan, has a “cost” assigned to it. The Cost Threshold for Parallelism setting tells SQL run a query in parallel when that “cost” is higher than X value. The default is 5. The industry best practice is to set this to 50. Why? Because they said so.
The problem in the explanation from Microsoft documentation is that the “specific hardware configuration” referenced was from the late 1990s when SQL Server version 7 was developed and there is no longer a reference that explains the “specific hardware configuration.” Because of this, using a cost threshold value of 50 is a good starting point. Queries with a cost of 51 will be run in parallel and queries with a cost of 50 or below will be run serially. But why not just run all queries in parallel? Well, queries with a low cost value are considered “trivial” queries and those queries run and complete faster when run against a single processor than when the query is divided up to multiple processors, run and then the results recombined into a single result set. The key is to find the “cost” sweet spot for your specific workload where queries run faster in serial or in parallel. Raise your hand if you know how to do this. No? Set it to 50 and walk away.
References: