Having fun with maxed out uniqifiers on a non unique clustered index

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

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

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?