DBCC OPTIMIZER_WHATIF

 

As a DBA, one of the common issues you may face is slow running queries or stored procedures. Sometimes, you may find that a query runs fine in production but is slow in development, even though production has more hardware resources compared to the Dev/QA servers. So how can you mimic the production environment in development without enhancing the hardware?

 

This is where the undocumented DBCC OPTIMIZER_WHATIF command comes in. Using this command, you can force your development SQL Server to think it has the same core-count and memory as production, for the purposes of plan-creation. It is important to note that, like all undocumented features, you should not try this in production.

 

Memory and core count affect plan choice, and it may be that lower memory or lower core-count can make a query run much quicker if the query optimizer happens to choose a “better” plan when resources are low. You can specify properties like Status, CPUs, and MemoryMBs using the following syntax:

 

DBCC OPTIMIZER_WHATIF ({property/cost_number | property_name} [, {integer_value | string_value}]);

 

To display the current settings in effect, you can run:

 

DBCC OPTIMIZER_WHATIF ('Status');

 

On SQL Server 2017, this will output the current settings.

 

To set the number of cores to 16, for example, you can run:

 

dbcc optimizer_whatif ('CPUs', 16);

To view the setting you have set for the optimizer, you need to enable the trace by running:

 

dbcc traceon (2588);

dbcc traceon (3604);

 

Trace flag 2588 forces DBCC HELP to display syntax for undocumented DBCC statements, such as optimizer_whatif. 

Trace flag 3604 sends trace output to the client instead of writing it to the SQL Server Error Log. This flag is only effective when setting trace flags with DBCC TRACEON and DBCC TRACEOFF, i.e., you cannot set it from the command-line properties for the SQL Server service.

 

Setting the CPU and memory with optimizer_whatif to the same values used in your production environment and re-running the query you are debugging may be the only way to get the same plan as seen in production. 

You may need to flush the plan cache with DBCC FREEPROCCACHE prior to re-running your queries to ensure plans are regenerated. Note: reset these optimizer_whatif values after you’re done testing and turn off the trace flags.

 

Using DBCC OPTIMIZER_WHATIF, you can simulate production conditions in your development environment without enhancing the hardware. However, as this is an undocumented feature, use it with caution and only in non-production environments.