SQL Server监控

1.       原理与相关概念介绍

SQL Server Profiler，大家已经非常熟悉。常常在性能优化中使用，本文档详细介绍SQL Server Profiler。随着SQL Server2008的发布，伴随着XEvent的出现，SQL Profiler会慢慢的被替代，但是，Profiler依然是性能调优与故障排除中的一个利器。

SQL Server Profiler的中文意思是SQL Server事件探查，其实SQL Server Profiler就是一个Sql的监视工具，可以捕获和分析数据库中发生的相关的事件，例如，存储过程的执行等，可以具体到每一行Sql语句，每一次操作，和每一次的连接。感觉这个工具的作用还是很大的，给大家分享一下，对于新手了解Sql语句的写法和提高语句的性能方法有很大的帮助。

 为什么要学习这门技能，学习完了之后，可以给我们带来什么好处？可以这样说：区分一个有经验的DBA与一个新手DBA的区别就在于：有经验的DBA知道在核实充分利用何种工具可以快速的将问题搞定，如果熟练的掌握了SQL Profiler之后，有经验的DBA就可以对数据库的运行状况进行跟踪，并且利用跟踪的数据进行问题的分析与性能的诊断与优化，有时候，甚至可以再问题还没有发生之前发现潜在的问题。

掌握SQL Profiler的好处列下来：

1.可以监视SQL Server内部发生的各种活动：例如发生在数据库引擎中的活动，发生在Analysis Services以及Integration Services中的活动。

2.监控并且确认某些查询，事务相关的性能问题等。

3.可以对执行计划进行分析。

4.可以监视并且确认SQL Server内部的发生的一些错误和警告。

5.可以捕获数据库发生的数据，并且还可以对数据库的压力进行重放。

 6.可以对数据库中用户的活动进行审计跟踪。

7.可以通过多种方式对数据进行分析与跟踪。

8.可以将跟踪的数据与系统的性能计数器关联起来，从而更加全面的检查发生的问题。

9.可以将跟踪的数据以DTA来进行分析

10.还可以对SQL Profiler进行编程，可以不使用它的GUI工具。

1.1SQL Server Profiler的工作原理

其实SQL Server Profiler这个工具是SQL Trace的一个GUI的版本，而SQL Trace是一组脚本，自SQL Server 2000就已经有了的，这一组脚本捕获和跟踪SQL Server内部发生一些事件，并且将跟踪的数据保存以便后续使用。从本质上说，SQL Trace就是一个非常简单的工具，它的作用就是捕获客户端应用与SQL Server之前的通信信息。它扮演一个非常特殊的网络嗅探器的功能，用来捕获与SQL Server相关的网络活动，同时也允许我们查看客户端应用的请求触发了SQL Server的哪些事件。另外，与我们熟悉的网络嗅探器不同的是：SQL Trace只是捕获发生在SQL Server内的相关的事件和活动，而不会获取网络上面传输的数据。 

那么对于上面的图：

1、SQL Server事件是指由SQL Server本身内部触发的活动，或者由客户端与SQL Server进行连接时在SQL Server内部触发的活动。事件包含很多不同的种类。

2、因为事件发生在SQL Server的内部，SQL Trace的任务就是根据Filter来捕获自己感兴趣的事件。

3、一旦事件被SQL Trace捕获，那么发生的事件与相关的事件信息就会被保存在内存的队列中。

4、SMO(Server Management Objects)，是可以直接的访问SQL Server的对象模型。其实，SQL Trace对于我们而言，就可以将之看成是一个黑盒，我们能做的就是通过一些方式和工具去与这个黑盒交互，SQL Profiler和系统的存储过程就是这样的工具。

1.2SQL Profiler中常用的术语概念

事件(Events)。简而言之就是发生的活动，例如一个存储过程的执行，就是一个活动，也是发生了一个事件。SQL Profiler允许我们捕获大约170多种不同的与SQL Server有关的事件。另外，在SQL Profiler中，使用Event Category这个概念，对不同的事件进行了一个大致的分类，使得为我们的监控更加的方便，例如所有与存储过程相关的事件都被划分在一个分类中。

数据列(Data Columns)

