SQL Server Create Index – sys.dm_exec_requests Percent Completed

I had an observation today in regards to creating an index that you may find interesting.  Per SQL Server 2008 (not R2) documentation for sys.dm_exec_requests it mentions that percent_completed works for the following commands:

  • ALTER INDEX REORGANIZE
  • AUTO_SHRINK option with ALTER DATABASE
  • BACKUP DATABASE
  • CREATE INDEX
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • KILL (Transact-SQL)
  • RESTORE DATABASE,
  • UPDATE STATISTICS.

sys.dm_exec_requests (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177648(v=sql.100).aspx

I was creating an index and I wanted to know how much time was remaining until the index creation finished.

So I executed the following query. I constantly ran this query, but percent_complete never changed from 0.

This seemed odd. Based on SQL Server documentation percent_completed should have been provided for Create Index.

Here’s an individual that talks about the discrepancies for sys.dm_exec_requests
Differences in documentation for sys.dm_exec_requests
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/07/differences-in-documentation-for-sys-dm-exec-requests.aspx

SQL Server documentation for 2008 R2 and 2012 does not include Create Index for percent_complete.

The conclusion here is that for sys.dm_exec_requests, percent_complete is not provided for Create Index.  And I’m not sure if it ever was supported.

Other links
DOC : sys.dm_exec_requests topic is inconsistent between versions
http://connect.microsoft.com/SQLServer/feedback/details/641790/doc-sys-dm-exec-requests-topic-is-inconsistent-between-versions

Leave a Reply

Your email address will not be published. Required fields are marked *