xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

profile使用技巧,数据库优化实践

时间:2019-11-06 12:36来源:澳门新葡8522最新网站
介绍 经常会有人问profile工具该怎么使用?有没有方法获取性能差的sql的问题。自从转mysql我自己也差不多2年没有使用profile,忽然profile变得有点生疏不得不重新熟悉一下。这篇文章主要

介绍

经常会有人问profile工具该怎么使用?有没有方法获取性能差的sql的问题。自从转mysql我自己也差不多2年没有使用profile,忽然profile变得有点生疏不得不重新熟悉一下。这篇文章主要对profile工具做一个详细的介绍;包括工具的用途和使用方法等。profile是SQLServer自带的一个性能分析监控工具,它也可以生成数据库引擎优化顾问分析需要的负载数据,比如开发对功能进行调试需要收集执行sql使用profile就是一个非常好的办法,profile主要用于在线实时监控和收集数据用于后期的分析使用,它可以将收集的数据保存成文件和插入到表。

 

 

 诊断数据库性能问题就象医生诊断病人病情一样,既要结合自己积累的经验,又要依靠科学的诊断报告,才能准确地判断问题的根源在哪里。前面三篇文章我们介绍了许多优化数据库性能的方法,固然掌握优化技巧很重要,但诊断数据库性能问题是优化的前提,本文就介绍一下如何诊断数据库性能问题。

跟踪属性


  第八步:使用SQL事件探查器和性能监控工具有效地诊断性能问题

一、常规

将跟踪的记录保存到指定的文件。

1.最大文件大小

指定最大文件大小的跟踪在达到最大文件大小时,会停止将跟踪信息保存到该文件。使用此选项可将事件分组成更小、更容易管理的文件。此外,限制文件大小使得无人参与的跟踪运行起来更加安全,因为跟踪会在达到最大文件大小后停止。可以为通过 Transact-SQL 存储过程或使用 SQL Server Profiler创建的跟踪设置最大文件大小。

最大文件大小选项的上限为 1 GB。默认最大文件大小为 5 MB

注意:最大文件的大小建议不要设的太大,特别是需要用于数据库引擎优化顾问使用的文件,太大的跟踪文件需要很长的分析的时间而且由于数据库引擎优化顾问也是把收集的负载文件执行一遍有时候可能会导致负载过大分析失败,同时对服务器的压力持续的时间过长对业务影响也会比较大,默认大小即可,同时启动文件滚动更新,多次分析。

2.启用文件滚动更新

如果使用文件滚动更新选项,则在达到最大文件大小时,SQL Server 会关闭当前文件并创建一个新文件。新文件与原文件同名,但是文件名后将追加一个整数以表示其序列。例如,如果原始跟踪文件命名为 filename_1.trc,则下一跟踪文件为 filename_2.trc,依此类推。如果指定给新滚动更新文件的名称已经被现有文件使用,则将覆盖现有文件,除非现有文件为只读文件。默认情况下,将跟踪数据保存到文件时,会启用文件滚动更新选项。

3.服务器处理跟踪数据

确保服务器记录每个跟踪事件,如果记录事件会显著降低性能,可以清除服务器处理跟踪数据,这样服务器不会再记录事件。

  在SQL Server应用领域SQL事件探查器可能是最著名的性能故障排除工具,大多数情况下,当得到一个性能问题报告后,一般首先启动它进行诊断。

4.最大行数

指定有最大行数的跟踪在达到最大行数时,会停止将跟踪信息保存到表。每个事件构成一行,因此该参数可设置收集的事件数的范围。设置最大行数使得无人参与的跟踪运行起来更加方便。例如,如果需要启动一个将跟踪数据保存到表的跟踪,同时希望在该表变得过大时停止跟踪,则可以使其自动停止。

如果已指定并且达到了最大行数,将在运行 SQL Server Profiler的同时继续运行跟踪,但不再记录跟踪信息。SQL Server Profiler将继续显示跟踪结果,直到跟踪停止

5.启用跟踪停止时间 

启用跟踪停止时间之后,到了指定的时间跟踪自动停止。每一次跟踪建议都必须得设置一个跟踪停止时间防止忘记关闭跟踪导致服务器空间被占满,默认跟踪1小时。

 

