从一个存储过程的优化说起

sp join 优化 sqlserver

最近有个客户SQL Server2012 Always On的混合系统,页面超时阀值基本都是30S左右,这对某些大型的报表业务来说真的比较尴尬。

这个存储过程运行完结果集在7W行左右,、  写了密密麻麻的1000多行,涉及到20多张千万级的表连接以及各种运算排序。第一次报超时的时候sp运行完跑了5min左右,开发人员说把sp重新创建一个且重命名再跑速度很快(10s左右),但是改回去又恢复原状。对这种现象第一反应就是存储过程的Parameter Sniffing(参数嗅觉),什么是parameter sniffing?

存储过程的变量无外乎两种:

一种是存储过程外部定义的,运行的时候必须给他代入值,这种变量存储过程在编译的时候知道它是多少,如:

create proc test (@i int) as ...   ,运行时:exec test 10;

另一种是存储过程内部定义,它的值是sp内部运行后才得到的,这种变量sp在编译的时候不知道它的确切值,如:

Create proc test2(@i int) as

Declare @j int

Set @j=@i

...   这里的@j变量就是存储过程运行过程中算出来的

 

 

SQL Server在处理存储过程的时候,是一次编译,多次重用的。

对于上述第一种情况就引申出parameter sniffing,第一次运行时代入的值生成的执行计划,是不是就适合所有变量呢?特别是对于离散型比较大的数据,举个例子很可能第一次生成的nested loop执行计划就不适合后面大数据量的hash loop,看过一篇牛人的测试博客,性能上有时差别数十倍不止。

对于上述第二种情况引申出本地变量概念,它有时候可能是parameter sniffing的解决方法。它会根据表里的数据分布,猜测出一个返回值,那么不管后面代入参数是多少,执行计划都是一样的,这种方式得到的执行计划比较中庸,不会最慢也不会最优。

所以测试了一下在spcost最大的一段sql中加入了option (recompile)强制让它每次执行到这里的时候都重新编译,事实证明效果还是挺好的,这一段的重编译的时间和消耗相比之前的execution time已经很低了,语句基本在10s内能跑完。就这样大概运行了两个星期左右,这个sp又坍塌了。。。恢复到之前的5minoption(recompile)不再管用了。第一时间有试过让整个sp重编译而不是某一段sql(这个sp中有很多if..else...判断),加入with recomplie,运行速度在8s内,速度上时可以接受的,但是整个sp重编译的时候cpu cost太大,几度cpu飙到90%,如果批量并行运行肯定是不行的。没办法只能从execution plan着手看是否能有优化的地方。

对于这么大的sp执行计划看起来是比较头疼的,过程比较漫长,最后找到了这个nested loop(简略的totalcost截图如图1)。我修改了SP里的这一段sql,让它强制走Merge joinHash join(对这个参数所得数据量来说两者效率差不多),最后7W行数据拉出仅用5S左右(图2,图3)。estimated rows与实际返回rows相差很大,导致这一步SQL Server自己没能找到最佳的执行计划。


                                                                             图1


                                                                                图2

同时通过一部分表的IO和cpu消耗也可以比对该例下nested loops join和merge或hash join的性能差距,相差近百倍:图3为nested loops join方式,图4为merge join方式


                                                                       图3

                                                                        图4



从原执行计划看,OUTER Tablet_rb_truck,INNER Tablet_truck表,OUTER TABLE返回行为48789行,NESTED LOOPS JOIN导致INNER TABLE的执行次数也高达48789次。同时可以看出这两步的totalsubtreecost都很高(一个2.4,一个4.4)。

NESTED LOOPS JOIN是一种最基本的JOIN方式,以其中一张表AOUTER TABLE,另外一张表BINNER TABLE。对于A表的每条记录,到B表中筛选符合join条件的行出来。这种连接方式无需建立其他的数据结构,所以比较省内存空间,同时无需tempdb的空间。但缺点也很明显,它的算法复杂度=INNER TABLE乘以OUTER TABLE,尤其是OUTER TABLE比较大的时候,INNER TABLE会被扫描很多次,所以它比较合适数据量比较小的表连接。对于我们这个案例,它很明显是不适合的。

我们用到的MERGE JOIN这种方式,它的逻辑是从两个table中各取一条记录,比较如果相等,则返回,若不等,则丢掉那个小的值,按顺序取下一个更大的继续比较。整个算法的复杂度就是两边数据集里较大的那个,比起nested loops join两个数据集相乘确实小了很多,但它的局限性很多(有兴趣朋友可以查查资料参考下)。但到我们这个例子里,join字段上都有唯一非聚集索引,两边的数据集都是通过index seek排好序过的,没有many-to-manyjoin消耗,所以它的cost是比较低的!同样这里改用hash join也是可以的,测试下来发现和merge joincost是差不多的。Hash join适合大数据集的匹配,而且没什么限制条件,不需要数据集事先排好序,也不要求上面有索引,而且可以比较容易使用多处理器的并行执行计划,对于大数据集是很有优势的,但是它需要在内存里建立hash表,hash表需要在内存或tempdb中存放,所以它对数据库的负荷也是比较重的,这三种join方式各有利弊和应用场景,dba需要根据实际情况选择合适的方式来提升效率。

