首页 > 代码库 > 转--也不知是哪位大侠写的了
转--也不知是哪位大侠写的了
试应用环境:SQL2008 R2、SQL2012、SQL2014[sql] view plaincopy--语句1:获取前20逻辑读取次数或逻辑写入次数或CPU 时间 SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - 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_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER 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时间[sql] view plaincopy在CODE上查看代码片派生到我的代码片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) desc WAITFOR 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) desc SELECT 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.StoredProcedure ORDER BY 5 desc drop table #temp drop table #temp2
试应用环境:SQL2008 R2、SQL2012、SQL2014
[sql] view plaincopy
- --语句1:获取前20逻辑读取次数或逻辑写入次数或CPU 时间
- SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
- ((CASE qs.statement_end_offset
- WHEN -1 THEN DATALENGTH(qt.TEXT)
- ELSE qs.statement_end_offset
- END - 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_plan
- FROM sys.dm_exec_query_stats qs
- CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
- CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
- ORDER 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时间
[sql] view plaincopy
- 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) desc
- WAITFOR 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) desc
- SELECT 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.StoredProcedure
- ORDER BY 5 desc
- drop table #temp
- drop table #temp2
转--也不知是哪位大侠写的了
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。