DBCC OPTIMIZER_WHATIF: Query Tuning

Problem

You need to tune a query on a non-production server. To do this, you have exported database statistics and loaded them onto a development environment. But even doing this you can’t get the same results as in production, mostly because of differences on hardware. In this tip we look at using the DBCC OPTIMIZER_WHATIF command to help simulate query plans on servers that have different hardware configurations.

Solution

Your day as a SQL Server developer can become harsh when the boss approaches you telling that your query is not performing well on the production environment. You look at the query plan and it seems fine and not knowing what else to do you ask the DBA for support. When the DBA sends you the query plan of the production environment you realize that it is very different than the one you got. Of course, the development server doesn’t have the same amount of memory or CPUs as the production server and therefore parallel operators won’t work the same way.

To bypass this limitation we have the undocumented DBCC OPTIMIZER_WHATIF command. This command allows you to hypothetically set a value for the number of CPU’s, amount of RAM and system architecture amongst other things.As usual with undocumented DBCC commands you need to set trace flag 2588 on in order to view the skimpy command help.