在SQL server的性能优化过程中的常见技巧

在SQL server 的性能优化过程中，TSQL的语句优化是很重要的一环。当您使用各种手段找出系统最需要优化的语句后，应该如何对该语句进行优化呢？下面列出一些TSQL 语句优化的常见技巧。 1． 语句的执行计划分析 首先要对该语句的执行计划（execution plan）进

在SQL server 的性能优化过程中，TSQL的语句优化是很重要的一环。当您使用各种手段找出系统最需要优化的语句后，应该如何对该语句进行优化呢？下面列出一些TSQL 语句优化的常见技巧。

1． 语句的执行计划分析

首先要对该语句的执行计划（execution plan）进行分析，找出语句运行慢的原因。比如说，

在检查执行计划是否包含table scan /index scan等昂贵的操作？

对table， worktable是否进行了大量的逻辑读？

是否使用了不合适的join类型？

并发（串行）执行计划是否不合适 等等

举一个的例子，

Table 'myTable'. Scan count 1, logical reads 15877, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 174 ms.

Rows Executes StmtText

------ --------- ----------------------------------------------------------------------------------------------

10 1 select EmpNo, Code, MAX(DueDate) from myTable where EmpNo = '21250' group by EmpNo,Code

10 1 |--Stream Aggregate(GROUP BY:([myTable].[Code]) DEFINE:([Expr1002]=MAX([CERTIFICAT

10 1 |--Sort(ORDER BY:([myTable].[Code] ASC))

10 1 |--Table Scan(OBJECT:([SSS].[dbo].[myTable]), WHERE:([myTable]

大家看上图，logical reads15877，很大的一个值。 执行计划里面有table Scan，那么明显就是一个缺少index导致表被全扫描的例子。加一个索引就会好了。

再看另外一个例子：

表 'myTableStatus'。扫描计数 0，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次

表 'myTable'。扫描计数 8，逻辑读取 1408666 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次

表 'myTableType'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 'Transactions'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

Rows Executes StmtText

-------------------- -------------------- -----------------------------------------------------------------------------------

2 1 SELECT * FROM vwmyTableItems WHERE 1=1 AND "myTableTypeId" = 1 AND "BranchId" = 1

2 1 |--Sort(DISTINCT ORDER BY:([j].[myTableID] DESC, [j].[UUID] ASC, [j].[ParentID] A

2 1 |--Compute Scalar(DEFINE:([Expr1009]=[Log_DB].[dbo].[FormatDate]([Log_DB].[d

2 1 |--Nested Loops(Inner Join)

1 1 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[Transactions].[PK_

2 1 |--Nested Loops(Inner Join)

1 1 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[myTableType].

2 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([j].[myTableSta

0 0 |--Compute Scalar(DEFINE:([Expr1011]=(((substring(replic

2 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnI

468971 1 | |--Index Seek(OBJECT:([Log_DB].[dbo].[myTable]

2 468971 | |--Clustered Index Seek(OBJECT:([Log_DB].[dbo]

2 2 |--Clustered Index Seek(OBJECT:([Log_DB].[dbo].[myTableS

从上面计划看，问题是表myTable逻辑读取 1408666 次，非常巨大。另外Nested Loop的cluster index seek 执行了468971 次. 这样的执行计划导致CPU 很高。 如何减少逻辑读和减少Nested Loop里面的执行次数是关键。 对这类执行计划，可以考虑改写语句，或者尝试不同的join type。比如，使用option(hash join) 来改变join类型，看看性能是否改善。

2． 语句的常见优化手段

分析完毕执行计划，你知道了语句为什么慢。接下来语句的优化常见方法是如下。

表/索引 的统计信息是否最新？运行update statistics with FULLSCAN更新统计信息再看看。

对有table scan或者index scan的地方，仔细检查是否缺少索引？运行Database tuning wizard对该语句分析下，或者手工加上索引看看。也可以查询sys.dm_db_missing_index_details来看看系统是否大量缺少index。

join的类型是否合适，使用join hint试试试用不同的join类型。

使用index hint 试下不同的index

index是否合适，索引字段的顺序是否最佳？

WHERE 语句的写法是否不够有效率？比如说，它是否包含了OR， ,等符号？

语句里面是否使用了自定义函数UDF？UDF常导致table scan。

语句是否导致频繁recompile？ 看看是不是temp table导致的。

语句是否返回了大量的结果集合？ 返回几万十几万笔资料是有些多哦。可以使用TOP N限制结果集。

是否使用了低效率的游标？尽量使用fast_forward readonly 类型的游标比较好。

如果语句开销很大，那么该语句是否有必要？能否减少它的执行次数？

3．简化和重写语句

在系统的整体性能优化里面， TSQL优化优先级并不是最高的。 下面按照对系统性能影响的重要程度依次列出优化的几个层面：

Application

Database Design

Microsoft SQL Server

Operating System

Hardware

也就是说，程序的优化效果最明显，接下来是的设计优化，再接下来才是TSQL的优化。硬件的优化是最后考虑比较好。一味增加内存和CPU未必能够解决性能问题。

在程序的优化里面，如果能够改写数据库访问逻辑，改写TSQL语句， 或者简化TSQL语句，有时候你能够获得惊人的性能回报。