Optimizer, what if I had more CPUs?

In the March issue of SQL Server Pro Itzik Ben-Gan wrote about an undocumented DBCC command that was recently discovered in the wild by Eladio Rincon (http://www.sqlmag.com/article/sql-server/options-affecting-parallelism-141505).
Itzik closed the article by expressing his hope to get this statement documented as it can be very helpfull to database professionals trying to performance tune
a query on a none production system, that does not completely match the production environment.

I agree with this opinion, and so I decided to spend some time investigating this functionality further.

Optimizer_WhatIf?

The DBCC command is the OPTIMIZER_WHATIF cammand. DBCC HELP gives you the following information about it:

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

This is – according to Google – the only publicly available information about this command. Eladio discovered it in use at a customer site.
The customer was told by Microsoft that with the first parameter equal to one, the second would influence parallelism decisions of the optimizer.

After reading Itziks article it seemed clear that the "1" meant "number of schedulers" or "number of CPUs".
The above documentation snippet says that you can either specify a number or a name for the first parameter, so knowing the possible non-numeric values
for it would be a start in understanding what else this command does.

Finding Strings

Every windows programm has to somehow contain the strings it is using. SQL Server is no exception. A well designed program will have related strings close by each other.

You can use a program like Mark Russinovich's Process Explorer
to get a list of all strings in the memory space of a running process.

As all statements (including DBCC commands) are strings, you can expect to find the possible values for the first parameter next to each other. With that thought in mind I went ahead and
searched for strings containing "CPU" or "scheduler". I found this list of possible property_name values for SQL Server 2008 and SQL Server 2008 R2:

Status
ResetAll
CPUs
MemoryMBs
Bits
ParallelCardThreshold
ParallelCostThreshold
ParallelCardCrossProd

In SQL Server 2012 these properties were added.

LowCEThresholdFactorBy10
HighCEThresholdFactorBy10
CEThresholdFactorCrossover
DMLChangeThreshold

DBCC OPTIMIZER_WHATIF was available in SQL Server 2005 as well, but "ResetAll" and "Status" are not working, so I did not investigate further.

Warning

All property names are case sensitive, even in a case insensitive installation! You also will not get an error message if an invalid name or value is passed in, so be careful.

Many More CPUs

The third one, "CPUs" causes the functionality described by Itzik: If you run

DBCC OPTIMIZER_WHATIF(CPUs, 16) WITH NO_INFOMSGS;

the optimizer will subsequently optimizer all statements submitted in the same session as if the system had 16 CPUs.

Administrative Help

If you run

DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS;

all changes caused by this DBCC command to the current session are undone.

DBCC OPTIMIZER_WHATIF(Status) WITH NO_INFOMSGS;

finally gives you the currently set values and their defaults. It also returns the integer representation of each property:

---------------------------------------------------------

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

(As with all undocumented DBCC commands, you first need to run "DBCC TRACEON (3604) WITH NO_INFOMSGS;" in your session to get any output.)

Conclusion

This DBCC command is a great tool, when you are trying to answer questions like: What if the optimizer had more (or less) CPUs available than there are in my current system.

As with all undocumented functionality however, don't use this in production unless advised to do so by Microsoft support.

This post covered only three of the possible values for the first parameter. The other ones will be addressed in future posts.