SQL优化（1） - 可能存在问题语句查找

在查找运行比较慢、次数多的语句之前先学习的基础知识。

一、基础知识

 1、sys.dm_exec_sql_text函数（sys.dm_exec_query_plan(plan_handle)与这个函数类似可以查询执行计划）

 语法：sys.dm_exec_sql_text(sql_handle | plan_handle)  

 说明：返回由指定的 sql_handle 标识的 SQL 批处理的文本。 该表值函数将替换系统函数 fn_get_sql。

返回列如下：

--dbid    smallint    数据库的 ID。
--对于临时和预定义 SQL 语句，指编译这些语句时所在的数据库的 ID。
--objectid    int    对象的 ID。
--对于临时和预定义 SQL 语句为 NULL。
--number    smallint    对于带编号的存储过程，此列返回存储过程的编号。 有关详细信息，请参阅sys.numbered_procedures (Transact-SQL)。
--对于临时和预定义 SQL 语句为 NULL。
--encrypted    bit    1 = SQL 文本已加密。
--0 = SQL 文本未加密。
--text    nvarchar(max)    SQL 查询的文本。
--对于已加密对象为 NULL。

2、CROSS APPLY 运算符

--使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。
--APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。
---详情可查看：https://msdn.microsoft.com/zh-cn/library/ms175156.aspx

3、sys.dm_exec_query_stats表常用列说明
--statement_start_offset    int    指示行所说明的查询在其批查询或持久化对象文本中的开始位置（以字节为单位，从 0 开始）。
--plan_handle    varbinary(64)    表示查询所属的已编译计划的标记
--creation_time    datetime    编译计划的时间
--last_execution_time    datetime    上次开始执行计划的时间。
--execution_count    bigint    计划自上次编译以来所执行的次数
--total_worker_time    bigint    此计划自编译以来执行所用的 CPU 时间总量（以微秒为单位报告，但仅精确到毫秒）。
--last_worker_time    bigint    上次执行计划所用的 CPU 时间
--min_worker_time    bigint    此计划在单次执行期间所用的最小 CPU 时间（以微秒为单位报告，但仅精确到毫秒）。 1
--max_worker_time    bigint    此计划在单次执行期间所用的最大 CPU 时间（以微秒为单位报告，但仅精确到毫秒）。 1
--total_physical_reads    bigint    此计划自编译后在执行期间所执行的物理读取总次数。
--***_logical_writes    此计划自编译后在执行期间所执行的逻辑写入总次数。当查询内存优化的表时，此项将始终为 0。
--total_rows    bigint    查询返回的总行数。 不能为 Null。当本机编译的存储过程查询内存优化的表时，此项将始终为 0。

4、

二、下面我们进入正题

1、查SQL 语句执行时间
SELECT 
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
 ,execution_count N'执行次数'
 ,plan_handle
  ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1
  ,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句'
   ,creation_time N'语句编译时间' ,
   last_execution_time N'上次执行时间'
   FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
   WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
   ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
   not like '%fetch%' and qs.last_execution_time between GETDATE()-1 and GETDATE() ORDER BY total_elapsed_time / execution_count DESC;

2、查询最近执行的Sql语句
SELECT TOP 1000
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time/1000 AS '耗时',--ms
       QS.last_worker_time AS '12',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       QS.creation_time AS '执行时间' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN GETDATE()-1 AND GETDATE()--这里为查询的时间段
ORDER BY  QS.total_elapsed_time DESC

3、查询正在执行的SQL

SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),
  [User] = nt_username, [Status] = er.status,
  [Wait] = wait_type,
  [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
   (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                       * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                        [Parent Query] = qt.text,
                        Program = program_name, Hostname,
                        nt_domain, start_time
 FROM    
      sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid
      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
 WHERE     session_Id > 50 /* Ignore system spids.*/ -- AND session_Id  -- NOT IN (@@SPID)