6.性能调整和优化

　　性能调整和优化，涵盖表结构设计、存储方式设计、查询设计、参数调整等方面，前文介绍的压缩和并行都是简单的参数调整手段，如果是实际的查询，而不是基准测试，我们就需要充分利用SQL Server数据库的功能，针对每个查询单独优化。

　　（1）执行计划的查看

　　要利用改写查询语句提高执行效率，首先要了解现有的执行计划。

　　执行计划的查看，使用set showplan_text /showplan_all/showplan_xml开关，showplan_text显示比较简略的执行计划信息。另2个开关的使用参见文档。

　　下面创建一个表，观察添加主键前后的执行计划。表扫描变成了聚集索引扫描。

1> create table t(c varchar(10) not null,c1 int,c2 varchar(10));
2> go
1> insert into t values(1,2,3);
2> insert into t values(2,21,31);
3> insert into t values(3,31,91);
4> go
(1 行受影响)
(1 行受影响)
(1 行受影响)
1> set showplan_text on
2> go
1> select sum(c1) from t;
2> go
StmtText
-------------------------
select sum(c1) from t;

(1 行受影响)
StmtText
-------------------------------------------------------------------------------------------
   |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
        |--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([tpch].[dbo].[t].[c1]), [Expr1006]=SUM([tpch].[dbo].[t].[c1])))
             |--Table Scan(OBJECT:([tpch].[dbo].[t]))

(3 行受影响)
1> set showplan_text off
2> go
1> alter table t add constraint t_pk  PRIMARY KEY(c);
2> go
1> set showplan_text on
2> go
1> select sum(c1) from t;
2> go
StmtText
-------------------------
select sum(c1) from t;

(1 行受影响)
StmtText
--------------------------------------------------------------------------------------------
   |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END))
        |--Stream Aggregate(DEFINE:([Expr1004]=COUNT_BIG([tpch].[dbo].[t].[c1]),
[Expr1005]=SUM([tpch].[dbo].[t].[c1])))
             |--Clustered Index Scan(OBJECT:([tpch].[dbo].[t].[t_pk]))

(3 行受影响)

 　　注意这个开关只是显示SQL语句的执行计划，并不实际执行该语句。另外，text显示的主要是访问路径，并不包含成本的估算值。

　　(2) 统计信息收集和管理

　　正确的统计信息对查询优化器得出较好的执行计划有十分重要的影响，在大量插入或更新数据以后，包括对表进行rebuild后，需要重新收集统计信息。前面已经使用了sp_createstats和sp_updatestats命令。下面再介绍另一个CREATE STATISTICS命令，可以收集指定的统计信息。语法如下：

CREATE STATISTICS <stats_name> ON <table_name> (column_list) WITH FULLSCAN

 　　比如，对上面第一步创建的t表的c列收集统计信息。

1> CREATE STATISTICS st_t_c on t(c) with fullscan;
2> go

 　　(3) 利用提示

　　SQL Server也提供了人工添加提示来影响执行计划，这需要相当丰富的开发经验。

　　提示主要分3种，联接提示、表提示和查询提示。

　　联接提示：联接提示用于指定查询优化器在两个表之间强制执行联接策略。

<join_hint> ::=     { LOOP | HASH | MERGE | REMOTE }

 　　参数

　　LOOP | HASH | MERGE

　　指定查询中的联接应使用循环、哈希或合并。使用 LOOP |HASH | MERGE JOIN 在两个表之间强制执行特定联接。不能同时将 LOOP 与 RIGHT(或 FULL)指定为联接类型。

　　REMOTE

　　指定联接操作在右表处执行。这在左表是本地表而右表是远程表的情况下很有用。

　　表提示：通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项，表提示可在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。

WITH ( <table_hint> [ [ , ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW
  | FORCESEEK  --强制表扫描
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK   --锁定表
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

 　　查询提示：查询提示指定应在整个查询中使用指示的提示。查询提示影响到语句中的所有运算符。如果主查询中涉及 UNION，则只有涉及 UNION 运算符的最后一个查询才能有OPTION 子句。查询提示作为 OPTION 子句的一部分指定。如果一个或多个查询提示导致查询优化器不能生成有效计划，则引发 8622 错误。

<query_hint > ::=
{ { HASH | ORDER } GROUP
  | { CONCAT | HASH | MERGE } UNION
  | { LOOP | MERGE | HASH } JOIN
  | FAST number_rows
  | FORCE ORDER
  | MAXDOP number_of_processors
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN
  | KEEP PLAN
  | KEEPFIXED PLAN
  | EXPAND VIEWS
  | MAXRECURSION number
  | USE PLAN N'xml_plan'
  | TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}

 　　注意：由于 SQL Server 查询优化器通常会为查询选择非常好的执行计划，因此开发人员和数据库管理员只有在不得已时才可使用提示。

　　下面是一个查询提示的例子。

　　对上述t表删除聚集主键，改用非聚集索引以启用表扫描。

1> set showplan_text off
2> go
1> alter table t drop constraint t_pk;
2> go
1> alter table t add constraint t_pk  PRIMARY KEY nonclustered(c);
2> go
1> set showplan_text on
2> go
1> select * from t; --访问全部列则默认使用表扫描
2> go
StmtText
-------------------
select * from t;

(1 行受影响)
StmtText
-------------------------------------------
   |--Table Scan(OBJECT:([tpch].[dbo].[t]))

(1 行受影响)
1> select c from t; --访问主键列则默认使用索引扫描
2> go
StmtText
-------------------
select c from t;

(1 行受影响)
StmtText
--------------------------------------------------
   |--Index Scan(OBJECT:([tpch].[dbo].[t].[t_pk]))

(1 行受影响)
1> select c from t WITH (FORCESEEK); --访问主键列强制使用表扫描失败
2> go
消息 8622，级别 16，状态 1，服务器 WIN-NF10S6S7KTC，第 1 行
由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。
1> select c from t with (index(t_pk)); --访问主键列强制使用索引扫描，这和默认选择一致
2> go
StmtText
--------------------------------------
select c from t with (index(t_pk));

(1 行受影响)
StmtText
--------------------------------------------------
   |--Index Scan(OBJECT:([tpch].[dbo].[t].[t_pk]))

(1 行受影响)
1> select * from t with (index(t_pk)); --访问全部列强制使用索引扫描，执行计划变化了
2> go
StmtText
--------------------------------------
select * from t with (index(t_pk));

(1 行受影响)
StmtText
------------------------------------------------------------------------------------------
   |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
        |--Index Scan(OBJECT:([tpch].[dbo].[t].[t_pk]))
        |--RID Lookup(OBJECT:([tpch].[dbo].[t]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

(3 行受影响)
1>

 　　还有其他的优化手段，比如添加必要的索引，由于时间所限，没有进行测试。从对SQL Server的表添加主键的效果来看，应该也是很有效的。有兴趣的读者可以自行检验。SQL Server还有很多功能，如分析函数、分区、递归with查询，已经超出了tpc-h测试的范围，也留给读者测试。