Click here to monitor SSC
  • Av rating:
  • Total votes: 36
  • Total comments: 4
Fabiano Amorim

Using Optimizer_WhatIF and StatsStream to Simulate a Production Environment

30 January 2013

SQL Server's Query optimiser judges the best query plan from the data in the relevant tables and the server's hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer's production server when you can neither access the server, nor recreate the database from a backup?

As part of my work as a SQL Server consultant, I've been asked many times to reproduce locally a specific query plan from a customer’s production environment, and then to debug it. You may think that this is just a matter of restoring a production database backup onto a development machine, and then we would be ready to start trying the query. Unfortunately you can seldom do this because:

  • The Production database is likely to be too large: The database backup can be too large to be restored in a local machine or to be transferred to another machine. It would take too long to take a backup of, say, a 1TB database, compress the file (suppose it is a SQL Server 2005 without native compression), send it to a FTP, download it, decompress the file, and finally restore the database.
  • The data may be confidential: The customer may not be able to share their data even if you have a non-disclosure agreement, because it contains confidential information.
  • Your hardware is almost certainly different: Unless you are blessed with a 1TB SSD and 32GB memory laptop your hardware will be very different from the customer's server. The Query optimizer will produce different plans for it because it takes account of the available memory and the number of available processors.

The query optimizer will use a number of factors to come up with the best query execution plan. If you change any of them, there will be no guarantee that you will be able to reproduce a specific query plan. These factors include:

  • TTable metadata: Which indexes are available and the metadata of the tables (data type columns, nullable columns etc.).
  • Session SET OPTIONS: SET options may change the cardinality of a predicate or a join. For instance, setting ANSI_NULLS to OFF will make SQL Server evaluates “NULL = NULL” to true and not as UNKNOWN as expected in the default behavior: this change may affect the cardinality of a filter expression or an outer join.
  • Constraints: SQL Server will try to use a foreign key or a check constraint to create a better query plan: You can see a sample here.
  • Statistics: During the creation of a query plan, the Query Optimizer can read statistics to use histograms, density, string summary etc.
  • Number of rows and pages in the table: Based on how many rows a logical query operator will process, the Query Optimizer can decide which physical operator will be used (i.e. a hash or a stream aggregation). In addition, the greater the number of pages in the table, the larger will be the cost associated with I/O operations, so the Query Optimizer has to consider the cost of scanning a table with many pages.
  • Available physical memory: The cost of sort and hash operators depends on the relative amount of memory that is available to SQL Server. For example, if the size of the data is larger than the cache, the query optimizer knows that the data must always be spooled to disk. However, if the size of the data is much smaller than the cache, it will probably perform the operation in memory.
  • Number of available CPUs: SQL Server considers different optimizations according to the number of CPUs. The cost associated to a parallel operation is different depending on the number of CPUs that are available. Even if a plan is parallel, SQL Server can identify when you have CPU pressure and will decide at the point of executing the plan not to run the actual plan using many threads, but the estimated plan will remain the same.
  • 32 or 64 bit system: On a 32 bit machine you are limited to the user mode address space, so even you have a server with a lot of memory, you cannot necessarily use all that memory in a sort or hash join operation.

So bearing in mind that it is almost impossible to keep all these factors the same in test, the question then is: how can one simulate a production environment in a lab machine?

The answer is:

  1. Generate a script of the database metadata including the statistics information
  2. Set the database session options
  3. Simulate the same CPU/Memory hardware from the production server.

The items 1 and 2 are not so complex to do and they are fully documented. Basically what you have to do is to generate a script using SSMS and it will create the commands using WITH STATS_STREAM to set a specific histogram in a statistics without the need to populate the tables with real data: Even if the table is empty, the statistics will be the same as those in the production server. To simulate the hardware, you’ll need to use an undocumented DBCC command called OPTIMIZER_WHATIF, let’s explore it a little bit to see how to use it and how it can affect a query plan.

Before we start a very important note:

Note: DBCC OPTIMIZER_WHATIF is an undocumented command, you can’t trust that this will not change in a service pack or a new product release, use it carefully and never use in a production environment.


The objective of this DBCC command is to set some of the hardware information that the query optimizer uses to create a query plan such as the CPU and memory. The changed settings will be applied only in the session scope.

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

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

If you run the OPTIMZER_WHATIF with property 0 and the traceflag 3604 you can see which values are actually used:


You’ll see different results depending on the version of SQL Server that you are running, because the optimizer_whatif has received new properties/options since it was first made available in SQL Server 2005. This is the result I get from SQL Server 2012 RTM:


Optimizer what-if status


property_number current_value default_value property_name


         1                  0             0 CPUs

         2                  0             0 MemoryMBs

         3                  0             0 Bits

         4               1000          1000 ParallelCardThreshold

         5                  1             1 ParallelCostThreshold

         6                200           200 ParallelCardCrossProd

         7                 50            50 LowCEThresholdFactorBy10

         8                 12            12 HighCEThresholdFactorBy10

         9             100000        100000 CEThresholdFactorCrossover

        10                 10            10 DMLChangeThreshold

I’ll not touch on items 4 to 10 because I don’t understand them myself. For now, let’s see a practical example that uses a server with 16 CPUs. Let’s try to simulate the same query plan on my humble notebook.

Suppose you have the following query to perform a running aggregation in a table called TestRunningTotals:

SELECT ID_Account,
       (SELECT SUM(b.ColValue)
          FROM TestRunningTotals b
         WHERE b.ColDate <= a.ColDate) AS RunningTotal
  FROM TestRunningTotals a
 ORDER BY ID_Account, ColDate

