Lightweight diagnostics with Dynamic Management Views on MSSQL
- The standard way of doing diagnostics on MSSQL has been to use the SQL Server Profiler.
- The problem with SQL Server profiler is that it can be expensive on a heavy load server. So you should use the SQL Server Profiler the least amount you can on a production system.
- With MSSQL 2005, Microsoft has added Dynamic Management Views. These are views that are very simple to query and are very lightweight on any server.
Applies to:
Microsoft SQL Server 2005
Dynamic Management Views
There are easily accessible through the SQL Server Management Studio. You select your database, go to the views, expand the system views, scroll down and you will see all the dynamic management views, they all start with dm_.
System views.
| Name prefix | Category | Information |
|---|---|---|
| dm_broker | Service brokers | Services broker statistics |
| dm_clr | CLR | Common Language Runtime info |
| dm_db | Database | Databases and objects |
| dm_exec | Execution | Execution of user code |
| dm_fts | Full text | Full text search |
| dm_io | IO | Disk IO |
| dm_os | Operating system | All OS info as seen by SQL Server |
| dm_qn | Query notifications | Active query notification subscriptions |
| dm_repl | Replication | All replication information, including articles, punlications and transactions |
| dm_trans | Transactions | Transactions and isolation levels |
SELECT * FROM sys.dm_exec_connections;
go
go
session_id most_recent_session_id connect_time net_transport protocol_type protocol_version endpoint_id encrypt_option auth_scheme node_affinity num_reads num_writes last_read last_write net_packet_size client_net_address client_tcp_port local_net_address local_tcp_port connection_id parent_connection_id most_recent_sql_handle ----------- ---------------------- ----------------------- ---------------------------------------- ---------------------------------------- ---------------- ----------- ---------------------------------------- ---------------------------------------- ------------- ----------- ----------- ----------------------- ----------------------- --------------- ------------------------------------------------ --------------- ------------------------------------------------ -------------- ------------------------------------ ------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------- 51 51 2007-09-04 07:51:42.060 Shared memory TSQL 1913192450 2 FALSE NTLM 0 94 136 2007-09-06 09:25:34.797 2007-09-06 09:25:34.797 4096 <local machine> NULL NULL NULL 667AE5CA-08BD-41DE-9DD3-A8BEBA3B3219 NULL 0x010008000A43142D98BF8F04000000000000000000000000 52 52 2007-09-04 07:51:51.280 Shared memory TSQL 1913192450 2 FALSE NTLM 0 29 3375 2007-09-06 10:05:24.640 2007-09-06 09:28:22.640 4096 <local machine> NULL NULL NULL F1FA9ECA-229E-47B4-AF63-04813F867715 NULL 0x02000000F7E479032B47DF80AA7CC8B2630396A588D62FA9 (2 row(s) affected)