I have used many different queries of DMV/DMF over the past years to get various information for SQL Server OS. So I am going to start keeping a record of all the queries and try to explain their meaning and usage as I understand them.

There are many DMV/DMF and it is hard to remember them all, in SQL Server 2005 there were 12 categories (link), totaling 78 DMV/DMFs and in SQL Server 2008 there are 17 categories (link), totaling 117 DMV/DMFs. I will make sure to point out any differences between two as I run into them. These help make a DBA life easier by providing alot of the low level system information we didn’t have access to before.

This post will be catch all for all the pages I use for my queries references. I think everyone’s work should be referenced fully; so if I have forgotten to reference your work please let me know. Thanks.

List of References are in order I run into them …

  1. The Code Project. Dynamic Management Views. Hari Prasand K. (2006, Dec 21). Accessed from
  2. Microsoft. SQL Server 2008 Books On-Line: sys.dm_exec_query_stats (Transact-SQL). (2009, Feb). Accessed from
  3. Microsoft. MSDN Magazine. SQL Server: Uncover Hidden Data to Optimize Application Performance. Ian Stirk. (2008, Jan). Accessed from
  4. SQL Solutions. SQL Server Cache Hits Ratio and SQL Server Performance. (2007, July 30). Accessed from
  5. Microsoft. SQL Server 2005 for Administrator Workshop.
  6. SQLCast. How to Check Fragmentation in SQL 2005. (2009, December 9). Accessed From
  7. MSSQLTips. Index Fragmentation Report in SQL Server 2005 and 2008. (2009, March 16). Accessed from
  8. Inside sys.dm_db_index_physical_stats (2010, March 9). Accessed from
  9. sys.dm_io_virtual_file_stats. (2007, July 27). Accessed from
  10. Database Journal. SQL Server Database File I/O Report. (2009, January 20). Accessed from
  11. SQL Workshops. Webcast #1, Part 1, 2 and 3. Accessed from
  12. SQL Workshops. Webcast #2, Part 1, 2, and 3. Accessed from
  13. CPU Pressure. SQL Workshops. Webcast #3, Part 1, 2, and 3.  Accessed from
  14. Bart Duncan’s SQL Weblog. Are you Using SQL’s Missing Index DMVs?. Access from
  15. Kalen Delaney. Inside Microsoft SQL Server 2005: The Storage Engine. Link.
  16. Simple-Talk. Troubleshooting with Dynamic Management Views. Link.
  17. SQL Server Magazine. Troubleshooting Parallel Queries on SQL Server 2005. Link.
  18. TechNet WebCast: SQL Server 2005 Performace Troubleshooting and Tuning (Part 1 of 2) (Level 200).
  19. TechNet WebCast: SQL Server 2005 Performace Troubleshooting and Tuning (Part 2 of 2) (Level 200).
  20. Slava Oks’s Weblog. SQLOSDMV’s Continue. Link.
  21. The Rambling DBA: Jonathan Kehayias, Tuning ‘cost threshold for parallelism’ from the Plan Cache. Link.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.