摘要:本文是转载,地址: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调优(索引部分)心中都有这么一个概念,知道第一步作什么,第二步作什么。还是那句话,索引调优,基本上是最简单的。但是貌似简单的东西,我们越应该重视。你随便找一个应用跟踪一下,各种效率低下的索引,会让你实在#¥*#(**……¥
分享到:
相关推荐
本文将从入门级的角度介绍如何进行SQL Server的性能优化,主要关注语句和存储过程的调优,并利用SQL Server内置的Profiler工具。 Profiler是SQL Server提供的一个强大的诊断工具,用于捕获和分析数据库的活动。在...
《源码T-SQL性能调优秘笈 基于SQL Server 2012窗口函数》是一本深入探讨T-SQL性能优化的专著,特别聚焦于SQL Server 2012中的窗口函数。该资源包含五章节的源代码,旨在帮助读者理解并掌握如何利用窗口函数来提升...
《SQL Server从入门到精通》是一本专门为初学者和进阶者设计的全面教程,旨在帮助读者掌握SQL Server 2008的核心概念和技术。这本书由明日科技编写,提供了扫描的完整版,使得读者可以方便地在线查阅或下载学习。 ...
《SQL Server 中文版》是一本专为初学者设计的数据库管理系统教程,旨在帮助读者从零开始掌握SQL Server的基础知识并逐步深入到高级应用。在SQL Server的学习过程中,理解关系数据库的概念是至关重要的,因为它是SQL...
《SQL Server 2005从入门到精通》是一本旨在帮助初学者和有一定基础的用户全面掌握SQL Server 2005的书籍。SQL Server 2005是微软公司推出的一款强大的关系型数据库管理系统,它在企业级数据管理和分析方面具有广泛...
SQL Server Management Studio(SSMS)中的性能监视器和动态管理视图(DMV)提供了大量信息。通过分析查询执行计划,调整索引或内存设置,可以显著提高系统性能。 七、复制技术 SQL Server 2008的复制功能允许数据...
SQL SERVER 2008 简单入门 SQL SERVER 培训教程 本教程是针对sql的初级者,提炼出的sql server 2008入门的基本概念和基础操作 目录 数据库概述 3 定义(其一): 3 历史: 3 属性: 3 基本结构 3 数据库管理 5 ...
2. **性能调优**:为了提高系统的响应速度和吞吐量,需要对SQL Server进行合理的调优。具体措施包括但不限于调整服务器硬件资源分配、优化查询语句、建立合适的索引结构等。 3. **安全设置**:合理设置用户权限可以...
根据提供的信息,“SQL Server 2008编程入门经典(第3版)”这本书主要针对的是初学者到中级用户,旨在帮助读者深入了解SQL Server 2008的使用方法及编程技巧。虽然部分内容并未给出具体章节或实例,但从书名和描述...
本压缩包"sql server 2008 从入门到精通的源代码.zip"包含了学习SQL Server 2008数据库管理、开发与优化的实例源程序,旨在帮助初学者快速掌握该技术。 一、SQL Server 2008基础知识 SQL Server 2008提供了一整套...
本课件旨在帮助初学者快速入门并进一步提升对SQL Server 2000的理解和操作技能。 一、SQL Server 2000基础 SQL Server 2000基于SQL Server 7.0进行升级,引入了更多的功能和性能优化。它的核心组件包括:数据库引擎...
总的来说,《SQL Server 2008从入门到精通》涵盖了数据库设计、数据管理、性能优化、备份恢复、安全性等多个方面,旨在帮助你成为熟练掌握SQL Server 2008的专业人士。通过这份教程的学习,你不仅可以提升SQL技能,...
11. **性能调优**:监控和优化SQL Server的性能,包括查询优化、索引调整、内存管理、查询计划等。 通过这个全面的教程,你将掌握SQL Server 2008的基本操作和高级特性,从而能够有效地管理和利用数据库资源,提升...
《SQL Server 2008编程入门经典(第3版)》是一本为数据库编程初学者设计的书籍,旨在帮助读者快速掌握SQL Server数据库的基本使用和编程技巧。这本书是早期版本《SQL Server 2005编程入门经典》的继承和发展,非常...
**SQL_SERVER_2000入门培训PPT**是一份专为初学者设计的教程资料,旨在帮助读者快速掌握Microsoft SQL Server 2000的基本概念、安装与配置、数据库管理以及查询语言等方面的知识。SQL Server 2000是微软公司推出的一...
根据提供的文件信息,“SQL SERVER-2008从入门到精通.pdf”这一标题与描述,主要涉及的是关于SQL Server 2008的学习资料。下面将详细介绍与该主题相关的几个重要知识点。 ### 1. SQL Server 2008概述 - **版本介绍...
5. **性能优化**:监控和性能优化是数据库管理员的关键任务,这包括使用性能计数器、查询分析器、索引调整和资源调优。Service Broker的性能管理和全文搜索的优化也至关重要。 6. **安全性**:SQL Server 2005提供...
《SQL Server 2008编程入门经典(第3版)》是一本针对初学者的指南,旨在帮助读者深入理解并掌握SQL Server 2008的使用与开发技巧。SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业级数据存储...