转--也不知是哪位大侠写的了

试应用环境: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. --语句1：获取前20逻辑读取次数或逻辑写入次数或CPU 时间  
2. SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,  
3. ((CASE qs.statement_end_offset  
4. WHEN -1 THEN DATALENGTH(qt.TEXT)  
5. ELSE qs.statement_end_offset  
6. END - qs.statement_start_offset)/2)+1),  
7. qs.execution_count,  
8. qs.total_logical_reads, qs.last_logical_reads,  
9. qs.total_logical_writes, qs.last_logical_writes,  
10. qs.total_worker_time,  
11. qs.last_worker_time,  
12. qs.total_elapsed_time/1000000 total_elapsed_time_in_S,  
13. qs.last_elapsed_time/1000000 last_elapsed_time_in_S,  
14. qs.last_execution_time,  
15. qp.query_plan  
16. FROM sys.dm_exec_query_stats qs  
17. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt  
18. CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
19. ORDER BY qs.total_logical_reads DESC -- 逻辑读取次数  
20.  --ORDER BY qs.total_logical_writes DESC -- 逻辑写入次数  
21.  --ORDER BY qs.total_worker_time DESC -- CPU 时间  
22.   
23.   
24. --语句2：获取前20执行的 SP 命令的总工作时间 （CPU 压力）  
25.     SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',   
26.     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
27.     qs.execution_count AS 'Execution Count',   
28.     ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',  
29.     ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',   
30.     qs.max_logical_reads, qs.max_logical_writes,   
31.     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'  
32.     FROM sys.dm_exec_query_stats AS qs  
33.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
34.     WHERE qt.dbid = db_id() -- 当前数据库  
35.     ORDER BY qs.total_worker_time DESC  
36.   
37. --语句3： 获取前20 执行的 SP 命令逻辑写入/分钟  
38.     SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',  
39.     qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',    
40.     qs.execution_count AS 'Execution Count',   
41.     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',   
42.     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
43.     qs.total_worker_time AS 'TotalWorkerTime',  
44.     qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
45.     qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
46.     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',  
47.     qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid  
48.     FROM sys.dm_exec_query_stats AS qs  
49.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
50.     WHERE qt.dbid = db_id() -- 当前数据库  
51.     ORDER BY qs.total_logical_writes DESC  
52.   
53. --语句4： 获取前20执行的 SP 命令的逻辑读取（内存压力）   
54.     SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads,   
55.     qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',  
56.     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',   
57.     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
58.     qs.total_worker_time AS 'TotalWorkerTime',  
59.     qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
60.     qs.total_logical_writes,  
61.     qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
62.     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid   
63.     FROM sys.dm_exec_query_stats AS qs  
64.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
65.     WHERE qt.dbid = db_id() -- 当前数据库  
66.     ORDER BY total_logical_reads DESC  
67.   
68. --语句5： 获取前20执行的 SP 命令由物理读取 （读取 I/O 压力）  
69.     SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',  
70.     qs.execution_count AS 'Execution Count',  
71.     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',    
72.     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
73.     qs.total_worker_time AS 'TotalWorkerTime',  
74.     qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
75.     qs.max_logical_reads, qs.max_logical_writes,    
76.     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid   
77.     FROM sys.dm_exec_query_stats AS qs  
78.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
79.     WHERE qt.dbid = db_id() -- 当前数据库  
80.     ORDER BY qs.total_physical_reads DESC  
81.   
82. --语句6： 获取前20执行的 SP 命令执行计数  
83.     SELECT TOP 20 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',    
84.     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
85.     qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
86.     qs.total_worker_time AS 'TotalWorkerTime',  
87.     qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
88.     qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
89.     DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'  
90.     FROM sys.dm_exec_query_stats AS qs  
91.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
92.     WHERE qt.dbid = db_id() -- Filter by current database  
93.     ORDER BY qs.execution_count DESC  

 

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

[sql] view plaincopy

1. SELECT DB_NAME(st.dbid) DBName  
2.       ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName  
3.       ,OBJECT_NAME(st.objectid,dbid) StoredProcedure  
4.       ,max(cp.usecounts) Execution_count  
5.       ,sum(qs.total_worker_time) total_cpu_time  
6.       ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time  
7.  into #temp  
8.  FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle  
9.       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st  
10.  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'  
11.  group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)   
12.  order by sum(qs.total_worker_time) desc  
13.   
14. WAITFOR DELAY '00:00:10'   
15.   
16. SELECT DB_NAME(st.dbid) DBName  
17.       ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName  
18.       ,OBJECT_NAME(st.objectid,dbid) StoredProcedure  
19.       ,max(cp.usecounts) Execution_count  
20.       ,sum(qs.total_worker_time) total_cpu_time  
21.       ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time  
22.  into #temp2  
23.  FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle  
24.       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st  
25.  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'  
26.  group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)   
27.  order by sum(qs.total_worker_time) desc  
28.   
29. SELECT a.DBNAMe, a.SchemaName, a.StoredProcedure,  
30. b.Execution_count - a.Execution_count as ExecCnt,  
31. b.total_cpu_time - a.total_cpu_time as CPU   
32. FROM #temp a inner join #temp2 b on a.DBName = b.DBname and a.SchemaName = b.SchemaName and a.StoredProcedure = b.StoredProcedure  
33. ORDER BY 5 desc  
34.   
35. drop table #temp  
36. drop table #temp2