This weekend I set up a Windows Azure virtual machine running SQL Server 2014 CTP2 (12.0.1524).\u00a0 I had a local copy of SQL Server 2014 CTP2 in VMware as well, but thought it would be more fun to explore it on Windows Azure (kill two explorations with one stone).\u00a0 On a side note, I\u2019m really digging having the azure credits to play with each month.\u00a0 If you have an MSDN subscription, be sure to take advantage of this benefit.<\/em><\/p>\n

In this post I\u2019m just sharing my initial exploration steps regarding the query_optimizer_estimate_cardinality XE event.\u00a0 I\u2019m not entirely sure how well this event will be documented, but I\u2019m definitely interested in learning more about it.<\/p>\n

For my test scenario, I attached a version of AdventureWorksLT2012, set it to compatibility level 120 and then created the following session:<\/p>\n

\r\nCREATE EVENT SESSION [XE_Card_Calculator] ON SERVER\r\nADD EVENT sqlserver.query_optimizer_estimate_cardinality\r\nWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,\r\nMAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)\r\nGO\r\n<\/pre>\n
If you\u2019re looking for the query_optimizer_estimate_cardinality in the GUI, keep in mind that it is in the Debug channel (so you\u2019ll need to select this in order to see it).\u00a0 This also implies that there is likely nontrivial overhead to enabling this event, so while there isn\u2019t an explicit warning for this event like other more invasive ones, I would still use it with caution.<\/p>\n
As for the description of this event in the GUI, it is as follows:<\/p>\n
\u201cOccurs when the query optimizer estimates cardinality on a relational expression.\u201d<\/p><\/blockquote>\n
Okay \u2013 no big deal, right?\u00a0 Why care?<\/p>\n
So in keeping things simple, I executed the following query against a single table (using the RECOMPILE so I can get the event each time I tested it out):<\/p>\n
\r\nSELECT AddressLine1\r\nFROM [SalesLT].[Address]\r\nWHERE AddressID = 9\r\nOPTION (RECOMPILE);\r\n<\/pre>\n
The actual query execution plan had a Clustered Index Seek with an estimate of 1 row.\u00a0 And gathering the query_optimizer_estimate_cardinality event I saw two events surfaced.<\/p>\n
The first event had the following information:<\/p>\n\n\n\n\n\n\n\n\n
calculator<\/td>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcUniqueKeyFilter<\/span>” \/>
\n<\/CalculatorList><\/td>\n<\/tr>\n
creation_time<\/td>\n
2013-11-16 16:56:35.6666666<\/td>\n<\/tr>\n
input_relation<\/td>\n
<Operator Name=”LogOp_Select” ClassNo=”32″>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”450.00″ TableName=”SalesLT.Address” \/>
\n<Operator Name=”ScaOp_Comp ” ClassNo=”100″>
\n<CompInfo CompareOp=”EQ” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Address]” ColumnName=”AddressID” \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”int” Value=”(9)” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
query_hash<\/td>\n
13158512245962950952<\/td>\n<\/tr>\n
stats_collection<\/td>\n
<StatsCollection Name=”CStCollFilter<\/span>” Id=”2″ Card=”1.00″ \/><\/td>\n<\/tr>\n
stats_collection_id<\/td>\n
2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The second event had the following information:<\/p>\n\n\n\n\n\n\n\n\n
calculator<\/td>\n
<CalculatorList \/><\/td>\n<\/tr>\n
creation_time<\/td>\n
2013-11-16 16:56:35.6666666<\/td>\n<\/tr>\n
input_relation<\/td>\n
<Operator Name=”LogOp_SelectIdx” ClassNo=”43″>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”450.00″ TableName=”SalesLT.Address” \/>
\n<Operator Name=”ScaOp_Comp ” ClassNo=”100″>
\n<CompInfo CompareOp=”EQ” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Address]” ColumnName=”AddressID” \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”int” Value=”(9)” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
query_hash<\/td>\n
13158512245962950952<\/td>\n<\/tr>\n
stats_collection<\/td>\n
<StatsCollection Name=”CStCollFilter” Id=”2″ Card=”1.00″ \/><\/td>\n<\/tr>\n
stats_collection_id<\/td>\n
2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
So there is a lot here to dig through, but I highlighted a couple of values that stood out. And I know that AddressID happens to be my clustered, unique, primary key column for this table.<\/p>\n
What happens if I reference a non-unique key value that is covered by an index (such as StateProvince)?<\/p>\n
\r\nSELECT AddressID\r\nFROM [SalesLT].[Address]\r\nWHERE StateProvince = 'Arizona'\r\nOPTION (RECOMPILE);\r\n<\/pre>\n
This query uses in Index Seek as I expected, and this time for query_optimizer_estimate_cardinality I saw a new calculator value:<\/p>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcColumnInInterval<\/span>” Selectivity=”0.029″ TableName=”[AdventureWorksLT2012].[SalesLT].[Address]” ColumnName=”StateProvince” StatId=”5″ \/>
\n<\/CalculatorList><\/p><\/blockquote>\n
The stats_collection value was as follows:<\/p>\n
<StatsCollection Name=”CStCollFilter” Id=”2″ Card=”13.00″>
\n<LoadedStats>
\n<StatsInfo DbId=”5″ ObjectId=”69575286″ StatsId=”5″ \/>
\n<\/LoadedStats>
\n<\/StatsCollection><\/p><\/blockquote>\n
Also \u2013 for a scenario where I didn\u2019t have stats \u2013 and disabled them from being auto-created (to simulate a wild guess scenario), I saw the following calculator list:<\/p>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcPointPredsFreqBased<\/span>“>
\n<SubCalculator Role=”DistinctCountPlan”>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanLeaf<\/span>“ Guesses=”1″<\/span><\/span> CoveringStatId=”4″ CoveringStatDensity=”450.000″ \/>
\n<\/SubCalculator>
\n<\/FilterCalculator><\/span>
\n<\/CalculatorList><\/p><\/blockquote>\n
The \u201cGuesses\u201d part looks promising (thinking magic numbers\/selectivity guesses\/heuristics, whatever you like to call it).<\/p>\n
When executing a query that kicked off auto-stats operations, I saw the following operator information:<\/p>\n
<Operator Name=”LogOp_GbAgg” ClassNo=”31″>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”847.00″ TableName=”SalesLT.Customer” \/>
\n<Operator Name=”AncOp_PrjList ” ClassNo=”137″>
\n<Operator Name=”AncOp_PrjEl ” ClassNo=”138″>
\n<Operator Name=”ScaOp_AggFunc ” ClassNo=”90″>
\n<AggFuncInfo AggType=”STATMAN” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Customer]” ColumnName=”LastName” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/p><\/blockquote>\n
And I saw the following calculator information (for the auto-stats operations):<\/p>\n
<CalculatorList>
\n<DistinctCountCalculator CalculatorName=”CDVCPlanTrivial” \/>
\n<\/CalculatorList><\/p><\/blockquote>\n
And lastly, I tried a query with a bad-practice (fiddling with the column reference via concatenation) to see what steps would be taken:<\/p>\n
\r\nSELECT [CustomerID]\r\nFROM [SalesLT].[Customer]\r\nWHERE LastName + ' ' = 'Gates'\r\nOPTION (RECOMPILE);\r\n<\/pre>\n
This query plan just had a Clustered Index Scan, but spawned five query_optimizer_estimate_cardinality events associated with it (and I tested this a few times to see if the 5-event output was consistent):<\/p>\n\n\n\n\n\n\n\n\n
calculator<\/strong><\/td>\n
input_relation<\/strong><\/td>\n<\/tr>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcHistogramComparison” Selectivity=”0.002″ ComparisonType=”Interval” \/>
\n<\/CalculatorList><\/td>\n
<Operator Name=”LogOp_Select” ClassNo=”32″>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”847.00″ TableName=”SalesLT.Customer” \/>
\n<Operator Name=”ScaOp_Comp ” ClassNo=”100″>
\n<CompInfo CompareOp=”EQ” \/>
\n<Operator Name=”ScaOp_Arithmetic ” ClassNo=”87″>
\n<ArithmeticInfo Operation=”ADD” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Customer]” ColumnName=”LastName” \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(1)” Value=”N’ ‘” \/>
\n<\/Operator>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(5)” Value=”N’Gates'” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
<CalculatorList \/><\/td>\n
<Operator Name=”LogOp_Project” ClassNo=”29″>
\n<OpProjectInfo \/>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”847.00″ TableName=”SalesLT.Customer” \/>
\n<Operator Name=”AncOp_PrjList ” ClassNo=”137″>
\n<Operator Name=”AncOp_PrjEl ” ClassNo=”138″>
\n<Operator Name=”ScaOp_Arithmetic ” ClassNo=”87″>
\n<ArithmeticInfo Operation=”ADD” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Customer]” ColumnName=”LastName” \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(1)” Value=”N’ ‘” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcHistogramComparison” Selectivity=”0.002″ ComparisonType=”Interval” \/>
\n<\/CalculatorList><\/td>\n
<Operator Name=”LogOp_Select” ClassNo=”32″>
\n<StatsCollection Name=”CStCollProject” Id=”3″ Card=”847.00″ \/>
\n<Operator Name=”ScaOp_Comp ” ClassNo=”100″>
\n<CompInfo CompareOp=”EQ” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo ColumnName=”Expr1002″ \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(5)” Value=”N’Gates'” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
<CalculatorList \/><\/td>\n
<Operator Name=”LogOp_Project” ClassNo=”29″>
\n<OpProjectInfo \/>
\n<StatsCollection Name=”CStCollBaseTable” Id=”1″ Card=”847.00″ TableName=”SalesLT.Customer” \/>
\n<Operator Name=”AncOp_PrjList ” ClassNo=”137″>
\n<Operator Name=”AncOp_PrjEl ” ClassNo=”138″>
\n<Operator Name=”ScaOp_Arithmetic ” ClassNo=”87″>
\n<ArithmeticInfo Operation=”ADD” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo TableName=”[AdventureWorksLT2012].[SalesLT].[Customer]” ColumnName=”LastName” \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(1)” Value=”N’ ‘” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcHistogramComparison” Selectivity=”0.002″ ComparisonType=”Interval” \/>
\n<\/CalculatorList><\/td>\n
<Operator Name=”LogOp_Select” ClassNo=”32″>
\n<StatsCollection Name=”CStCollProject” Id=”5″ Card=”847.00″ \/>
\n<Operator Name=”ScaOp_Comp ” ClassNo=”100″>
\n<CompInfo CompareOp=”EQ” \/>
\n<Operator Name=”ScaOp_Identifier ” ClassNo=”99″>
\n<IdentifierInfo ColumnName=”Expr1002″ \/>
\n<\/Operator>
\n<Operator Name=”ScaOp_Const ” ClassNo=”98″>
\n<ConstInfo Type=”nvarchar(5)” Value=”N’Gates'” \/>
\n<\/Operator>
\n<\/Operator>
\n<\/Operator><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Lots of scenarios to mull over and dig through as time permits.<\/p>\n
Why care?<\/p>\n
Many query performance issues (and associated query plan quality issues) are due to cardinality estimate skews.\u00a0\u00a0\u00a0 It would be great to have a way to more efficiently point to how <\/em>the various estimates are being calculated and why<\/em> the estimates are off.<\/p>\n
I\u2019m not sure how in-depth this event and associated calculators will be documented by Microsoft, and my assumption is that we\u2019ll need to figure it out via collective reverse-engineering.\u00a0 But in the meantime this new XE event might prove to be quite useful for troubleshooting the more mysterious cardinality estimates.<\/p>\n","protected":false},"excerpt":{"rendered":"
This weekend I set up a Windows Azure virtual machine running SQL Server 2014 CTP2 (12.0.1524).\u00a0 I had a local copy of SQL Server 2014 CTP2 in VMware as well, but thought it would be more fun to explore it on Windows Azure (kill two explorations with one stone).\u00a0 On a side note, I\u2019m really […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,24,28],"tags":[],"class_list":["post-1073","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-indexing","category-performance"],"yoast_head":"\n