SQL Server Performance 分析 – 之四

《SQL Server Performance 分析 – 之三》探讨了外因（索引）对 SQL 执行性能的影响，也为内因（查询引擎）开了个小头。今天终于可以拨开密云见天日了，让我们一起看看查询引擎带来的影响。

本文的重点是查询优化器的机制，包括查询操作符，查询优化树。

1) 逻辑处理顺序logical processing :

Select top (X)|Distinct 

        t1.Field1

        ,t2.Field4

        ,sum(t2.Field6) as Filed6 
From dbo.tableone t1

     Left|right|full Join dbo.tabletwo t2 

        on t1.Filed2 = t2.Field1 

Where t1.Field3 = ‘XXX’ 
Order by t2.Field5

这里的重点有三个：多表 join先是做笛卡儿积运算，然后根据Left,Right Join将内部表的不符合记录都删除，在这个例子里，假设dbo.tableone有10条记录，dbo.tabletwo有20条记录，首先会生成有10*20=200条记录的结果集，假设我们用的是left join,那么tableone里面符合Field3=’XXX’的记录都将选出来，而tabletwo里面没有匹配对的记录都将附上值NULL；join on 与where 的作用根据join type的不同也将影响执行顺序；order by 子句是最后第二步执行的，在这个时候，所有的获取数据的操作都已经完成了，排序的操作里面就可以用得字段的alias。

Microsoft除了四本 《 Inside SQL Server 》的书，其中有一本谈到了 T-SQL 的执行顺序，T-SQL 语句并不是像它所写的顺序一样，从 Select 开始执行，而是从 From 开始的：

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_condition>

(8) SELECT

(9) DISTINCT

(9) ORDER BY <order_by_list>

(10) <TOP_specification> <select_list>

2)影响优化器选择执行计划的因素：

2.1 Statistics: 及时更新Statistics

select name
    ,is_auto_update_stats_on 
from sys.databases 
where name = 'lenistest4'

alter database lenistest4
set auto_update_statistics on

2.2 Fragmentation of table and index:碎片会导致随机读(random read)，都只知道顺序读(sequential read)是效率最高的。那么怎么去判断我们要执行碎片整理了呢，什么样的碎片整理方法有效呢？在DMV里面有张表，Sys.dm_db_index_physical_stats ,我们要关注的一个字段就是avg_fragmentation_in_percent。

当avg_fragmentation_in_percent 在5%和30%之间的时候，用Alter index Recoganize;当avg_fragmentation_in_percent 大于30%的时候i，用Alter Index Rebuild。当然终极方法 drop index之后create index也是可以的，但是你想想会有什么后果。

2.3 Join Type: 关于join type的论述，网上有太多的资料可以查询了。我们在这里可以简单讲讲了：Hash Join, Merge Join, Loop Join，各自的适用范围有所不同， Hash Join 适用在没有索引可用的情况下，Merge join 适用在左右两表都要排序的场景下，Loop join适合小表的应用。至于怎么去改写这三种join的执行计划，下面有提到。

3)执行计划操作符：

参考：Showplan Logical and Physical Operators Reference

非常全面的微软精品，分析了所有逻辑运算符号下面，物理数据的读取方法。通过这些符号的认识，你能从“人海中多看了一眼”，一眼秒杀那些慢的操作符。

比如像： Table Scan, Index Scan 这种类型的符号用在了一张亿级表上。

4)手工干预执行计划的生成: hint

4.1 Left|Right|Full{Loop|merge|hash} join:

select top 500000 
    f.record_date
    , d.object_name
    , d.counter_name
    , d.instance_name
    , f.cntr_value 
from dimstatisticscounters d
 inner merge join fctstatisticscollection f 
    on f.row_id = d.row_id

4.2 With(index(index_name)):

select object_id
    ,column_id 
    ,column_name
from siebeldbTableSchema with(index(idx_obj_col_id))
where column_id = 11 
    and object_id = 1415428562

4.3 语句级别的 hint :

Select xxx 

from table_name 

option(table tableName

             index(indexName)

    ):

select object_id
    ,column_id 
    ,column_name
from siebeldbTableSchema
where table_name = 'S_CONTACT' 
    and column_id = 11 
    and object_id = 1415428562
option( table hint (siebeldbTableSchema
        ,index(idx_colstr_sts))
       )
       

这里还有这么一个坑，就是exposed object name必须和 from的表对象引用一致，否则出现类似这个错误：

select object_id
    ,column_id 
    ,column_name
from siebeldbTableSchema
where table_name = 'S_CONTACT' 
    and column_id = 11 
    and object_id = 1415428562
option( table hint (dbo.siebeldbTableSchema
        ,index(idx_colstr_sts))
       )

