Complete DMV/DMF List

DMV/DMF Name	2005	2008
dm_audit_actions		X
dm_audit_class_type_map		X
dm_broker_activated_tasks	X	X
dm_broker_connections	X	X
dm_broker_forwarded_messages	X	X
dm_broker_queue_monitors	X	X
dm_cdc_errors		X
dm_cdc_log_scan_sessions		X
dm_clr_appdomains	X	X
dm_clr_loaded_assemblies	X	X
dm_clr_properties	X	X
dm_clr_tasks	X	X
dm_cryptographic_provider_properties		X
dm_database_encryption_keys		X
dm_db_file_space_usage	X	X
dm_db_index_usage_stats	X	X
dm_db_mirroring_auto_page_repair		X
dm_db_mirroring_connections	X	X
dm_db_mirroring_past_actions		X
dm_db_missing_index_details	X	X
dm_db_missing_index_group_stats	X	X
dm_db_missing_index_groups	X	X
dm_db_partition_stats	X	X
dm_db_persisted_sku_features		X
dm_db_script_level		X
dm_db_session_space_usage	X	X
dm_db_task_space_usage	X	X
dm_exec_background_job_queue	X	X
dm_exec_background_job_queue_stats	X	X
dm_exec_cached_plans	X	X
dm_exec_connections	X	X
dm_exec_procedure_stats		X
dm_exec_query_memory_grants	X	X
dm_exec_query_optimizer_info	X	X
dm_exec_query_resource_semaphores	X	X
dm_exec_query_stats	X	X
dm_exec_query_transformation_stats	X	X
dm_exec_requests	X	X
dm_exec_sessions	X	X
dm_exec_trigger_stats		X
dm_filestream_file_io_handles		X
dm_filestream_file_io_requests		X
dm_fts_active_catalogs	X	X
dm_fts_fdhosts		X
dm_fts_index_population	X	X
dm_fts_memory_buffers	X	X
dm_fts_memory_pools	X	X
dm_fts_outstanding_batches		X
dm_fts_population_ranges	X	X
dm_io_backup_tapes	X	X
dm_io_cluster_shared_drives	X	X
dm_io_pending_io_requests	X	X
dm_os_buffer_descriptors	X	X
dm_os_child_instances	X	X
dm_os_cluster_nodes	X	X
dm_os_dispatcher_pools		X
dm_os_dispatchers		X
dm_os_hosts	X	X
dm_os_latch_stats	X	X
dm_os_loaded_modules	X	X
dm_os_memory_allocations	X	X
dm_os_memory_brokers		X
dm_os_memory_cache_clock_hands	X	X
dm_os_memory_cache_counters	X	X
dm_os_memory_cache_entries	X	X
dm_os_memory_cache_hash_tables	X	X
dm_os_memory_clerks	X	X
dm_os_memory_node_access_stats		X
dm_os_memory_nodes		X
dm_os_memory_objects	X	X
dm_os_memory_pools	X	X
dm_os_nodes		X
dm_os_performance_counters	X	X
dm_os_process_memory		X
dm_os_ring_buffers	X	X
dm_os_schedulers	X	X
dm_os_spinlock_stats		X
dm_os_stacks	X	X
dm_os_sublatches	X	X
dm_os_sys_info	X	X
dm_os_sys_memory		X
dm_os_tasks	X	X
dm_os_threads	X	X
dm_os_virtual_address_dump	X	X
dm_os_wait_stats	X	X
dm_os_waiting_tasks	X	X
dm_os_worker_local_storage	X	X
dm_os_workers	X	X
dm_qn_subscriptions	X	X
dm_repl_articles	X	X
dm_repl_schemas	X	X
dm_repl_tranhash	X	X
dm_repl_traninfo	X	X
dm_resource_governor_configuration		X
dm_resource_governor_resource_pools		X
dm_resource_governor_workload_groups		X
dm_server_audit_status		X
dm_tran_active_snapshot_database_transactions	X	X
dm_tran_active_transactions	X	X
dm_tran_commit_table		X
dm_tran_current_snapshot	X	X
dm_tran_current_transaction	X	X
dm_tran_database_transactions	X	X
dm_tran_locks	X	X
dm_tran_session_transactions	X	X
dm_tran_top_version_generators	X	X
dm_tran_transactions_snapshot	X	X
dm_tran_version_store	X	X
dm_xe_map_values		X
dm_xe_object_columns		X
dm_xe_objects		X
dm_xe_packages		X
dm_xe_session_event_actions		X
dm_xe_session_events		X
dm_xe_session_object_columns		X
dm_xe_session_targets		X
dm_xe_sessions		X

CPU Pressure: Length of Runnable Queues

How to identify CPU pressure? One way to see is how far it is in completing the work assigned to each CPU.

SELECT scheduler_id,
      current_tasks_count,
      runnable_tasks_count,
      current_workers_count,
      active_workers_count
 FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