简而言之就是描述事件发生的一些信息。这个概念和我们在.NET编程中的事件信息的概念是一样的。如图所示，可以看到事件发生的时候，包含了一些实事件信息，即数据列。图中列出了很多事件的数据列，例如，对于RPC:Completed事件而言，包含了TextData，ApplicationName等。同时，还可以看出 ，不是所有事件都有相同的数据列。 

过滤器(Filters)。过滤器主要是SQL Profiler用来对发生的事件进行过滤的，只捕获感兴趣的事件。过滤器非常有用，因为它可以决定我们收集到的数据的正确性，减少采集的数据量。可以根据很多条件来创建灵活的过滤，如图所示：

 

跟踪(Trace)。请注意：这里的跟踪，不是个动词，而是一个名词！每一个跟踪包含了发生的事件以及相关的数据列信息，并且我们常常将这些信息保存在物理的文件中。其实，我们常常所说的跟踪，就是一个保存SQL Profiler数据的跟踪文件。跟踪文件可以以很多的方式保存：保存在内存中，以特定的格式导出，保存在数据库中，保存为XML文件等。下图就是在SQL Profiler中直接显示了跟踪信息，即跟踪信息保存在内存中： 

 

2.       快速使用SQL Server Profiler

在开始使用SQL Profiler之前，有一些问题需要注意。

1、不要随意的在生产环境下（或者说：实际的数据库工作的服务器环境）轻易的使用SQL Profiler，特别是在初学的时候。因为使用SQL Profiler会对服务器产生压力，带来一定的性能影响，在初学的时候，不要拿正式环境做实验。

2、最好在自己的本地搭建测试的数据库，然后采用一些脚本或者工具来模拟对数据库的使用。

2.1理解如何配置权限

在SQL Server中，正如不是谁都可以创建表，视图一样，也不是谁都可以自由的运行SQL Profiler的。因为SQL Profiler在运行的过程中可以看到很多与服务器以及客户端相关的信息，所以要求运行这个工具的权限也很高。在默认情况下，只有sa（SQL Server中的一个超级用户）和在SYSADMIN组中的用户可以运行SQL Profiler。在SQL Server 2005中，也可以通过用sa或者SYSADMIN用户给其他用户授权，从而使得其他的SQL Server用户也可以有这个权限。授权的SQL 脚本如下图所示（将LoginID替换为我们想要授权的用户名）：

 

下面的一段脚本是收回这个授权的，如图所示：

 

2.2启动SQL Profiler

可以通过很多的方式打开SQL Profiler。

1、使用命令行工具打开。步骤非常简单，只要在CMD命令窗口输入“profiler“，然后按下“回车”就行了。这里需要注意的是，如果安装的是SQL Server 2008，那么就输入“profiler”，如果安装的是SQL Server 2005，那么就输入“profiler90”。

2、直接在SQL Server Management Studio的菜单中打开，如图所示：

 

3、我们单击开始--程序--Microsoft SQL Server 2005--性能工具--SQL Server Profiler，如图：

 

不管用什么方式，打开SQL Profiler之后，就看到如下的界面，如图所示：

 

2.3建立跟踪任务

我们单击文件---【新建跟踪（N）...】（在菜单“File”中选择“New Trace”）,这是一个多窗口多任何的工具，我们可以同时新建不同的跟踪窗口，也可以是不同的数据库，

 

 

在这里我们输入我们的跟踪的数据库的服务器名称，用户名和密码等信息。单击连接进入下一个界面

  

 在这个界面中，包含了两个选项卡，一个是“General”（常规），另外一个是“Events Selection”（事件选择）。这里做一些简单的介绍，在“General”选项卡中，我们可以对跟踪进行命名，还可以选择不同的模板，并且还可以设置很多不同的选项。其中，Trace Provider name（跟踪提供程序名称）就是指的我们要跟踪的SQL Server的实例名；Trace provide type（跟踪提供程序类型），指的就是数据库的版本名，而version就是版本的数字表示。在这里，比较重要的一个选择就是“Use the template”（使用模板），如图所示：

 

  在这里可以选择一个跟踪的模板，不同的模板，功能不一样，并且模板中事件，数据列，过滤器等都不一样！每一种模板的用处。

另外，在“Events Selection选项卡”（事件选择）中，选择我们要跟踪的事件，如图所示：

 