注意:

  • 从 SQL Server 2005 开始,服务器以微秒(百万分之一秒或 10-6 秒)为单位报告事件的持续时间,以毫秒(千分之一秒或 10-3 秒)为单位报告事件使用的 CPU 时间。
  • 在 SQL Server 2000 中,服务器以毫秒为单位报告持续时间和 CPU 时间。
  • 在 SQL Server 2005 及更高版本中,SQL Server Profiler图形用户界面默认以毫秒为单位显示“持续时间”列,但是当跟踪保存到文件或数据库表中之后,将以微秒为单位在“持续时间”列中写入值。

  你可能已经知道,SQL事件探查器是一个跟踪和监控SQL Server实例的图形化工具,主要用于分析和衡量在数据库服务器上执行的TSQL性能,你可以捕捉服务器实例上的每个事件,将其保存到文件或表中供以后分析。例如,如果生产数据库速度很慢,你可以使用SQL事件探查器查看哪些存储过程执行时耗时过多。

二、事件选择

对于不同跟踪选择不同的跟踪事件;通过勾选“显示所有跟踪事件”可以看到所有的跟踪事件,总共有21个事件分类。用得最多的两个分类就是存储过程和TSQL这两个分类主要用来记录执行的存储过程和SQL语句,把鼠标移动到具体的事件上面会显示该事件和事件列的具体说明,接下来就分析几个常用的事件和常用的事件列。

1.显示所有跟踪事件

勾选之后会将所有的事件都显示出来

2.显示所有列

勾选之后会将所有的列显示出来

3.列筛选

对列增加一些条件,其实可以将它理解在TSQL语句的WHERE后面添加条件,对于整形列直接输入数值即可,对于字符串列就相当于like一样使用不带引号的%%模糊匹配方法。通过勾选“排除不包含值的行”之后跟踪结果就会筛选掉不满足条件的记录。

4.列组织

列组织可以理解成TSQL语句里面做GROUP BY操作,可以将相同的条件放在一起去重。

 

  SQL事件探查器的基本用法

事件

1.SQL:Stmt*******

[SQL:StmtStarting]:启动TSQL语句时记录

[SQL:StmtCompleted]:完成TSQL语句时记录

图片 1

这两事件的区别也同单词的意思一样,StmtStarting是记录事件的开始不关注这个事件在接下来会做什么,StmtCompleted是记录事件结束之后在开始和结束这个过程中做的一些操作比如一些常用的列"Duration","Cpu","Reads","Writes","EndTime"这些列就会出现在StmtCompleted事件中。所以如果你需要收集的记录不关心整个事件过程中的操作只需要收集数量那么可以使用Starting事件比如记录某个语句或者存储过程执行的次数等。

2.SQL:Batch******

[SQL:BatchStarting]:启动TSQL批处理时记录

[SQL:BatchCompleted]:完成TSQL批处理时记录

 图片 2

图片 3

这次我把两个select语句放在一起来执行,可以从batch事件中可以看到它记录的整个批处理的SQL同时还包括相关注释,同时整个批处理两个TSQL作为一条事件记录,而stmt事件记录具体的TSQL语句把两个TSQL语句作为两条记录来记录。同时还可以发现两个TSQL的Duration相加是小于整个批处理的duration的,这也是正常的整个批处理在sql编译分析执行这块肯定比单个TSQL需要耗费更多的时间,但是相差也是非常的小。

 

batchcompleted事件多用于引擎优化顾问,而stmtcompleted事用于分析单个TSQL语句。同样Stored分类里面的starting事件和completed事件和TSQL里面的是一样的意思。

事件列

列举常用的事件列

TextData:文本详细信息,比如详细的执行SQL语句等等。

ApplicationName:连接SQLSever的客户端应用程序名称。

NTUserName:windows用户名

LoginName:SQLServer登入用户名。

CPU:事件占用的CPU时间,在图形化界面但是是毫秒(千分之一秒或 10-3 秒),在文本文件或者数据库表中单位是微妙(百万分之一秒或 10-6 秒)。

Reads:执行逻辑读的次数。

Writes:物理磁盘写入的次数。

Duration:事件的持续时间,也就是统计信息里面显示的占用时间,在图形化界面但是是毫秒(千分之一秒或 10-3 秒),在文本文件或者数据库表中单位是微妙(百万分之一秒或 10-6 秒)

ClientProcessID:调用SQLServer的应用程序进程ID。

