DMV/DMF

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 http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx.
2. Microsoft. SQL Server 2008 Books On-Line: sys.dm_exec_query_stats (Transact-SQL). (2009, Feb). Accessed from http://msdn.microsoft.com/en-us/library/ms189741.aspx.
3. Microsoft. MSDN Magazine. SQL Server: Uncover Hidden Data to Optimize Application Performance. Ian Stirk. (2008, Jan). Accessed from http://msdn.microsoft.com/en-us/magazine/cc135978.aspx.
4. SQL Solutions. SQL Server Cache Hits Ratio and SQL Server Performance. (2007, July 30). Accessed from http://www.sqlsolutions.com/articles/articles/SQL_Server_Cache_Hits_Ratio_and_SQL_Server_Performance.htm.
5. Microsoft. SQL Server 2005 for Administrator Workshop.
6. SQLCast. How to Check Fragmentation in SQL 2005. (2009, December 9). Accessed From http://blogs.msdn.com/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx.
7. MSSQLTips. Index Fragmentation Report in SQL Server 2005 and 2008. (2009, March 16). Accessed from http://www.mssqltips.com/tip.asp?tip=1708.
8. SQLSkills.com. Inside sys.dm_db_index_physical_stats (2010, March 9). Accessed from http://sqlskills.com/BLOGS/PAUL/post/Inside-sysdm_db_index_physical_stats.aspx.
9. SQLBlogs.com. sys.dm_io_virtual_file_stats. (2007, July 27). Accessed from http://sqlblog.com/blogs/louis_davidson/archive/2007/07/27/sys-dm-io-virtual-file-stats.aspx.
10. Database Journal. SQL Server Database File I/O Report. (2009, January 20). Accessed from http://www.databasejournal.com/features/mssql/article.php/3796551/SQL-Server-Database-File-IO-Report.htm.
11. SQL Workshops. Webcast #1, Part 1, 2 and 3. Accessed from http://www.sqlworkshops.com/.
12. SQL Workshops. Webcast #2, Part 1, 2, and 3. Accessed from http://www.sqlworkshops.com/.
13. CPU Pressure. SQL Workshops. Webcast #3, Part 1, 2, and 3.  Accessed from http://www.sqlworkshops.com/cpupressure.htm.
14. Bart Duncan’s SQL Weblog. Are you Using SQL’s Missing Index DMVs?. Access from http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx.
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.