SQL Server DMV/DMFs

                                                        SQL Server DMV/DMFs

What dynamic management views (DMV) or dynamic management functions (DMF).

There are well over 150 DMV/DMFs spread across 20 some categories in 2012 now

 

Execution Related

·                                 sys.dm_exec_connections

·                                 sys.dm_exec_sessions

·                                 sys.dm_exec_requests

·                                 sys.dm_exec_cached_plans

·                                 sys.dm_exec_query_plans

·                                 sys.dm_exec_sql_text

·                                 sys.dm_exec_query_stats

 

Index Related

·                                 sys.dm_db_index_physical_stats --> Returns size and fragmentation information for the data and indexes of the specified table or view.

·                                 sys.dm_db_index_usage_stats   -->  Returns counts of different types of index operations and the time each type of operation was last performed.

·                                 sys.dm_db_index_operational_stats--> Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

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

                    sys.dm_db_missing_index_details -->    Returns detailed information about missing indexes, excluding spatial indexes.

                    sys.dm_db_missing_index_groups -->   Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes

 

SQL Server Operating System

·                                 sys.dm_os_performance_counters

·                                 sys.dm_os_schedulers

·                                 sys.dm_os_nodes

·                                 sys.dm_os_waiting_tasks

·                                 sys.dm_os_wait_stats

I/O Related

·                                 sys.dm_io_virtual_file_stats

 

            Get a Listing of All Connections:

SELECT *  FROM sys.dm_exec_connections

 

            Get a Listing of All Sessions

select  *  FROM sys.dm_exec_sessions

 

         Get a listing of all user connections:

    SELECT   c.session_id, c.auth_scheme, c.node_affinity

           , s.login_name

         , db_name(s.database_id) AS database_name

           , CASE s.transaction_isolation_level

             WHEN 0 THEN 'Unspecified'

             WHEN 1 THEN 'Read Uncomitted'

             WHEN 2 THEN 'Read Committed'

             WHEN 3 THEN 'Repeatable'

             WHEN 4 THEN 'Serializable'

             WHEN 5 THEN 'Snapshot'

             END AS transaction_isolation_level

           , s.status

           , c.most_recent_sql_handle

      FROM sys.dm_exec_connections c

INNER JOIN sys.dm_exec_sessions s

        ON c.session_id = s.session_id

http://sqlcan.wordpress.com/2012/05/25/a-microsoft-sql-server-dmvdmf-cheat-sheet/

                               DMVs Identify blocking

sys.dm_exec_requests

You can use the sys.dm_exec_requests dynamic management view to obtain detailed information about the requests currently executing on SQL Server.The columns you are most likely to use when troubleshooting a block or deadlock are as follow:

To view blocked processes execute the following query:

USE [master]

GO

SELECT  session_id ,blocking_session_id ,wait_time ,wait_type ,last_wait_type ,wait_resource ,transaction_isolation_level ,lock_timeout

FROM sys.dm_exec_requests WHERE blocking_session_id <> 0

GO

sys.dm_tran_locks

You can view information about current locks and the processes blocking them using the sys.dm_tran_locks dynamic management view. This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information about all locks with a request status of CONVERT, you execute the following:

USE [master]

GO

SELECT * from sys.dm_tran_locks WHERE request_status = 'CONVERT'

GO

Check Table Locks

 select   object_name(resource_associated_entity_id) as 'TableName' ,*

from   sys.dm_tran_locks where resource_type = 'OBJECT'   and resource_database_id = DB_ID()

GO

exec sp_who 'active'

exec sp_lock

The request_session_id column contains the Process ID for the process. To view locking in the particular database, execute the following query that joins sys.dm_tran_locks with sys.partitions:

USE [master]

GO

SELECT   tl.resource_type,tl.resource_associated_entity_id ,OBJECT_NAME(p.object_id) AS object_name ,tl.request_status

 ,tl.request_mode ,tl.request_session_id ,tl.resource_description

FROM sys.dm_tran_locks tl LEFT JOIN sys.partitions p

ON p.hobt_id = tl.resource_associated_entity_id

WHERE tl.resource_database_id = DB_ID()

GO

sys.dm_os_waiting_tasks

The sys.dm_os_waiting_tasks dynamic management view reports information about the blocked and blocking processes. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column.

Execute the following to view wait stats for all block processes on SQL Server:

USE [master]

GO

SELECT w.session_id,w.wait_duration_ms,w.wait_type,w.blocking_session_id,w.resource_description,s.program_name,t.text,t.dbid,s.cpu_time,

s.memory_usage FROM sys.dm_os_waiting_tasks w INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id

INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

WHERE s.is_user_process = 1

GO

This detail is good for a big picture, or to get a quick idea of the types of waits occurring, but most of the real diagnostics and tuning will occur at a statement level.