相关推荐

  • 网站优化站内和站外优化的全过程   网络上的SEO知识包罗万象,当然,我下面说的可能你们都知道,但现在是总结SEO从开始到结束的站内和站外,希望你们可以看完:  1、SEO大体分为站内和站外两大块,首先我们先讲站内SEO  第一:网站标题(title,keywords,d
  • 淘宝终极营销之转化率14:让运营水平提升一个境界的转化率优化技巧【换一个视角】泰国女孩撞脸鹿晗 【前言:读懂并理解接下来的几篇文章,你的水平会上升到一个新境界】不要总站在自己的角度上去思考问题,因为买你东西的是消费者,消费者买还是不买,你必须清楚消费者是怎么考虑的!所以接下来的几篇文章,我会从买家行为的角度给大家做一个解读,你知道消费
  • 韩语网站优化 怎样做一个WEB2.0站点 第一点:内容的暴露。最简单的理解博客现在是通常连标题带内容列出,而论坛相对是标题的陈列和一级一级由大标题到小标题,最后才可以看到内容。第二,把需要打开进入才有的功能,呈现在入口位置。第三,由于新鲜玩儿意儿层出不穷,所以解释和引导到处可见,对
  • 侠客行-达摩院 让运营水平提升一个境界的转化率优化技巧【换一个视角】 当当当当~~~新年第一篇,以后会继续加油哦!!本文地址:淘宝大学南京侠客行官网不要总站在自己的角度上去思考问题,因为买你东西的是消费者,消费者买还是不买,你必须清楚消费者是怎么考虑的!所以接下来的几篇文章,我会从买家行为的角度给大家做一个解
  • HELP,多线程通过同一个socket发送数据到服务端,socket底动会不会同步呢? HELP,多线程通过同一个socket发送数据到服务端,socket底动会不会同步呢?最近写了一个网络通过框架,在运行过程中,在同一时刻,多个线程通过同一个socket发送数据到服务端发送数据到服务器,程序在VMWARE上一切正常,但到实际
  • 一个存储过程一分钟一键生成所有实体类(适用于FW3.5以上版本) 刚刚这个存储过程是的输出采用的以下格式C#codepublicstringaaa{set{_aaa=value;}get{return_aaa;}}修改了一个小细节,输出后的格式为C#codepublicstringaaa{get;set;
  • 分享网站优化之整站优化的过程 分享网站优化之整站优化的过程网站优化一般分为指定关键词优化和整站优化。指定关键词优化就不细说了,很容易理解。下面来说说整站优化:整站优化的流程包括网站优化策划与营销思维的结合,每完成一个项目,我们都会不断总结经验完善搜索引擎优化的流程。我们
  • 网站优化之整站优化的过程 网站优化之整站优化的过程网站优化一般分为指定关键词优化和整站优化。指定关键词优化就不细说了,很容易理解。下面来说说整站优化:整站优化的流程包括网站优化策划与群发软件的结合,每完成一个项目,我们都会不断总结经验完善搜索引擎优化的流程。我们务求
  • 网站前期优化过程中比较常见的问题解答 很多SEO新手们在做网站SEO优化的过程中,常常会碰到一些问题,而又不知道怎么解决,那么,本文就主要针对这些的SEO新手们,列出了一系列在网站优化过程中比较常见的问题,本文主要讲得是网站前期的优化过程中,遇到的一些比较常见的问题,从而,帮助
  • 网站优化过程中通过提升优质量实现搜索优化价值最大化 什么是优化质量度?具体而言就是进行网站优化过程中各种需要我们优化的分支,比如外链、网站评估内容友情链接、网站结构等等细节都需要注重质量,这就是优化需要把握的质量度问题,作为站长我们究竟可以通过那些细节来分析和思索这些因素呢?第一,网站外链质
  • 优化托管:SEO优化过程中不能触犯的四个错误 我们在SEO优化的过程中或多或少都会犯一些错误,我们犯的这些错误当中有的错误可以轻松地弥补,但是我们有的一些错误一旦犯了即使别的优化手段做得再多也起不了很大的作用。下面我们大家来看下SEO优化过程当中四个致命的错误。1、外链发布数量来回变化
  • 存储过程的编写经验和优化 存储过程的编写经验和优化一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  二、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会

热门评论

你的评论

就没有什么想说的吗?

最新博客

关于我们 加入传客 媒体报道 帮助中心 传客活动 免责声明 联系我们 移动版 移动应用

©2017传客网    琼ICP备15003173号-2    

本站部分文章来源于互联网,版权归属于原作者。
本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长(weishubao@126.com),我们收到后立即删除。
站内所有资源仅供学习与参考,请勿用于商业用途,否则产生的一切后果将由您自己承担!

X