上图中可以进行一个基本设置，使用的模板选择，和文件的保存选择等。我们一般使用默认的就OK了，不用动上面的东西。

也可以自己选择 事件名称、以及需要显示的列名信息。然后将其保存为模版，方便以后跟踪直接使用。也就是说我们要跟踪的事件有那个，在这里可以一一的选择，基本上Sql上有的事件都有，包括你用SQL Server Management Studio操作数据库的过程都可以跟踪的到。具体的事件和说明大家可以自己看一下。只要单击显示所有事件就可以进行全部事件的选择了。假如我需要经常使用SQL Server Profiler监控死锁的信息，可以如下图所示操作

选择自己需要的事件和列名信息

 

   我们还可以对统计的字段进行筛选，单击任意一个列标题可以查看列的说明如下图，例如执行时间大于1000ms的事务

 

我们从上图上依次说明为：

TextDate

依赖于跟踪中捕获的事件类的文本值

ApplicationName 

 创建 SQL Server 连接的客户端应用程序的名称。此列由该应用程序传递的值填充，而不是由所显示的程序名填充的；

NTusername    Windows

用户名

LoginName    

用户的登录名(SQL Server 安全登录或 Windows 登录凭据，格式为“域\用户名”)

CPU     

事件使用的 CPU 时间(毫秒)。

Reads

由服务器代表事件读取逻辑磁盘的次数。

Writes         

由服务器代表事件写入物理磁盘的次数。

Duration 

事件占用的时间。尽管服务器以微秒计算持续时间，SQL Server Profiler 却能够以毫秒为单位显示该值，具体情况取决于“工具”>“选项”对话框中的设置

ClientProcessID  

调用 SQL Server 的应用程序的进程 ID。

SPID     

SQL Server 为客户端的相关进程分配的服务器进程 ID。

StratTime

事件(如果可用)的启动时间。

EndTime

事件结束的时间。对指示事件开始的事件类(例如 SQL:BatchStarting 或 SP:Starting)将不填充此列。

BinaryData        

依赖于跟踪中捕获的事件类的二进制值。

     

 全部操作完成后，就开始启动SQL Profiler去监控数据库中发生的活动：点击“Run”按钮，如图所示：

 

2.4介绍SQL Profiler 的GUI

下面熟悉一下对于SQL Profiler的跟踪窗口：

 

1、界面分为两个部分，在上面标记为红色的那个区域，在这里就显示了Profiler捕获到的事件，每一行就代表一个发生的事件。

2、不是所有的事件都包含相同的数据列，例如在图中，有些事件，如Audit Logout, 在TextData列没有数据，这就说明，TextData不是这个事件的事件信息列。

 3.在界面下半部分的那个红色区域，显示了TextData的详细的信息（前提是如果事件有TextData列），另外，对于不同的事件，TextData显示的数据是不一样的。例如，对与RPC:Completed事件，TextData就显示了正在执行的存储过程的名称。对于一些事件，还可以显示执行计划的XML格式。

在界面的最下面，还显示了“Trace is running”，显示当前Profiler的状态。我们还可以点击工具栏中的按钮，对其中的数据进行分析，查询，跟踪可以暂停，开始和停止操作，可以同时启动多个跟踪，同时跟踪不同的数据库和表

 

2.5保存一个跟踪

当Profiler运行了一段时间之后，就可以停止，并且将跟踪的数据保存。这里需要注意的是：因为我们之前采用了默认的设置，此时数据全部是在服务器的内存中的，如果跟踪的时间越长，那么数据量就会越多，会消耗很多的内存。

（1）如图，将跟踪结果保存为一个trc文件，保存此次跟踪：

 

在弹出的界面中输入跟踪名称后数据就保存起来了，如果要查看这个跟踪文件中的数据，可以再用Profiler加载，如图所示：

 

加载之前我们创建的跟踪文件，这时候，我们就可以查看之前数据库中发生的活动，从而进行分析，如图所示：

 

（2）将跟踪结果保存到数据库的某个数据表中。分析时利于进行数据筛选。

SQL Server Profiler跟踪结束后，依次点击【文件】→【另存为】→【跟踪表】，在弹出框中输入数据库信息，点击【连接】按钮。

 

选择导出数据库，输入表名，点击【确定】按钮，跟踪结果即可导出到表log中。

 

