Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Usage of DBCC OPTIMIZER_WHATIF for SQL Server Query Tuning


By:   |   Read Comments (2)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

Command help

Usage

The usage is very simple; you only need to pass a property, either by its name or number, and the desired value as parameters of this DBCC command. If you choose to use parameter names keep in mind they are case sensitive.

To get the current status and a list of the available options you need to pass Status as the parameter or the number zero. Then you will get something similar to the image below depending on your SQL Server version.

DBCC OPTIMIZER_WHATIF Current Status

Just to make things clear, although this command will allow you to use a query plan as if you have N number of cores, you don't actually have more cores. So you won't have extra performance, in fact this could be detrimental to query performance because you are using a query plan that is not suitable for your actual configuration.

Sample

Now I will show you with an example how to set a hypothetical number of CPUs to see the performance effect on execution plans.

Step 1: Sample Database Creation

Let's create a sample database.

USE [master]
GO

CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , 
SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB.ldf' , 
SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

ALTER DATABASE [TestDB] SET RECOVERY SIMPLE 

Step 2: Test table Creation

For the purpose of this tip, I am using a large table. Take into consideration that you will need 10GB of free space to create this table.

USE TestDB
GO

IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
    DROP TABLE dbo.TestTable
GO

SELECT  A.name ,
        A.id ,
        A.xtype ,
        A.uid ,
        A.info ,
        A.status
INTO    TestTable
FROM    sys.sysobjects A
        CROSS JOIN sys.sysobjects B
        CROSS JOIN sys.sysobjects C
        CROSS JOIN sys.sysobjects D

If you prefer, instead of executing the previous script, the script below will just create a statistics only table. This is a table that has the metadata about statistics and density vectors, but no actual data.

USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestTable](
 [name] [sysname] NOT NULL,
 [id] [int] NOT NULL,
 [xtype] [char](2) NOT NULL,
 [uid] [smallint] NULL,
 [info] [smallint] NULL,
 [status] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
UPDATE STATISTICS [dbo].[TestTable] WITH ROWCOUNT = 71639296, PAGECOUNT = 485119
GO
/****** Object:  Statistic [_WA_Sys_00000001_3D2915A8]    Script Date: 28/06/2014 06:28:54 p.m. ******/
CREATE STATISTICS [_WA_Sys_00000001_3D2915A8] ON [dbo].[TestTable]([name]) WITH STATS_STREAM = 0x0100000001000000000000000000000068C419FB00000000FD1F000000000000BD1F000000000000E7030000E7000000000100000000000028D0000001000000070000009A172E0158A3000000214504000000007102070000000000000000004316323C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005C0000005C000000010000001000000019D3DC4120A4884C0000000019D3DC410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000130000000000000000000000000000001415000000000000491E000000000000511E000000000000E002000000000000170300000000000040030000000000008F03000000000000E403000000000000330400000000000058040000000000008904000000000000DC040000000000002F050000000000007E05000000000000C705000000000000100600000000000059060000000000008606000000000000C106000000000000F2060000000000001F070000000000004A070000000000007507000000000000A607000000000000D707000000000000FE070000000000002F0800000000000058080000000000008308000000000000AE08000000000000DD08000000000000140900000000000043090000000000007A09000000000000B309000000000000DE09000000000000070A0000000000002E0A000000000000550A0000000000007E0A000000000000A70A000000000000D00A000000000000F90A000000000000260B000000000000490B000000000000720B000000000000A70B000000000000DE0B000000000000090C000000000000360C000000000000630C0000000000008C0C000000000000B70C000000000000EC0C000000000000150D0000000000004A0D000000000000790D000000000000A20D000000000000C70D000000000000F80D0000000000001F0E000000000000420E0000000000006F0E000000000000980E000000000000CB0E000000000000F60E000000000000270F000000000000520F0000000000007B0F0000000000009E0F000000000000D10F000000000000FC0F0000000000002F1000000000000066100000000000009910000000000000C810000000000000FD100000000000002A110000000000005B110000000000008211000000000000AF11000000000000DE110000000000001312000000000000401200000000000075120000000000009E12000000000000C512000000000000DE120000000000000F1300000000000054130000000000009B1300000000000004140000000000006D14000000000000CE14000000000000F71400000000000030001000A9B22149000000000000803F0400000100370043007500730074006F006D0065007200430061007400650067006F007200790030001000A4A34549000000000000803F0400000100290043007500730074006F006D006500720073003000100039E22949000000000000803F04000001004F004500760065006E0074004E006F00740069006600690063006100740069006F006E004500720072006F00720073005100750065007500650030001000E0E33F49000000000000803F04000001005500660069006C006500730074007200650061006D005F0074006F006D006200730074006F006E0065005F003200300037003300300035003800340032003100300010001A453149000000000000803F04000001004F00660069006C0065007400610062006C0065005F0075007000640061007400650073005F00320031003000350030003500380035003300350030001000A2022549000000000000803F0400000100250049006E0076006F006900630065003000100009B36149000000000000803F0400000100310049006E0076006F00690063006500440065007400610069006C00300010002C802D49000000000000803F0400000100530050004B005F005F0043007500730074006F006D00650072005F005F003900340033004500440033004400440032004300430030003300450034004500300010000D885B49000000000000803F0400000100530050004B005F005F0043007500730074006F006D00650072005F005F0041003400410045003600340042003800350042003600440036004300390034003000100042F96449000000000000803F04000001004F00510075006500720079004E006F00740069006600690063006100740069006F006E004500720072006F0072007300510075006500750065003000100004DE6749000000000000803F04000001004900710075006500750065005F006D0065007300730061006700650073005F003100390037003700300035003800300037003900300010007EEB0B49000000000000803F04000001004900710075006500750065005F006D0065007300730061006700650073005F0032003000300039003000350038003100390033003000100088E14F49000000000000803F04000001004900710075006500750065005F006D0065007300730061006700650073005F0032003000340031003000350038003300300037003000100055C51649000000000000803F04000001002D00530061006C00650073005200650070006F0072007400300010007CD22049000000000000803F04000001003B005300650072007600690063006500420072006F006B00650072005100750065007500650030001000E6D32749000000000000803F0400000100310073007900730061006C006C006F00630075006E006900740073003000100038E62F49000000000000803F04000001002D007300790073006100730079006D006B0065007900730030001000BD336549000000000000803F04000001002B00730079007300610075006400610063007400730030001000383F8649000000000000803F04000001002B00730079007300620069006E006F0062006A00730030001000E6D32749000000000000803F04000001003100730079007300620069006E007300750062006F0062006A00730030001000699D2D49000000000000803F0400000100310073007900730062007200690063006B00660069006C006500730030001000F6F13249000000000000803F040000010027007300790073006300650072007400730030001000ED663349000000000000803F040000010031007300790073006300680069006C00640069006E0073007400730030001000A4A34549000000000000803F0400000100290073007900730063006C006F006E006500730030001000975C3749000000000000803F04000001002B0073007900730063006C0073006F0062006A00730030001000383F8649000000000000803F04000001002B0073007900730063006F006C00700061007200730030001000CA976449000000000000803F04000001002F0073007900730063006F006D006D006900740074006100620030001000BA0C1249000000000000803F0400000100370073007900730063006F006D00700066007200610067006D0065006E007400730030001000E0598749000000000000803F04000001002F0073007900730063006F006E007600670072006F007500700030001000674A6049000000000000803F040000010037007300790073006300730063006F006C007300650067006D0065006E00740073003000100060F33949000000000000803F0400000100390073007900730063007300640069006300740069006F006E00610072006900650073003000100017D97849000000000000803F04000001002B0073007900730064006200660069006C00650073003000100041902349000000000000803F0400000100290073007900730064006200660072006100670030001000C4781D49000000000000803F040000010027007300790073006400620072006500670030001000F3857A49000000000000803F040000010027007300790073006400650072006300760030001000E7EE1549000000000000803F0400000100290073007900730064006500730065006E00640030001000F7D23E49000000000000803F0400000100290073007900730065006E00640070007400730030001000DE7C8149000000000000803F0400000100290073007900730066006700660072006100670030001000A4A34549000000000000803F04000001002900730079007300660069006C0065007300310030001000712E3649000000000000803F04000001002D0073007900730066006F00710075006500750065007300300010001B263D49000000000000803F0400000100230073007900730066006F00730030001000391E0F49000000000000803F040000010029007300790073006600740069006E00640073003000100053C91C49000000000000803F0400000100350073007900730066007400700072006F00700065007200740069006500730030001000D8354649000000000000803F0400000100370073007900730066007400730065006D0061006E0074006900630073006400620030001000648E5149000000000000803F04000001002B0073007900730066007400730074006F007000730030001000C6552349000000000000803F04000001002D0073007900730067007500690064007200650066007300300010008D0D1D49000000000000803F04000001002D0073007900730069006400780073007400610074007300300010008C4D0849000000000000803F040000010029007300790073006900730063006F006C00730030001000E5111049000000000000803F04000001002B007300790073006C006E006B006C0067006E00730030001000C8D95249000000000000803F040000010035007300790073006D0075006C00740069006F0062006A00720065006600730030001000A4A34549000000000000803F040000010029007300790073006E0073006F0062006A007300300010006B992749000000000000803F040000010035007300790073006F0062006A006B006500790063007200790070007400730030001000D86F2849000000000000803F04000001002F007300790073006F0062006A00760061006C0075006500730030001000FF445349000000000000803F040000010029007300790073006F0077006E006500720073003000100065021649000000000000803F04000001002500730079007300700068006600670030001000D8AD1049000000000000803F040000010031007300790073007000720069006F0072006900740069006500730030001000C4781D49000000000000803F0400000100270073007900730070007200690076007300300010000B8E6449000000000000803F0400000100230073007900730070007200750030001000712E3649000000000000803F04000001002D00730079007300700072007500660069006C006500730030001000E7EE1549000000000000803F0400000100290073007900730071006E0061006D006500730030001000824A3E49000000000000803F04000001003300730079007300720065006D00730076006300620069006E00640073003000100024D03049000000000000803F04000001002B0073007900730072006D0074006C0067006E00730030001000D11A8549000000000000803F0400000100310073007900730072006F00770073006500740072006500660073003000100024D03049000000000000803F04000001002B0073007900730072006F00770073006500740073003000100094BF1C49000000000000803F040000010029007300790073007200730063006F006C007300300010001B263D49000000000000803F04000001002300730079007300720074007300300010004E427649000000000000803F040000010033007300790073007300630061006C006100720074007900700065007300300010003EB72349000000000000803F04000001002B007300790073007300630068006F0062006A00730030001000BBB13849000000000000803F04000001003300730079007300730065006F0062006A00760061006C0075006500730030001000286D4B49000000000000803F04000001003700730079007300730069006E0067006C0065006F0062006A0072006500660073003000100030DF5F49000000000000803F0400000100330073007900730073006F00660074006F0062006A00720065006600730030001000C89F7049000000000000803F04000001002F00730079007300730071006C0067007500690064006500730030001000B1094849000000000000803F04000001003500730079007300740079007000650064007300750062006F0062006A00730030001000C6108749000000000000803F04000001002D0073007900730075007300650072006D007300670073003000100085E95B49000000000000803F040000010031007300790073007700650062006D006500740068006F006400730030001000F6F13249000000000000803F0400000100270073007900730078006C0067006E007300300010008D0D1D49000000000000803F04000001002D0073007900730078006D006900740062006F006400790030001000CF8B5249000000000000803F04000001002F0073007900730078006D0069007400710075006500750065003000100076012D49000000000000803F0400000100350073007900730078006D006C0063006F006D0070006F006E0065006E007400300010001C7D1049000000000000803F04000001002D0073007900730078006D006C006600610063006500740030001000D4415849000000000000803F0400000100350073007900730078006D006C0070006C006100630065006D0065006E00740030001000A4A34549000000000000803F040000010029007300790073007800700072006F0070007300300010002AA12449000000000000803F04000001002700730079007300780073007200760073003000100094F0FE48000000000000803F04000001001900540030001000ED663349000000000000803F0400000100310054006500730074005F0053006E0069006600660069006E0067003000100084463849000000000000803F0400000100450054006500730074005F0053006E0069006600660069006E0067005F00440075006D006D0079005F005600610072003000100003FD5B49000000000000803F0400000100470054006500730074005F0053006E0069006600660069006E0067005F00510075006500720079005F00480069006E00740030001000D9503449000000000000803F0400000100690054006500730074005F0053006E0069006600660069006E0067005F00510075006500720079005F00480069006E0074005F004F007000740069006D0069007A0065005F0055006E006B006E006F0077006E00300010006DB00F49000000000000803F0400000100690054006500730074005F0053006E0069006600660069006E0067005F00510075006500720079005F00480069006E0074005F004F007000740069006F006E005F005200650063006F006D00700069006C0065003000100028A93049000000000000803F0400000100610054006500730074005F0053006E0069006600660069006E0067005F00510075006500720079005F00480069006E0074005F0051005500450052005900540052004100430045004F004E0030001000A4A34549000000000000803F0400000100290054006500730074005400610062006C00650030001000398B5649000000000000803F04000001001D007A007A007A00FF0100000000000000610E00005C000000280000002800000000000000000000000603000043007500730074006F006D0065007200430061007400650067006F00720079004500760065006E0074004E006F00740069006600690063006100740069006F006E004500720072006F007200730051007500650075006500660069006C006500730074007200650061006D005F0074006F006D006200730074006F006E0065005F0032003000370033003000350038003400320031007400610062006C0065005F0075007000640061007400650073005F00320031003000350030003500380035003300350049006E0076006F00690063006500440065007400610069006C0050004B005F005F0043007500730074006F006D00650072005F005F0039003400330045004400330044004400320043004300300033004500340045004100340041004500360034004200380035004200360044003600430039003400510075006500720079004E006F00740069006600690063006100740069006F006E004500720072006F007200730051007500650075006500750065005F006D0065007300730061006700650073005F003100390037003700300035003800300037003900320030003000390030003500380031003900330034003100300035003800330030003700530061006C00650073005200650070006F0072007400650072007600690063006500420072006F006B0065007200510075006500750065007900730061006C006C006F00630075006E00690074007300730079006D006B00650079007300750064006100630074007300620069006E006F0062006A0073007300750062006F0062006A0073007200690063006B00660069006C006500730063006500720074007300680069006C00640069006E007300740073006C006F006E006500730073006F0062006A0073006F006C0070006100720073006D006D0069007400740061006200700066007200610067006D0065006E00740073006E007600670072006F0075007000730063006F006C007300650067006D0065006E0074007300640069006300740069006F006E006100720069006500730064006200660069006C00650073007200650067006500720063007600730065006E00640065006E00640070007400730066006700660072006100670069006C006500730031006F00710075006500750065007300740069006E0064007300700072006F007000650072007400690065007300730065006D0061006E0074006900630073006400620074006F007000730067007500690064007200650066007300690064007800730074006100740073006C006E006B006C0067006E0073006D0075006C00740069006F0062006A0072006500660073006E0073006F0062006A0073006F0062006A006B0065007900630072007900700074007300760061006C0075006500730077006E006500720073007000680066006700720069006F0072006900740069006500730071006E0061006D0065007300720065006D00730076006300620069006E00640073006D0074006C0067006E0073006F00770073006500740072006500660073007300630061006C00610072007400790070006500730068006F0062006A00730065006F0062006A00760061006C0075006500730069006E0067006C0065006F0062006A0072006500660073006F00660074006F0062006A00720065006600730071006C00670075006900640065007300740079007000650064007300750062006F0062006A00730075007300650072006D007300670073007700650062006D006500740068006F006400730078006C0067006E0073006D006900740062006F00640079006C0063006F006D0070006F006E0065006E0074006600610063006500740070006C006100630065006D0065006E007400700072006F0070007300730072007600730054006500730074005F0053006E0069006600660069006E0067005F00440075006D006D0079005F00560061007200510075006500720079005F00480069006E0074005F004F007000740069006D0069007A0065005F0055006E006B006E006F0077006E006F006E005F005200650063006F006D00700069006C00650051005500450052005900540052004100430045004F004E005400610062006C0065007A007A007A00870000004000000000C008000000900808000023100102000029901C10000024C0042C0000901B3000002810184B000026D0076300002210066A000030C00E70000090107E00002410108E00002EC0039E00009019A1000031400CBA0000900AC60000304002D000009008D200001E1008DA00002CC001E20000900AE300001F9011ED0000224002FE0000C00100010090090101002390070A010025100611010031C001170100C00218010090041A01003810071E010024100925010024C0012E010090042F010026900933010025C0013C010090043D01002A100541010027C001460100900547010038C0014C010090064D010030100A5301001F10075D0100394001640100900B6501002F100C70010027C0017C0100C0017D0100C0017E010090047F010035100355010022100383010021400186010090038701003510048A01001F90068E010029C00194010090059501003690059A01002AC0019F01009006A00100261001020000284001A601009004A701001E900AAB0100214001B50100900AB601002A1004C001002C9008C4010022C001CC01009007CD01002110056401001D9007D401001E900CDB01002C9006E701002AC001ED0100C002EE01009009F00100231006F90100241005FF01002CC0010402009003050200204001080200C00109020090070A02001E10028901002150010100003010052C00002790061102001FC001170200900A18020028900622020026C00528020090042D020038100102000025900564010021100208010028C001310200C00132020090093302003310053C020023900A41020027900C4B02002A900A57020030100861020032900C6902002A900875020039900A7D02002E4001870200900488020026C0018C0200C0028D020090048F0200211005A001002C400193020090099402002590059D02001E1009A202002E9005AB0200291004B0020023D001B402001B4003B50200D009B80200254001C102009009C2020027500ACB02002F4001D50200C004D60200900CDA020025100CE602001F100CF20200251005FE02002910030303002E000021450400000000
GO

Step 3: The query

I decided to use a simple query to show this DBCC command because the query isn't relevant to learning the usage of this command.

USE TestDB
GO

SELECT  DENSE_RANK() OVER ( PARTITION BY name ORDER BY name )
FROM    dbo.TestTable
WHERE   name LIKE '%s%'
GROUP BY name
OPTION  ( RECOMPILE )

On my eight core test environment this query has the execution plan as shown in the image below.

Sample Query Execution Plan

As you can see we have parallel operators for the table scan and the hash match.

Step 4: Let's see what if we have 128 CPUs

In order to get a query plan as if we have 128 CPUs we must first execute the DBCC OPTIMIZER_WHATIF command setting the number of CPUs to 128. For those of you who don't know, the first and second DBCC commands below are to clean the plan cache and the buffer pool.

USE TestDB
GO

DBCC FREESYSTEMCACHE('ALL')
DBCC DROPCLEANBUFFERS()

DBCC OPTIMIZER_WHATIF(1,128)
GO

SELECT  DENSE_RANK() OVER ( PARTITION BY name ORDER BY name )
FROM    dbo.TestTable
WHERE   name LIKE '%s%'
GROUP BY name
OPTION  ( RECOMPILE )

Looking at the resulting plan on the image below, we can see that query cost for the Hash match operator has dropped from 23% to 13%.

Sample Query Execution Plan with 128 CPUs

Step 5: Let's see what if we have only one CPU

Like in previous step, we first execute the DBCC OPTIMIZER_WHATIF to set the CPU value to 1.

USE TestDB
GO

DBCC FREESYSTEMCACHE('ALL')
DBCC DROPCLEANBUFFERS()

DBCC OPTIMIZER_WHATIF(1,1)
GO

SELECT  DENSE_RANK() OVER ( PARTITION BY name ORDER BY name )
FROM    dbo.TestTable
WHERE   name LIKE '%s%'
GROUP BY name
OPTION  ( RECOMPILE )

As expected the query uses a non-parallel plan.

Sample Query Execution Plan with 1 CPU

The next image is a comparison of the Estimated Subtree Cost for the different executions.

Cost Comparisson of Different Executions

Conclusion

With the help of this DBCC command you can easily take cost estimations about hardware changes on the fly and determine the scalability of your application.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 14, 2014 - 3:14:17 AM - Eelco Back To Top

Thanks for sharing!!

Had to turn on TF 3604 too, to get the DBCC output on screen


Thursday, August 07, 2014 - 1:43:18 PM - Sri Back To Top

Thanks Daniel fr sharing thi Tip! Did't know this exist before.Hoping to see more from you!!!


Learn more about SQL Server tools