December 17, 2018 at 12:11 pm
None of those indexes match the name in the error message.
What is the DDL for index IX_Payment_PurchaseId?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 12:25 pm
ScottPletcher - Monday, December 17, 2018 12:11 PMNone of those indexes match the name in the error message.What is the DDL for index IX_Payment_PurchaseId?
Sorry for the mistype. I have corrected the index name in the original post to match the name in the error message.
Here it is again:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:30 pm
I have confirmed with the ETL developers that they are using READ COMMITTED ISOLATION on both remote source database and target database.
The version of the source database server is as follows:
Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) Sep 23 2016 16:56:29
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:31 pm
Eric M Russell - Monday, December 17, 2018 12:25 PMScottPletcher - Monday, December 17, 2018 12:11 PMNone of those indexes match the name in the error message.What is the DDL for index IX_Payment_PurchaseId?
Sorry for the mistype. I have corrected the index name in the original post to match the name in the error message.
Here it is again:
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
Interesting. I take it those 4 columns are not unique, it takes the "uniquifier" -- in this case 32999 -- to make them unique. If it's possible you might be deleting all the old rows with those leading 4 keys, I'm guessing that SQL for whatever reason lost the "ghost row" it sometimes seems to use to keep track of the uniquifier, and thuse ended up reusing a value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 12:50 pm
I'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.
Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)
I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 12:57 pm
Eric M Russell - Monday, December 17, 2018 12:50 PMI'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
As I stated, I think it's the uniquifier for that index, and that somehow SQL is reusing a uniquifier (which of course it should never do, hence it clearly seems to be a bug; I was just guessing the cause of the bug, but purely a guess).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2018 at 1:10 pm
ScottPletcher - Monday, December 17, 2018 12:57 PMEric M Russell - Monday, December 17, 2018 12:50 PMI'm not on the ETL team, so I can't vouch for the specifics of why the table / indexes were modeled the way they are. However, it appears the index IX_Payment_PurchaseId is intended to query all payments made against a purchase. It should not be unique, and it isn't declared as unique.Actually, the column values cited in the error don't line up the key columns in the index. I would say that the error message is citing the wrong index name, but the duplicate value combination doesn't appear to line up with the primary key or unique index either. I'm not sure what column that 5th value 32999 is aligned with.
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)The duplicate key value is
(2999
, C1234
, 2018-12-13
, 2018-12-14
, 32999)I've run DBCC CHECKDB several times the past couple of months, and it does not indicate any corruption. Also, the error message occurs sporatically, once a week or every couple of weeks in all three environments, and the environments are loaded independently (not restored from production).
As I stated, I think it's the uniquifier for that index, and that somehow SQL is reusing a uniquifier (which of course it should never do, hence it clearly seems to be a bug; I was just guessing the cause of the bug, but purely a guess).
OK, I see your point now.
My understanding is that: Every row in the leaf level of a b-tree index should uniquely point to one row in the table, even for logically non-unique indexes. SQL Server does this by adding a uniquifier, and this mystery 5th value of 32999 is the uniquifier. Your theory regarding the duplicate key violation is that SQL Server is somehow duplicating the uniquifier?
That might be the case, but for non-unique non-clustered indexes, I would think the uniquifier would be the actual row id coming from the table. It may be time to get Microsoft PSS involved on this one.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 17, 2018 at 1:20 pm
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 7:35 am
Eric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
December 18, 2018 at 10:26 am
RAThor - Tuesday, December 18, 2018 7:35 AMEric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
I've scripted out the table and confirmed no triggers, change tracking, CDC, FK constraints, function based defaults, function based check constraints, or computed columns. It's just a very large (100+ GB) table with basic column design. I suspect it's the DML against the table that's tricky. I'm currently looking at Query Store to try and isolate which operation was running against this specific table around the time the error occurred.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 11:18 am
Eric M Russell - Tuesday, December 18, 2018 10:26 AMRAThor - Tuesday, December 18, 2018 7:35 AMEric M Russell - Monday, December 17, 2018 12:05 PMCannot insert duplicate key row in object 'Payment' with unique index 'IX_Payment_PurchaseId'. The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).Do you, perchance, have an insert trigger on Payment, and a "Payment" audit table also having an "IX_Payment_PurchaseId" index?
I've scripted out the table and confirmed no triggers, change tracking, CDC, FK constraints, function based defaults, function based check constraints, or computed columns. It's just a very large (100+ GB) table with basic column design. I suspect it's the DML against the table that's tricky. I'm currently looking at Query Store to try and isolate which operation was running against this specific table around the time the error occurred.
can you also post the DDL for index IX_Payment_PurchaseId on Table Payment - not the one you posted before as it is a different table
December 18, 2018 at 11:54 am
frederico_fonseca - Tuesday, December 18, 2018 11:18 AMcan you also post the DDL for index IX_Payment_PurchaseId on Table Payment - not the one you posted before as it is a different table
I'm having the obscure the actual object names. The index cited in the error is as originally posted, although I posted it with the wrong table name originally.
Cannot insert duplicate key row in object 'PurchasePayment' with unique index 'IX_Payment_PurchaseId'.
The duplicate key value is (2999, C1234, 2018-12-13, 2018-12-14, 32999).
CREATE NONCLUSTERED INDEX [IX_Payment_PurchaseId] ON [PurchasePayment]
(
[PurchaseId] ASC,
[StoreCode] ASC,
[RowStartDate] ASC,
[RowEndDate] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE)
ON [ps_RowEndDate] ( [RowEndDate] )
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2018 at 12:34 pm
Check for other dependencies on the table. I'm thinking that someone may have snuck in an indexed view or synonym on you. I can't imagine why it would cause a problem but you might also want to check to see if any form of auditing has been enabled.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2018 at 2:56 pm
Just curious - but is it possible the PAGE compression on the index is contributing to this issue?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 18, 2018 at 3:20 pm
Jeffrey Williams 3188 - Tuesday, December 18, 2018 2:56 PMJust curious - but is it possible the PAGE compression on the index is contributing to this issue?
Is there a known issue with page compression and false key violation errors?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply