`

SQL Server性能调优入门(图文版)

阅读更多

摘要:本文是转载,地址: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性能调优入门

    本文将从入门级的角度介绍如何进行SQL Server的性能优化,主要关注语句和存储过程的调优,并利用SQL Server内置的Profiler工具。 Profiler是SQL Server提供的一个强大的诊断工具,用于捕获和分析数据库的活动。在...

    源码T-SQL性能调优秘笈 基于SQL Server 2012窗口函数

    《源码T-SQL性能调优秘笈 基于SQL Server 2012窗口函数》是一本深入探讨T-SQL性能优化的专著,特别聚焦于SQL Server 2012中的窗口函数。该资源包含五章节的源代码,旨在帮助读者理解并掌握如何利用窗口函数来提升...

    《SQL Server从入门到精通》.(明日科技 ).pdf 扫描 完整版(SQL Server 2008)

    《SQL Server从入门到精通》是一本专门为初学者和进阶者设计的全面教程,旨在帮助读者掌握SQL Server 2008的核心概念和技术。这本书由明日科技编写,提供了扫描的完整版,使得读者可以方便地在线查阅或下载学习。 ...

    SQL Server[中文版]从入门到精通.zip

    《SQL Server 中文版》是一本专为初学者设计的数据库管理系统教程,旨在帮助读者从零开始掌握SQL Server的基础知识并逐步深入到高级应用。在SQL Server的学习过程中,理解关系数据库的概念是至关重要的,因为它是SQL...

    SQL Server 2005从入门到精通

    《SQL Server 2005从入门到精通》是一本旨在帮助初学者和有一定基础的用户全面掌握SQL Server 2005的书籍。SQL Server 2005是微软公司推出的一款强大的关系型数据库管理系统,它在企业级数据管理和分析方面具有广泛...

    sql server 2008 dba入门经典代码

    SQL Server Management Studio(SSMS)中的性能监视器和动态管理视图(DMV)提供了大量信息。通过分析查询执行计划,调整索引或内存设置,可以显著提高系统性能。 七、复制技术 SQL Server 2008的复制功能允许数据...

    新员工SQL培训教程 SQL SERVER 2008 简单入门

    SQL SERVER 2008 简单入门 SQL SERVER 培训教程 本教程是针对sql的初级者,提炼出的sql server 2008入门的基本概念和基础操作 目录 数据库概述 3 定义(其一): 3 历史: 3 属性: 3 基本结构 3 数据库管理 5 ...

    [网盘]SQL Server从入门到精通.pdf.2018_03_19

    2. **性能调优**:为了提高系统的响应速度和吞吐量,需要对SQL Server进行合理的调优。具体措施包括但不限于调整服务器硬件资源分配、优化查询语句、建立合适的索引结构等。 3. **安全设置**:合理设置用户权限可以...

    SQLServer 2008编程入门经典(第3版)

    根据提供的信息,“SQL Server 2008编程入门经典(第3版)”这本书主要针对的是初学者到中级用户,旨在帮助读者深入了解SQL Server 2008的使用方法及编程技巧。虽然部分内容并未给出具体章节或实例,但从书名和描述...

    sql server 2008 从入门到精通的源代码.zip

    本压缩包"sql server 2008 从入门到精通的源代码.zip"包含了学习SQL Server 2008数据库管理、开发与优化的实例源程序,旨在帮助初学者快速掌握该技术。 一、SQL Server 2008基础知识 SQL Server 2008提供了一整套...

    SQL Server 2000 入门与提高课件

    本课件旨在帮助初学者快速入门并进一步提升对SQL Server 2000的理解和操作技能。 一、SQL Server 2000基础 SQL Server 2000基于SQL Server 7.0进行升级,引入了更多的功能和性能优化。它的核心组件包括:数据库引擎...

    SQL SERVER-2008从入门到精通

    总的来说,《SQL Server 2008从入门到精通》涵盖了数据库设计、数据管理、性能优化、备份恢复、安全性等多个方面,旨在帮助你成为熟练掌握SQL Server 2008的专业人士。通过这份教程的学习,你不仅可以提升SQL技能,...

    SQL SERVER-2008从入门到精通pdf

    11. **性能调优**:监控和优化SQL Server的性能,包括查询优化、索引调整、内存管理、查询计划等。 通过这个全面的教程,你将掌握SQL Server 2008的基本操作和高级特性,从而能够有效地管理和利用数据库资源,提升...

    SQL Server 2008编程入门经典(第3版)

    《SQL Server 2008编程入门经典(第3版)》是一本为数据库编程初学者设计的书籍,旨在帮助读者快速掌握SQL Server数据库的基本使用和编程技巧。这本书是早期版本《SQL Server 2005编程入门经典》的继承和发展,非常...

    SQL_SERVER_2000入门培训PPT

    **SQL_SERVER_2000入门培训PPT**是一份专为初学者设计的教程资料,旨在帮助读者快速掌握Microsoft SQL Server 2000的基本概念、安装与配置、数据库管理以及查询语言等方面的知识。SQL Server 2000是微软公司推出的一...

    SQL SERVER-2008从入门到精通.pdf

    根据提供的文件信息,“SQL SERVER-2008从入门到精通.pdf”这一标题与描述,主要涉及的是关于SQL Server 2008的学习资料。下面将详细介绍与该主题相关的几个重要知识点。 ### 1. SQL Server 2008概述 - **版本介绍...

    SQL Server 2005 联机丛书入门 WORD版

    5. **性能优化**:监控和性能优化是数据库管理员的关键任务,这包括使用性能计数器、查询分析器、索引调整和资源调优。Service Broker的性能管理和全文搜索的优化也至关重要。 6. **安全性**:SQL Server 2005提供...

    SQL Server 2008编程入门经典(第3版)

    《SQL Server 2008编程入门经典(第3版)》是一本针对初学者的指南,旨在帮助读者深入理解并掌握SQL Server 2008的使用与开发技巧。SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业级数据存储...

Global site tag (gtag.js) - Google Analytics