T-SQL语句优化的常见技巧
在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优化优先级并不是最高的。 下面按照对系统性能影响的重要程度依次列出优化的几个层面：
l Application
l Database Design
l Microsoft SQL Server
l Operating System
l Hardware
也就是说，程序的优化效果最明显，接下来是数据库的设计优化，再接下来才是TSQL的优化。硬件的优化是最后考虑比较好。一味增加内存和CPU未必能够解决性能问题。
在程序的优化里面，如果能够改写数据库访问逻辑，改写TSQL语句， 或者简化TSQL语句，有时候你能够获得惊人的性能回报。关于SQL语句的优化技术，可以参考之前的博文：
https://blogs.msdn.com/b/apgcdsd/archive/2011/01/11/sql-1.aspx
https://blogs.msdn.com/b/apgcdsd/archive/2011/01/11/sql-2.aspx
Comments
- Anonymous
November 20, 2012
如果数据量非常大，但是又非常重要，必须要求高度的事务完整性，当机器硬件已经达到极限的情况下，那么，我觉得要处理的话，必须利用分布式方案了，如：分布式分区视图，可以横向扩展，添加更多的机器进来，必然在伸缩性上能够解决这个问题，但是微软官方上说“分布式事务以及跨数据库的事务”是不能进行镜像或者Always on的，那么，如何解决呢？