文章: SQL Server大负载的生产环境下的性能优化:初识元数据优化

标签: 文章 sql server | 发表时间:2011-12-21 06:00 | 作者:
出处:http://pipes.yahoo.com/pipes/pipe.info?_id=10560380f804c7341f042a2b8a03e117

相信朋友对SQL Server性能调优相关的知识或多或少都有一些了解。虽然说现在NOSQL相关的技术非常的火热,但是RMDB(关系型数据库)与NOSQL是并存的,并且适用在各种的项目中。在一般的企业级开发中,主要还是RMDB占据主导地位。并且在互联网项目中,也不是摒弃了RMDB,例如MySQL就在很多的互联网应用中发挥着作用。所以,对数据库的调优是个值得深入学习的课题。本系列文章,主要讲述与SQL Server相关的调优知识,希望能够为朋友们带来一些帮助。

本篇提纲如下:

  • 传统SQL Server调优方式的比较
  • 什么是DMV
  • DMV简单示例
  • DMV可以解决哪些问题

传统SQL Server调优方式的比较

我们平时可以采用很多工具对SQL Server的性能进行诊断分析与调优:性能计数器,SQL Server Profiler和Database Engine Tuning Advisor(数据库引擎优化顾问,简称DTA)。下面我们就来对每一个简单的说明一下,并且讲述各自的优缺点。

性能计数器

对于使用Windows操作系统的开发朋友,对性能计数器已经不陌生了,最简单的方式就是运行”perfmon”,就可以打开性能监视器的窗口,然后添加对应的计数器,进行监控。如下图所示:

性能计数器每隔一段时间就回去收集相关的数据,并且我们还可以把这些数据保存起来,便于以后的分析。

优点:使用方便,并且数据收集的比较全面。因为这些操作系统是集成的,不需要额外的花费,就可以直接使用。

缺点:数据不够准确,分析数据成本很高,并且对系统的性能产生影响。因为性能计数器在收集数据的时候,要定时的去抓取系统相关的数据,这样,会对性能造成影响,如果抓取的时间间隔越短,那么对系统的影响就越大。另外,对于收集到的数据,也需要有经验丰富的人去分析数据,并且如果要准确的分析出结果,需要收集大量的数据,人力和时间的成本也高。

SQL Server Profiler

每次谈到SQL Server调优,势必要讲到SQL Server Profiler(为了后续的讲述方便,我们将会把它简称为Profiler)。使用Profiler能够捕获一段时间内SQL执行的每个查询的记录。当数据库服务器上有大量很少运行的查询时,或者有特别的用户查询运行的时候,这个工具很有用。使用Profiler还能捕获到指定时间段内的工作负荷,然后可以在恢复的数据库系统中进行重现。

下面就是一个使用Profiler的界面:

在使用Profiler的时候,在哪里启动Profiler以及将跟踪的数据保存在何处,是一个特别需要考虑的问题。下面,我们就用来对比地看看各种不同的情况。

启动Profiler的位置

跟踪文件

说明

在正在监视的数据库服务器上启动Profiler的跟踪

将跟踪的数据文件保存到服务器的文件系统或共享目录中

优点:这种方式适用于非生产环境和低负载服务器上的开发和对数据库应用进行性能诊断和跟踪。

缺点:加大了服务器的I/O读写操作

在本机开启Profiler,连接远程的数据库服务器

将跟踪的数据文件保存在本地

优点:减小了数据库服务器的I/O操作。

缺点:加大了网络传输的压力,占用数据库服务器的网络资源和CPU资源。因为需要将大量的跟踪数据传送到本机,这样占用了网络资源;而数据在传输的时候,需要CPU将之序列化,加大了CPU的操作。

数据库引擎优化顾问(DTA)

DTA一般需要和Sql Server Profiler结合在一起使用。DTA主要是对Profiler中收集到的数据进行纯数据的综合分析,所以它分析结果的准确性非常依赖于Profiler收集的数据量的多少。Profiler收集的数据越多,那么DTA分析的就越准确,但是这样也对数据库服务器的压力越大,反之。所以,一般不建议在大负载或者生产环境下的数据库服务器上面采用。

如果有需要在大负载,或者生产环境下对数据库的性能进行分析与调优,那如何处理?

这就是我们本次系列文章要讨论的话题。

什么是DMV

使用过SQL Server的朋友,对DMV(Dynamic Management Views,动态管理视图)或多或少都有一些了解或者耳闻。其实DMV就是SQL Server内核的元数据,通过对内部的元数据的分析,我们快速而准确获取很多与SQL Server内部相关的信息,从而进行性能分析。

当查询在SQL Server中运行时,SQL Server会自动的将此次活动的相关信息记录下来,并且保存在内存之中,这些活动信息,就称之为:DMV。

不同类型的DMV信息,有不同的用途,例如,可以相关的DMV来对性能进行诊断,从而提升性能,或对数据库的运行进行监控,或解决故障等。

DMV是以SQL Server实例为级别进行保存的。也就说,如果在服务器上面,安装了一个SQL Server,那么此时这个SQL Server就是一个实例,那么这个实例里面的所有的数据库的DMV都是保存在相同的内存中。当然,我们在使用的时候,可以根据需要只提取更低级别的DMV,例如提取某个数据库的DMV,某个表的DMV,甚至是某个查询的DMV。

因为DMV信息是保存在内存中的,我们不需要额外的操作,只需要将这些信息取出来,按照我们的要求进行运算,统计,分析就够了,获取信息的数据非常快,并且不会对服务器产生压力。另外,因为DMV是SQL Server本身保存的,并且已经做了统计的信息,所以,数据更加的接近于数据库本身的状态。

SQL Server运行的时候越长,DMV中保存的信息就越多(当然,DMV非常小,不会对内存造成压力),利用DMV分析就越准确。这一点和之前的Profiler和DTA是完全不一样的。唯一的一个问题就是:每次SQL Server服务重启,这些保存在内存中的DMV信息就没有了,又是从头开始,慢慢的保存。当然,对于这个问题,我们有很多的解决方案,例如,我们可以定期的将DMV的信息导出,保存在磁盘上。

DMV包含了的信息有很多:索引相关的,查询执行相关的,还有SQL Server OS相关的,Common Language Runtime(CLR)相关的,事务相关,安全相关的,资源管理相关的,数据备份相关的,I/O相关,全文查找相关,数据库镜像相关的,等等信息。所以,我们完全可以使用已经保存在DMV中的信息来进行我们的分析。

因为SQL Server内部的DMV很多,我们本次系类的文章注重在性能分析与调优上,所以,我们主要关注以下几类DMV:索引相关,执行相关,SQL Server OS相关,CLR相关,事务相关,I/O相关,数据库相关。

下面,我们分析一个查询的运行,看看在这个过程中SQL Server都记录了哪些信息(或者说,DMV中保存了什么信息):

  1. 查询的执行计划(即描述了一个查询是如何被执行的)
  2. 什么索引被使用
  3. 什么索引本来应该被用到,但是又没有使用。(因为此时存在缺失索引的性能问题)
  4. I/O的状态(包含逻辑I/O操作和物理的I/O操作)
  5. 查询执行消耗的时间
  6. 查询等待其他资源消耗的时间
  7. 查询在等待什么资源

通过分析这些信息,不仅仅可以使得我们更好的理解查询的是如何工作的,并且还可以让我们思考如何更加合理,高效的使用资源,提高性能。

一般而言,在使用DMV的时候,我们很多时候也需要将其与DMF(Dynamic Management Functions)一起使用。我们可以简单的将DMF理解为SQL Server内部的一系列函数。例如,通过分析sys.dm_exec_query_stats,可以知道查询的相关信息,如果将sys.dm_exec_query_stats里面的sql_handle传给sys.dm_exec_sql_text,那么,我们就可以知道查询的语句的内容。

DMV简单示例

为了使得大家对DMV有更加深入的了解,我们首先来看看一个使用DMV来找出那些查询运行的最慢。(朋友们可能对这里提到的相关的DMV和DMF不太熟悉,没关系,后续文章会介绍)

在SQL Server的查询分析器中,运行一下SQL 语句:

此时,运行的结果如下:

在这个查询中,我们主要是通过将sys.dm_exec_query_stats这个DMV与sys.dm_exec_sql_text和sys.dm_exec_query_plan这两个DMF结合,通过分析查询所消耗的时间,然后按照从高到低进行排序,选出前20个进行展示。

从这个示例中,我们可以知道几点:

  1. 查询DMV时,应该尽可能的将对数据库的影响降到最小。所以,我们在查询的最上面,加上了:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。因为每次在运行查询的时候,或多或少的会对数据库产生不同程度的锁定,并且锁定的级别各不一样。通过上面的设置,就告诉SQL Server,接下来的执行的查询将锁定的级别定为:Read Uncommitted。从而将影响减小到最小。
  2. 每次进行性能问题诊断的时候,首先要解决最严重的性能问题。所以,我们此处只是找出前20个运行最慢的查询语句。
  3. 虽然原生的DMV信息提供了很多的信息,但是很多时候需要对DMV进行复杂的统计分析。(这个成本比分析SQL Server Profiler收集到的数据小,也更加简单,准确。)

DMV可以解决哪些问题

看完了上面的简单的例子之后,相信朋友们对DMV有了一个感性的认识,下面,我们就来看看,利用DMV,我们可以解决哪些问题。

故障诊断