SPID:SQLServer为连接分配的数据库进程ID,也就是sys.processes里面记录的进程ID。

StartTime:事件的开始时间。

EndTime:事件的结束时间。

DBUserName:客户端的sqlserver用户名。

DatabaseID:如果指定了USE database就是指定的数据库id,否则就是默认的数据库id(也就是master的数据库id)。所以该列的作用不是很大。

Error:事件的错误号,通常是sysmessage中存储的错误号。

ObjectName:正在引用的对象名称。

  你可能已经知道如何使用它,那么你可以跳过这一小节,但我还是要重复一下,也许有许多新手阅读本文。

三、自带跟踪模板

工具自带了几个比较实用的跟踪模板,一般的跟踪都可以直接使用自带的跟踪模板解决,同时自己也可以创建自定义的跟踪事件和跟踪属性保存成模板供以后使用。

SP_Counts:计算已运行的存储过程数,并且按存储过程的名称进行分组统计,此模板可以分析某时间段存储过程的行为。

Standard:记录所有存储过程和T-SQL语句批处理运行的时间,当你想要监视常规数据库服务器活动时即可使用该模板,一般的跟踪需要使用该模板就可以解决,这也是默认的模板。

TSQL:记录客户端提交给sqlserver的所有T-SQL语句的的内容和开始时间,通常使用该模板用于程序调试。

TSQL_Duration:记录客户端提交给sqlserver的所有T-SQL语句批处理信息以及执行这些语句所需的时间(毫秒),并按时间进行分组,使用该模板可以分析执行慢的查询,此模板的跟踪记录可以用于数据库引擎优化顾问分析使用。

TSQL_Grouped:按提交客户端和登入用户进行分组记录所有提交给SQLServer的T-SQL批处理语句及其开始时间,此模板用于分析某个客户或者用户执行的查询。

TSQL_Locks:记录所有开始和完成的存储过程和T-SQL语句,同时记录死锁信息,此模板用于跟踪死锁。

TSQL_Replay:记录有关已发出的T-SQL语句的详细信息,此模板记录重播跟踪所需的信息,此模板可执行跌到优化,例如基准测试。

TSQL_SPs:记录有关执行的所有存储过程的详细信息,此模板可以分析存储过程的组成步骤。如果你怀疑正在重新编译存储过程,请添加SP:Recomple事件

Tuning:记录有关存储和T-SQL语句批处理的信息以及执行这些语句所需的时间(毫秒),使用此模板生产跟踪输出可用于数据库引擎优化顾问工作负载来优化索引、优化性能。此模板和TSQL_Druation相似后者是做了时间分组。

 

  1)启动SQL事件探查器,连接到目标数据库实例,创建一个新跟踪,指定一个跟踪模板(跟踪模板预置了一些事件和用于跟踪的列),如图1所示;

数据库引擎优化顾问


1.如果需要用数据库引擎优化顾问分析跟踪事件记录必须捕获了以下跟踪事件:

  • RPC:Completed

  • SQL:BatchCompleted

  • SP:StmtCompleted

也可以使用这些跟踪事件的 Starting 版本。 例如,SQL:BatchStarting。 但是,这些跟踪事件的 Completed 版本包括 Duration 列,它能使数据库引擎优化顾问更有效地优化工作负荷。 数据库引擎优化顾问不优化其他类型的跟踪事件。

图片 4

2.包含 LoginName列

数据库引擎优化顾问在优化过程中提交显示计划请求。 当包含 LoginName 数据列的跟踪表或跟踪文件被用作工作负荷时,数据库引擎优化顾问将模拟 LoginName 中指定的用户。 如果没有为此用户授予 SHOWPLAN 权限(该权限使用户能够为跟踪中包含的语句执行和生成显示计划),数据库引擎优化顾问将不会优化这些语句。 

避免为跟踪的 LoginName 列中指定的每个用户授予 SHOWPLAN 权限

  1. 通过从未优化的事件中删除 LoginName 列来创建新的工作负荷,然后只将未优化的事件保存到新的跟踪文件或跟踪表中。

  2. 将不带 LoginName 列的新工作负荷重新提交到数据库引擎优化顾问。

数据库引擎优化顾问将优化新的工作负荷,因为跟踪中未指定登录信息。 如果某个语句没有相应的 LoginName,数据库引擎优化顾问将通过模拟启动优化会话的用户(sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员)来优化该语句。

