Continuing the subject of exponential backoffs (from the 2nd and 3rd posts in this series), let\u2019s restore the Credit sample database back to the baseline version and execute the following script:<\/p>\n

\r\nUSE [master];\r\nGO\r\n\r\nALTER DATABASE [Credit] SET COMPATIBILITY_LEVEL = 120;\r\nGO\r\n\r\nUSE [Credit];\r\nGO\r\n\r\n-- Add four new columns\r\nALTER TABLE [dbo].[member]\r\nADD [arbitrary_1] BIGINT NULL;\r\n\r\nALTER TABLE [dbo].[member]\r\nADD [arbitrary_2] BIGINT NULL;\r\n\r\nALTER TABLE [dbo].[member]\r\nADD [arbitrary_3] BIGINT NULL;\r\n\r\nALTER TABLE [dbo].[member]\r\nADD [arbitrary_4] BIGINT NULL;\r\n<\/pre>\n
I changed the database to the latest version so we use the new CE and then added four new columns.<\/p>\n
Next, let\u2019s update the values of the four new columns using different distributions:<\/p>\n
\r\n;WITH\u00a0\u00a0\u00a0 CTE_NTILE\r\nAS ( SELECT\u00a0\u00a0 [member_no] ,\r\nNTILE(10) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_1] ,\r\nNTILE(2) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_2] ,\r\nNTILE(4) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_3] ,\r\nNTILE(250) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_4]\r\nFROM\u00a0\u00a0\u00a0\u00a0 [dbo].[member]\r\n)\r\nUPDATE\u00a0 [dbo].[member]\r\nSET\u00a0\u00a0\u00a0\u00a0 [arbitrary_1] = [c].[arbitrary_1] ,\r\n[arbitrary_2] = [c].[arbitrary_2] ,\r\n[arbitrary_3] = [c].[arbitrary_3] ,\r\n[arbitrary_4] = [c].[arbitrary_4]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member] AS [m]\r\nINNER JOIN CTE_NTILE AS [c]\r\nON\u00a0\u00a0\u00a0\u00a0\u00a0 [c].[member_no] = [m].[member_no];\r\nGO\r\n<\/pre>\n
Looking at the estimates for single-predicate queries, if I execute the following, I\u2019ll get an estimate of 1,000 rows:<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_1] = 1\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
For this next query I\u2019ll get an estimate of 5,000 rows:<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_2] = 1\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
And for this next query, an estimate of 2,500 rows:<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_3] = 1\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
And lastly (for single-predicate examples anyhow), an estimate of 40 rows:<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_4] = 1\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
Now let\u2019s start adding multiple predicates per statement.\u00a0 The first example with multiple predicates uses two predicates \u2013 one with a selectivity of 0.1 and one of 0.5:<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_1] = 1 AND -- 0.1 selectivity\r\n[arbitrary_2] = 1 -- 0.5 selectivity\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
The estimate for this query is 707.107 with the new CE, which we can derive using the POWER function in T-SQL as follows (I used Excel last time to do this, so see the previous posts for the background information on this calculation):<\/p>\n
\r\nSELECT\u00a0 10000 *0.10 * POWER(0.500000, 0.50);\r\n<\/pre>\n
That returned 707.107.<\/p>\n
Now what about a query with three predicates, with selectivities of 0.1, 0.5 and 0.25?<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_1] = 1 AND -- .1 selectivity\r\n[arbitrary_2] = 1 AND -- .5 selectivity\r\n[arbitrary_3] = 1 -- .25 selectivity\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
The estimate for this was 420.448, and we can derive this via the following expression (and notice the order of selectivities goes from smallest to highest):<\/p>\n
\r\n-- Notice the selectivity order (0.10, 0.25, .50)\r\nSELECT\u00a0 10000 * 0.10 * POWER(0.250000,0.50) * POWER(0.500000, 0.25);\r\n<\/pre>\n
Now let\u2019s reference all four columns (with selectivities of 0.1, 0.5, 0.25 and 0.004):<\/p>\n
\r\nSELECT\u00a0 [member_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [arbitrary_1] = 1 AND -- .1 selectivity\r\n[arbitrary_2] = 1 AND -- .5 selectivity\r\n[arbitrary_3] = 1 AND -- .25 selectivity\r\n[arbitrary_4] = 1\u00a0 -- 0.004 selectivity\r\nOPTION\u00a0 ( RECOMPILE );\r\n<\/pre>\n
The estimate is 8.20193 and we can derive this via the following:<\/p>\n
\r\nSELECT\u00a0 10000 * 0.004* POWER(0.1000000, 0.50) * POWER(0.2500000, 0.25) * POWER(0.5000000, 0.125);\r\n<\/pre>\n
The selectivities are ordered from most selective to least selective, and the the less selective values get the \u201cback offs\u201d in order of none, 1\/2, 1\/4, and 1\/8.<\/p>\n","protected":false},"excerpt":{"rendered":"
This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator \u201cCSelCalcCombineFilters_ExponentialBackoff\u201d Calculator\u2013 Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,28],"tags":[],"class_list":["post-1138","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\n