首页 > 代码库 > 转--也不知是哪位大侠写的了

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

试应用环境: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   

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