This code returns one row for each CPU-Core; so if you are running Dual-CPU Quad-Core, you'll get 8 rows. Each row is shows the current workload, if the number in runnable_tasks_count is too high it means CPU is not keeping up with the work. The runnable tasks mean these tasks have all the resources they need they just need some CPU time to finish the work.

The scheduler ID 255 and greater are hidden schedulers; 255 is DAC. So if you run this without where clause and don't see 255, means your DAC is not enabled.

No difference in the 2005 and 2008 version for this DMV.

Ref #5.

Top 10 - Reads and Write by Database

     SELECT TOP 10 SUM(total_logical_reads) AS [Total Reads]
           ,SUM(qs.execution_count) AS [Execution Count]
           ,DB_NAME(qt.dbid) AS [Database Name]
      FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
  GROUP BY DB_NAME(qt.dbid)
  ORDER BY [Total Reads] DESC;

    SELECT TOP 10 SUM(total_logical_writes) AS [Total Writes]
           ,SUM(qs.execution_count) AS [Execution Count]
           ,DB_NAME(qt.dbid) AS [Database Name]
      FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
  GROUP BY DB_NAME(qt.dbid)
  ORDER BY [Total Writes] DESC;

These two queries list the total reads and writes for database. When you run these queries, you'll notice the DB name might be null for some databases; "this setting identifies ad hoc and prepared SQL statements" (I. Stirk, SQL Magazine). You can use the query listed in this post to find out what is the actual SQL Statement that is causing excessive reads/writes.

Another thing to note these queries check Logical values only; I think both Physical values and Logical values are important. In this it is usually logical values to show how much reads/writes are happening to the pages in the cache. You can use the physical value to see how much is being read from hdd into memory for the query to process. This is where you can get the Cache Hit Ratio from ...

So Cache Hit Ratio = 100% - (Physical Reads/Logical Reads), you can use following query on here to see how well your query is performing for CHR:

     SELECT TOP 10 (100 - ( SUM(total_physical_reads)
                            / (CASE WHEN SUM(total_logical_reads) = 0 THEN
                                  1
                               ELSE
                                  SUM(total_logical_reads)
                               END))) AS [Cache Hit Ratio]
            ,SUM(qs.execution_count)  AS [Execution Count]
            ,DB_NAME(qt.dbid)         AS [Database Name]
       FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS QT
   GROUP BY DB_NAME(qt.dbid)
   ORDER BY [Cache Hit Ratio] DESC;

Ref #3, #4.

Top 10 - Resource Wait Times

SELECT TOP 10
  wait_type AS [Wait Type],
  (wait_time_ms / 1000) AS [Wait Time (s)],
  CONVERT(DECIMAL(12,2),wait_time_ms*100.0/SUM(wait_time_ms) OVER())
FROM
  sys.dm_os_wait_stats
WHERE
  wait_type NOT LIKE '%SLEEP%'
ORDER BY
  wait_time_ms DESC

This code gives information for TOP 10 wait types and can be used to judge what is causing the biggest blockage in your SQL Server; might it be Disk I/O, Network I/O, etc.

This DMV cache information can be cleared using the DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); command. The reason to do this might be to get a more accurate picture of system performance; this DMV will also recycle its information between SQL Restarts. The DMV has not changed between 2005 and 2008. There are many wait types that can show up in this DMV; new wait-types were added in 2008 and some were removed.

Wait type information can be accessed from MSDN site; 2005 (link) and 2008 (link).
Service Broker Wait Types Details, Link.

Ref #3.

Execution Related Dynamic Management Views and Functions Differences

sys.dm_exec_query_stats

SQL 2008: Added two new fields the the DMV (query_hash and query_plan_hash); designed to help performance tune queries with similar logic, you can read more about it at this link.

sys.dm_exec_sql_text

SQL 2008: The handle can now be also be from the sys.dm_exec_connections DMV; before it was from sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants DMV only.

Get SQL Query that was executed from Query Cache

The following code look at the DMV and uses a DMF to get SQL query that was executed. The reason you have to parse the string is because the information stored in the dm_exec_sql_text stores the complete batch that was executed as one row of entry. Where as dm_exec_query_stats records the information on per SQL statement base.

     SELECT QS.last_execution_time AS [Time],
         SUBSTRING(ST.TEXT,
                   (QS.statement_start_offset/2) + 1,
                   ( ( CASE QS.statement_end_offset WHEN -1 THEN
                          DATALENGTH(ST.TEXT)
                       ELSE
                          QS.statement_end_offset
                       END - QS.statement_start_offset ) / 2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats             AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
ORDER BY QS.last_execution_time DESC

Information retrieved from the dm_exec_query_stats is tied back to the cached plans for the query; if the plan is cycled out of the cache then the rows matching the query are also removed.

This query is same for both SQL2008 and SQL2005 but there is a couple new fields added into the sys.dm_exec_query_stats in 2008. And the function sys.dm_exec_sql_text has not changed in the structure but now we have additional batch info beings stored from the sys.dm_exec_connections DMV.

Ref #2.

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.