Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems.
The best and safest way to tune your queries is to run them in a development environment. However, in most cases, the result you get from the development environment is completely different from the result that you get from production, giving you a misleading or even wrong indications about the query performance. This can be true even though you copy the same database with all indexes and statistics from the production environment to development.
These differences in the results mainly occur due to the hardware gap between the development environment and the production one, especially the Processor core counts and the server memory. These variables are involved heavily in the SQL Server Query Optimizer decision while creating the query execution plan, and therefore the parallel operators will not work in the same way.
Planning to have identical development and production SQL Servers isn’t feasible, in most cases,, due to the cost that will be required in a server using for testing only. Then, in light of this, how could we over come this challenge and at least simulate production SQL queries in the development environment?
The undocumented command DBCC OPTIMIZER_WHATIF is a potential solution
The DBCC OPTIMIZER_WHATIF command, introduced in SQL Server 2005, allows you to simulate a production environment by hypothetically altering the SQL Server Query Optimizer perception of the SQL Server resources. What is required from you is to pass the logical CPU cores, the RAM amount and the Cost Threshold for Parallelism of the production environment to the DBCC OPTIMIZER_WHATIF command, and combine that command with the query to be tuned on the development SQL Server, you will get a near real estimates of the query execution plan on the production SQL Server. As the DBCC OPTIMIZER_WHATIF command changes how the SQL Server Query Optimizer see the server available resources.
To see what information is available about this undocumented command, we will use the DBCC HELP command along with the Trace Flag 2588.
|
1 2 3 4 |
DBCC TRACEON (2588) WITH NO_INFOMSGS DBCC HELP ('OPTIMIZER_WHATIF') WITH NO_INFOMSGS |
The only information that will be retrieved about DBCC OPTIMIZER_WHATIF are the results below:
As you can see in the results, you need to provide it with the property name or number and the value for that property. Keep in your mind when using the property names in the DBCC OPTIMIZER_WHATIF command, that they are case sensitive, even in a case of insensitive installation. No error message will be shown if an invalid name or value is passed in, so you should be careful.
The valid properties that can be overridden using the DBCC OPTIMIZER_WHATIF command are:
- Property Name: CPUs, Property number : 1.
- Property Name: MemoryMBs, Property number : 2.
- Property Name: Bits, Property number : 3.
- Property Name: ParallelCardThreshold, Property number : 4.
- Property Name: ParallelCostThreshold, Property number : 5.
- Property Name: ParallelCardCrossProd, Property number : 6.
In SQL Server 2012 a new four properties were added to that command:
- Property Name: LowCEThresholdFactorBy10, Property number : 7.
- Property Name: HighCEThresholdFactorBy10, Property number : 8.
- Property Name: CEThresholdFactorCrossover, Property number : 9.
- Property Name: DMLChangeThreshold, Property number: 10.
The current status of the properties that can be changed by the DBCC OPTIMIZER_WHATIF command can be viewed by passing the Status statement or number zero to that command as a single parameter. Before that, we should turn on the Trace Flag 3604 in order to see the command result on the screen. The generated result depends mainly on the SQL Server version.
The below command is used to check the status of these properties on my SQL Server 2016 instance:
|
1 2 3 4 |
DBCC TRACEON(3604) WITH NO_INFOMSGS DBCC OPTIMIZER_WHATIF(0) WITH NO_INFOMSGS; |
It can be also written as:
|
1 2 3 4 |
DBCC TRACEON(3604) WITH NO_INFOMSGS DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS; |
Both queries will return the same result, which looks like the below in my case:
You can also reset all the properties values and return it to its default values, if you are afraid that someone perform any change, by passing the ResetAll parameter to the DBCC OPTIMIZER_WHATIF command as follows:
|
1 2 3 |
DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS; |
Let us start our demo.
In this scenario, we will be working on a development SQL Server instance with 4 CPU cores and 8 GB RAM. We have been requested to tune the performance of the below query that is slow on the production SQL Server. The development team sent the query to us with the execution plan and then we executed it on the development SQL Server:
|
1 2 3 4 5 6 7 8 9 10 |
USE SQLShackDemo GO SELECT EMP.EmpName , EMP.EmpAddress , EMPD.DepName FROM Employees EMP CROSS APPLY Employee_Department EMPD WHERE EMP.EmpName like '%Sanya%' ORDER BY EMPD.DepName OPTION (RECOMPILE) |
The generated execution plan for that query differs from the execution plan received from the production server:
It took 54 ms to be executed and 16ms of the CPU time as follows:
With 19,528 byes Memory Granted and 10.7688 Estimated Subtree Cost as in the SELECT operator properties window below:
The query has no problem on the development SQL Server as you can see from the statistics shown previously. In order to check the real query performance, we should simulate it on the production environment. This can be done by changing the SQL Server Query Optimizer view of the development SQL Server resources that is achieved using the DBCC OPTIMIZER_WHATIF command.
The below script allows the SQL Server Query optimizer to generate an execution plan for the submitted query assuming that the SQL instance is working on a server with 80 CPU cores.
Take into consideration that the DBCC OPTIMIZER_WHATIF command should be combined with the query to be tuned in the same session, as this command is working on a session scope. We turned on the Trace Flag 3604 here in order to be able to query the current properties values of the DBCC OPTIMIZER_WHATIF command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE SQLShackDemo GO -- Simulate 80 CPU cores DBCC OPTIMIZER_WHATIF(1,80) -- To be able to see the result DBCC TRACEON(3604) WITH NO_INFOMSGS -- Check the current status DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS; -- The query to be tuned SELECT EMP.EmpName , EMP.EmpAddress , EMPD.DepName FROM Employees EMP CROSS APPLY Employee_Department EMPD WHERE EMP.EmpName like '%Sanya%' ORDER BY EMPD.DepName OPTION (RECOMPILE) |
The Recompile option is added here to make sure that a new plan will be generated for the query with the new resources.
In the query Messages tab, you will see that the CPUs value of the DBCC OPTIMIZER_WHATIF command is changed to be 80 as follows:
… and that it took 130ms on the live server to be executed, not 54ms as previously shown on the development server, and 109ms of the CPU time, not 16ms as in the previous result:
This is the execution plan that we have received
You can compare the Sort operator weight in the new plan after modifying the number of CPUs, which is 37%, with the old result, which is 86%, and how it is enhanced:
Less memory is granted to the query after modifying the number of CPUs, with 6,656 bytes compared with the 19,528 byes Memory Granted with four CPU cores previously. The Estimated Subtree Cost of the SELECT operator has increased now to 11.8752, compared to the old value 10.7688 as below:
DBCC OPTIMIZER_WHATIF command can be used to alter other SQL Server resources hypothetically, as mentioned previously. Assume that we need to simulate the 80 CPU cores, the 256 GB RAM and the 64-bit system of the production server on the development server to test our query again. The query below will alter the SQL Server resources for the SQL Server Query Optimizer with the specified values, showing the results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE SQLShackDemo GO DBCC TRACEON(3604) WITH NO_INFOMSGS DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSG GO DBCC OPTIMIZER_WHATIF(CPU,80) -- DBCC OPTIMIZER_WHATIF(1,80) GO DBCC OPTIMIZER_WHATIF(MemoryMBs, 262144) -- DBCC OPTIMIZER_WHATIF(2,262144) GO DBCC OPTIMIZER_WHATIF(Bits, 64) -- DBCC OPTIMIZER_WHATIF(3,64) GO SELECT EMP.EmpName , EMP.EmpAddress , EMPD.DepName FROM Employees EMP CROSS APPLY Employee_Department EMPD WHERE EMP.EmpName like '%Sanya%' ORDER BY EMPD.DepName OPTION (RECOMPILE) |
The changed memory and system bits values will be like:
After finishing the tuning process, we need to reset all properties back to the default values and show it again:
|
1 2 3 4 5 |
DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS; DBCC TRACEON(3604) WITH NO_INFOMSGS DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS; |
Back to defaults again:
Conclusion
DBCC OPTIMIZER_WHATIF gives us a near real estimation about how the query will perform in the production environment by spoofing the production SQL Server resources on the development SQL Server, which helps us in tuning our queries more efficiently.
In this article, you saw practically how it works and how we can get benefits from it in our performance tuning tasks.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021