Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzIndex is blocked by index rebuilds on an identity column due to bug in sys.identity_columns #2176

Open
BrentOzar opened this issue Oct 26, 2019 · 0 comments
Assignees

Comments

@BrentOzar
Copy link
Member

@BrentOzar BrentOzar commented Oct 26, 2019

Version of the script
SELECT @Version = '7.9', @VersionDate = '20191024';

What is the current behavior?
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, sp_BlitzIndex gets blocked.

If the current behavior is a bug, please provide the steps to reproduce.
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 PK_Id PRIMARY KEY CLUSTERED (Id));
GO
BEGIN TRAN
ALTER INDEX PK_Id 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.)

What is the expected behavior?
I think we're gonna have to do this, but I'm open to other ideas:

  • (Later, if this bug is ever fixed, check the SQL Server version to see if we're on a vulnerable build)
  • Check to see if there are any locks on sys.identity_columns
  • If so, log a high priority warning in the sp_BlitzIndex output (or raise an error, since some @modes like table-level output won't show warnings)
  • Skip the query with sys.identity_columns (thankfully there's only one)

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
The query on sys.identity_columns has always been in sp_BlitzIndex, but I'm not sure when this bug showed up in SQL Server. I tested on 2017 CU17, but I had a client hit this on 2014.

I reported the bug to Microsoft: https://feedback.azure.com/forums/908035-sql-server/suggestions/38897821-bug-in-sys-identity-columns-does-not-honor-read-u

@BrentOzar BrentOzar added this to the 2019-11 Release milestone Oct 26, 2019
@BrentOzar BrentOzar self-assigned this Oct 26, 2019
@BrentOzar BrentOzar removed this from the 2019-12 Release milestone Dec 1, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.