Dynamic Management Views and Functions Tips

25 Jul
2010

Dynamic Management Views and Functions feature comes with SQL Server 2005 version. They provides some information about system, and what is going under when SQL Server running. So you can use DMV and DMF objects to monitor the system and tune it. These object located under sys schema and names are start with the dm. As previous system objects in SQL Server, internal implementation of these objects can be change with the future releases.

I want to share some code snippets about some of DMV/F’s which I had use while tuning and monitoring SQL Server.

sys.dm_exec_sessions

sys.dm_exec_sessions provides information about running sessions in SQL Server.

Returns one row for each session in SQL Server. You can filter out system and user session with is_user_process filed.

Listing user sessions:

  

SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1

Please read MSDN documentation for all fields returned by sys.dm_exec_sessions view to more information. Some useful fields are session_id (this can be input for other DMV/F for more information), login_time, host_name, status, cpu_time, memory_usage (x8KB –this contains number of data pages-), last_request_start_time, transaction_isolation_level, row_count (total returned row count in this session).

Another example, grouping by program name and login name and getting active session count by username:

  

SELECT 'Authentication Method'=(
            CASE
                WHEN nt_user_name IS not null THEN 'Windows Authentication'
                ELSE 'SQL Authentication'
            END),
           login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
           COUNT(session_id) AS 'Session Count',
           SUM(row_count) 'Total Number Of Returned Rows by login+programname',
           program_name
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name,program_name

Output:

dm_exec_sessions1

You can list the users who have more than the normal count of sessions.

You can use sys.dm_exec_sessions DMV object conjunction with  sys.sysprocesses view to get to current executing sql queries sql_handle.

  

select sp.sql_handle,es.session_id,es.login_time,es.host_name,es.program_name,es.status,
       es.row_count,es.last_request_start_time,es.is_user_process
from sys.dm_exec_sessions es
inner join sys.sysprocesses sp on sp.spid=es.session_id
where is_user_process=1

Output :

dm_exec_sessions2

First column of the result set comes from sys.sysprocesses view’s sp.sql_handle column.

This values is the memory address of the executing sql query. You can use fn_get_sql function to get the actual sql query text. (You can copy and paste the sql_handle value from the previous result window.Value at the below is a unique value for every query. So it will not work in your environment with this value.)

select text sql_query FROM fn_get_sql(0x01000100AB5E5014301E99800000000000000000)

Calling fn_get_sql one by one for each sql_handle value in the result set can be hard for monitoring. You can use query at the below for adding sql query in the main result set as a column.

  

select (select text sql_query FROM fn_get_sql(it.sql_handle)) sql,
it.* from (
            select sp.sql_handle,es.session_id,es.login_time,es.host_name,es.program_name,es.status,
                es.row_count,es.last_request_start_time,es.is_user_process
            from sys.dm_exec_sessions es
                 inner join sys.sysprocesses sp on sp.spid=es.session_id
            where is_user_process=1
) it

Output

dm_exec_sessions3

Alternatively sys.dm_exec_sql_text DMV can be used to get actual sql query text. Before using this DMV object I want to talk about another DMV object named sys.dm_exec_query_stats.

sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans. For example you can list the TOP N query by the average execution times.

List cached sql query plans statistics and order by average execution time.

  

SELECT eqs.total_worker_time/eqs.execution_count AS [Avg CPU Time]
,eqs.*
FROM sys.dm_exec_query_stats eqs
ORDER BY [Avg CPU Time] DESC

Output

dm_exec_query_stats1

This DMV contains total_worker_time and execution_count columns in the result set, total_worker_time is sum of the execution times of this query plans, and execution_count is number execution time of this query plan.

p.s.: This DMV uses query cache plan as a source for listing statistics. If systems workload is overloaded, results can be incorrect. In this situation rerunning the query can be the cure.

After SQL Server restart or after calling DBCC FREEPROCCACHE function, SQl Server cleans the query plan cache, so you can’t list the queries which are executed before this actions (restart and freeproccache).

For listing the actual sql query text and ordering by average execution time, you can add the sys.dm_exec_sql_text DMV object to the query as an subquery.

List TOP 10 sql query

  

SELECT TOP 10
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,
    (CASE WHEN statement_end_offset = -1 then
    LEN(CONVERT(nvarchar(max), text)) * 2
    ELSE statement_end_offset end -statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

Output

dm_exec_query_stats2

I think, an sql administrator must be create an view with the above query. This query can be used for monitoring the executing query when your system started to slow down in the middle of the work hours. You can detect a erroneous queries written by a sleepy developer.



Comment Form

top