SQLBlog.org

archives disclaimers
archives shortcuts bad habits
disclaimers
SQLBlog Home
bad habits
shortcuts
MERGE NOLOCK dates dynamic SQL
splitting SQL Server 2022 how to ask

Differences in documentation for sys.dm_exec_requests

Aaron Bertrand SQL Server 3

I've already complained about this on Connect (see #641790), but I just wanted to point out that if you're trying to make sense of the sys.dm_exec_requests document and what it lists as the commands supported by the percent_complete column, you should check which version of the documentation you're reading.  I noticed the following discrepancies.  I can't explain why certain operations are missing, except that the Denali topic was generated from the 2008 topic (or maybe from the 2008 R2 topic before it was updated).  The main problem here is that it is not clear which document to trust… if you're creating an index, for example, and it has been running for 20 minutes but shows 0 percent complete, is it really 0 percent complete?  I don't think the answer is different whether you are running SQL Server 2008 or SQL Server 2008 R2, and the documentation should be accurately reflecting that.

SQL Server 2008 SQL Server 2008 R2 SQL Server Denali
ALTER INDEX REORGANIZE ALTER INDEX REORGANIZE ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE AUTO_SHRINK option with ALTER DATABASE AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
CREATE INDEX NO CREATE INDEX HERE CREATE INDEX
DBCC CHECKDB DBCC CHECKDB DBCC CHECKDB
DBCC CHECKFILEGROUP DBCC CHECKFILEGROUP DBCC CHECKFILEGROUP
DBCC CHECKTABLE DBCC CHECKTABLE DBCC CHECKTABLE
DBCC INDEXDEFRAG DBCC INDEXDEFRAG DBCC INDEXDEFRAG
DBCC SHRINKDATABASE DBCC SHRINKDATABASE DBCC SHRINKDATABASE
DBCC SHRINKFILE DBCC SHRINKFILE DBCC SHRINKFILE
KILL (Transact-SQL) NO KILL HERE KILL (Transact-SQL)
NO RECOVERY HERE RECOVERY NO RECOVERY HERE
RESTORE DATABASE RESTORE DATABASE RESTORE DATABASE
NO ROLLBACK HERE ROLLBACK NO ROLLBACK HERE
NO TDE ENCRYPTION HERE TDE ENCRYPTION NO TDE ENCRYPTION HERE
UPDATE STATISTICS NO UPDATE STATISTICS HERE UPDATE STATISTICS

The 2005 version of Books Online gets a free pass.  I complained about this on Connect, and while they have certainly fixed the issue for 2008+, the 2005 docs still show no commands and have a weirdly formatted note box:

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him.

3 Responses

  1. AaronBertrand says:
    October 29, 2011 at 9:13 PM

    John, do you mean you are running UPDATE STATISTICS and percent_complete is never a positive value? How long does the command take, and what options are you using? Do you see different results on other versions of SQL Server?

  2. John Uhrskov says:
    October 29, 2011 at 6:04 PM

    On my 2008 installation (version 10.0.4064.0). The UPDATE STATISTICS does not work!

  3. Kimberly L. Tripp says:
    February 15, 2011 at 4:06 AM

    Don't you have time just to test all of those… on all of the versions? Common Aaron – where's your community spirit?! 😉
    And, yes, definitely a bug in docs. Would be good to get the 411 on it though. It's got to be relatively easy to find in the code?!
    Cheers,
    kt