So far and for recent versions of SQL Server, we have 2 (I’d say 3) different versions of the CE. It was one of the big announcements for SQL Server 2014 and that left the picture like this.<\/p>\n
- \n
- SQL Server 2012 and earlier version till 2000, CE version 70<\/li>\n
- SQL Server 2014, new CE version 120<\/a><\/li>\n<\/ul>\n
\nThese numbers match with the compatibility level of the SQL Server versions they were released, coincidence? I don’t think so \ud83d\ude42<\/p>\nSo in SQL Server 2016 we have been given an improved version of the CE, which for the examples of this post, I think would be version 130.<\/p>\n
I’m sure there are many small differences but I just want to talk about how the estimated number of rows differ from one version to another when we query a single table using two predicates and we have multi-column statistics that cover the combination.<\/p>\n
In order to provide the 3 examples, I run my SQL Server 2016 Dev Edition instance with the sample database AdventureWorks2014.<\/p>\n
Let’s get a fresh copy of the database and create the statistics<\/p>\n
\r\nUSE master\r\nGO\r\nIF DB_ID('AdventureWorks2014') IS NOT NULL BEGIN\r\n\tALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n\tDROP DATABASE AdventureWorks2014\r\nEND\r\n\r\nRESTORE DATABASE AdventureWorks2014\r\nFROM DISK = '.\\AdventureWorks2014.bak'\r\nWITH RECOVERY\r\n, MOVE 'AdventureWorks2014_Data' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQL2016\\MSSQL\\DATA\\AdventureWorks2014_Data.mdf'\r\n, MOVE 'AdventureWorks2014_Log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQL2016\\MSSQL\\DATA\\AdventureWorks2014_Log.ldf'\r\nGO\r\n\r\nUSE AdventureWorks2014\r\n\r\nCREATE STATISTICS Person_Address_PostalCode_City ON Person.Address (PostalCode, City) WITH FULLSCAN\r\n\r\n<\/pre>\nOnce it’s done, we can test the different CE<\/p>\n
\r\nALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 110 WITH ROLLBACK IMMEDIATE\r\nGO\r\nSELECT * \r\nFROM Person.Address\r\nWHERE City = N'Wokingham'\r\nAND PostalCode = N'RG41 1QW'\r\nOPTION (RECOMPILE)\r\nGO\r\n-- \r\n<\/pre>\n
![\"01_estimate_ce_70\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/01_estimate_CE_70.png\")
<\/a><\/p>\nThis estimated number comes from the statistics we have just created, if we have a look at them, I’ll show you the maths.<\/p>\n
\r\nDBCC SHOW_STATISTICS ('Person.Address', 'Person_Address_PostalCode_City') \r\n<\/pre>\n![\"02_dbcc_show_statistics_multi_column\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/02_DBCC_SHOW_STATISTICS_multi_column.png\")
<\/a><\/p>\nBy multiplying the number of rows by the all density for the combination of columns, we get the number shown above 19614 * 0.001485884 = 29.144128776<\/b>.<\/p>\n
The actual rows is 31, so in this case the estimated are not far off the actual.<\/p>\n
This is with the called ‘legacy CE’ so let’t see how is with the first review of the new CE<\/p>\n
\r\nALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120 WITH ROLLBACK IMMEDIATE\r\nGO\r\nSELECT * \r\nFROM Person.Address\r\nWHERE City = N'Wokingham'\r\nAND PostalCode = N'RG41 1QW'\r\nOPTION (RECOMPILE)\r\nGO\r\n<\/pre>\n
![\"03_estimate_ce_120\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/03_estimate_CE_120.png\")
<\/a><\/p>\nSee how this is way off, all this marketing about the benefits of the new CE and this… Disappointing!<\/p>\n
This version of the CE does not care about the existence of multi-column stats and just does the maths looking at single column stats.<\/p>\n
If we look at the single column stats, we can work the numbers out<\/p>\n
**Thanks to Fraser Watson for pointing this out<\/em><\/strong><\/p>\n
\r\nDBCC SHOW_STATISTICS ('Person.Address', 'City') WITH HISTOGRAM\r\nDBCC SHOW_STATISTICS ('Person.Address', 'Person_Address_PostalCode_City') WITH HISTOGRAM\r\n<\/pre>\n![\"04_dbcc_show_statistics_single_column_historgram\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/04_DBCC_SHOW_STATISTICS_single_column_Historgram.png\")
<\/a><\/p>\nThe new CE assumes some correlation between predicates within a single table, using the following formula<\/p>\n
\nmost selective * SQRT(2nd most selective) * number of rows\n<\/p><\/blockquote>\n
In our case the numbers are <\/p>\n
SELECT (CONVERT(FLOAT, 31) \/ 19614) * SQRT((CONVERT(FLOAT, 31) \/ 19614)) * 19614\r\n-- 1.23242203675524\r\n<\/pre>\n
which makes the estimated value. <\/p>\n
Forgetting about multi-column is bad in this case, probably it works better for some cases, but let’s see the facts <\/p>\n
- \n
- ‘Wokingham’ has an estimate of 31 in the histogram of the column stats [City]<\/li>\n
- ‘RG41 1QW’ has an estimate of 31 in the histogram of the multi column stats [Person_Address_PostalCode_City]<\/li>\n<\/ul>\n
\nThe average is 29 and the current is 31 just because both are directly correlated, but what if we were requesting a post code from a different city? The result would be zero, so 1,24 would be way better… Difficult to estimate correctly every time.<\/p>\nAnd finally, how things are in SQL Server 2016?<\/p>\n
\r\nALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 130 WITH ROLLBACK IMMEDIATE\r\nGO\r\nSELECT * \r\nFROM Person.Address\r\nWHERE City = N'Wokingham'\r\nAND PostalCode = N'RG41 1QW'\r\nOPTION (RECOMPILE)\r\nGO\r\n<\/pre>\n
![\"05_estimate_ce_130\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/05_estimate_CE_130.png\")
<\/a><\/p>\nAgain we have the 29.1, so the new new CE is looking at multi column stats again.<\/p>\n
\nConclusion<\/b><\/p>\nThe thing we can learn from this is that is impossible to be always right when you have to estimate the number of rows if your only resource is statistics, doesn’t matter single or multi-column, there is a set of values out there ready to defeat your logic.<\/p>\n
However I think it’s a good idea that SQL Server 2016 gets back to look into multi-column for a simple reason, these are user created stats and therefore gives us (DBA’s, DEV’s) more power over how rows are estimated.<\/p>\n
And With great power there must also come great responsibility<\/a><\/em>, if we create multi-column stats randomly, we might be hurting performance for a lot of queries that would benefit from some correlation, but imagine we have for example dynamically loaded select lists which depend on the previous, this can be a great enhancement.<\/p>\n
As always there’s no single truth. It will vary for each and every case, so test, test and test before opting for one or another.<\/p>\n
Just to wrap up, a few links for reference.<\/p>\n
- \n
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 <\/a>(White paper)<\/li>\n
- Cardinality Estimator (SQL Server 2014)<\/a><\/li>\n
- Cardinality Estimator (SQL Server 2016)<\/a><\/li>\n
- Multi-column statistics and exponential backoff<\/a><\/li>\n<\/ul>\n
\nThanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"The new cardinality estimator in SQL Server 2014 brought new and improved query performance but as always there are caveats. Let’s check a specific case to see the different behaviors for the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,21],"tags":[32,48,23,26],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/323"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=323"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/323\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Cardinality Estimator (SQL Server 2014)<\/a><\/li>\n
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 <\/a>(White paper)<\/li>\n