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.
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.
SELECT percent_complete, estimated_completion_time, * FROM sys.dm_exec_requests AS r WHERE r.session_id <> @@SPID AND r.session_id > 50
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
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.
DOC : sys.dm_exec_requests topic is inconsistent between versions