摘要:本文是转载,地址:http://blog.joycode.com/juqiang/archive/2007/01/19/91848.aspx
第一步,在业务高峰期抓取样本数据(2个小时左右)。采用的工具是sqlserver自带的profiler,也叫事件探查器,如下图:
进入后,点击最左面的按钮,建立一个新的跟踪:
登录需要用DBO权限,所以可以用sa登录,也可以用windows集成验证方式(如果当前登录的就是sqlserver的话)
新建跟踪,一共有4个tab页进行配置,首先看第一个。跟踪名称不用更改,默认的即可。保存一共有两种方式,一是文件,扩展名是.trc(这种方式方便你把客户那里的跟踪结果发给你),其二是数据库中的表。
为了分析方便,我们把它另存为表。此时sql提示你重新进行登录,这里我们把表保存到master中
假设表名字叫做jq(如果有重复的,系统会提示是否覆盖)
确定后回到了刚才的第一个tab页中:
然后切换到第二个选项卡中:
左面列出了各种事件类(Event Class),右面是当前已有的事件类。对于性能调优,我们不需要安全审核、会话信息,点击删除按钮即可:
继续切换到第三个tab页上,这里的数据列默认就够了,当然,如果你看着不顺眼,可以把Appname/NT username等都删除。
最后一个tab页上,我们需要把系统自己产生的事件ID屏蔽掉:
把那个排除系统ID进行check即可,如下图:
所有项目配置好后,点击“运行”按钮。持续运行两个小时左右即可(业务高峰期,能典型的反应客户最近一段时间内的业务模式)
好了,第一步的准备工作完成了,等待一段时间后,我们开始检查刚才自动保存到master中的表jq。
第二步,开始查找影响速度的地方。
打开查询分析器(sql analyzer),登录到master中,从 表jq里面按照I/O倒序,读取若干个sql。根据我的习惯,一般是读取1000条记录。为什么根据I/O来找呢,而不是根据时间来找呢?原因很简单,一句SQL执行,“稳定”的是I/O,而duration是一个不稳定的因素。我们进行sql调优的目的,就是降低I/O成本,从而提高效率。(一般而言,I/O降低了,duration自然就会降低)详细内容,参考我以前的post:http://blog.joycode.com/juqiang
执行完成后,我们仔细看下面的输出。
1、 XL_TALLY_Proc04这个sp的reads最大,将近100w,duration也达到了25秒多。
2、 Erp_IM_GMBill_GetBill这个sp的I/O不算大,才7w,duration平均都在1秒多点。但是这个sp执行的次数非常多。
经过询问客户,XL_TALLY_Proc04这个sp执行的频度很低,一天也就一两次,但是Erp_IM_GMBill_GetBill大概5分钟就要一次。这样整体I/O就占用的非常大。
所以这里我们要重点分析Erp_IM_GMBill_GetBill这个sp,而不是第一个!
总结一个原则就是:调整的重点是客户最关心的内容,是执行频度最高、看起来I/O又比较大的那种。I/O最大的,不一定是我们要优先解决的内容。
第三步,开始分析刚才看到的那个语句。既然我们要分析I/O,那么就要把I/O打开,这样每次调整sql,我们都能随时看到I/O的变化情况。这句很有用处地:set statistics io on
单纯看I/O变化,我们会晕倒的。因为我们不知道自己做的任何改动,对I/O是如何产生影响的。所以,还要看sql的执行计划是怎佯的。 在查询分析器中,我们按Ctrl+K,或者如下图的菜单,check上即可。
好了,准备工作都做好了,下面开始干活了。
我们首先看sql语句的调优,假设下面这条sql语句性能低下:
上面的sql一共读取了6636条数据,逻辑读是1126。那么这个I/O是否合理呢?大了还是小了?还有改进的余地吗?我们看执行计划:
哦,一共4个咚咚在里面。Index seek的成本占了2%, index scan的占了47%,hash match占了51%,select最终是0%。我们应该牢记第二个原则,所有的index,尽可能的都走index seek。
我们看一下billsoflading的索引信息:
当前索引为什么走scan,这里就不说了,感兴趣的可以随便找一本介绍数据库索引的书籍来看看即可。根据我以前那篇blog的描述,我们知道应该建立一个复合索引(也叫convered index):boldate+companyid+bolcode
然后我们重新执行sql,看看I/O变化情况:
Ooh,非常cool!logical reads降低到了50。为什么会这样呢?我们看一下执行计划:
原来是index scan变成了index seek,效率自然大大的提升!
Sql语句在index上调优的方法,基本就是这样。我们继续看sp的。
对于sp的调优,有一点是和sql调优不同的:sp内部的逻辑处理可能非常复杂。单纯从查询分析器中,我们无法得知哪一小块的sql执行的I/O最大,我们只能看到一个总体的描述。所以,我们要知道sp内部的信息。
首先,了解自己当前的spid是多少。一种方法是select @@spid,另一种方法是看查询分析器下面的status bar的信息。
Ooh,我的spid是101。(上图的最下面那个tips)
然后我重新打开profiler(事件探查器),重新建立一个跟踪,这里面要修改第二个tab页的信息,把左面事件列“存储过程”中的SmtpCompleted加上
增加后的样子如下:
然后修改第4个tab页,把刚才看到的spid=101的信息填上:
点击运行后,这样profiler只能抓到在查询分析器中,spid=101那个窗口发送的sql。我们切换回查询分析器,执行有问题的sp,执行完成后,我们再回到profiler,点停止按钮。一个sp内部所有执行的sql,都被分开了!
这次的结果假设保存在了jq2表中,我们把所有执行的小片sql都列出来:
第一个是sp执行后的总体结果,I/O为62328,就是这个sp自己的。第二个是向临时表中插入数据,I/O为61514,我们很容易看到,这一句占用了整个sp的大概95%以上的成本。如果我们把这句insert into #temptable搞定,整个sp的成本自然就下来了。所以我们需要把这句insert搞出来。
但是慢着!default情况下,sqlserver的results只显示很少的字符,第二行的sql,我们根本抓不全的,所以我们需要修改一下设置。在查询分析器的工具-选项菜单中,切换到“结果”这个tab页,修改每列最多字符个数为8192(这是最大的允许值),然后点击“确定”按钮,重新从jq2中读取信息。也许你会问,如果某个sql特别长,怎么办?其实很简单,在你的代码中把这句sql单独写到log中,或者直接修改sp,把这句print出来即可。
Ok,我们把这句insert sql抓下来后,放到查询分析器中。因为temptable我们没有它的结构,所以我们把insert部分注释掉,看后面的select语句。执行后,ooh,在goodsmovement表上的成本是57834。
老办法,我们继续看执行计划:
其实,现在又回归到了sql调优的步骤,下面的工作我就不写啦!
这个步骤,看起来很简单,希望大家对于sql调优(索引部分)心中都有这么一个概念,知道第一步作什么,第二步作什么。还是那句话,索引调优,基本上是最简单的。但是貌似简单的东西,我们越应该重视。你随便找一个应用跟踪一下,各种效率低下的索引,会让你实在#¥*#(**……¥
分享到:
相关推荐
sqlserver性能调优入门,包括语句调优思想和存储过程调优思想,利用sqlserver自带工具进行检查。
SQL Server2005性能调优(简化翻译版) SQL Server性能调优入门(图文版)
SQL SERVER 2008 简单入门 SQL SERVER 培训教程 本教程是针对sql的初级者,提炼出的sql server 2008入门的基本概念和基础操作 目录 数据库概述 3 定义(其一): 3 历史: 3 属性: 3 基本结构 3 数据库管理 5 ...
以下是使用等待统计信息分析SQLServer性能并排除故障的实用指南。学习如何准确地确定查询运行缓慢的原因。测量每个瓶颈所消耗的时间,以便您可以首先集中精力进行最大的改进。此版本被更新,以涵盖查询存储中等待...
《程序员的SQL金典》主要介绍SQL的语法规则及在实际开发中的应用,并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析。《程序员的SQL金典》分为3部分:第1部分为基础篇,主要讲解数据库对增、删、改...
本书特色:主要介绍SQL的语法规则及在实际开发中的应用,并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析;详细讲解数据库对增、删、改、查等SQL的支持并给出了相应的SQL应用案例;透彻分析函数...
10.4.2 MS SQL Server中的自动增长字段 10.4.3 Oracle中的自动增长字段 10.4.4 DB2中的自动增长字段 10.5 业务主键与逻辑主键 10.6 NULL的学问 10.6.1 NULL与比较运算符 10.6.2 NULL和计算字段 ...
10.4.2 MS SQL Server中的自动增长字段 10.4.3 Oracle中的自动增长字段 10.4.4 DB2中的自动增长字段 10.5 业务主键与逻辑主键 10.6 NULL的学问 10.6.1 NULL与比较运算符 10.6.2 NULL和计算字段 ...
10.4.2 MS SQL Server中的自动增长字段 10.4.3 Oracle中的自动增长字段 10.4.4 DB2中的自动增长字段 10.5 业务主键与逻辑主键 10.6 NULL的学问 10.6.1 NULL与比较运算符 10.6.2 NULL和计算字段 ...
10.4.2 MS SQL Server中的自动增长字段 10.4.3 Oracle中的自动增长字段 10.4.4 DB2中的自动增长字段 10.5 业务主键与逻辑主键 10.6 NULL的学问 10.6.1 NULL与比较运算符 10.6.2 NULL和计算字段 ...
常用的 DBMS 有 MYSQL、Oracle、SQL Server、DB2 等,每种 DBMS 都有其独特的管理方式。 三、SQL 语言的分类 SQL 是用于访问数据库的最常用标准化语言,分为三类: * DML(数据操作语言):用于检索、增加、修改...
可以通过SQLab xpert来对当前SQL语句进行全面的分析,给出各种合理的调优意见,只需要单击工具栏上的Tuning按钮: 可以方便的保存,打开和运行SQL脚本 支持对查询结果进行修改, 可以把查询结果以...
手把手视频详细讲解项目开发全过程,需要的小伙伴自行百度网盘下载,链接见附件,永久有效。 课程简介 知识点介绍、代码演示、逻辑分析、灵活举例、...7.实时应用性能调优(数据本地性、反压机制、动态资源和日志管理)
数据库开发基础、Microsoft SQLServer基础、SQL语言基础、索引、事务、SQL语言高级技术(空值处理、聚合与分组、数据分页、Union、日期函数、类型转换函数、流控函数、表连接、子查询、存储过程、触发器)、数据库...
使用 sp_configure 和 sp_sysmon 进行性能调优 .......... 66 在集群环境中使用配置参数 .......... 66 sp_configure 输出 .......... 67 命名高速缓存配置参数 .......... 69 sysconfigures 和 syscurconfigs 表 .....
│ 第54节:性能调优和配置使用建议.avi │ 第55节:Nginx+Varnish组合应用.avi │ 第56节:Varnish对性能的提升和优化.avi │ 第57节:应用上Varnish后的体系结构.avi │ 第58节:Memcached入门和缓存的含义.avi │...