【整理贴】DBA-常用到的动态视图分析语句

测试应用环境:SQL2008 R2、SQL2012、SQL2014

--语句1：获取前20逻辑读取次数或逻辑写入次数或CPU 时间SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2)+1),qs.execution_count,qs.total_logical_reads, qs.last_logical_reads,qs.total_logical_writes, qs.last_logical_writes,qs.total_worker_time,qs.last_worker_time,qs.total_elapsed_time/1000000 total_elapsed_time_in_S,qs.last_elapsed_time/1000000 last_elapsed_time_in_S,qs.last_execution_time,qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY qs.total_logical_reads DESC -- 逻辑读取次数 --ORDER BY qs.total_logical_writes DESC -- 逻辑写入次数 --ORDER BY qs.total_worker_time DESC -- CPU 时间--语句2：获取前20执行的 SP 命令的总工作时间 （CPU 压力）    SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',    qs.execution_count AS 'Execution Count',     ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',     qs.max_logical_reads, qs.max_logical_writes,     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'    FROM sys.dm_exec_query_stats AS qs    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt    WHERE qt.dbid = db_id() -- 当前数据库    ORDER BY qs.total_worker_time DESC--语句3： 获取前20 执行的 SP 命令逻辑写入/分钟    SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',      qs.execution_count AS 'Execution Count',     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',    qs.total_worker_time AS 'TotalWorkerTime',    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid    FROM sys.dm_exec_query_stats AS qs    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt    WHERE qt.dbid = db_id() -- 当前数据库    ORDER BY qs.total_logical_writes DESC--语句4： 获取前20执行的 SP 命令的逻辑读取（内存压力）     SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads,     qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',    qs.total_worker_time AS 'TotalWorkerTime',    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',    qs.total_logical_writes,    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid     FROM sys.dm_exec_query_stats AS qs    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt    WHERE qt.dbid = db_id() -- 当前数据库    ORDER BY total_logical_reads DESC--语句5： 获取前20执行的 SP 命令由物理读取 （读取 I/O 压力）    SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',    qs.execution_count AS 'Execution Count',    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',    qs.total_worker_time AS 'TotalWorkerTime',    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',    qs.max_logical_reads, qs.max_logical_writes,      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid     FROM sys.dm_exec_query_stats AS qs    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt    WHERE qt.dbid = db_id() -- 当前数据库    ORDER BY qs.total_physical_reads DESC--语句6： 获取前20执行的 SP 命令执行计数    SELECT TOP 20 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',      qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',    qs.total_worker_time AS 'TotalWorkerTime',    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'    FROM sys.dm_exec_query_stats AS qs    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt    WHERE qt.dbid = db_id() -- Filter by current database    ORDER BY qs.execution_count DESC

查看10秒时间内存储过程执行次数和CPU时间

SELECT DB_NAME(st.dbid) DBName      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure      ,max(cp.usecounts) Execution_count      ,sum(qs.total_worker_time) total_cpu_time      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time into #temp FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)  order by sum(qs.total_worker_time) descWAITFOR DELAY '00:00:10' SELECT DB_NAME(st.dbid) DBName      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure      ,max(cp.usecounts) Execution_count      ,sum(qs.total_worker_time) total_cpu_time      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time into #temp2 FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)  order by sum(qs.total_worker_time) descSELECT a.DBNAMe, a.SchemaName, a.StoredProcedure,b.Execution_count - a.Execution_count as ExecCnt,b.total_cpu_time - a.total_cpu_time as CPU FROM #temp a inner join #temp2 b on a.DBName = b.DBname and a.SchemaName = b.SchemaName and a.StoredProcedure = b.StoredProcedureORDER BY 5 descdrop table #tempdrop table #temp2