In a production server with 16 cores available we get the following query plan:

The query plan when running 16 cores

As we can see, the query is running in parallel and it is taking 41 seconds to run (because I really ran in a server with 16 cores). The CPU usage is very high since many cores are working to process the query.The CPU usage graph when running the multithreaded query is shown below.

The high CPU usage

If I run the same query, but throttling it by specifying the hint ‘MAXDOP 1’, the query takes 4 minutes and 24 seconds to run; and, as one might expect, the CPU usage is much lower since there is only one thread running the query. The following is the CPU usage graph that one sees when running the query with only one thread.

Lower CPU usage on many cores due to the throttled query

Now let’s suppose you have to optimize this query to consume less CPU, but you can’t limit the MAXDOP because, without running in parallel, it is taking too long to run the query.

First you have to simulate the same query plan in a test environment, so let’s first get the script to create the table and the statistics related to it:

Generating scripts for the table and statistics

To complete the script generation you can follow the instructions from following Microsoft KB:

Because the script generated is too big, I’ll not post it here, but you can download the script here.

After we have created the database and the table related to the query, let’s see the query plan:

SELECT ID_Account,
       (SELECT SUM(b.ColValue)
          FROM TestRunningTotals b
         WHERE b.ColDate <= a.ColDate) AS RunningTotal
  FROM TestRunningTotals a
 ORDER BY ID_Account, ColDate

This is the actual query plan:

The actual query plan

Since the query doesn’t have any real data, you’ll not be able to test it; but, from the perspective of the query optimizer, the table has the same data as the production table.

As we can see, the plan isn’t the same. The plan generated on my notebook isn’t running in parallel. Since my notebook only has four cores, the query optimizer considers that it would be too expensive to run this plan in parallel and so it doesn’t create a parallel plan.

To force the query optimiser to optimize the plan on the same production hardware I could use DBCC OPTIMIZER_WHATIF to mimic 16 cores, let’s see:

SELECT ID_Account,
       (SELECT SUM(b.ColValue)
          FROM TestRunningTotals b
         WHERE b.ColDate <= a.ColDate) AS RunningTotal
  FROM TestRunningTotals a
 ORDER BY ID_Account, ColDate

Actual query plan:

The query plan for the new script

Now that you have the same plan as you got from production environment, you can start trying to create indexes, or change the query to obtain a plan with better cost and potentially better performance.

You can also set the other configurations like memory and 64 bits, for instance:

-- Set ammount of memory in MB, in this case 512GB
-- Set to 64 bit system

To reset the values to default, you can call it using value 0, for instance DBCC OPTIMIZER_WHATIF(2,0) will reset the CPU property.


Using this technique, you can simulate a production environment “in-house” using nothing more than an ordinary laptop, with a very few commands. This simulation of the production environment is sufficient to enable you to debug or optimize SQL Queries that might otherwise require production data to deal with.

I am sure I don’t need to tell you not to use this is in production environment, do I? This is undocumented stuff, so nobody can guarantee what it is really doing, or that there aren’t horrible side-effects unless Microsoft chooses to make it officially public and documented.

That’s all folks…

Fabiano Amorim

Author profile:

Fabiano is fascinated by the SQL Server Query Processor and the way it works to optimize queries, procedures and functions. He graduated as a Technical Processor from Colégio Bezerra de Menezes, SP- Brazil, and has worked for several years with SQL Server, focusing in creating Data Warehouses and optimizing T-SQL codes for many companies in Brazil and Argentina. Fabiano is a SQL Server MVP, MCP for SQL Server 2000, MCTS and MCITP Data Base Developer for SQL Server 2005 and 2008. He also is actively involved in SQL Server community though forums such as MSDN and TechNet Brazil, writes articles for Simple-Talk and SQL Server Magazine Brazil, and he also presents online Webcasts and In-Person events for Microsoft Brazil. His blog is on

Search for other articles by Fabiano Amorim

Rate this article:   Avg rating: from a total of 36 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Thanks
Posted by: Allan S. Hansen (not signed in)
Posted on: Wednesday, February 13, 2013 at 1:46 AM
Message: Thanks a million. This is instant-bookmark for me. Very useful.

Subject: Excellent article
Posted by: dba-one (view profile)
Posted on: Thursday, March 21, 2013 at 7:28 AM
Message: Very useful information. Much appreciated.

Subject: Correction
Posted by: SQLCereal (view profile)
Posted on: Thursday, March 21, 2013 at 11:24 AM
Message: Great article. This is some really useful and relatively unknown information.

There may be an error in the last image where you are setting the memory in MB (OPTIMIZER_WHATIF(2, 524288)). You state that in this case you are using 512MB, but wouldn't this be 512GB?

Subject: SQLCereal
Posted by: mcflyamorim (view profile)
Posted on: Thursday, March 21, 2013 at 9:18 PM
Message: Hi tks, for the message.

Yes, it is 512GB, I'll ask guys from ST to fix it.

Tks again!


Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

Predictive Analysis Basics
 Statistics holds out the promise of teasing out significant relationships and to determine cause and... Read more...

The Enterprise DBA Mindset as a Practical Problem-solving Approach
 In order to keep the demands of the job under control, any DBA needs to automate as many as possible of... Read more...

In-Memory OLTP - Row Structure and Indexes
 There are several decisions to be made when designing indexes for Memory-optimized tables in In-Memory... Read more...

Automatically Creating UML Database Diagrams for SQL Server
 SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is... Read more...

SQL Server Security Audit Basics
 SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.