Execution & Thread related SQL Server Dynamic Management Views

from http://www.sqlserverspecialists.com/2013/03/execution-thread-related-sql-server.html

These DMVs and functions show what activity is executing on the server. The DMV sys.dm_exec_query_transformation_stats is used internally by SQL Server .
 
 
Returns a row for each query processor job that is scheduled for asynchronous, i.e. background, execution. This will show update statistics jobs.
 
 
Returns a row that provides aggregate statistics for each query processor job submitted for background execution.
 
To see the percentage of failed background jobs for all executed queries:
 
SELECT CASE ended_count WHEN 0
THEN ‘No jobs ended’
ELSE CAST((failed_lock_count + failed_giveup_count + failed_other_count) /
CAST(ended_count AS float) * 100 AS varchar(20)) END AS percent_failed
FROM sys.dm_exec_background_job_queue_stats;
 
 
 
Similar to syscacheobjects in SQL Server 2000. Returns a row for each query plan that is held in procedure cache, and containing information like the cached query plans, the cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
 
Returning the batch text of cached entries that are reused
 
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC;
 
 
Returning query plans for all cached triggers
 
SELECT plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE objtype ='Trigger';
 
 
Returns a row for each TSQL execution plan, CLR execution plan, and cursor associated with a plan.
 
 
Returns server-level information about a connection to SQL Server, such as the client network address, client TCP port, and client authorization scheme.
 
 
Returns information about cursors that are open in one or more databases on the server.
 
Detecting old cursors which are open on the server longer than the specified time of 36 hours.
 
SELECT creation_time, cursor_id, name, c.session_id, login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
 
 
Returns one row per plan attribute for a specific plan identified by its plan handle, such
 
Returning the attributes for a specific plan
 
The following example returns all plan attributes for a specified plan. The sys.dm_exec_cached_plans dynamic management view is queried first to obtain the plan handle for the specified plan. In the second query, replace <plan_handle> with a plan handle value from the first query.
 
SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype
FROM sys.dm_exec_cached_plans;
GO
SELECT attribute, value, is_cache_key
FROM sys.dm_exec_plan_attributes(<plan_handle>);
GO
 
 
Returning the SET options for compiled plans and the SQL handle for cached plans
 
The following example returns a value representing the options that each plan was compiled with. In addition, the SQL handle for all the cached plans is returned.
 
SELECT plan_handle, pvt.set_options, pvt.sql_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
 
 
Returns information about queries that have acquired memory grants or that still require a memory grant to execute. This DMV is useful for determining query timeouts, since only queries that have to wait on a memory grant will appear in this view.
 
 
Returns detailed statistics about the operation of the SQL Server query optimizer, such as the total number of optimizations, the elapsed time value, or sophisticated assessments like comparing the query optimization cost of the current workload to that of a tuned workload. Some counters shown by this DMV are for internal use only.
 
Viewing statistics on optimizer execution
What are the current optimizer execution statistics for this instance of SQL Server?
 
SELECT * FROM sys.dm_exec_query_optimizer_info;
 
Viewing the total number of optimizations
 
How many optimizations are performed?
 
SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';
 
Average elapsed time per optimization
 
What is the average elapsed time per optimization?
 
SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';
 
Fraction of optimizations that involve subqueries
 
What fraction of optimized queries contained a subquery?
 
SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /
       (SELECT CAST (occurrence AS float)
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
        AS ContainsSubqueryFraction;
 
 
Returns a given query’s Showplan output in XML format, as specified by the plan handle.
 
To see the top 5 SQL statements executing on the server by CPU time:
 
SELECT TOP 5 total_worker_time/execution_count AS AVG_CPU_Time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
 
 
 
Returns two rows, one for the regular resource semaphore and the other for the small-query resource semafor, information about the current query-resource semaphore status. When used with sys.dm_os_memory_clerks, this DMV provides a complete picture of memory status information about query executions and allows you todetermine whether the system can access enough memory.
 
 
 
Returns one row per query statement within a cached plan, detailing aggregated performance statistics for cached query plans. Because the information is aggregated, you may sometimes get better information by rerunning this DMV.
 
To find top five queries ranked by average CPU time:
 
USE AdventureWorks2012;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
 
 
Returns one row for each request executing within SQL Server, but does not contain information for code that executes outside of SQL Server, such as distributed queries or extended stored procedures.
 
 
Returns one row per authenticated session, including both active users and internal tasks, running anywhere on SQL Server.
 
To find out the users connect to the server:
 
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
 
 
Similar to fn_get_sql in SQL Server 2000, this DMV returns the text of the SQL batch that is identified by the specified sql_handle.
 
 
Returns Showplan output in text format for a given TSQL batch or a statement within the batch. It’s similar sys.dm_exec_query_plan, except that it returns data as text, is not limited in size, and may be specific to an individual statement within a batch.
 
 
Returns information about one or all active handles that opened with the sp_xml_prepare document system stored procedure.
 
To see the SQL text of all cached plans who have been used at least three times:
 
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts >= 3
ORDER BY usecounts DESC;
 
To find any cursors that have been open for more than 24 hours:
 
SELECT name, cursor_id, creation_time, c.session_id, login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 24;
 
To find the fraction of optimized queries containing a hint:
 
SELECT (SELECT CAST (occurrence AS float)
FROM sys.dm_exec_query_optimizer_info WHERE counter = ‘hints’) /
(SELECT CAST (occurrence AS float)
FROM sys.dm_exec_query_optimizer_info WHERE counter = ‘hints’)
AS [Fraction Containing Hints];
posted @ 2014-06-26 18:12  princessd8251  阅读(165)  评论(0)    收藏  举报