3.数据库引擎优化顾问不能执行下列操作:

  • 建议对系统表建立索引。

  • 添加或删除唯一索引或强制 PRIMARY KEY 或 UNIQUE 约束的索引。

  • 优化单用户数据库。

4.数据库引擎优化顾问具有下列限制:

  • 数据库引擎优化顾问通过数据采样收集统计信息。因此,在相同的工作负荷上重复运行该工具可能生成不同的结果。

  • 数据库引擎优化顾问不能用于优化 Microsoft SQL Server 7.0 或更早版本的数据库中的索引。

  • 如果为优化建议指定的最大磁盘空间超过了可用空间,数据库引擎优化顾问将使用指定的值。但是,当您执行建议脚本来实施它时,如果未先添加更多磁盘空间,则脚本会失败。可以使用 dta 实用工具的 -B 选项指定最大磁盘空间,也可以通过在“高级优化选项”对话框中输入值来指定最大磁盘空间。

  • 为了安全起见,数据库引擎优化顾问不能优化驻留在远程服务器上的跟踪表中的工作负荷。若要解除此限制,可以选择以下选项之一:

    • 使用跟踪文件而不使用跟踪表。

    • 将跟踪表复制到远程服务器。

  • 当强制实施约束时,例如为优化建议指定最大磁盘空间时强制的约束(通过使用 -B 选项或“高级优化选项”对话框),数据库引擎优化顾问可能会被迫删除某些现有的索引。在此情况下,生成的数据库引擎优化顾问建议可能生成负的预期提高值。

  • 指定限制优化时间的约束时(通过使用 dta 实用工具的 -A 选项或通过选择“优化选项”选项卡上的“限制优化时间”),数据库引擎优化顾问可能超过该时间限制,以便针对到当时为止已处理的工作负荷,生成精确预期的提高值和分析报告。

5.数据库引擎优化顾问可能在下列情况下不提供建议:

  • 正在优化的表所包含的数据页数少于 10。

  • 建议的索引对当前物理数据库设计的查询性能预计带来的提高值不够。

  • 运行数据库引擎优化顾问的用户不是 db_owner 数据库角色或 sysadmin 固定服务器角色的成员。工作负荷中的查询在运行数据库引擎优化顾问的用户的安全上下文中进行分析。该用户必须是 db_owner 数据库角色的成员。

6.数据库引擎优化顾问可能在下列情况下不提供分区建议:

  • 未启用 xp_msver 扩展存储过程。此扩展存储过程用于提取要优化的数据库所在服务器上的处理器数目以及可用内存。请注意,安装 SQL Server 后,默认情况下,此扩展存储过程处于打开状态。有关详细信息,请参阅了解外围应用配置器和 xp_msver (Transact-SQL)。

7.性能注意事项

在分析过程中,数据库引擎优化顾问可能占用相当多的处理器及内存资源。若要避免降低生产服务器速度,请采用下列策略之一:

  • 在服务器空闲时优化数据库。数据库引擎优化顾问可能影响维护任务性能。

  • 使用测试服务器/生产服务器功能。有关详细信息,请参阅减轻生产服务器优化负荷。

  • 指定数据库引擎优化顾问仅分析物理数据库设计结构。数据库引擎优化顾问提供许多选项,但是请仅指定所需选项。

注意:由于数据库引擎优化顾问进行性能优化时也是将负载记录中的语句执行一篇查询分析执行计划的操作,所以对服务器同样存在压力。特别是对于大的负载分析可能需要分析一个小时甚至更长,这样可能会持续对服务器造成压力,所以避免在业务高峰期进行使用引擎优化顾问进行负载分析。

  图 1 选择跟踪模板

实例 


接下来就列举三个案例,使用数据库引擎优化顾问来分析跟踪记录优化索引的案例、监控死锁的案例、创建自定义跟踪模板案例。

  2)作为可选的一步,你还可以选择特定事件和列

案例1:优化索引

1.创建测试数据

--创建测试表
CREATE TABLE [dbo].[book](
    [id] [int] NOT NULL PRIMARY KEY,
    [name] [varchar](50) NULL);


--插入10W条测试数据
DECLARE @id int
SET @id=1
WHILE @id<100000
BEGIN
INSERT INTO book values(@id,CONVERT(varchar(20),@id))

