Execution Plan Cache
Execution Plan Cache is a SQL statement in the Administration category. SQL Server: views into the execution plan cache to identify expensive or long-running queries. The syntax is SELECT * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;. It returns query performance stats. A typical example: SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu, qs.total_logical_reads / qs.execution_count AS avg_reads, 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 query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_cpu DESC; -- Top 10 most CPU-intensive queries A close relative is CREATE USER, which creates a new database user account. A close relative is GRANT, which grants specific privileges (SELECT, INSERT, UPDATE, DELETE, ALL) to a user on a database/table. A close relative is REVOKE, which removes previously granted privileges from a user. More about this category: Database administration — CREATE DATABASE, GRANT, REVOKE, SHOW, DESCRIBE.