（3）提取死锁事件

 

（4）保存跟踪模板

可以选择另存为-->“跟踪模版”（当然，也可以自己预定义模版）

也可以将模版导出，方便在别处使用

 

再次新建跟踪的时候，看模版选项，就可以看到并使用我们自己保存的模版了。

 

3.       SQL Profiler脚本自动化

介绍了SQL Profiler 的使用，那么除了通过GUI界面操作，还可以通过脚本来实现。这样不用每天都打开 SQL Server Profiler 点一点鼠标，而是每天定时自动抓取一段时间的数据库SQL运行情况来进行同比分析，实现长期的SQL语句的性能监控。下面做一些介绍和举个例子，大家可以通过注释和联机文档中的说明来进行了解。

DECLARE @TraceID int

DECLARE @fileAddress nvarchar(1000)

DECLARE @maxFileSize bigint

DECLARE @endDateTime datetime

DECLARE @result int

DECLARE @strSqlCmd VARCHAR(2000)

--跟踪文件保存在D盘下面，文件名加上当天的年月日

SET @fileAddress = N'D:\' + Convert(char(10),GetDate(),120)

--如果文件存在先删除

SET @strSqlCmd= 'DEL '+@fileAddress+'.trc'

EXEC master.dbo.XP_CMDSHELL @strSqlCmd

--每个文件最大200M，不限定个数

SET @maxFileSize = 200

--5分钟后跟踪结束

SET @endDateTime = DATEADD(minute,5,GETDATE())

--定义一个跟踪

--参数可以参考联机文档，不做详述

EXECUTE @result = sp_trace_create @TraceID output, 2, @fileAddress, @maxFileSize, @endDateTime

IF (@result != 0) GOTO error

DECLARE @on BIT

SET @on=1

--在跟踪中添加或删除事件或事件列

--设定需要跟踪的事件，跟踪远程调用存储过程

  EXECUTE sp_trace_setevent @TraceID, 10,  1, @on

  EXECUTE sp_trace_setevent @TraceID, 10,  8, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 10, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 11, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 13,@on

  EXECUTE sp_trace_setevent @TraceID, 10, 14, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 15, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 16, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 17, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 18, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 27, @on

  EXECUTE sp_trace_setevent @TraceID, 10, 35, @on

--跟踪 Transact-SQL

  EXECUTE sp_trace_setevent @TraceID, 12,  1, @on

  EXECUTE sp_trace_setevent @TraceID, 12,  8, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 10, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 11, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 13, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 14, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 15, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 16, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 17, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 18, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 27, @on

  EXECUTE sp_trace_setevent @TraceID, 12, 35, @on

  
--设定需要过滤的条件，不跟踪 SQL Server Profiler 和 SQLAgent

  EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL%'

  EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft%'

--按照HostName排除请求

  EXECUTE sp_trace_setfilter @TraceID, 8, 0, 7, N'hostname%'

--启用跟踪

  EXECUTe sp_trace_setstatus @TraceID, 1

SELECT TraceID = @TraceID

error:SELECT ErrorCode = @result

--间隔5:30分钟跟踪结束后再返回

WAITFOR DELAY '00:05:30'

4.       数据库引擎优化顾问使用方法

有了这个跟踪的记录我们怎么样使用数据库引擎优化顾问对其进行分析优化呢？当然第一步我们要先把跟踪到的记录导出到文件.trc类型的文件。单击文件--另存为

 

假如我们把文件保存为123.trc，现在我们来打开数据库引擎优化顾问  我们单击开始--程序--Microsoft SQL Server 2005--性能工具--数据库引擎优化顾问

如下图

 

和上面一样我们要先输入数据库的登录信息，单击连接进入如下界面   

 

      

我们可以看一下界面，一般不用选择只要安默认的就OK了，需要配置的我上面都有注释。在这里大家一定要记得选择用于工作负荷的数据库和表，意思就是设置要分析的数据库否则会分析不成功。现在就可以单击开始了

 

在这里我们可以看到分析成功后的分析报告，这还不算，我们单击上面的建议选项卡

 

在这里还给出了你的那些表，需要优化，应该怎么建立索引和视图才能更有效的提高性能，更好玩的是连需要优化的Sql语句都给生成好了，我们直接复制执行就要可以了。