8 Most Useful Dynamic Management Views And Functions I Often Use

Short Introduction: Dynamic management views and functions return information that can be used to monitor the health of a server instance, diagnose problems, and tune performance

I have found the following 8 DMV and DMF very useful

Database Related: •sys.dm_db_file_space_usage •sys.dm_db_session_space_usage •sys.dm_db_partition_stats Execution Related: •sys.dm_exec_query_stats •sys.dm_exec_sql_text

Index Related: •sys.dm_db_index_operational_sta

•sys.dm_db_index_physical_stats •sys.dm_db_missing_index_colum

sys.dm_db_file_space_usage Returns space usage information for each file in database tempdb (not available for other databases ). This DMV is specially useful if tempdb utilization is high Example: display tempdb files, sizes and page counts select M.physical_name, M.size, U.unallocated_extent_page_count, U.user_object_reserved_page_count, U.internal_object_reserved_page_count, U.mixed_extent_page_count from sys.dm_db_file_space_usage U join

sys.dm_db_file_space_usage Example 2: returns the amount of free space in tempdb SELECT free_pages = SUM(unallocated_extent_page_count), Free_space_in_MB = (SUM(unallocated_extent_page_count)/128.) sys.dm_db_file_space_usage

sys.dm_db_session_space_usage

# of pages used by each SPID tempdb. You can use this DMV to find the session filling up tempdb. Example: top five tasks consuming most tempdb space select top 5 session_id, user_objects_alloc_page_count * 8 / 1024.0 [Allocated space in MB] from sys.dm_db_session_space_usage ORDER BY (user_objects_alloc_page_count +internal_objects_alloc_page_count) DESC

sys.dm_db_partition_stats returns space utilization within a database. I found this DMV very helpful to compute and report space used by db objects Example: determines space used by objects select convert(char(30), object_name(object_id)), sum(in_row_reserved_page_count)/128., sum(lob_reserved_page_count)/128. from sys.dm_db_partition_stats group by object_id order by sum(in_row_reserved_page_count)

sys.dm_exec_query_stats Returns aggregate statistics for cached query plans. This DMV contains 1 row/query within cached plans.

** When a plan is removed from the cache, the corresponding rows are removed from this view. I have some examples on the next slides:

sys.dm_exec_query_stats …

CONTINUED

-- Cached SP's By Execution Count SELECT TOP (25) sp_name = qt.text , db_name = db_name(qt.dbid), qs.execution_count AS 'Exec. Count', Cpu = qs.total_worker_time qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC;

sys.dm_exec_query_stats …

CONTINUED

-- Cached SP's By Logical Reads SELECT TOP (25) sp_name = qt.text, db_name = db_name(qt.dbid), total_logical_reads, qs.total_worker_time, qs.total_logical_writes, qs.total_physical_reads qs.max_logical_reads, qs.max_logical_writes FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY total_logical_reads DESC;

•sys.

dm_exec_sql_text( sql handle |plan_ha

*table valued function used along with other DMVs to identify the sql text specified by the sql handle Example the below query returns the SQL statement and average CPU time for the top five queries. SELECT TOP 5 avg_cpu = total_worker_time/execution_count, Statement_text = st.text, FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS

sys.dm_db_index_operational_stats

Provides useful stats on how many times an index has been used, locked, waited on, etc. Example on the next slide…

The below query provides information on reads/writes to a table select objectname=object_name(s.object_id), indexname=i.name, i.index_id,reads=range_scan_count+singleton_l ookup_count, writes = leaf_insert_count+leaf_update_count+ leaf_delete_count , nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count leaf_page_splits = leaf_allocation_count from sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL) s, sys.indexes I where objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id

sys.dm_db_index_physical_stats

Found this query invaluable to fetch size and fragmentation information for the data and indexes of the specified table. Example: select object_name(object_id) , avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(null,null, null,null,null) where database_id = db_id('DBA_rep')

sys.dm_db_index_usage_stats Returns query optimizer use of an index in a plan. 

•Usage information is re-recorded each time

the plan is executed.  • This DMV provides the following information: User seeks i.e. the number of times a query was used to find a single row User scans i.e. A range of values, or to resolve a non-unique query User_lookups if it has been used to resolve a bookmark lookup and User updates or how many changes were

sys.dm_db_missing_index_columns

Returns information about database table columns that are missing an index, excluding spatial indexes.

the End