{"id":1027,"date":"2008-08-20T19:11:02","date_gmt":"2008-08-20T19:11:02","guid":{"rendered":"\/blogs\/paul\/post\/Can-GUID-cluster-keys-cause-non-clustered-index-fragmentation.aspx"},"modified":"2008-08-20T19:11:02","modified_gmt":"2008-08-20T19:11:02","slug":"can-guid-cluster-keys-cause-non-clustered-index-fragmentation","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/","title":{"rendered":"Can GUID cluster keys cause non-clustered index fragmentation?"},"content":{"rendered":"


\n

At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID<\/FONT>) that are also unique. Their uniqueness is what makes them attractive to many developers as a key value, without understanding the havoc they can cause in production in terms of fragmentation and poor query performance.<\/P>
\n

A GUID key causes fragmentation because of its randomness. The insertion point of a new record in an index is dictated by the value of the index key, so if the key value is random, so is the insertion point. This means that if an index page is full, a random insert that happens to have to go onto that page will cause a page split to make room for the new record. A page-split is where a new page is allocated and (as near as possible to) half the rows from the splitting page are moved to the new page. The new row is then inserted into one of the two pages, determined by the key value. Usually the newly allocated page is not physically contiguous to the splitting page, and so fragmentation has been caused. In this case *two* kinds of fragmentation have been caused – logical fragmentation (where the next logical page as determined by the index order is not the next physical page in the data file) and physical (or internal) fragmentation (where space is being wasted on index pages). These can both affect query performance (topic for a later post), as well as the expense of having to do the page split in the first place.<\/P>
\n

It’s fairly well known that GUIDs can cause fragmentation in the index where the GUID is the key (e.g. a clustered index), but not about the knock-on effects in non-clustered indexes. Here’s an example – I’ll create two clustered indexes with GUID keys (one generated from NEWID<\/FONT> and one from NEWSEQUENTIALID<\/FONT>), plus a non-clustered index on each. Let’s see what happens when we insert 100000 rows:<\/P>
\n


\n

— Create a table with a GUID key
<\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> BadKeyTable <\/FONT>(
   <\/FONT><\/FONT>c1 <\/FONT>UNIQUEIDENTIFIER<\/FONT> DEFAULT<\/FONT> NEWID<\/FONT> ()<\/FONT> ROWGUIDCOL<\/FONT>,
   <\/FONT><\/FONT>c2 <\/FONT>DATETIME<\/FONT> DEFAULT<\/FONT> GETDATE<\/FONT> (),
   <\/FONT><\/FONT>c3 <\/FONT>CHAR<\/FONT> (<\/FONT>400)<\/FONT> DEFAULT<\/FONT> ‘a’<\/FONT>);
<\/FONT><\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>CLUSTERED<\/FONT> <\/FONT>INDEX<\/FONT> BadKeyTable_CL <\/FONT>ON <\/FONT><\/FONT>BadKeyTable <\/FONT>(<\/FONT>c1);<\/FONT><\/FONT>
<\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>NONCLUSTERED<\/FONT> <\/FONT>INDEX<\/FONT> BadKeyTable_NCL <\/FONT>ON <\/FONT><\/FONT>BadKeyTable <\/FONT>(<\/FONT>c2);
<\/FONT><\/FONT>GO<\/P><\/FONT>
\n

— Create another one, but using <\/FONT>NEWSEQUENTIALID instead
<\/FONT><\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> BadKeyTable2 <\/FONT>(
   <\/FONT><\/FONT>c1 <\/FONT>UNIQUEIDENTIFIER<\/FONT> DEFAULT<\/FONT> NEWSEQUENTIALID<\/FONT> ()<\/FONT> ROWGUIDCOL<\/FONT>,
   <\/FONT><\/FONT>c2 <\/FONT>DATETIME<\/FONT> DEFAULT<\/FONT> GETDATE<\/FONT> (),
   <\/FONT><\/FONT>c3 <\/FONT>CHAR<\/FONT> (<\/FONT>400)<\/FONT> DEFAULT<\/FONT> ‘a’<\/FONT>);
<\/FONT><\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>CLUSTERED<\/FONT> <\/FONT>INDEX<\/FONT> BadKeyTable2_CL <\/FONT>ON <\/FONT><\/FONT>BadKeyTable2 <\/FONT>(<\/FONT>c1);<\/FONT><\/FONT>
<\/FONT>CREATE<\/FONT><\/FONT> <\/FONT>NONCLUSTERED<\/FONT> <\/FONT>INDEX<\/FONT> BadKeyTable2_NCL <\/FONT>ON <\/FONT><\/FONT>BadKeyTable2 <\/FONT>(<\/FONT>c2);
<\/FONT><\/FONT>GO<\/P><\/FONT>
\n

DECLARE<\/FONT><\/FONT> @a <\/FONT>INT<\/FONT>;
<\/FONT><\/FONT>SELECT<\/FONT><\/FONT> @a <\/FONT>=<\/FONT> 1<\/FONT>;
<\/FONT><\/FONT>WHILE<\/FONT><\/FONT> <\/FONT>(<\/FONT>@a <\/FONT><<\/FONT> 10000<\/FONT>)
<\/FONT><\/FONT>BEGIN
   <\/FONT>INSERT<\/FONT> INTO<\/FONT> BadKeyTable DEFAULT<\/FONT> VALUES<\/FONT>;
   <\/FONT><\/FONT>INSERT<\/FONT> INTO<\/FONT> BadKeyTable2 DEFAULT<\/FONT> VALUES<\/FONT>;
   <\/FONT><\/FONT>SELECT<\/FONT> @a =<\/FONT> @a +<\/FONT> 1;
<\/FONT><\/FONT>END<\/FONT><\/FONT>;
<\/FONT><\/FONT>GO<\/FONT><\/P>
\n

— And now check for fragmentation
<\/FONT>SELECT
   <\/FONT>OBJECT_NAME<\/FONT> (<\/FONT>ips.<\/FONT>[object_id])<\/FONT> AS<\/FONT> ‘Object Name’<\/FONT>,
   <\/FONT>si.<\/FONT>name AS<\/FONT> ‘Index Name’<\/FONT>,
   ROUND<\/FONT> (<\/FONT>ips.<\/FONT>avg_fragmentation_in_percent,<\/FONT> 2)<\/FONT> AS<\/FONT> ‘Fragmentation’<\/FONT>,
   <\/FONT>ips.<\/FONT>page_count AS<\/FONT> ‘Pages’<\/FONT>,
   <\/FONT>ROUND<\/FONT> (<\/FONT>ips.<\/FONT>avg_page_space_used_in_percent,<\/FONT> 2)<\/FONT> AS<\/FONT> ‘Page Density’
<\/FONT><\/FONT>FROM<\/FONT> sys.dm_db_index_physical_stats <\/FONT>(<\/FONT>DB_ID<\/FONT> (<\/FONT>‘DBMaint2008’<\/FONT>), <\/FONT>NULL, <\/FONT>NULL, <\/FONT>NULL, <\/FONT>‘DETAILED’<\/FONT>)<\/FONT> ips
CROSS<\/FONT> <\/FONT>APPLY<\/FONT> <\/FONT>sys.indexes<\/FONT> si
<\/FONT>WHERE
   <\/FONT>si.<\/FONT>object_id<\/FONT> =<\/FONT> ips.<\/FONT>object_id
   <\/FONT>AND<\/FONT> si.<\/FONT>index_id =<\/FONT> ips.<\/FONT>index_id
   AND<\/FONT> ips.<\/FONT>index_level =<\/FONT> 0;<\/FONT>
<\/FONT>GO<\/FONT><\/FONT><\/P>
\n

Object Name    Index Name        Fragmentation  Pages  Page Density
————-  —————-  ————-  —–  ————
BadKeyTable    BadKeyTable_CL    99.13          8092   66.08
BadKeyTable    BadKeyTable_NCL   30.97          78     64.1
BadKeyTable2   BadKeyTable2_CL   0.83           5556   96.26
BadKeyTable2   BadKeyTable2_NCL  1.88           372    99.61<\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
\n

The BadKeyTable_CL<\/FONT> clustered index with the GUID key generated from NEWID<\/FONT> is almost perfectly fragmented, with 34% space being wasted on each page. Conversely, the BadKeyTable2_CL<\/FONT> clustered index with the GUID key generated from NEWSEQUENTIALID<\/FONT> is hardly fragmented and only 4% of free space is wasted (and this is just because of the row size chosen). These numbers are entirely expected given the nature of the cluster keys.<\/FONT><\/FONT><\/FONT><\/P>
\n

Now look at the non-clustered indexes. BadKeyTable_NCL<\/FONT> is 31% fragmented with 36% space wasted on each page! BadKeyTable2_NCL<\/FONT> is harldy fragmented with no free space wasted on each page. So what’s going on? The non-clustered index key in both cases is a datetime column, which has a minimum granularity of 3 milliseconds. The code above runs in a tight loop inserting records and so can insert more than one record per 3ms time interval – and I allow this because I didn’t create make the non-clustered indexes unique. For all the records inserted in one 3ms time interval, there *has* to be something that makes the non-clustered index key unique internally (as even though an index can be defined as non-unique, the Storage Engine requires that each record really is unique in an index and will add whatever it needs to so that happens).<\/FONT><\/FONT><\/FONT><\/P>
\n

In this case, the clustered index key (which must be present in the non-clustered index anyway) is used to differentiate between all the non-clustered index records with the same datetime value. For BadKeyTable_NCL<\/FONT>, the cluster key is a random GUID, so the non-clustered index record insertion points ALSO become random within each 3ms time interval – leading to the fragmentation above. The BadKeyTable2_NCL<\/FONT> non-clustered index has the same time interval issue, but it’s cluster key is a sequential GUID, so the non-clustered index doesn’t get fragmented. If the non-clustered index key was a time datatype with a larger minimum granularity (like smalldatetime, or the new date), the fragmentation of BadKeyTable_NCL<\/FONT> would be even worse – try it for yourself and you’ll see.<\/FONT><\/FONT><\/FONT><\/P>
\n

So the answer to the question in the blog post title is really – it depends! Under the right conditions, a GUID cluster key can also seriously fragment a non-clustered index as well.<\/FONT><\/P><\/FONT><\/FONT><\/FONT><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,42,47,66],"tags":[],"class_list":["post-1027","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-fragmentation","category-indexes-from-every-angle","category-performance-tuning"],"yoast_head":"\nCan GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal<\/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\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-08-20T19:11:02+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/\",\"name\":\"Can GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-08-20T19:11:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can GUID cluster keys cause non-clustered index fragmentation?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Can GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal","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\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/","og_locale":"en_US","og_type":"article","og_title":"Can GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal","og_description":"At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/","og_site_name":"Paul S. Randal","article_published_time":"2008-08-20T19:11:02+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/","name":"Can GUID cluster keys cause non-clustered index fragmentation? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-08-20T19:11:02+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/can-guid-cluster-keys-cause-non-clustered-index-fragmentation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Can GUID cluster keys cause non-clustered index fragmentation?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1027","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1027"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}