Dynamic Management Views (DMVs)

I tend to do a fair bit of performance tuning and optimisation of reports and find the following views very useful.

select * from sys.dm_db_index_usage_stats
where database_id = 5
order by last_user_seek DESC 

This gives index usage stats – useful in finding out whether the optimiser is using your indexes, or whether you have inefficient indexes which get a lot of DML – if you find scans, seeks and lookups at 0 and updates high then you have an inefficient index

select * from sys.dm_exec_sessions 

This gives you similar output to sp_who2 but in more detail. Logical reads and physical reads are important if you want to see if your data is being read out of memory/cache or disk

select * from sys.dm_exec_requests

In Depth detail about each user request – some duplication with exec_sessions but this also gives you a more details command breakdown and percent complete

If you are tuning a query and want to relate to IO usage, then run SET STAISTICS IO ON before the query.

select  TOP 100
      LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

Find plans in cache – i.e. compiled query plans etc

More Examples:

–The sys.dm_db_file_space_usage can used to troubleshoot insufficient disk space in tempdb.
–To find the number of unallocated pages in kb,we can use the sys.dm_db_file_space_usage DMV

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
         (SUM(unallocated_extent_page_count)*8) AS [free space in KB]
    FROM sys.dm_db_file_space_usage

–To determine the number of sessions established by every login
 , count(session_id) as session_count
 from sys.dm_exec_sessions 
 group by login_name


–Returns the cursors that are open in the databases


SELECT * FROM sys.dm_exec_cursors(0)


–Returns information about the connections made to the
–instance of the SQL Server and the details of each connection.


select a.session_id
     , b.login_name
  FROM sys.dm_exec_connections a ,sys.dm_exec_sessions b
  WHERE a.session_id=b.session_id


–Returns the query plans which are cached by the SQL Server


select * from sys.dm_exec_cached_plans


–This gives information about the threads that waiting for resources in milliseconds


select * from sys.dm_os_wait_stats


–Returns information about the computer and the resources available and consumed by the SQL Server


select * from Sys.dm_os_sys_info


–Returns information about the transactions in a SQL Server instance


select * from Sys.dm_tran_active_transactions


–Returns a single row which gives information about the current transaction in the current session


select * from Sys.dm_tran_current_transaction


–Returns information about the transactions in that database


select * from Sys.dm_tran_database_transactions

Many more DMV’s – these are the ones I use mainly. More Details – http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx


Leave a Reply

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

WordPress.com Logo

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

Google+ photo

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

Twitter picture

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

Facebook photo

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


Connecting to %s

%d bloggers like this: