您的位置:时时app平台注册网站 > 彩世界网址 > 数据库服务器负荷的分析与优化

数据库服务器负荷的分析与优化

2019-11-21 02:23
  1. 数据库引擎优化顾问优化完测试服务器 Shell 数据库后,将生成优化建议。

  2. 将通过优化测试服务器得到的建议应用于生产服务器。

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 Server数据库在执行查询作业的时候,都是单线程操作的。不够数据库为了应对比较大流量的查询作业,故SQL Server提供了并行查询的功能。

  并行查询其优势就是可以通过多个线程来处理查询作业,从而提高查询的效率。SQL Server数据库为具有多个CPU的数据库服务器提供并行查询的功能,以优化查询作业的性能。也就是说,只要数据库服务器有多个CPU,则数据库系统就可以使用多个操作系统进程并行执行查询操作,来加速完成查询作业。

  一、并行查询三步走。

  并行查询作业在数据库中,主要经过三个步骤。

  首先,数据库会判断是否需要进行并行查询。在数据库中有一个查询优化器,会对SQL语句进行优化,然后数据库才会去执行查询语句。而这个查询器在对SQL语句进行查询优化时,其中一个动作就是判断是否需要对SQL语句进行查询优化。也就是说,并不是所有的SQL查询语句都可以从并行查询中获取收益。如果查询优化器认为查询语句可以从并行查询中获取收益的话,则就会将交换运算符插入到查询执行计划中,为并行查询做准备。故哪些语句需要采用并行查询,哪些不需要,这不用数据库管理员关心。数据库查询优化器会帮管理员作出这个决定。数据库管理员需要清楚的是,在哪些情况下,数据库SQL优化器会认为不宜采用并行查询。通常情况下,只要满足以下条件的任何一个,则就不会执行并行查询。一是对于特定的查询,查询优化器认为串行查询执行计划要快于任何可能的并行执行计划;二是查询的串行执行成本并不高,不需要进行并行查询;三是查询中包含无法并行运行的标量运算符或者关系运算符。若从数据库管理员的角度讲,第三个条件对我们具有最大的影响。当数据库预计未来可能利用并行查询来提高数据库性能时,则在数据库设计时,就需要注意避免使用那些无法在并行查询功能中使用的运算符。因为某些关系运算符或者逻辑运算符可能会要求查询计划一定要在串行模式中进行,或者部分需要在串行模式下进行。如此的话,查询优化器就不会利用并行查询功能来提高查询语句的性能。这是数据库管理员在数据库设计时必须要考虑到的一个细节问题。

  其次,确定并行的进程数。当查询优化器在查询语句中插入交叉运算符之后,数据库就会执行并行查询。并行查询在执行计划时可以使用多个线程。此时,就又遇到了一个问题,数据库会把这个查询作业分成几个进程操作呢?此时,数据库管理员就需要知道上什么叫做并行度。其实。在处理并行查询的时候,数据需要知道最大可使用的进程与实际使用的进程。而最大可使用的进程就叫做并行度。这个并行度的值是在服务器级别中进行设置,也可以通过系统存储过程来进行修改。但是,最大可使用进程数不一定等于实际是用进程数。实际是用进程数是数据库在查询计划执行时初始化的时候确定的。也就是说,这不用数据库管理员去额外的设定。数据库系统会自动根据计划的复杂程度来确定合理的进程数目。当然其实际采用的进程数不能够超过并行度,即最大可以使用的进程数。

  最后执行查询。当以上内容确定好之后,数据库就会执行具体的查询语句。在这一步中,需要注意一个问题。数据库管理员还可以在查询语句中指定MAXDOP查询提示来修改这个进度值。也就是说,如果某个查询作业数据库管理员认为可能会耗时比较久,就可以为这个查询作业设置比较大的进度值。当利用MAXDOP查询提示设置这个并行进度值之后,它会覆盖预先设置的默认值。从而实现针对单个查询语句设置额外的进度值,以提高某些特殊查询作业的性能。

  二、并行查询中需要注意的内容。

  注意点一:需要注意硬件方面的限制。

  并行查询是数据库提高查询性能的一个有力举措。不过其往往受到比较大的约束。如上面提高的一些基于成本考虑之外,还有一些硬性的限制。如通常情况下,只有在数据库服务器有多个微处理器(CPU )的情况下数据库才会考虑执行并行查询。也就是受,只有具有多个CPU的计算机才能够使用并行查询。这是一个硬性的限制条件。另外在查询计划执行过程中,数据库还会判断当时是否有足够多的线程可以使用。每个查询操作都要求一定的线程数才能够执行;而且执行并行计划比执行串行计划需要更多的线程,所需要的线程数也会随着并行度的提高而提高。如果在并行计划执行的时候,当时数据库服务器没有足够的线程让并行计划使用的话,数据库引擎就会自动减少并行度,甚至会放弃并行查询而改为串行计划。所以说,数据库是否能够执行并行查询,要受到其硬件的限制。为此,如果企业真的需要通过并行查询来提高数据库性能的话,则管理员就需要根据情况来调整硬件配置。

  注意点二:不建议对所有查询都使用并行查询。

  通常情况下,笔者认为最好只对大型表的连接查询、大量数据的聚合操作、大型结果集的重复排序等等操作才应用并行查询的功能。如果在这些操作上执行并行查询的话,那么其改善数据库性能的效果是非常明显的。相反,如果对于简单查询执行并行查询的话,可能执行并行查询所需要的额外协调工作会大于其潜在的性能提升。所以,数据库管理员在确定是否需要执行并行查询功能的话,需要慎重。笔者的建议是,在数据库服务器级别上,最好不要设置并行查询。即把并行度设置为1或者一个比较小的值。然后对于一些特殊的查询操作,利用MAXDOP查询提示来设置最大的可使用进程数。如此的话,可能会更加的合理。如果有时候数据库管理员不知道是否需要采用并行查询功能的话,则可以通过数据库自带的统计功能进行判断。为了区别并行查询计划到底有没有从并行查询中受益,数据库引擎可以将执行查询的估计开销与并行查询的开销阀值进行比较。并行计划只有对需时较长的查询通常更加有益;因为其性能优势将抵消初始化、同步和终止并行计划所需的额外时间开销。

  注意点三:数据库会根据查询所涉及到的行数来判断是否要并行查询。

  上面谈到,最好对大型表的连接查询、大量数据的聚合操作、大型结果集的重复排序等等操作才应用并行查询的功能。因为只有如此,并行查询带来的收益才会超过其付出的代价。但是,并不是说连接查询、聚合操作、排序等作业都适合采用并行查询。当数据库在考虑并行查询计划的时候,查询优化器还会去确定所涉及到的行数。如果所涉及到的行数台少,则将不会考虑执行并行查询计划。而会采用串行方式执行查询语句。如此的话,可以避免因为启动、分发、协调的开销大大超过并行执行作业所带来的收益。这本来是一个不错的设计,但是也会给数据库管理员带来一定的麻烦。如现在数据库管理员想要测试并行查询到底可以在多大程度上影响查询操作,就有点麻烦。因为其有数据量的限制。如果数据库管理员需要进行这个测试,还不得不先在数据库系统中导入足够多的数据才行。这就限制了数据库管理员的测试操作。不过话说回来,这个机制仍然是不错的。因为数据库管理员不用去考虑,当数据库规模到多大的时候采用并行查询。

  注意点四:同一个操作在不同时候会采用不同的进程数。

  上面说到过,并行查询到第采用多少进程数除了跟操作的复杂程度相关外,还直接跟当时的服务器状态相关,如是否有足够的进程数等等。所以,在不同的时间,即使是相同的数据、相同的操作,其并行查询所用的进程数也可能不同。其所需要的时间也就不同了。因为只有在并行查询真正进行的时候,数据库引擎才去收集当前系统的工作负荷,如进程数,和其他对一些配置信息,然后数据库才确定最佳的并行进程数。从查询开始,到这个查询作业结束,将一直采用这个进程数。如果下次要继续查询,则数据库引擎会继续收集这些信息。此时,如果系统工作负荷有所改善,在数据库可能会采用更多的进程数来执行这个查询。从而查询作业的性能会更加的高。相反,如果此时系统的负荷比前一次查询要重了,则数据库就可能会采用比较少的进程来处理这个作业。此时,第二次查询的速度反而更慢了。所以,如果在数据库服务器中同时部署了其他应用,则其他应用所占用系统资源的多少也会对并行执行产生难以估测的影响。

 Microsoft® SQL Server™ 可以自动并行执行查询。从而优化多处理器计算机中的查询执行。不是使用一个 OS 线程执行一个查询,而是将工作划分为多个线程,复杂查询可以更快、更有效地完成。

优化程序生成查询计划,并决定何时将并行执行查询。当进行决策时,它考虑以下事项:

·计算机有多个处理器吗?

·可用内存足以并行执行查询吗?

·服务器上的 CPU 负荷怎样?

·正在运行哪种类型的查询?

当允许 SQL Server 运行并行操作(如并行 DBCC 和索引创建)时,服务器资源变得紧张,当发生大量并行操作时会看到警告消息。

当服务器上有活动用户时请不要运行大量的并行执行的查询。

尝试在卸载期间执行 DBCC 和 INDEX 创建等维护作业。这些作业可以并行执行。

监视磁盘 I/O 性能。在性能监视器中观察磁盘队列长度,以进行升级硬盘或将数据库重新分发到不同磁盘上的决策。

如果 CPU 使用较高,请升级或添加更多的处理器。

影响并行查询的配置设置

以下服务器配置可能影响查询的并行执行:

·并行性的成本域值

·最大并行度

·最大工作线程

·查询调控器成本限制

  1. 优化测试服务器上的工作负荷。

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

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

1.创建TSQL语句跟踪

图片 1

图片 2

图片 3

2.创建跟踪模板

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

3.列筛选

图片 4

 

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

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

图片 5

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

4.列组织

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

图片 6

图片 7

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

a. 数据库引擎优化顾问将元数据从生产数据库导入到测试服务器 Shell 数据库。此元数据包括空表、索引、视图、存储过程和触发器等。这使得对测试服务器 Shell 数据库执行工作负荷查询成为可能。

