Blog Post

Core Infrastructure and Security Blog
3 MIN READ

CREATE INDEX Monitoring Progress

Carlos Lopez's avatar
Carlos Lopez
Icon for Microsoft rankMicrosoft
Mar 15, 2019
First published on MSDN on Dec 22, 2016 One of the challenges for DBA's are to work on heavy load and critical systems where maintenance windows are usually very short, recently I was asked for a...
Updated Apr 28, 2020
Version 4.0
CarlosLopez
tomfahlberg's avatar
tomfahlberg
Copper Contributor
Oct 04, 2021

We had some challenges with this.  Enabling statistic profiling worked, in the sense that we could see the progress of the index creation in dm_exec_query_profiles.

However, index creations started failing randomly.  Sql Server crashed occasionally, but even worse was when the PK creation failed silently.  It happened with both clustered and nonclustered indexes, but happened most frequently on big tables.  As soon as I removed the statistics profiling everything returned to normal.

 

Select @@VERSION:

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64)
Aug 20 2020 22:33:27
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Linux (Amazon Linux 2)

(1 row affected)


Completion time: 2021-10-04T17:02:26.5187593+00:00