诊断就是要识别出问题的所在。有很多的方式和工具可以帮助我们达到这个目的,但是,有了DMV,可能效率会更快:没有什么比分析SQL Server内部的元数据来的更快。

很多时候,对问题的诊断也是性能调优的第一步,搞清楚了问题,才好对症下药。

利用DMV可以诊断出以下问题:最慢的查询语句,常见的等待与阻塞,没有用的索引,大量的I/O操作,利用率最低的执行计划。

正如之前所说,我们可以在不同的级别上面分析问题,例如从整个服务器级别,数据库级别,甚至是某个查询。我们可以通过在获取DMV信息时,设置获取信息的条件来办到。例如,在上一小节的示例中,就是获取整个SQL Server中找出最慢的前20个查询,如果需要,我们完全可以将条件缩小到某个数据库。

很多时候,在识别问题的时候,不是那么容易,仅仅通过一个DMV就搞定了的,需要和DMF结合。甚至要和其他的DMV一起结合分析(在后续文章中,我们会理解的更加深刻)。

诊断出了问题,是一个方面,解决问题也尤为重要。

性能调优

性能调优主要是利用相关的技巧技术之前诊断中出现的问题,从而提升性能。我们后续会详细讲述,这里就不再赘述了。

状态监控

很多的DMV(特别是那些以 sys.dm_exec_开头的)都反映了数据库服务器执行的状态。通过查看这些DMV,我们可以清楚的知道数据库服务器的现在的状态和历史的状态(当然,如何SQL Server服务被重启,那么之前的信息都丢失了,除非定期做了保存)。例如,数据库需要做批处理等长时间的操作,如果其中操作执行超时或运行的非常慢,这个时候,我们就可以查询DMV来分析。如果采用Profiler或者相关的Profiler脚本跟踪,会对数据库服务器的压力相当大。再如,还可以分析数据库中现在有哪些查询在运行,有多少请求在处理,打开多少连接等等,主要是对数据库的操作,都可以通过DMV查询到。

到这里,不知道朋友们是否有点“跃跃欲试”的冲动,我们在后续,会为朋友们一一奉上。敬请关注。

关于作者

汪洋,现任惠普架构师、信息分析师《NET应用架构设计:模式、原则与实践》作者。上海益思研发管理咨询有限公司首席软件架构专家,软件咨询组副组长。


感谢 崔康对本文的审校。

给InfoQ中文站投稿或者参与内容翻译工作,请邮件至 editors@cn.infoq.com。也欢迎大家加入到 InfoQ中文站用户讨论组中与我们的编辑和其他读者朋友交流。

相关 [文章 sql server] 推荐:

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 中的事务

- - CSDN博客推荐文章
       事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分. 即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行.         事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入、修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.

SQL Server优化50法

- - CSDN博客数据库推荐文章
  虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:. 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷). I/O吞吐量小,形成了瓶颈效应. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 查询步骤 - pursuer.chen

- - 博客园_首页
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/查询步骤.       查询步骤是很基础也挺重要的一部分,但是我还是在周围发现有些人虽然会语法,但是对于其中的步骤不是很清楚,这里就来分解一下其中的步骤,在技术内幕系列里面都会有讲到.  TOP于ORDER BY的关系. INSERT INTO Customers VALUES(1,'深圳'),(2,'广州'),(3,'武汉'),(4,'上海'),(5,'北京').

文章: SQL Server大负载的生产环境下的性能优化:初识元数据优化

- - InfoQ cn
相信朋友对SQL Server性能调优相关的知识或多或少都有一些了解. 虽然说现在NOSQL相关的技术非常的火热,但是RMDB(关系型数据库)与NOSQL是并存的,并且适用在各种的项目中. 在一般的企业级开发中,主要还是RMDB占据主导地位. 并且在互联网项目中,也不是摒弃了RMDB,例如MySQL就在很多的互联网应用中发挥着作用.

sql server复灾 你懂了吗?

- brett80 - 博客园-首页原创精华区
很多时候我们不小心错误delete了一下,或者update一下怎么办,或者直接把数据库删除了,怎么办呢,是不是就一定没有办法呢. 下面让我来教大家我现学现卖的两招. 做之前我们要设置数据库恢复模式:. 首先我们创建一个表:插入几条数据. 我们现在有五条数据了,我们对数据做一个备份. 做任何差异备份,和日志之前,一定要做一个完整备份.

监控 SQL Server 的运行状况

- Bloger - 博客园-首页原创精华区
Microsoft SQL Server 2005 提供了一些工具来监控数据库. 动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能. 常规服务器动态管理对象包括:. dm_db_*:数据库和数据库对象. dm_exec_*:执行用户代码和关联的连接.

SQL Server 数据库巡检脚本

- - CSDN博客数据库推荐文章
select '现在没有阻塞和死锁信息' as message. select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'. select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'.