By: Daniel Farina | Updated: 2014-08-07 | Comments (2) | Related: > Performance 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.
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.
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.
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%.
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.
The next image is a comparison of the Estimated Subtree Cost for the 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
- Read about DBCC Commands on MSDN.
- If you want to perform query testing then you will need this: Clearing Cache for SQL Server Performance Testing. .
- Take a look at Testing Tips category.
- Also, if you want to dig further o query plans then this one will be of help: How to read SQL Server graphical query execution plans.
- Read this tip if you don't know much about SQL Server Queries With Hints.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-08-07
