So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor. I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex queries with cost-based plan choices. If the QP doesn’t have the information, then sometimes the query plans will be sub-optimal because, well, garbage-in garbage-out. While the SQL Server QP does a tremendous job at making complex plan choices compared to some of the other commercial alternatives, there are still limits on what the Optimizer can model in a reasonable amount of time. As such, there are seams where the product tends to not work as well as one would hope. This will always be true. While I suppose that will also keep me employable, it is useful to understand those limits because it will help you know where to look or, if it’s really hard, when to ask for help.<\/p>\n
The SQL Server QP knows a couple of things about the data stored in a table in the storage engine:
1. How many physical pages it uses
2. How many rows it has in it (approximately)
3. Single-column statistics over a sample of the data
4. A basic notion of column interdependance to help in estimating queries with multiple scalar predicates.<\/p>\n
From 1 and 2 it can derive the average row width. That’s useful for determining things like “how big will my sort be” if the query needs to sort. That’s a good thing – it leads to reasonable estimates for many choices in the QP.<\/p>\n
So let’s add sparse columns into the mix. Sparse columns are useful for data with lots of NULLs. Often this is a result of a non-traditional third-normal form database problem or, perhaps someone who is not a database person not really trying to make something into a database problem early enough in its lifecycle. The point is that commercial database systems have a sweet spot around handling data sets with known (and small) sets of columns that can be stored in tables. There is a TON of expressiveness available in query processors that manipulate this data because this format of data is better supported than other formats.<\/p>\n
None of this really means that your problem is going to easily fit into a nice third-normal form system. Often there are legacy or performance concerns that push an application away from that sweet spot. Over time, various technologies have tried to bridge that gap (property tables, XML, and object-relational mappings). Each of them have their own reasons to be, and I don’t want to get into them in depth in my post. I’m going to talk about how the QP deals with these from a modeling perspective.<\/p>\n
I built two examples to explore how SQL Server 2008 reasons about sparse columns. One example creates lots of traditional, nullable float columns while the other is exactly the same except that it uses the sparse attribute.<\/p>\n
A few things I learned immediately:
1. Sparse columns don’t change the maximum number of columns you can create in a table. On the surface, this seems unfortunate, since it will limit the kinds of applications that can use the feature.
2. It does seem to use less space per row. This isn’t hard, as the row format for SQL Server has a null bitmap and also needs 2 bytes per column to store the variable offset pointers.<\/p>\n
create<\/span> table<\/span> sp1(aaa int<\/span>)\r\ncreate<\/span> table<\/span> sp2(aaa int<\/span>)\r\n\r\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> (@i < 990)\r\nbegin<\/span>\r\ndeclare<\/span> @sql nvarchar<\/span>(400);\r\ndeclare<\/span> @s nvarchar<\/span>(20);\r\nset<\/span> @s = @i;\r\nset<\/span> @sql = 'alter table sp1 add col'<\/span> + @s + ' float sparse'<\/span>\r\nexec<\/span> sp_executesql<\/span> @sql\r\nset<\/span> @i=@i+1\r\nend<\/span>\r\n\r\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> (@i < 990)\r\nbegin<\/span>\r\ndeclare<\/span> @sql nvarchar<\/span>(400);\r\ndeclare<\/span> @s nvarchar<\/span>(20);\r\nset<\/span> @s = @i;\r\nset<\/span> @sql = 'alter table sp2 add col'<\/span> + @s + ' float'<\/span>\r\nexec<\/span> sp_executesql<\/span> @sql\r\nset<\/span> @i=@i+1\r\nend<\/span><\/span><\/pre>\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> @i < 20000 \r\nbegin<\/span>\r\ninsert<\/span> into<\/span> sp1(col2) values<\/span> (123.4)\r\nset<\/span> @i=@i+1\r\nend<\/span>\r\n\r\ndeclare<\/span> @i int<\/span>\r\nset<\/span> @i=0\r\nwhile<\/span> @i < 20000 \r\nbegin<\/span>\r\ninsert<\/span> into<\/span> sp2(col2) values<\/span> (123.4)\r\nset<\/span> @i=@i+1\r\nend<\/span><\/span><\/pre>\nIf we run “set statistics io on” and then run “select * from sp1” and “select * from sp2”, you’d like to see some difference in IOs:<\/p>\n
sp1:
(20000 row(s) affected)
Table ‘sp1’. Scan count 1, logical reads 86, physical reads 0, read-ahead reads 80, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
sp2:
(20000 row(s) affected)
Table ‘sp2’. Scan count 1, logical reads 20000, physical reads 1, read-ahead reads 19978, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/p>\n
Well, that’s good – the sparse format on largely sparse data saves space. We can confirm that with a quick look into the system tables:<\/p>\n
SELECT<\/span> o.name AS<\/span> table_name, au.type_desc, au.used_pages\r\nFROM<\/span> sys.allocation_units AS<\/span> au\r\n JOIN<\/span> sys.partitions AS<\/span> p ON<\/span> au.container_id = p.partition_id\r\n JOIN<\/span> sys.objects AS<\/span> o ON<\/span> p.object_id<\/span> = o.object_id<\/span>\r\nWHERE<\/span> o.name in<\/span> (N'sp1'<\/span>, N'sp2'<\/span>)<\/span><\/pre>\ntable_name type_desc used_pages
——————————————————————————————————————————– ———————————————————— ——————–
sp1 IN_ROW_DATA 87
sp1 ROW_OVERFLOW_DATA 0
sp2 IN_ROW_DATA 20001<\/p>\n
(3 row(s) affected)<\/p>\n
We’ve now confirmed that we actually do have fewer pages. This is also good.<\/p>\n
Now let’s see how far into the QP this extends. Does the QP model the costs for these two queries differently?<\/p>\n
SP1 TotalSubtreeCost: 0.08824496
SP2 TotalSubtreeCost: 14.83936<\/p>\n
And that, my friends, is a “good thing”. This means that sparse columns are going to help your complex queries when you use a table with sparse columns in it. The easiest way to implement this is to simply ignore the new feature in the QP, and obviously someone did a good job to make sure that it was costed properly. <\/p>\n
I don’t believe that there are additional statistical structures to tell the QP which things are on\/off row. This will show up in a small number of scenarios (similar to how LOB data can be on\/off row). This is outside of the model for how the QP reasons about plan cost, at least from what I’ve seen from SQL 2008 and from what was publicly said about 2005.<\/p>\n
Thanks all,<\/p>\n
Conor Cunningham<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor. I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-402","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\n
Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor. I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-23T19:35:01+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/\",\"name\":\"Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-03-23T19:35:01+00:00\",\"dateModified\":\"2008-03-23T19:35:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Hunting for SQL 2008 SPARSE Column Easter Eggs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/","og_locale":"en_US","og_type":"article","og_title":"Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham","og_description":"So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor. I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/","og_site_name":"Conor Cunningham","article_published_time":"2008-03-23T19:35:01+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/","name":"Hunting for SQL 2008 SPARSE Column Easter Eggs - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-03-23T19:35:01+00:00","dateModified":"2008-03-23T19:35:01+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/hunting-for-sql-2008-sparse-column-easter-eggs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Hunting for SQL 2008 SPARSE Column Easter Eggs"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/402","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=402"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/402\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=402"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}