\nBackground<\/strong><\/p>\n
This far I assume, in general terms, everyone agrees that using a UNIQUEIDENTIFIER column as our clustered index key is bad, we’ve heard many times it will produce fragmentation and that’s really bad, so we tend not to although there might be some use cases (one of these days I’ll try write something).<\/p>\n
But how does this happen and how bad is it? Well, that’s the fun part, so let’s get to it.<\/p>\n
We’d need a new database and a new table with our bad practice ready to hit us.<\/p>\n I’ve just created the table and done 1000 single INSERT, since the clustered key has a pretty random value returned by NEWID(), inserts have been occurring in random places all over our table (clustered index).<\/p>\n In order to remove the fragmentation we have, I’m going to rebuild it.<\/p>\n You can see how the size of the index is now smaller because we got rid of the fragmentation. <\/p>\n So far I haven’t show you the real deal, because with a new table is difficult but since we now have a number of rows and virtually no fragmentation, we can see the effect of inserting new rows.<\/p>\n Before doing so I want to be sure all my transaction rows will stay in the log, so time for a backup.<\/p>\n Now we are ready to do another 10 (random) INSERT and see what has been going on there.<\/p>\n You can see how we have grown 9 pages, almost 1 page per row we have inserted… wow! that’s huge!<\/p>\n First and most noticeable consequence is fragmentation, but that’s not deep enough. I was performing a full plus transaction log backups to have less activity in our transaction log so I can find the 10 row I’ve just inserted easily.<\/p>\n Hopefully so far everything makes sense, so let’s check those 2 pages to see how the data is stored, to see inside any specific page we use DBCC PAGE(). In a new Query window we run:<\/p>\n We’ve seen the message ‘Moved 19 row(s) at slot 16 from page 0001:00000158 to page 0001:00000179<\/em>‘. See how row (slot) 13 is at the end of the page (offset 7901), not immediately between 12 and 14, but that’s not important as far as it is sorted correctly in the slot array, where obviously it is.<\/p>\n If we check the new page generated by the split page operation we can see how for sure we have the 19 rows moved from page 344.<\/p>\n See how there are 19… errrrr 20? OMG! hahaha… don’t loose your faith just yet! there is one extra row because since our 10 INSERT are random, probably another row happened to be inserted on this very page, see how slot 5 is also at the end of the page, so I can be certain that insert can be found later in the transaction log.<\/p>\n There you go! Another INSERT in the same page (x0179 → 377) slot 5. Boom!<\/p>\n Writing this demo has been pretty awesome for me and I have to say I needed to do this process at work to find the latest inserted rows in a table where the clustered key was not an IDENTITY value, so all these geekin’ around has some real value, internals are so cool!<\/p>\n And just to wrap up some of the concepts I have used or referenced in this post.<\/p>\n – The Clustered Index debate by Kimberly Tripp<\/a> Thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":" There has been a permanent debate about which is the right clustering key for our tables since the beginning of the time, this time I’ll get a bit deeper to show you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[35,40,5,12,25],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/316"}],"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=316"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/316\/revisions"}],"predecessor-version":[{"id":321,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/316\/revisions\/321"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=316"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=316"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nPreparing the playground<\/strong><\/p>\n\r\nCREATE DATABASE bad_cluster_key\r\nGO\r\n\r\nUSE bad_cluster_key\r\nGO\r\n\r\nCREATE TABLE dbo.BadClusterKey(\r\nID UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_BadClusterKey PRIMARY KEY DEFAULT NEWID()\r\n, Col1 CHAR(100) DEFAULT REPLICATE('A', 100)\r\n, Col2 CHAR(100) DEFAULT REPLICATE('B', 100)\r\n)\r\nGO \r\n\r\nINSERT INTO dbo.BadClusterKey DEFAULT VALUES\r\nGO 1000\r\n<\/pre>\n\r\nSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.BadClusterKey'), NULL, NULL, 'DETAILED')\r\n-- 41 pages\r\n\r\nALTER INDEX PK_BadClusterKey ON dbo.BadClusterKey REBUILD\r\n\r\nSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.BadClusterKey'), NULL, NULL, 'DETAILED')\r\n-- 29 pages\r\nGO\r\n<\/pre>\n![\"01_physical_stats\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/09\/01_physical_stats.png\")
<\/a><\/p>\n\r\nBACKUP DATABASE bad_cluster_key TO DISK = 'NUL'\r\nGO\r\nBACKUP LOG bad_cluster_key TO DISK = 'NUL'\r\nGO\r\nCHECKPOINT\r\nGO\r\n<\/pre>\n
\r\nINSERT INTO dbo.BadClusterKey DEFAULT VALUES\r\nGO 10\r\n\r\nSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.BadClusterKey'), NULL, NULL, 'DETAILED')\r\nGO\r\n-- 38\r\n<\/pre>\n<\/a><\/p>\n\r\nSELECT [Begin Time]\r\n\t\t, [Transaction ID]\r\n\t\t, [Transaction Name]\r\n\t\t, [AllocUnitName]\r\n\t\t, [Page ID]\r\n\t\t, [Slot ID]\r\n\t\t, [Parent Transaction ID]\r\n\t\t, SUSER_SNAME([Transaction SID]) AS [Login Name]\r\n\t\t, [New Split Page]\r\n\t\t, [Rows Deleted]\r\n\t\t, [Description]\r\n\t\t--, * \r\n\tFROM fn_dblog(NULL, NULL)\r\nGO\r\n<\/pre>\n
<\/a>
\nThat’s still hell of a lot of information, so I’m going to focus in a single INSERT (transaction) and to show you what I’m interested in:<\/p>\n\n
\nThere are many things to get done, hence the amount of records in the transaction log.<\/li>\n
\nAll this trouble just to get one new row inserted. If this was happening on regular basis, imagine what a waste when almost each INSERT results in a page split and then your maintenance task have to go and remove the fragmentation by rebuilding the clustered index (which is not online unless Expensive Edition) and all the log to be sent to your HA\/DR replicas… <\/p>\n
\nThe physical row<\/strong><\/p>\n\r\nUSE bad_cluster_key\r\nGO\r\n-- Moved 19 row(s) at slot 16 from page 0001:00000158 to page 0001:00000179\r\n-- We need to convert from Hexadecimal to decimal\r\nSELECT CONVERT(INT, 0x0158), CONVERT(INT, 0x0179)\r\n-- 344\t377\r\nGO\r\nDBCC TRACEON(3604)\r\nGO\r\nSELECT DB_ID()\r\n-- 12\r\nDBCC PAGE(12, 1, 344, 1)\r\n\/*\r\nOFFSET TABLE:\r\n\r\nRow - Offset \r\n16 (0x10) - 3441 (0xd71) \r\n15 (0xf) - 3218 (0xc92) \r\n14 (0xe) - 2995 (0xbb3) \r\n13 (0xd) - 7901 (0x1edd) <----------- This is our row \r\n12 (0xc) - 2772 (0xad4) \r\n11 (0xb) - 2549 (0x9f5) \r\n10 (0xa) - 2326 (0x916) \r\n9 (0x9) - 2103 (0x837) \r\n8 (0x8) - 1880 (0x758) \r\n7 (0x7) - 1657 (0x679) \r\n6 (0x6) - 1434 (0x59a) \r\n5 (0x5) - 1211 (0x4bb) \r\n4 (0x4) - 988 (0x3dc) \r\n3 (0x3) - 765 (0x2fd) \r\n2 (0x2) - 542 (0x21e) \r\n1 (0x1) - 319 (0x13f) \r\n0 (0x0) - 96 (0x60) \r\n*\/\r\n<\/pre>\n
\r\nDBCC PAGE(12, 1, 344, 3)\r\n\/*\r\nSlot 13 Column 1 Offset 0x4 Length 16 Length (physical) 16\r\n\r\nID = e75e82eb-74d6-4502-bdc1-507fe87323fb \r\n\r\nSlot 13 Column 2 Offset 0x14 Length 100 Length (physical) 100\r\n\r\nCol1 = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\r\n\r\nSlot 13 Column 3 Offset 0x78 Length 100 Length (physical) 100\r\n\r\nCol2 = BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB\r\n\r\nSlot 13 Offset 0x0 Length 0 Length (physical) 0\r\n*\/\r\n<\/pre>\n
\nSo, after moving 19 rows, 16 were left in this page (Slots 0-15) and then the row was inserted (slot 13) hence we have now 17 rows (slots 0-16).<\/p>\n\r\nDBCC PAGE(12, 1, 377, 1)\r\n\/*\r\nOFFSET TABLE:\r\n\r\nRow - Offset \r\n19 (0x13) - 4110 (0x100e) \r\n18 (0x12) - 3887 (0xf2f) \r\n17 (0x11) - 3664 (0xe50) \r\n16 (0x10) - 3441 (0xd71) \r\n15 (0xf) - 3218 (0xc92) \r\n14 (0xe) - 2995 (0xbb3) \r\n13 (0xd) - 2772 (0xad4) \r\n12 (0xc) - 2549 (0x9f5) \r\n11 (0xb) - 2326 (0x916) \r\n10 (0xa) - 2103 (0x837) \r\n9 (0x9) - 1880 (0x758) \r\n8 (0x8) - 1657 (0x679) \r\n7 (0x7) - 1434 (0x59a) \r\n6 (0x6) - 1211 (0x4bb) \r\n5 (0x5) - 4333 (0x10ed) \r\n4 (0x4) - 988 (0x3dc) \r\n3 (0x3) - 765 (0x2fd) \r\n2 (0x2) - 542 (0x21e) \r\n1 (0x1) - 319 (0x13f) \r\n0 (0x0) - 96 (0x60) \r\n \r\n*\/\r\n<\/pre>\n
<\/a><\/p>\n
\nConcepts\/Take away’s<\/strong><\/p>\n
\n– Index Fragmentation by Paul Randal<\/a>
\n– Backup to NUL by Gail Shaw<\/a>
\n– fn_dblog() by Paul Randal<\/a>
\n– DBCC PAGE by Paul Randal<\/a><\/p>\n