创立一个囤积进度:dba_WhatSQLIsExecuting

翻看被缓存的询问安插

  然后执行这一个蕴藏进度就能够查阅相关的音讯了。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
    st.text AS [SQL]
    , cp.cacheobjtype
    , cp.objtype
    , COALESCE(DB_NAME(st.dbid),
        DB_NAME(CAST(pa.value AS INT))+’*’,
        ‘Resource’) AS [DatabaseName]
    , cp.usecounts AS [Plan usage]
    , qp.query_plan
FROM sys.dm_exec_cached_plans cp                      
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = ‘dbid’
  AND st.text LIKE ‘%这里是查询语句包括的内容%’  

  MS SQL 实施进程中履长势况,可查阅当前正值执行的sql等音信

 

  当前试行到哪句SQL,等,那一个能够辅助长日子的SQL实践做进程条。

结果是:

  USE [RMA_DWH]

图片 1

  GO

 

  /****** Object: StoredProcedure
[dbo].[dba_WhatSQLIsExecuting] Script Date: 07/12/2013 10:28:27
******/

能够依据查询字段来遵照注重字查看缓冲的询问安排。

  SET ANSI_NULLS ON

 

  GO

翻看某一询问是什么运用查询安排的

  SET QUOTED_IDENTIFIER ON

 

  GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset
    END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END – qs.statement_start_offset)/2) + 1)
LIKE ‘%点名询问包括的字段%’  

  CREATE PROC [dbo].[dba_WhatSQLIsExecuting]

 

  AS

结果是:

  /*——————————————————————–

图片 2

  Purpose: Shows what individual SQL statements are currently
executing.

 

  ———————————————————————-

查阅数据库中跑的最慢的前十几个查询以及它们的实施安排

  Parameters: None.

 

  Revision History:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
                                     AS [Total Duration (s)]
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
                               AS DECIMAL(28, 2)) AS [% CPU]
  , CAST((qs.total_elapsed_time – qs.total_worker_time)* 100.0 /
        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
  , qs.execution_count
  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
                AS DECIMAL(28, 2)) AS [Average Duration (s)]
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     
    ((CASE WHEN qs.statement_end_offset = -1
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset
      END – qs.statement_start_offset)/2) + 1) AS [Individual Query
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC                    

  24/07/2008
[email protected]
Initial version

图片 3

  Example Usage:

 

  1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting

翻看数据库中哪些查询最开支能源拉动你消除难题

  ———————————————————————*/

 

  BEGIN

被卡住时间最长的前十多少个查询以及它们的实践布置

  – Do not lock anything, and do not get held up by any locks.

 

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST((qs.total_elapsed_time – qs.total_worker_time) /     
        1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
        AS DECIMAL(28,2)) AS [% CPU]
  , CAST((qs.total_elapsed_time – qs.total_worker_time)* 100.0 /
        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
  , qs.execution_count
  , CAST((qs.total_elapsed_time  – qs.total_worker_time) /
1000000.0
    / qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average
(s)]
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC                      

  – What SQL Statements Are Currently Running?

结果如图:

  SELECT [Spid] = session_Id

图片 4

  , ecid

 

  , [Database] = DB_NAME(sp.dbid)

找寻那类查询也是数据库调优的必须品