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

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

You can pass in a key/value pair but we need to dig a little deeper to find the property list.

DBCC TRACEON(3604) WITH NO_INFOMSGS -- TF to send output results to console
DBCC OPTIMIZER_WHATIF(0) WITH NO_INFOMSGS; -- Current and valid values

optimizer_whatif-properties

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.

--Spoof 1 core
DBCC OPTIMIZER_WHATIF(1, 1);
-- Set ammount of memory in MB
DBCC OPTIMIZER_WHATIF(2, 512);
-- Set to 64 bit system
DBCC OPTIMIZER_WHATIF(3, 64);

SELECT *
FROM [Person].[Person] p1
CROSS APPLY [Person].[Person] p2
ORDER BY p1.FirstName, p2.FirstName
OPTION (RECOMPILE) --Demostration purposes only.

serial-plan-1

A serial plan was selected because you told the optimizer that there was only one core. The affect of the memory grant becomes apparent when you spoof the resources of the production machine.

--Spoof 16 cores
DBCC OPTIMIZER_WHATIF(1, 16);
-- Set ammount of memory in MB, in this case 512GB
DBCC OPTIMIZER_WHATIF(2, 524288);
-- Set to 64 bit system
DBCC OPTIMIZER_WHATIF(3, 64);

SELECT *
FROM [Person].[Person] p1
CROSS APPLY [Person].[Person] p2
ORDER BY p1.FirstName, p2.FirstName
OPTION (RECOMPILE)

parallel-plan-1

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.

This article has 1 comment

  1. […] Derik Hammer shows how to use DBCC OPTIMIZER_WHATIF to get an idea of how your query would run with …: […]

Leave a Reply