Msg 8723, Level 16, State 1, Line 62

Cannot execute query. Object ‘dbo.siebeldbTableSchema’ is specified in 
the TABLE HINT clause, but is not used in the query or does not match 
the alias specified in the query. Table references in the TABLE HINT 
clause must match the WITH clause.

5)获取执行计划: 执行计划的存储有2种方式，一种XML格式，一种文本格式。可视化的方法都是基于XML来展现的。

Show plan:一次只用一条命令，用完set off

SET SHOWPLAN_TEXT ON

SET SHOWPLAN_ALL ON

SET SHOWPLAN_XML ON

SET STATISTICS PROFILE ON

SET STATISTICS XML ON

这些命令都是捕获接下来要执行的SQL的执行计划。区别在于SHOWPLAN_TEXT,SHOWPLAN_ALL,SHOWPLAN_XML是不执行SQL的，只是做estimate;而statistics profile, statistics xml都是会执行 SQL的。

用这些命令的时候要注意两个地方：

首先，必须先执行set 命令，否则会出现类似这个错误：

set showplan_text on ;

select object_id
    ,column_id 
    ,column_name
from siebeldbTableSchema
where table_name = 'S_CONTACT'

Msg 1067, Level 15, State 2, Line 1

The SET SHOWPLAN statements must be the only statements 
in the batch.

其次， 要改变执行计划或者说要用另一种set，必须先关闭之前的set option。举个例子，第一次我们使用了 set showplan_text on，那么在使接下来用到的 showplan_xml on 起作用之前，先用set showplan_text off关闭之前的开关。

SSMS: 这个方法一开始用SQL Server的时候就会碰到，没啥好讲。就是点击图形化的勾选执行并包含查询计划的按钮。

Extended events: 

真有人这样干 ：sql server – How do I obtain a Query Execution Plan?。 

定义完这个event，就可以在SSMS里面直接起用他，并且获得可视化效果。

/*
    Generated via "Query Detail Tracking" template.
*/

CREATE EVENT SESSION [GetExecutionPlan] ON SERVER 
ADD EVENT 
    sqlserver.query_post_execution_showplan(
    ACTION(package0.event_sequence
        ,sqlserver.plan_handle
        ,sqlserver.query_hash
        ,sqlserver.query_plan_hash
        ,sqlserver.session_id
        ,sqlserver.sql_text
        ,sqlserver.tsql_frame
        ,sqlserver.tsql_stack)),
        /* Remove any of the following events 
        (or include additional events) as desired. */

    ADD EVENT sqlserver.error_reported(
    ACTION(package0.event_sequence
            ,sqlserver.client_app_name
            ,sqlserver.database_id
            ,sqlserver.plan_handle
            ,sqlserver.query_hash
            ,sqlserver.query_plan_hash
            ,sqlserver.session_id
            ,sqlserver.sql_text
            ,sqlserver.tsql_frame
            ,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]
            ([sqlserver].[database_id],(4)) 
            AND [package0].[equal_boolean]
            ([sqlserver].[is_system],(0))))
           ）
   ADD EVENT sqlserver.module_end(
       SET collect_statement=(1)
   
    ADD TARGET package0.ring_buffer
    WITH (MAX_MEMORY=4096 KB
            ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY=30 SECONDS
            ,MAX_EVENT_SIZE=0 KB
            ,MEMORY_PARTITION_MODE=NONE
            ,TRACK_CAUSALITY=ON
            ,STARTUP_STATE=OFF)
GO

以上针对捕获的SQL执行计划都是基于当前的，要看之前的执行计划，我们可以用DMV：

SELECT UseCounts
    , Cacheobjtype
    , Objtype
    , TEXT
    , query_plan
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)

这里的格式是XML。 并且列出了stored procedure里面，逐行逐句的SQL执行计划。

上面列出的都是我自己头脑里的第一个针对查询优化器的反应。比较零散，不成系统，所以从网络上看看别人尤其是出书的作者们是怎么分析这方面的知识的，有助于自己的梳理，也可以看到别人的知识框架与写作方法。

参考文章来源于 ：The SQL Server Query Optimizer

作者的写作手法与我的不同地方有很多，首先他是从框架入手，比如SQL Server 的各个engine 入手，这里涉及到的两个engine 是storage engine, relational engine。接着作者讲解了很多细节性的知识，包括各个组件是怎么衔接，每个组件的输入与输出。

Relational Engine也可以通俗地称为Query Processor，简单来讲，可以由四大步骤组成：

下一篇我将这片 《The SQL Server Query Optimizer》翻译过来，并且加入一些自己的理解，尽请关注！