Bug in sys.identity_columns: does not honor read uncommitted, causes blocking
There's a bug in sys.identity_columns: it ignores requests for read uncommitted. This means if someone's rebuilding a large index on an identity column, queries get blocked.
To reproduce it, create a table with a clustered index on an identity column, and rebuild it:
CREATE TABLE dbo.Test (Id INT IDENTITY(1,1), CONSTRAINT PKId PRIMARY KEY CLUSTERED (Id));
GO
BEGIN TRAN
ALTER INDEX PKId ON dbo.Test REBUILD WITH (ONLINE = OFF);
GO
Then in another window, try to query sys.identity_columns with dirty reads:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM sys.identity_columns WITH (NOLOCK);
(I know, those two isolation level hints are redundant, just being clear that nothing you ask for will get you past the blocking on sys.identity_columns.) Reproduced on SQL Server 2017 CU 17 (14.0.3238.1.)
Brent Ozar
shared this idea
1 comment
-
Anonymous
commented
1 year on and no comment from anyone at Microsoft!