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