Performance Tuning with DBCC OPTIMIZER_WHATIF in SQL Server

Introduction

DBCC OPTIMIZER_WHATIF is a powerful diagnostic command in SQL Server that allows database administrators and developers to simulate various hardware configurations. By doing so, it shows how the query optimizer would behave under different CPU and memory allocations—without requiring you to physically change server hardware. This makes it an invaluable tool for performance tuning, troubleshooting, and strategic planning in SQL Server environments.

How DBCC OPTIMIZER_WHATIF Works

1. Purpose
   The command adjusts the query optimization process by mimicking different hardware configurations. This is particularly useful for reproducing query plans from diverse server environments or evaluating how potential hardware upgrades (like additional CPUs or memory) might impact query performance.
2. Usage
   The syntax for using DBCC OPTIMIZER_WHATIF is:

   DBCC OPTIMIZER_WHATIF({property_number | property_name}, {value});

   • property_number or property_name: Specifies the hardware or configuration parameter to simulate.
   • value: The degree or amount of simulation (e.g., how many CPUs or how many MB of memory).

   For example, to simulate an environment with 8 CPUs, you could run:

   DBCC OPTIMIZER_WHATIF(1, 8);

Key Features and Usage Tips

• Simulating CPU and Memory
  • CPUs: DBCC OPTIMIZER_WHATIF(1, 8); tells the optimizer that your server has 8 logical CPUs.
  • Memory: DBCC OPTIMIZER_WHATIF(2, 2048); indicates the server has 2GB of RAM.
• Properties to Adjust
  Beyond CPU and memory, you can adjust several other properties that influence query plan generation. Examples include thresholds for parallelism or costs that can alter how the optimizer chooses execution plans.
• Resetting Simulations
  To revert to actual hardware settings, set the property’s value to zero. For instance,

  DBCC OPTIMIZER_WHATIF(1, 0);  -- Reverts CPUs to the real configuration

• Checking Current Settings
  • Run DBCC OPTIMIZER_WHATIF(0, 0); to see which simulations are currently in place.
  • Enable trace output with DBCC TRACEON(3604); before running the above command to view the results in your session.
• Clearing Settings
  To remove all custom settings, reset properties back to their defaults (often 0). For instance:

  DBCC OPTIMIZER_WHATIF(1, 0);  -- Clears custom CPU simulation
  DBCC OPTIMIZER_WHATIF(2, 0);  -- Clears custom Memory simulation

Explanation of DBCC OPTIMIZER_WHATIF Properties

Below is a summary of the primary properties you can set through DBCC OPTIMIZER_WHATIF. Each property_number corresponds to a property_name.

property_number	property_name	Description
1	CPUs	Simulates the number of logical CPUs.
2	MemoryMBs	Simulates the amount of memory in MB available for query operations.
3	Bits	Specifies the architecture mode (32-bit vs. 64-bit).
4	ParallelCardThreshold	Controls the row count threshold for considering parallel plans.
5	ParallelCostThreshold	Sets the cost threshold for parallel plans. Affects when the optimizer decides to use a parallel execution plan.
6	ParallelCardCrossProd	Adjusts how cross-product row counts are calculated or considered for parallel plan generation.
7	LowCEThresholdFactorBy10	Fine-tunes the threshold factor for low cardinality estimates, multiplied by 10.
8	HighCEThresholdFactorBy10	Fine-tunes the threshold factor for high cardinality estimates, multiplied by 10.
9	CEThresholdFactorCrossover	Sets the crossover point for switching between low and high cardinality thresholds.
10	DMLChangeThreshold	Determines how changes in data (such as inserts/updates/deletes) influence cardinality or cost estimations.

Use these properties to experiment with how the SQL Server Query Optimizer might behave under different thresholds. For instance, increasing ParallelCostThreshold (property #5) means the optimizer will only generate parallel plans for queries with higher estimated costs.

Factors that Influence Query Plans

Even with DBCC OPTIMIZER_WHATIF, there are other variables that can affect the reproducibility of a query plan:

• Table Metadata: The presence of indexes, column data types, and nullable columns affect the optimizer’s choices.
• Session SET Options: Certain settings (e.g., ANSI_NULLS, ARITHABORT) can affect cardinality estimates and join logic.
• Constraints: Foreign keys and check constraints can prune unnecessary joins or filters if enforced.
• Statistics: Histograms, density vectors, and string summaries inform the optimizer’s estimated row counts.
• Row and Page Counts: The physical size of tables affects whether the optimizer chooses hash, merge, or nested loop joins.
• Available Memory: Sorting and hashing operations can use more efficient in-memory processing if there’s enough memory.
• CPU Availability: While a plan may allow for parallel execution, SQL Server can scale back based on runtime CPU pressure.
• System Architecture: On 32-bit systems, address space limitations can change memory-intensive operations like sorting and hash joins.

Reproducing a Production Environment for Testing

Because so many factors are at play, replicating a production environment on a test machine can be challenging. To approximate a production setup:

1. Clone Your Database
   Generate a script of the database metadata and statistics, or use DBCC CLONEDATABASE to copy metadata and stats.
2. Match Session Options
   Set ANSI_NULLS, ARITHABORT, and other session options to match your production environment.
3. Simulate Production Hardware
   Use DBCC OPTIMIZER_WHATIF to simulate the production server’s CPU and memory configuration.

With a cloned database and these DBCC settings, you can compile queries in the test environment to retrieve the estimated or actual execution plans. The plan generated will match what would have been produced on the production system, helping you confidently tune your queries.

Why DBCC OPTIMIZER_WHATIF is Beneficial

DBCC OPTIMIZER_WHATIF is especially helpful when you need to:

• Reproduce query plans from another server without matching hardware.
• Evaluate potential hardware upgrades, such as adding more CPUs or memory, to see how it might improve query performance.
• Diagnose performance bottlenecks by understanding how hardware constraints can influence execution plans.

By simulating different hardware scenarios, you can perform more accurate performance analyses, validate execution plans, and make informed decisions about where to invest in hardware upgrades or index strategies.

Conclusion

DBCC OPTIMIZER_WHATIF empowers you to explore “what-if” scenarios in your SQL Server environment without incurring the cost or downtime of physical changes. Whether you’re troubleshooting a production issue, planning for hardware upgrades, or simply refining execution plans in a lab environment, this command unlocks crucial insights that can help you optimize query performance with confidence.