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.
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
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
