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. 1234  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: 1234  DBCC TRACEON(3604) WITH NO_INFOMSGSDBCC OPTIMIZER_WHATIF(0) WITH NO_INFOMSGS;  It can be also written as: 1234  DBCC TRACEON(3604) WITH NO_INFOMSGSDBCC 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: 123  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: 12345678910  USE SQLShackDemo GOSELECT  EMP.EmpName , EMP.EmpAddress , EMPD.DepName  FROM Employees EMPCROSS APPLY Employee_Department EMPDWHERE EMP.EmpName like '%Sanya%'ORDER BY  EMPD.DepNameOPTION (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: 1234567891011121314151617  USE SQLShackDemo GO-- Simulate 80 CPU coresDBCC OPTIMIZER_WHATIF(1,80)   -- To be able to see the resultDBCC TRACEON(3604) WITH NO_INFOMSGS -- Check the current status DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;   -- The query to be tunedSELECT  EMP.EmpName , EMP.EmpAddress , EMPD.DepName  FROM Employees EMPCROSS APPLY Employee_Department EMPDWHERE EMP.EmpName like '%Sanya%'ORDER BY  EMPD.DepNameOPTION (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: 12345678910111213141516171819202122  USE SQLShackDemo GODBCC TRACEON(3604) WITH NO_INFOMSGS DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGGODBCC OPTIMIZER_WHATIF(CPU,80) -- DBCC OPTIMIZER_WHATIF(1,80)GODBCC OPTIMIZER_WHATIF(MemoryMBs, 262144)-- DBCC OPTIMIZER_WHATIF(2,262144)GODBCC OPTIMIZER_WHATIF(Bits, 64)-- DBCC OPTIMIZER_WHATIF(3,64)GOSELECT  EMP.EmpName , EMP.EmpAddress , EMPD.DepName  FROM Employees EMPCROSS APPLY Employee_Department EMPDWHERE EMP.EmpName like '%Sanya%'ORDER BY  EMPD.DepNameOPTION (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: 12345  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.

Author Recent Posts Ahmad YaseenAhmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

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 Latest posts by Ahmad Yaseen (see all) 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

Related posts

SQL query performance tuning tips for non-production environments Performance tuning – Nested and Merge SQL Loop with Execution Plans Using the SQL Execution Plan for Query Performance Tuning Performance tuning for Azure SQL Databases SQL Server performance tuning using Windows Performance Monitor 2,024 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ▼Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types  © 2022 Quest Software Inc. ALL RIGHTS RESERVED.

    GDPR     Terms of Use     Privacy