This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series (yeah \u2013 I\u2019ve decided to call this a series, since there will be quite a bit to talk about, time permitting):<\/p>\n
So for this post, I created a nonclustered index on the charge_dt column from the charge table in the Credit sample database and then inserted 10 new rows as follows:<\/p>\n
\r\nINSERT INTO [dbo].[charge]\r\n([member_no]\r\n,[provider_no]\r\n,[category_no]\r\n,[charge_dt]\r\n,[charge_amt]\r\n,[statement_no]\r\n,[charge_code])\r\nSELECT TOP 10 [member_no]\r\n,[provider_no]\r\n,[category_no]\r\n,GETDATE()\r\n,[charge_amt]\r\n,[statement_no]\r\n,[charge_code]\r\nFROM dbo.charge\r\nGO\r\n<\/pre>\nNotice I\u2019m using GETDATE() for the charge_dt, and if you look at the last few rows of the histogram for charge_dt you\u2019ll see that nothing in the year 2013 is represented (and I\u2019m only inserting 10 new rows, and haven\u2019t manually update statistics recently):<\/p>\n
<\/a><\/p>\n
In SQL Server 2012, what row estimate would you expect if I executed the following query?<\/p>\n
\r\nSELECT [charge_no]\r\nFROM dbo.[charge]\r\nWHERE [charge_dt] = \u20182013-11-17 12:46:44.307\u2019\r\nOPTION (RECOMPILE);\r\n<\/pre>\nIn this case, the plan tree (via SQL Sentry Plan Explorer) is as follows:<\/p>\n
<\/a><\/p>\n
The estimate is for 1 row (actual is 10 rows).<\/p>\n
What are my estimates if I switch the database to compatibility mode 120 and re-execute the same exact query?<\/p>\n
<\/a><\/p>\n
In 2014 CTP2 \u2013 my estimate is for 10 rows, and re-checking the histogram, the maximum RANGE_HI_KEY is still 1999-10-13 10:51:19.317 and not a more recent value.<\/p>\n
What does the query_optimizer_estimate_cardinality event show?<\/p>\n
<CalculatorList>
\n<FilterCalculator CalculatorName=”CSelCalcColumnInInterval<\/span>” Selectivity=”-1.000″
\n CalculatorFailed=”true”<\/span> TableName=”[Credit].[dbo].[charge]” ColumnName=”charge_dt” \/><\/p>\n<FilterCalculator CalculatorName=”CSelCalcAscendingKeyFilter<\/span>” Selectivity=”0.000″
\nTableName=”[Credit].[dbo].[charge]” ColumnName=”charge_dt” UseAverageFrequency=”true”<\/span> StatId=”7″ \/>
\n<\/CalculatorList><\/p><\/blockquote>\nI highlighted a couple of interesting areas \u2013 including the CalculatorFailed=\u201dtrue\u201d associated with CSelCalcColumnInInterval and then what seems to be the application of a second calculator, CSelCalcAscendingKeyFilter \u2013 which implies I would assume something related to the classic ascending key issue and a \u201ctrue\u201d for \u201cUseAverageFrequency\u201d.<\/p>\n
If this is average frequency, then we\u2019re looking at density times the row count, which would mean 1,600,010 rows times the 1.006087E-05 density from the that particular statistics object:<\/p>\n
<\/a><\/p>\n
However if you do that calculation, you\u2019ll get 16.0974926087 instead of the 10 row estimate.<\/p>\n
Looking further in the query_optimizer_estimate_cardinality output (still associated with the prior calculator) I see the following:<\/p>\n
<StatsCollection Name=”CStCollFilter” Id=”2″ Card=”10.00″<\/span>>
\n<LoadedStats>
\n<StatsInfo DbId=”6″ ObjectId=”229575856″ StatsId=”7″ \/>
\n<\/LoadedStats>
\n<\/StatsCollection><\/p><\/blockquote>\nContinuing the search, I loaded 90 more rows with that same \u20182013-11-17 12:46:44.307\u2019 value and re-executed the query to see the query_optimizer_estimate_cardinality event information.<\/p>\n
This time the estimates bumped up as follows (16 rows instead of a 10 row estimate, with 100 actual rows):<\/p>\n