SET @id=@id 1
END;

2.创建跟踪

这里使用默认的跟踪模板“tuning”

1.创建好跟踪后点击运行即可,事件选择这里保持默认

图片 5

图片 6

2.执行SQL

SELECT * FROM book WHERE name='10001';

由于name字段没有建索引所以该查询执行计划分析过后会返回创建name字段的索引,通过引擎优化顾问分析同样如此

3.停止跟踪

在使用数据库引擎优化顾问分析负载跟踪之前必须先停止跟踪。

4.打开数据库引擎优化顾问

可以直接在profile的工具栏选择打开,“文件”选择刚才的跟踪文件,“负载数据库”选择需要进行优化的数据库,“选择要优化的数据库和表”也就需要优化的数据库的相关表。优化选项没有特别的需求选择默认即可,然后点击“开始分析”。

图片 7

图片 8

引擎优化顾问会自动生成创建索引的脚步,同时还给出了创建该索引之后预计性能可以提供的百分比,如果同时存在很多表的索引建议可以勾选需要保存的建议保存成sql文件在“开始分析”栏旁边有一个保存建议的按钮可以将建议保存成sql文件。

建议:

1.数据库引擎优化顾问给出的建议不是每一个都是对的,自己对比该SQL的执行频率来判断是否需要创建该索引,比如我当前这个SQL如果我这个SQL只执行了一次后面就不会再执行了那么这个索引就没必要创建了。

2.修改引擎优化顾问给出的索引名,数据库引擎优化顾问给出的创建索引的索引名不够直观,建议自己手动更改,比如改成“ix_book_name”,“索引标示_表名_字段描述”的规则。

3.用来分析的文件不要太大否则可能会分析不完成,不要在业务高峰期进行分析。

图片 9

案例2:监控死锁

1.创建跟踪

图片 10

 

模板选择自带的“TSQL_Locks”模板,运行跟踪。

2.执行SQL

打开两个会话窗口分表执行如下SQL,先在会话1执行然后在10S内在会话2中执行,两个会话拥有各自的排他锁同时又去申请对方拥有的排他锁造成死锁。

会话1执行:当前会话1是62

BEGIN TRANSACTION
UPDATE book 
SET name='a'
WHERE ID=10

--延时10s执行
waitfor delay '0:0:10'

UPDATE book 
SET name='a'
WHERE ID=100

会话2执行:当前会话2是

BEGIN TRANSACTION
UPDATE book 
SET name='b'
WHERE ID=100

--延时20执行
waitfor delay '0:0:20'

UPDATE book 
SET name='b'
WHERE ID=10

msms客户端返回的错误消息显示当前62会话作为死锁的牺牲品。

图片 11

3.跟踪分析死锁

图片 12

 死锁跟踪事件使用图形和直观的返回了两个会话的死锁,其中62会话用了一个×表示当前的会话是死锁的牺牲品。

  图 2 选择跟踪过程要捕捉的事件

案例三:创建自定义跟踪模板

 标准模板就是一个比较好的参考模板,比如我们对执行语句进行监控就可以参考标准模板在其基础上修改保存成自己的模板。

1.创建TSQL语句跟踪

图片 13

图片 14

图片 15

2.创建跟踪模板

停止当前的TSQL跟踪,选择“文件”-“另存为跟踪模板”就可以保存成自己的跟踪模板。

3.列筛选

图片 16

 

当前是筛选跟踪的TSQL语句中包含book,这里的列筛选这执行 where like 的语法类似。

整形列的话就不需要带模糊条件:

图片 17

注意:如果要取消列筛选记得把刚才的筛选条件删除同时把“排除不包含值的行” 的勾选也去除,记得两者都要去掉否则跟踪还是包含筛选的跟踪。

4.列组织

列组织其实就是按某列进行分组显示跟踪,类似select查询里面的group by操作。比如我当前按持续时间进行分组跟踪。

图片 18

图片 19

通过对持续时间进行分组,相同的持续时间会放在一个分组里。

3)另外你还可以点击“组织列”按钮,在弹出的窗口中指定列的显示顺序,点击“列过滤器”按钮,在弹出的窗口中设置过滤器,例如,通过设置数据库的名称(在like文本框中),只跟踪特定的数据库,如果不设置过滤器,SQL事件探查器会捕捉所有的事件,跟踪的信息会非常多,要找出有用的关键信息就如大海捞针。

