在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语句,有时候你能够获得惊人的性能回报。
下一篇: div模拟默认select效果实例分享
推荐阅读
-
iOS开发教程之常见的性能优化技巧
-
推荐Sql server一些常见性能问题的解决方法
-
SQL Server优化技巧——如何避免查询条件OR引起的性能问题
-
淘宝卖家在优化店铺搜索流量过程中,比较常见的误区以及应对方法
-
SQL SERVER性能优化综述(很好的总结,不要错过哦)第1/3页
-
在SQL Server中,索引是一种增强式的存在,这意味着,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能,
-
Sql server常见性能问题的总结
-
Sql server常见性能问题的总结
-
SQL SERVER性能优化综述(很好的总结,不要错过哦)第1/3页
-
【SQL Server性能优化】删除大量数据的方法比较