Untold Secrets of SQL Server: What if?

Mouaz Alem February 5, 2014

It's common practice for IT vendors to keep some of the commands and statements for their own use, and to avoid publishing or documenting these tools for two reasons:

1. Some of these commands are unsafe for production environments and should be used carefully on non-production systems only - otherwise it may cause some adverse effects
2. To maintain an edge over third parties, so the vendor can always be superior in analysis, and quality of support.

Microsoft is not an exception. Today I'll share one of the cool secrets that Microsoft did not publish, document or even support: It's called what-if statement. In several cases, individuals would confront a situation where they would want to test the execution of a query or an application on a different hardware - upsize or downsize. One solution is to bring an actual server, perform the setup of OS and SQL Server, and restore the database to the new hardware. Another alternative is made possible by using the what-if statement: Simply run the query or application session after issuing few statements to the optimizer to simulate the needed hardware. Of course, if you are planning to downsize, you will get the full benefit of the command as the actual hardware in terms of CPUs and RAM is superior to the one being simulated. However, if you are planning to upgrade the server, such as in the case the actual server is using 4 CPUs while the emulated one is using 16 CPUs, then you still get the feel of what execution plans will be used. Unfortunately you will never get the performance of 16 cores using 4 cores only! The syntax of what-if statement goes like this: DBCC Optimizer_WhatIf (1,4) --tells optimizer that your server has 4 CPUs go DBCC Optimizer_WhatIf (2,3327) --tells optimizer to emulate a machine with only 3 GB of RAM go DBCC Optimizer_WhatIf (3,64) -- Sets your system to simulate 64 bit systems go --insert your query here DBCC Optimizer_WhatIf (1, 0) --clear CPU go DBCC Optimizer_WhatIf (2, 0) --clear RAM go DBCC Optimizer_WhatIf (3, 0) --clear 64 bits go Although the virtualization of SQL Server might resolve the issue of testing applications with higher-end or lower-end hardware, there will still be a significant amount of time consumed during the installation and/or restoring of the database. The what-if statement might give you the look and feel of how queries will perform on another hardware, without the hassle of actually installing one. Nevertheless, it is important to emphasize that the what-if statement is good in development and pre-testing stages --you still need to get servers (real or virtualized) for doing official testing, quality testing, users testing, stress testing, and of course going to production.