总结

 由于篇幅有限列举了一些简单常用的操作,其它的分类监控的方法类似有兴趣可以多去研究,profile是非常实用且界面化很好的监控工具这也是SQLServer独特的条件,应该熟练运用。

 

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

图片 20

  图 3 过滤器设置

  4)运行事件探查器,等待捕捉事件

图片 21

  图 4 运行事件探查器

  5)跟踪了足够的信息后,停掉事件探查器,将跟踪信息保存到一个文件中,或者保存到一个数据表中,如果保存到表中,需要指定表名,SQL Server会自动创建表中的字段。

图片 22

  图 5 将探查器跟踪数据保存到表中

  6)执行下面的SQL查询语句找出执行代价较高的TSQL

SELECT TextData,Duration,…, FROM Table_Name ORDER BY

  Duration DESC

图片 23

  图 6 查找成本最高的TSQL/存储过程

有效利用SQL事件探查器排除与性能相关的问题

  SQL事件探查器除了可以用于找出执行成本最高的那些TSQL或存储过程外,还可以利用它许多强大的功能诊断和解决其它不同类型的问题。当你收到一个性能问题报告后,或者想提前诊断潜在的性能问题时都可以使用SQL事件探查器。下面是一些SQL事件探查器使用技巧,或许对你有帮助。

  1)使用现有的模板,但需要时应创建你自己的模板

  大多数时候现有的模板能够满足你的需求,但当诊断一个特殊类型的数据库性能问题时(如数据库发生死锁),你可能需要创建自己的模板,在这种情况下,你可以点击“文件”*“模板”*“新建模板”创建一个新模板,需要指定模板名、事件和列。当然也可以从现有的模板修改而来。

图片 24

  图 7 创建一个新模板

图片 25

  图 8 为新模板指定事件和列

  2)捕捉表扫描(TableScan)和死锁(DeadLock)事件

  没错,你可以使用SQL事件探查器监听这两个有趣的事件。

  先假设一种情况,假设你已经在你的测试库上创建了合适的索引,经过测试后,现在你已经将索引应用到生产服务器上了,但由于某些不明原因,生产数据库的性能一直没达到预期的那样好,你推测执行查询时发生了表扫描,你希望有一种方法能够检测出是否真的发生了表扫描。

  再假设另一种情况,假设你已经设置好了将错误邮件发送到一个指定的邮件地址,这样开发团队可以第一时间获得通知,并有足够的信息进行问题诊断。某一天,你突然收到一封邮件说数据库发生了死锁,并在邮件中包含了数据库级别的错误代码,你需要找出是哪个TSQL创造了死锁。

  这时你可以打开SQL事件探查器,修改一个现有模板,使其可以捕捉表扫描和死锁事件,修改好后,启动事件探查器,运行你的应用程序,当再次发生表扫描和死锁事件时,事件探查器就可以捕捉到,利用跟踪信息就可以找出执行代价最高的TSQL。

  注意:从SQL Server日志文件中可能也可以找到死锁事件记录,在某些时候,你可能需要结合SQL Server日志和跟踪信息才能找出引起数据库死锁的数据库对象和TSQL。

图片 26

  图 9 检测表扫描

图片 27

  图 10 检测死锁

  3)创建重放跟踪

  某些时候,为了解决生产数据库的性能问题,你需要在测试服务器上模拟一个生产环境,这样可以重演性能问题。使用SQL事件探查器的TSQL_Replay模板捕捉生产库上的事件,并将跟踪信息保存为一个.trace文件,然后在测试服务器上播放跟踪文件就可以重现性能问题是如何出现的了。

图片 28

  图 11 创建重放跟踪

  4)创建优化跟踪

  数据库调优顾问是一个伟大的工具,它可以给你提供很好的调优建议,但要真正从它那获得有用的建议,你需要模拟出与生产库一样的负载,也就是说,你需要在测试服务器上执行相同的TSQL,打开相同数量的并发连接,然后运行调优顾问。SQL事件探查器的Tuning模板可以捕捉到这类事件和列,使用Tuning模板运行事件探查器,捕捉跟踪信息并保存,通过调优顾问使用跟踪文件在测试服务器上创建相同的负载。

