Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Strangely, my stored procedure started to receive Msg 666 for some input data.

Stored procedure fails on the last step when it tries to insert a row into a table with the following structure:

Columns:
A_Id: PK, int
B_Id: PK, FK, int
C_Id: PK, FK, int
D_Id: PK, smallint 

This is essentially a table that connects all referenced entities together.

Indexes:
IX_TableName_D_id - Clustered index on D_id column
PK_TableName - Unique non-clustered index on all columns (A_Id, B_Id, C_Id, D_Id)

Fragmentation for both indexes is low (<25%). However PK_TableName fragmentation quickly grows, since the amount of operation on the table is quite intense.

Table size:

Row count: ~80,000,000 rows

So, when I try to run a veeery simple query, for some of D_Id's I get the following message:

Msg 666. The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422223771074560. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Query example:

INSERT INTO TableName
(A_Id,B_Id,C_Id,D_id)
VALUES (1,1,1,14)

For example, when I set D_Id to some values - it fails, '14' for example. If I set D_ID to other values (1,2,3,...13, 15,16,...), the query runs fine.

I suspect there's something really bad going on with indexes... But I cannot get to the bottom of this... :( Why it fails?

share|improve this question
    
'script' the output of the tables in question and add the output to your post, please! –  Max Vernon Sep 6 '13 at 20:58
    
@SomeOne What version of SQL Server are you running ? Run Select @@version –  Kin Sep 6 '13 at 21:02
3  
Have you tried rebuilding the index? Perhaps after many inserts and deletes the uniqueifier is up to max int. –  Martin Smith Sep 6 '13 at 21:10
    
Did you perform DBCC CHECKTABLE? Did you try dropping and re-creating the indexes? Have you deleted billions of rows from this table? –  Aaron Bertrand Sep 6 '13 at 21:11
3  
Type of data is irrelevant. If you insert and delete 2 billion+ duplicates of the value "14" it doesn't matter if that's a string, a tinyint, a smallint, bigint, etc. The problem is the number of uniqueifiers generated. –  Aaron Bertrand Sep 8 '13 at 15:38

1 Answer 1

up vote 12 down vote accepted

The low selectivity issue mentioned by Remus is not sufficient on its own to cause the problem on that size table.

The uniqueifier starts at 1 and can go up to 2,147,483,646 before actually overflowing the range.

It also requires the right pattern of repeated deletes and inserts to see the issue.

CREATE TABLE T
(
X SMALLINT,
Y INT IDENTITY PRIMARY KEY NONCLUSTERED
)

CREATE CLUSTERED INDEX IX ON T(X)

INSERT INTO T VALUES (1),(1),(1),(2),(2)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 2 |           1 |
| 1 | 3 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Then running

DELETE FROM T 
WHERE Y IN (2,3)

INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 6 |           3 |
| 1 | 7 |           4 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing in that case the uniqueifier did not reuse the values from the deleted rows.

However then running

DELETE FROM T 
WHERE Y IN (6,7)
WAITFOR DELAY '00:00:10'
INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 8 |           1 |
| 1 | 9 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing that the high water mark can be reset after deleting the duplicate with the highest uniqueifier value. The delay was to allow the ghost record cleanup process to run.

Because life is too short to insert 2 billion duplicates I then used DBCC WRITEPAGE to manually adjust the highest uniqueifier to 2,147,483,644

enter image description here

I then ran

INSERT INTO T VALUES (1)

multiple times. It succeeded twice and failed on the third attempt with error 666.

This was actually one lower than I would have assumed. Meaning that the highest uniqueifier inserted was 2,147,483,646 rather than the maximum int size of 2,147,483,647

share|improve this answer
2  
I like this answer more because it provides more accurately the reasons of the failure: Combination of "CI with low selectivity" + "highly skewed distribution (in the CI)" + "intensive insert/delete workload in general (or at least one CI value)" –  ypercube Sep 8 '13 at 13:17
    
For informational purposes, can you check if TRUNCATE TABLE resets the uniqueifier? –  Jon Seigel Sep 8 '13 at 15:37
    
@JonSeigel - Yes, seems to. After running INSERT INTO T VALUES (1),(1),(1),(2),(2);TRUNCATE TABLE T;INSERT INTO T VALUES (1),(1),(1),(2),(2) then the highest uniqueifier is 2 I presume it looks at the highest uniqueifier that already exists for that key (including ghost records) –  Martin Smith Sep 8 '13 at 15:51
    
Based on this table design, the distinct values in D_id almost never change (there always will be at least some rows with "14", "15" and so on as per example). However, the delete/insert activity is very intense and single row is short-lived. This both factors can explain why the uniquefier were not reset. I'm thinking on changing the clustered index to other column which is more relevant to be a clustered index (per clustered index guidelines) and won't suffer from uniquefier exhaustion. –  Some One Sep 8 '13 at 16:06
    
@MartinSmith +1 and excellent explanation ! –  Kin Sep 8 '13 at 16:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.