Skip to content

Instantly share code, notes, and snippets.

@BrentOzar
Created February 20, 2020 14:25
Show Gist options
  • Save BrentOzar/b112f5417447b191f82ecd052a6a5910 to your computer and use it in GitHub Desktop.
Save BrentOzar/b112f5417447b191f82ecd052a6a5910 to your computer and use it in GitHub Desktop.
Showing the (in)accuracy of sys.index_resumable_operations.
DROP TABLE IF EXISTS dbo.DiningRoomTable;
GO
CREATE TABLE dbo.DiningRoomTable (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing CHAR(1000));
INSERT INTO dbo.DiningRoomTable (Stuffing)
SELECT 'Stuff'
FROM sys.messages;
GO
CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing)
WITH (ONLINE = ON, RESUMABLE = ON);
GO
/* Run this immediately in another window: */
ALTER INDEX IX_Stuffing ON dbo.DiningRoomTable PAUSE;
GO
/* Check the percent complete: */
SELECT name, percent_complete FROM sys.index_resumable_operations;
GO
/* Double the number of rows in the table: */
INSERT INTO dbo.DiningRoomTable (Stuffing)
SELECT 'Stuff'
FROM sys.messages;
GO
/* Check the percent complete again: */
SELECT name, percent_complete FROM sys.index_resumable_operations;
GO
/* Triple 'em: */
INSERT INTO dbo.DiningRoomTable (Stuffing)
SELECT 'Stuff'
FROM sys.messages;
GO
/* Check the percent complete again: */
SELECT name, percent_complete FROM sys.index_resumable_operations;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment