Mimic Production in Development

Attempting to debug production performance problems in your development environment can be problematic in many ways, leading to a frustrating troubleshooting experience. One very common situation is the resources on the development environment are substantially less robust than on the production system; for instance prod has 128 GB of RAM, while dev only has 16 GB, prod has 16 cores, while dev only has 4 cores. Unintuitively, this disparity can result in queries running faster in development than in production.

SQL Server has a little-known (and undocumented and unsupported) troubleshooting-related DBCC command that can be used to mimic production resource levels in your development environment. As with all undocumented features, do not try this in production.

DBCC OPTIMIZER_WHATIF

Memory and core count affect plan choice. This may seem obvious, however 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 force your development SQL Server to think it has the same core-count and memory in place, for the purposes of plan-creation, with DBCC OPTIMIZER_WHATIF.

The syntax is:

Properties you can specify include:

Status
CPUs
MemoryMBs

DBCC OPTIMIZER_WHATIF ('Status'); will display the current settings in effect. On SQL Server 2016, I see the following output:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  0             0 CPUs
         2                  0             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

So, if your production server has 16 cores, but your dev server only has 4 cores, you can trick your dev server query optimizer to act like it has 16 cores by running this:

Memory can be similarly “reconfigured”, with:

Now, when I run the Status command, I see:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                 16             0 CPUs
         2              20480             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

As an aside, you won’t be able to see the output above unless you enable the following trace flags first:

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.

Just a reminder; reset these optimizer_whatif values after you’re done testing, and turn off the trace flags.