I've just had to debug an issue which has me thoroughly confused.
An ETL Transform process on our Development Data warehouse has just failed after working successfully every day for months. The same SSIS job calling the same stored proc, with the same table schema, indexes and data works fine in production.
This step would normally take less than 2 min. Today, after 4 hours the job hasn't completed, but hasn't failed either. There are no reported errors. Nothing in the SQL log, and sp_who2
doesn't show anything blocking.
- Here is a link to the estimated plan when the query performs well.
- Here is a link to the estimated plan when the query does not finish.
The job truncates a staging table then inserts about 600,000 rows of data. The ETL process has exclusive access to the table. When I checked, all I could see was waits on CXPACKET
.
I have traced the fault to a unique clustered index.
The table has a non clustered primary key on an identity column (see below)
CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
[ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
[TransactionId] [varchar](255) NOT NULL,
[KeyType] [varchar](255) NOT NULL,
[FinancialYear] [varchar](255) NOT NULL,
[Job] [varchar](255) NOT NULL,
[Subjob] [varchar](255) NOT NULL,
[AnalysisCode] [varchar](255) NULL,
[etc] [varchar](255) NOT NULL,
[etc] [varchar](255) NOT NULL,
[etc] [varchar](255) NOT NULL
CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED
(
[ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The problem clustered index is:
CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
( [FinancialYear] ASC,
[KeyType] ASC,
[TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
There is a 3rd non-clustered index which doesn't affect the insert at all.
Two of us have been working this for 4 hours. I have dropped and re-added the Index 20-30 times trying options in different combinations.
Summary: Clustered index blocks inserts. Non clustered works fine.
We have tried:
- Server restart made no difference.
- Tables truncated and sp run manually from SSMS no diff. (With SA privs)
- Rebuilding the index didn't help.
- If I drop the clustered index the insert works.
- After insert above, I can add the clustered index without error.
- If drop and re-add the index as non-clustered it works.
- I have checked the data and it is unique grouped by those 3 fields.
- Changing index so that its not unique didn't make any difference.
- Adding/removing
with tablock
hint didn't help. - I tried sorting the data before inserting and it made no difference.
Running: Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3
Any ideas or suggestions would be greatly appreciated.