DBCC OPTIMIZER_WHATIF: Spoofing production hardware

Performance tuning in a development environment can be difficult. Processor core count and memory are important factors in execution plan generation. The undocumented command DBCC OPTIMIZER_WHATIF will alter the optimizer’s perception of its server resources.

Syntax

You can use the DBCC HELP command to find the syntax for the DBCC OPTIMIZER_WHATIF command.

DBCC TRACEON (2588) WITH NO_INFOMSGS -- TF to enable help to undocumented commands
DBCC HELP ('OPTIMIZER_WHATIF') WITH NO_INFOMSGS

Usage

DBCC OPTIMIZER_WHATIF can be used to pull down your resources or augment them. Often the differences in the execution plans have to do with parallelism and memory grants. This is an example of an execution plan running on an under powered development machine.

With more cores and more memory the execution plan goes parallel and a table spool operator comes into play to optimize for rewinds.

Wrap-up

DBCC OPTIMIZER_WHATIF is great for performing execution plan tuning in an environment where the resources do not match production. Just today I was tuning an ETL process which took 20 hours in the development environment and then generated a different execution plan which was projected to take 5 days in the test environment. The test environment had three times the cores and ten times the memory. DBCC OPTIMIZER_WHATIF would have helped expose the execution plan problem before promoting it up the environments.