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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 14 down vote accepted
+250

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
    
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

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.