图片 29

  图 12 创建Tuning事件探查器跟踪

  5)捕捉ShowPlan在事件探查器中包括SQL执行计划

  有时相同的查询在测试服务器和生产服务器上的性能完全不一样,假设你遇到这种问题,你应该仔细查看一下生产数据库上TSQL的执行计划。但问题是现在不能在生产库上执行这个TSQL,因为它已经有严重的性能问题。这时SQL事件探查器可以派上用场,在跟踪属性中选中ShowPlan或ShowPlan XML,这样可以捕捉到SQL执行计划和TSQL文本,然后在测试服务器上执行相同的TSQL,并比较两者的执行计划。

图片 30

  图 13 指定捕捉执行计划

图片 31

  图 14 在事件探查器跟踪中的执行计划

使用性能监视工具(PerfMon)诊断性能问题

  当你的数据库遇到性能问题时,大多数时候使用SQL事件探查器就能够诊断和找出引起性能问题的背后原因了,但有时SQL事件探查器并不是万能的。

  例如,在生产库上使用SQL事件探查器分析查询执行时间时,对应的TSQL执行很慢(假设需要10秒),但同样的TSQL在测试服务器上执行时间却只要200毫秒,通过分析执行计划和数据列,发现它们都没有太大的差异,因此在生产库上肯定有其它问题,那该如何揪出这些问题呢?

  此时性能监视工具(著名的PerfMon)可以帮你一把,它可以定期收集硬件和软件相关的统计数据,还有它是内置于Windows操作系统的一个免费的工具。

  当你向SQL Server数据库发送一条TSQL语句,会产生许多相关的执行参与者,包括TSQL执行引擎,服务器缓存,SQL优化器,输出队列,CPU,磁盘I/O等,只要这些参与者任何一环执行节奏没有跟上,最终的查询执行时间就会变长,使用性能监视工具可以对这些参与者进行观察,以找出根本原因。

  使用性能监视工具可以创建多个不同的性能计数器,通过图形界面分析计数器日志,此外还可以将性能计数器日志和SQL事件探查器跟踪信息结合起来分析。

  性能监视器基本用法介绍

  Windows内置了许多性能监视计数器,安装SQL Server时会添加一个SQL Server性能计数器,下面是创建一个性能计数器日志的过程。

  1)在SQL事件探查器中启动性能监视工具(“工具”*“性能监视器”);

图片 32

  图 15 启动性能监视工具

  2)点击“计数器日志”*“新建日志设置”创建一个新的性能计数器日志

图片 33

  图 16 创建一个性能计数器日志

  指定日志文件名,点击“确定”。

图片 34

  图 17 为性能计数器日志指定名字

  3)点击“添加计数器”按钮,选择一个需要的计数器

图片 35

  图 18 为性能计数器日志指定计数器

  4)从列表中选择要监视的对象和对应的计数器,点击“关闭”

图片 36

  图 19 指定对象和对应的计数器

  5)选择的计数器应显示在窗体中

图片 37

  图 20 指定计数器

  6)点击“日志文件”标签,再点击“配置”按钮,指定日志文件保存位置,如果需要现在还可以修改日志文件名

图片 38

  图 21 指定性能计数器日志文件保存位置

  7)点击“调度”标签,指定一个时间读取计数器性能,写入日志文件,也可以选择“手动”启动和停止计数器日志。

图片 39

  图 22 指定性能计数器日志运行时间

  8)点击“常规”标签,指定收集计数器数据的间隔时间

图片 40

  图 23 设置计数器间隔采样时间

  9)点击“确定”,选择刚刚创建的计数器日志,点击右键启动它。

图片 41

  图 24 启动性能计数器日志

  10)为了查看日志数据,再次打开性能监视工具,点击查看日志图标(红色),在“源”标签上选中“日志文件”单选按钮,点击“添加”按钮添加一个日志文件。

图片 42

  图 25 查看性能计数器日志

  11)默认情况下,在日志输出中只有三个计数器被选中,点击“数据”标签可以追加其它计数器。

图片 43

  图 26 查看日志数据时追加计数器

  12)点击“确定”,返回图形化的性能计数器日志输出界面

图片 44

  图 27 查看性能计数器日志

编辑:澳门新葡8522最新网站 本文来源:profile使用技巧,数据库优化实践

关键词: www8029com