一、常规

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

1.最大文件大小

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

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

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

2.启用文件滚动更新

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

3.服务器处理跟踪数据

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

在优化进程中,数据库引擎优化顾问将在测试服务器上创建 Shell 数据库。若要创建此 Shell 数据库并对其进行优化,数据库引擎优化顾问需要在下列情况下调用生产服务器:

介绍

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

 

 

优化较大的工作负荷会对所优化的服务器产生很大的开销。开销是由数据库引擎优化顾问在优化进程中多次调用查询优化器导致的。如果除生产服务器以外,再使用一台测试服务器,则可以消除此开销问题。

三、自带跟踪模板

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

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相似后者是做了时间分组。

 

开始之前,请确保两台服务器上都存在要使用测试服务器来优化生产服务器上的数据库的用户。这就需要您在测试服务器上创建用户及其登录帐户。如果您在两台计算机上都是 sysadmin 固定服务器角色成员,将不需要执行此步骤。

数据库引擎优化顾问


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

  • RPC:Completed

  • SQL:BatchCompleted

  • SP:StmtCompleted

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

注意:在数据库引擎优化顾问的图形用户界面 (GUI) 中不支持测试服务器优化功能。

跟踪属性


...

总结

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

 

 

 

 

备注:

    作者:pursuer.chen

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

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

《欢迎交流讨论》

下列步骤概要介绍了用于在测试服务器上优化生产数据库的过程:

事件

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

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

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

图片 8

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

2.SQL:Batch******

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

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

 图片 9

图片 10

这次我把两个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:正在引用的对象名称。

c. 数据库引擎优化顾问从生产服务器导入指定处理器数和可用内存量的硬件参数,为查询优化器提供生成查询计划所需的信息。

案例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.创建好跟踪后点击运行即可,事件选择这里保持默认

图片 11

图片 12

2.执行SQL

SELECT * FROM book WHERE name='10001';

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

3.停止跟踪

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

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

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

图片 13

图片 14

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

建议:

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

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

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

若要避免出现这些问题,数据库引擎优化顾问将通过把大部分优化负荷转移到测试服务器,优化生产服务器上的数据库。它通过使用生产服务器硬件配置信息,而不是真正地将数据从生产服务器复制到测试服务器,来执行该操作。数据库引擎优化顾问不会将实际数据从生产服务器复制到测试服务器中。它仅复制元数据和必要的统计信息。

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.性能注意事项

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

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

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

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

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

  数据库引擎优化顾问如何使用测试服务器

二、事件选择

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

1.显示所有跟踪事件

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

2.显示所有列

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

3.列筛选

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

4.列组织

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

 

b. 数据库引擎优化顾问从生产服务器导入统计信息,以便查询优化器可以准确优化对测试服务器的查询。

案例2:监控死锁

1.创建跟踪

图片 15

 

模板选择自带的“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会话作为死锁的牺牲品。

图片 16

3.跟踪分析死锁

图片 17

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

使用测试服务器的传统方法是将所有数据从生产服务器复制到测试服务器,优化测试服务器,然后在生产服务器上实现建议。此过程可以消除对生产服务器的性能影响,但这不是最佳解决方案。例如,将大量数据从生产服务器复制到测试服务器可能消耗大量时间和资源。此外,测试服务器硬件很少像生产服务器中部署的硬件那样功能强大。优化进程依赖于查询优化器,而它生成的建议部分依赖于基础硬件。如果测试服务器硬件和生产服务器硬件不相同,数据库引擎优化顾问建议的质量就会降低。

实例 


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

若要优化测试服务器上的工作负荷,必须通过 dta 命令行实用工具使用 XML 输入文件。在 XML 输入文件中,在 TuningOptions 父元素下使用 TestServer 子元素指定测试服务器的名称,并为其他子元素指定值。

  1. 确保两台服务器上都存在要使用测试服务器的用户。

本文由时时app平台注册网站发布于彩世界网址,转载请注明出处:数据库服务器负荷的分析与优化

关键词: