I was reading the post SQL Server Non-Unique Clustered Indexes Are Evil! by the Lone DB
In that post, the person created the following table
USE boo GO CREATE TABLE ClusteredIndexCrash ( i TINYINT NOT NULL ) ; CREATE CLUSTERED INDEX ClusteredIndexCrashIdx ON dbo.ClusteredIndexCrash( i ) ;
As you can see, the table has a non unique clustered index
If you were to fill this table with the same value 2147483648 times, you will get an error
So if you run this first (this will take a while believe me)
INSERT INTO dbo.ClusteredIndexCrash SELECT TOP 1000000 1 FROM syscolumns c1 , syscolumns c2 , syscolumns c3 ; GO 2146
Then you run the following statement
INSERT INTO dbo.ClusteredIndexCrash SELECT TOP 1000000 1 FROM syscolumns c1 , syscolumns c2 , syscolumns c3 ;
You would get the following error..straight from the beast himself apparently
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
I will be using DBCC PAGE and DBCC IND in this blog post, if you want to learn how to use these yourself, take a look at How to use DBCC PAGE
I decided to look in the pages of SQL Server itself. I grabbed all the pages by running DBCC IND
DBCC IND ('boo','ClusteredIndexCrash', 1);
That returned 4806619 rows, these are all the pages that exist for this table
Here is part of the output that matters, the highlighted row where NextPageFID is 0 is what we want, this is the last page.
Click on the image for a larger view
Then I grabbed the last page id from that result set and looked at the page by executing these commands
DBCC TRACEON (3604); GO DBCC PAGE (boo, 1, 4810967, 3) with TABLERESULTS;
Here is part of the output in text, I highlighted the uniqifier for you
PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Type PRIMARY_RECORD
PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Attributes NULL_BITMAP VARIABLE_COLUMNS
PAGE HEADER: Slot 158 Offset 0xa40 Length 16 Record Size 16
Slot 158 Offset 0xa40 Length 16 Memory Dump @0x00000000E832AA40 0000000000000000: 30000500 01020000 01001000 fdffff7f 0...........ýÿÿ.
Slot 158 Offset 0xa40 Length 16 Slot 158 Column 0 Offset 0xc Length 4 Length (physical) 4 UNIQUIFIER 2147483645
Slot 158 Offset 0xa40 Length 16 Slot 158 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1
Slot 158 Offset 0xa40 Length 16 Slot 158 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (5eeffbfe6491)
PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Type PRIMARY_RECORD
PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Attributes NULL_BITMAP VARIABLE_COLUMNS
PAGE HEADER: Slot 159 Offset 0xa50 Length 16 Record Size 16
Slot 159 Offset 0xa50 Length 16 Memory Dump @0x00000000E832AA50 0000000000000000: 30000500 01020000 01001000 feffff7f 0...........þÿÿ.
Slot 159 Offset 0xa50 Length 16 Slot 159 Column 0 Offset 0xc Length 4 Length (physical) 4 UNIQUIFIER 2147483646
Slot 159 Offset 0xa50 Length 16 Slot 159 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1
Slot 159 Offset 0xa50 Length 16 Slot 159 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (bedbd571a02d)
Here is the same output as an image from SSMS
Click on the image for a larger view
I decided to play around with this now that I had the table maxed out
What would happen if I deleted a bunch of rows? Would I still get the same error? I deleted 100 rows
Now I tried to insert 1 row..
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
So same issue, doesn't reuse uniqifiers
Would an index rebuild fix this?
-- 20 minutes later.......
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039566336. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
Nope, nothing changed
What about a truncate?
Now when I run DBCC IND...
This returns nothing.. that is fine..after all this table is empty
Let's try adding a row
No problem, error is gone
I can now again use DBCC IND like before
But since I know I only have 1 row now, I can also use the sys.fn_PhysLocCracker function together with the %%physloc%% virtual column, it will actually give me the page id the row is located on
Here is the query
This is the output from that query
i file_id page_id slot_id PhysicalLocationOfRow
1 1 7668633 0 0x9903750001000000
Running the DBCC PAGE command again with the pageid from the query above
And here is the output from DBCC PAGE
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Type PRIMARY_RECORD
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Attributes NULL_BITMAP
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Size 9
Slot 0 Offset 0x60 Length 9 Memory Dump @0x00000000C41EA060 0000000000000000: 10000500 01020000 01 .........
Slot 0 Offset 0x60 Length 9 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER 0
Slot 0 Offset 0x60 Length 9 Slot 0 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1
Slot 0 Offset 0x60 Length 9 Slot 0 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (fa469958a30a)
Here it is also as an image
Click on the image for a larger view
As you can see the uniqifier is now 0
So it looks like the uniqifier behaves like an identity property per value, a truncate will reseed it back to 0 but when you delete values uniqifiers are not reused
So maybe we would need a DBCC CHECKUNIQIFIER command :-)
So how important is this..will you ever run into this? I can see this becoming an issue if you have a staging table which you don't drop or truncate but you constantly insert and delete data from it
Maybe you should drop the table once in a while?
Have you ever maxed out the uniqifier?
Click on the image for a larger view
I decided to play around with this now that I had the table maxed out
What would happen if I deleted a bunch of rows? Would I still get the same error? I deleted 100 rows
DELETE TOP (100) FROM ClusteredIndexCrash
Now I tried to insert 1 row..
INSERT INTO dbo.ClusteredIndexCrash SELECT 1
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
So same issue, doesn't reuse uniqifiers
Would an index rebuild fix this?
ALTER INDEX ClusteredIndexCrashIdx ON ClusteredIndexCrash REBUILD
-- 20 minutes later.......
INSERT INTO dbo.ClusteredIndexCrash SELECT 1
Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039566336. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.
Nope, nothing changed
What about a truncate?
TRUNCATE TABLE ClusteredIndexCrash
Now when I run DBCC IND...
DBCC IND ('boo','ClusteredIndexCrash', 1);
This returns nothing.. that is fine..after all this table is empty
Let's try adding a row
INSERT INTO dbo.ClusteredIndexCrash SELECT 1
No problem, error is gone
I can now again use DBCC IND like before
DBCC IND ('boo','ClusteredIndexCrash', 1);
But since I know I only have 1 row now, I can also use the sys.fn_PhysLocCracker function together with the %%physloc%% virtual column, it will actually give me the page id the row is located on
Here is the query
SELECT top 10 *,%%physloc%% AS PhysicalLocationOfRow FROM ClusteredIndexCrash CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
This is the output from that query
i file_id page_id slot_id PhysicalLocationOfRow
1 1 7668633 0 0x9903750001000000
Running the DBCC PAGE command again with the pageid from the query above
DBCC TRACEON (3604); GO DBCC PAGE (boo, 1, 7668633, 3) with TABLERESULTS;
And here is the output from DBCC PAGE
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Type PRIMARY_RECORD
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Attributes NULL_BITMAP
PAGE HEADER: Slot 0 Offset 0x60 Length 9 Record Size 9
Slot 0 Offset 0x60 Length 9 Memory Dump @0x00000000C41EA060 0000000000000000: 10000500 01020000 01 .........
Slot 0 Offset 0x60 Length 9 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER 0
Slot 0 Offset 0x60 Length 9 Slot 0 Column 1 Offset 0x4 Length 1 Length (physical) 1 i 1
Slot 0 Offset 0x60 Length 9 Slot 0 Offset 0x0 Length 0 Length (physical) 0 KeyHashValue (fa469958a30a)
Here it is also as an image
Click on the image for a larger view
So it looks like the uniqifier behaves like an identity property per value, a truncate will reseed it back to 0 but when you delete values uniqifiers are not reused
So maybe we would need a DBCC CHECKUNIQIFIER command :-)
So how important is this..will you ever run into this? I can see this becoming an issue if you have a staging table which you don't drop or truncate but you constantly insert and delete data from it
Maybe you should drop the table once in a while?
Have you ever maxed out the uniqifier?
