一些优化你的SQL语句的TIPs
上一篇 / 下一篇 2012-07-12 13:14:12 / 个人分类:数据库
简介
.qJ&a,L"qI&J \$W051Testing软件测试网/R!Pl%Ma/gW对于写出实现功能的SQL语 句和既能实现功能又能保证性能的SQL语句的差别是巨大的。很多时候开发人员仅仅是把精力放在实现所需的功能上,而忽略了其所写代码的性能和对SQL Server实例所产生的影响(也就是IO,CPU,内存方面的消耗).这甚至有可能使整个SQL Server实例跪了。本文旨在提供一些简单的步骤来帮助你优化SQL语句。51Testing软件测试网t gf.tx
:B+lE!R"e,aYF1rsZ0 市面上已经有很多关于如何优化SQL Server性能的书籍和白皮书。所以本文并不打算达到那种深度和广度,而仅仅是为开发人员提供一个快速检测的列表来找到SQL语句中导致瓶颈产生的部分。51Testing软件测试网2y u-Q!O R p U^
51Testing软件测试网t|*b6eG5q在开始解决性能问题之前,合适的诊断工具是必须的。除去众所周知的SSMS和SQL Profiler,SQL Server 2008还带有众多DMV来提供关键信息。本篇文章中,我将使用SSMS和一些DMV来找到SQL的瓶颈
8pVVo0X;P051Testing软件测试网/h9v'fpNR%g V那么,我们从哪开始
1a5{X%MyL!Y/Q051Testing软件测试网:R6{H:UKzQ^D我的第一步是查看执行计划。这一步既可以通过SMSS也可以通过SQL Profiler实现,为了简便起见,我将在SMSS中获取执行计划。51Testing软件测试网|%F:@r.M
Zv.V2u Kx0 1) 检查你是否忽略掉了某些表的连接的条件,从而导致了笛卡尔积(Cross)连接(Join)。比如,在生产系统中有两个表,每个表中有1000行数据。这 其中绝大多数数据并不需要返回,如果你在这两个表上应用了Cross Join,返回的结果将会是100万行的结果集!返回如此数量的数据包括将所有数据从物理存储介质中读取出来,因而占用了IO。然后这些数据将会被导入内 存,也就是SQL Server的缓冲区。这会将缓冲区内的其它页Flush出去。
8o@~4iDmc.X051Testing软件测试网xm k,t.B#ePh Y2)查看你是否忽略了某些Where子句,缺少Where子句会导致返回额外不需要的行。这产生的影响和步骤一所产生的影响是一样的。51Testing软件测试网+bN-H"^ O1M.[ S6J]
X3L JBf0 3)查看统计信息是否是自动创建和自动更新的,你可以在数据库的属性里看到这些选项51Testing软件测试网W*q5q9M#KyUZZ
-Nn4{D H za M0在默认条件下创建一个新数据库,Auto Create Statistics和Auto Update Statistics选项是开启的,统计信息是用于帮助查询优化器生成最佳执行计划的。这份白皮书对于解释统计信息的重要性以及对于执行计划的作用解释的 非常到位。上面那些设置可以通过右键数据库,选择属性,在“选项”中找到。51Testing软件测试网UTv%w.pp0`
4)检查统计信息是否已经过期, 虽然统计信息是自动创建的,但是更新统计信息从而反映出数据的变化也同样重要。在一个大表中,有时候虽然Auto Update Statistics 选项已经开始,但统计信息依然无法反映出数据的分布情况。默认情况下,统计信息的更新是基于抽取表中的随机信息作为样本产生的。如果数据是按顺序存储的, 那么很有可能数据样本并没有反映出表中的数据情况。因此,推荐在频繁更新的表中,统计信息使用Full Scan选项来定期更新。这种更新可以放到数据库闲时来做。51Testing软件测试网b6?P-N#^yH
DBCC SHOW_STATISTICS命令可以用于查看上次统计信息的更新时间,行数以及样本行数.在这个例子中,我们可以看到Person.Address表上的AK_Address_rowguid索引的有关信息:
7a9E#nrR0USE AdventureWorks;51Testing软件测试网Y$xPN-r(q GO 'p7ga&p gp4n"R0DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);51Testing软件测试网 LG0bB:`9| GO |
Wz WgdW0 下面是输出结果,请注意Updated,Rows,Rows Sampled这三个列
tH4T |3vAo0O5uj&v)C7V$yXJAc0 如果你认为统计信息已经过期,则可以使用sp_updatestats这个存储过程来更新当前数据库中的所有统计信息:51Testing软件测试网!^0m,N"E0oe)|f
'U/QKmE#G1E,i0
:^uD1XG/t0Exec sp_updatestats |
c/B s4iC'T-rq0 或者使用FULLSCAN选项,则关于表Person.Address上的所有统计信息将会被更新:51Testing软件测试网6F7Ix7d6qgJ|
"K1u8K {0| u.^Bh q0
-Gw_8Oh0Yxt0UPDATE STATISTICS Person.Address WITH FULLSCAN |
5)查看执行计划是否出现任何表或者索引的扫描(译者注:不是查找),在大多数情况下(这里假设统计信息是最新的),这意味着索引的缺失。下面几个DMV对于查找缺失索引很有帮助:
._ FXbo_7zQ0*h\PJ6KJdy051Testing软件测试网;C$e g$axn5GTn
i) sys.dm_db_missing_index_details51Testing软件测试网m0S;}*p+G2hF ii) sys.dm_db_missing_index_group_stats51Testing软件测试网fMD AcIIG iii) sys.dm_db_missing_index_groups |
接下来的几个语句使用了上面的DMV,按照索引缺失对于性能的影响,展现出信息:
aX+m6S*F)\0GB2w:gI*Q&a0i0
U;} kexV c;P[0SELECT avg_total_user_cost,avg_user_impact,user_seeks, user_scans,51Testing软件测试网$Y6i
o2H D7Fr#r ID.equality_columns,ID.inequality_columns,ID.included_columns,ID.statement51Testing软件测试网9zR^HI&_3E4GL FROM sys.dm_db_missing_index_group_stats GS51Testing软件测试网R bb` o1H$i;E%|;p LEFT OUTER JOIN sys.dm_db_missing_index_groups IG On (IG.index_group_handle = GS.group_handle)51Testing软件测试网 oAo*d\;x[KB LEFT OUTER JOIN sys.dm_db_missing_index_details ID On (ID.index_handle = IG.index_handle) N+lF/jBaP3O0ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC |
你也可以使用数据引擎优化顾问来找出缺失的索引以及需要创建哪些索引来提高性能。51Testing软件测试网0ck2H u&BWqb
$h CB-?]k;B{ r0 6)查看是否有书签查找,同样,在执行计划中找到书签查找十分容易,书签查找并不能完全避免,但是使用覆盖索引可以大大减少书签查找。
]o4z(@ {0"DfA(RI_0 7)查看排序操作,如果在执行计划中排序操作占去了很大一部分百分比,我会考虑以下几种方案:51Testing软件测试网&R]dF#c-p|0_
51Testing软件测试网 OyAlQ"G a● 按照所排序的列创建聚集索引,但这种方式一直存在争议。因为最佳实践是使用唯一列或者Int类型的列作为主键,然后让SQL Server在主键上创建聚集索引。但是在特定情况下使用排序列创建聚集索引也是可以的
)`2T(Z;l5YG2JK.L)O051Testing软件测试网 C'qVw:R.B{%n● 创建一个索引视图,在索引视图上按照排序列创建聚集索引
x8FW5y@9Dg[D*R&gv051Testing软件测试网n0J_QC(wGj● 创建一个排序列的非聚集索引,把其他需要返回的列INCLUDE进去51Testing软件测试网5\!KKw2H8er
#}tc6yR0YR |0 8)查看加在表上的锁,如果所查的表由于一个DML语句导致上锁,则查询引擎需要花一些时间等待锁的释放。下面是一些解决锁问题的方法:51Testing软件测试网(@5TSH$`_)P
51Testing软件测试网sC J&ur9I%AID● 让事务尽可能的短
O H;w,X"u {NQ051Testing软件测试网2PE0B*w(r● 查看数据库隔离等级,降低隔离等级以增加并发51Testing软件测试网,xuY^^;NK)eiZ
51Testing软件测试网 Biu Fq7f● 在Select语句中使用表提示,比如READUNCOMMITTED 或 READPAST.虽然这两个表提示都会增加并发,但是ReadUnCommited可能会带来脏读的问题,而READPAST会只返回部分结果集
)TdM3Z.q6X J0Z[#mf(W,g'fH q0 9)查看是否有索引碎片,索引碎片可以使用sys.dm_db_index_physical_statsDMV轻松查看,如果索引碎片已经大 于30%,则推荐索引重建.而索引碎片小于30%时,推荐使用索引整理。索引碎片因为使查询需要读取更多的列从而增加了IO,而更多的页意味着占用更多的 缓冲区,因此还会形成内存压力。
zM&J B/g`d*N$s051Testing软件测试网Y]"]6VJ@D如下语句根据索引碎片的百分比查看所有索引:
l3uf(\S09A7iWM%g0Y\051Testing软件测试网L5^MXAa
*RC(o%t)Bg1~0Declare @db SysName;51Testing软件测试网z*|
[h6v~0U8vM.B SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name', |
下面语句可以重建指定表的所有索引:51Testing软件测试网 }nj I j7x1p
)u-y5WK#iN|0
yf*G B$v:p~"_0ALTER INDEX ALL ON <Table Name> REBUILD; |
#B ^ i"\k E0 下面语句可以重建指定索引:51Testing软件测试网 jq7x2tO1{
51Testing软件测试网~-ld@"K/dECM!A`51Testing软件测试网*^4F%CO/v
ALTER INDEX <Index Name> ON <Table Name> REBUILD; |
GL2_ TX0 当然,我们也可以整理索引,下面语句整理指定表上的所有索引:51Testing软件测试网f"C6O.dS%W-g9a
51Testing软件测试网v0G-_9S]9?51Testing软件测试网 ?3W%c!B b_5\8W9y
ALTER INDEX ALL ON <Table Name> REORGANIZE; |
3@-b0w]^)d$}0 下面语句指定特定的索引进行整理:51Testing软件测试网N9rdK#J$\
5xZ6p+e.[]m0v051Testing软件测试网1OqN"B0W?Q&Cw,?
ALTER INDEX <Index Name> ON <Table Name> REORGANIZE; |
在重建或整理完索引之后,重新运行上面的语句来查看索引碎片的情况。51Testing软件测试网.C!H;X/gw P
qc;t Tf0sV0 总结