说道SQL Server Profiler,相关很多朋友们都已经不陌生了。虽然说现在在网络上有很多的文章介绍它的使用,但是介绍的都不怎么系统与完整。因为笔者对SQL Server Profiler的使用也有些年了,并且常常在性能优化中使用,所以,借此机会,献出一整个介绍SQL Server Profiler的系列文章。
可以说说SQL Profiler是一个非常强大的工具,使用它,我们可以捕获和分析数据库中发生的相关的事件,例如,存储过程的执行等。而捕获的信息可以为我们在性能诊断中提供依据。
如果你对SQL Profiler不是很熟悉,那么,本篇文章将会带着你一步步的熟悉。在这篇文章中,我会讲述SQL Profiler的工作原理,还会解释在后面文章要常常要用的一些术语,同时也会讲述如何保持一个Profiler Traces跟踪文件。
通过本篇文章的越多,朋友们会清楚如下的问题:
1.理解SQL Profiler是如何工作的
2.对Profiler的一些术语和概念有一个比较清楚的认识,例如事件,数据列,过滤器等
3.理解如何配置权限启动Profiler
4.可以创建基本的Profiler跟踪,并且保存。
在开始学习任何一门技能之前,我们需要知道:为什么要学习这门技能,学习完了之后,可以给我们带来什么好处?毕竟人的精力是有限的,不可能随随便便学习一门对自己毫无用处的知识。
可以这样说:区分一个有经验的DBA与一个新手DBA的区别就在于:有经验的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工具。
说了这么多,似乎已经把SQL Profiler说的天花乱坠了!其实SQL Profiler也不是万能的,也只是在SQL Server诊断与分析中常常使用的一种工具而已,至于决定到底是否在使用它,还是取决于DBA或者技术人员本身,通过本系列的学习,相信大家会有一个比较清楚的认识。
首先,我们来简要的看看SQL Server Profiler的工作原理。
SQL 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内的相关的事件和活动,而不会获取网络上面传输的数据。
为了使得大家对SQL Trace有一个更加清楚的认识,我们来看看下面的一个图1-1:
那么对于上面的图1:
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和系统的存储过程就是这样的工具。
为了后续的理解方便,也为了保证我们的理解一致,我们下面介绍一些常见的概念和术语
SQL Profiler中常用的术语概念
这里主要介绍下面四个术语:
事件(Events)
数据列(Data Columns)
过滤器(Filters)
跟踪(Trace)
事件
相信熟悉微软技术的朋友对“事件”这个概念已经不陌生了,简而言之就是发生的活动,例如一个存储过程的执行,就是一个活动,也是发生了一个事件。SQL Profiler允许我们捕获大约170多种不同的与SQL Server有关的时间。
另外,在SQL Profiler中,使用Event Category这个概念,对不同的事件进行了一个大致的分类,使得为我们的监控更加的方便,例如所有与存储过程相关的事件都被划分在一个分类中。
数据列
这个概念理解起来非常简单,简而言之就是描述事件发生的一些信息。这个概念和我们在.NET编程中的事件信息的概念是一样的。如图1-2所示,可以看到事件发生的时候,包含了一些实事件信息,即数据列。
图1-2中列出了很多事件的数据列,例如,对于RPC:Completed事件而言,包含了TextData,ApplicationName等。同时,还可以看出 ,不是所有事件都有相同的数据列。
过滤器
顾名思义,过滤器主要是SQL Profiler用来对发生的事件进行过滤的,只捕获感兴趣的事件。可以根据很多的来创建灵活的过滤,如图1-3所示:
过滤器在使用SQL Profiler中非常有用,因为它可以决定我们收集到的数据的正确性,更加对收集到的数据的量产生很多的影响。
跟踪
请注意:这里的跟踪,不是个动词,而是一个名词!每一个跟踪包含了发生的事件以及相关的数据列信息,并且我们常常将这些信息保存在物理的文件中。其实,我们常常所说的跟踪,就是一个保存SQL Profiler数据的跟踪文件。
跟踪文件可以以很多的方式保存:保存在内存中,以特定的格式导出,保存在数据库中,保存为XML文件等。如图1-4所示,就现在SQL Profiler中直接显示了跟踪信息,即跟踪信息保存在内存中:
1. 如何配置权限
2. 启动SQL Profiler
3. 介绍SQL Profiler 的GUI
4. 保存一个跟踪
通过上一篇文章的讲述,相信大家已经对SQL Profiler有了一个初步的了解,或者有些朋友已经开始磨拳擦掌,想跃跃欲试了。在开始使用SQL Profiler之前,有一些问题需要注意。
1. 不要随意的在生产环境下(或者说:实际的数据库工作的服务器环境)轻易的使用SQL Profiler,特别是在初学的时候。因为使用SQL Profiler会对服务器产生压力,带来一定的性能的影响,在初学的时候,不要拿正式的环境做实验。
2. 最好在自己的本地搭建测试的数据库,然后采用一些脚本或者工具来模拟对数据库的使用。在后面的讲解中,我会运行一些示例的SQL 脚本来模拟。
3. SQL Profiler是一个操作性比较强的工具,最好是跟着本系列中的示例,一个个的动手操作和体会。
理解如何配置权限
在SQL Server中,正如不是谁都可以创建表,试图一样,也不是谁都可以自由的运行SQL Profiler的。因为SQL Profiler在运行的过程中可以看到很多的与服务器以及客户端相关的信息,所以要求运行这个工具的权限也很高。
在默认情况下,只有sa(SQL Server中的一个超级用户)和在SYSADMIN组中的用户可以运行SQL Profiler。在SQL Server 2005中,也可以通过用sa或者SYSADMIN用户给其他用户授权,从而使得其他的SQL Server用户也可以有这个权限。授权的SQL 脚本如下图2-1所示:
如上图所示,我们可以将LoginID替换为我们想要授权的用户名。
下面的一段脚本是收回这个授权的,如图2-2所示:
启动SQL Profiler
好,有了权限之后,我们就来好好的体验一把!
可以通过很多的方式打开SQL Profiler。我这里给大家介绍一下:
1. 使用命令行工具打开。步骤非常简单,只要在CMD命令窗口输入“profiler“,然后按下“回车”就行了。这里需要注意的是,如果安装的是SQL Server 2008,那么就输入“profiler”,如果安装的是SQL Server 2005,那么就输入“profiler90”。
我个人喜欢使用命令行方法,快捷!
注:这里非常抱歉,因为我所在的环境安装的都是英文版的操作系统与软件,所以很多的时候,一些界面会以英文的形式显示,希望朋友们见谅!
2. 直接在SQL Server Management Studio的菜单中打开,如图2-3所示:
不管用什么方式,打开SQL Profiler之后,就看到如下的界面,如图2-4所示:
然后,在菜单“File”中选择“New Trace”,如图2-5所示:
当我们选择了“New Trace”(创建新跟踪)之后,就会弹出如下的界面,如图2-6所示:
使用合法的身份验证方式进入之后,就会看到选择要跟踪的事件的界面,如图2-7所示:
在这个界面中,包含了两个选项卡,一个是“General”,另外一个是“Events Selection”。这里我们很有必要对界面中做一些简单的介绍。
在“General”选项卡中,我们可以对跟踪进行命名,还可以选择不同的模板,并且还可以设置很多不同的选项。
其中,Trace Provider name就是指的我们要跟踪的SQL Server的实例名;Trace provide type,指的就是数据库的版本名,而version就是版本的数字表示。
在这里,比较重要的一个选择就是“Use the template”,如图2-8所示:
在这里可以选择一个跟踪的模板,不同的模板,功能不一样,并且模板中事件,数据列,过滤器等都不一样!每一种模板的用处,我们会在后续文章讲述。
另外,在““Events Selection选项卡”中,选择我们要跟踪的事件,如图2-9所示:
这里出于体验的目的,我们将一切都保持默认!
下面,我们就开始启动SQL Profiler去监控数据库中发生的活动:点击“Run”按钮,如图2-10所示:
那么启动之后,因为这个时候SQL Profiler是对整个SQL Server实例进行监控的,也就是说,在SQL Server中的任意一个数据库发生了任何的活动,都会被SQL Profiler捕获到(当然,如果设置了过滤器就例外了)。
为了使得效果明显,我这里运行一些脚本,对数据库进行一些操作,起到模拟的作用。我们看到下面的一个效果图2-11所示:
从图中可以看到,我在SQL Server运行一个查询,这个时候SQL Profiler立刻就捕获到了这个动作。
介绍SQL Profiler 的GUI
对于SQL Profiler的跟踪窗口,有很多我们需要学习的东西,下面我们就通过一个比较全面的图(2-12)来看看:
对于图2-12:
1. 界面分为两个部分,在上面标记为红色的那个区域,在这里就显示了Profiler捕获到的事件,每一行就代表一个发生的事件。
2. 不是所有的事件都包含相同的数据列,例如在图中,有些事件,如Audit Logout, 在TextData列没有数据,这就说明,TextData不是这个事件的事件信息列。
3. 在界面下半部分的那个红色区域,显示了TextData的详细的信息(前提是如果事件有T extData列),另外,对于不同的事件,TextData显示的数据是不一样的。例如,对与RPC:Completed事件,TextData就显示了正在执行的存储过程的名称。对于一些事件,还可以显示执行计划的XML格式。
4. 在界面的最下面,还显示了“Trace is running”,显示当前Profiler的状态。
5. 通过点击工具栏的按钮,可以暂停此时跟踪,如图2-13所示:
6. 还可以停止跟踪,如图2-14:
保存一个跟踪
当Profiler运行了一段时间之后,就可以停止,并且将跟踪的数据保存。这里需要注意的是:因为我们之前采用了默认的设置,此时数据全部是在服务器的内存中的,如果跟踪的时间越长,那么数据量就会越多,会消耗很多的内存。
如图2-15,保存此次跟踪:
然后为跟踪命名,如图2-16所示:
之后,数据就保存起来了!
如果要查看这个跟踪文件中的数据,可以再用Profiler加载,如图2-17所示:
加载之前我们创建的跟踪文件,这时候,我们就可以查看之前数据库中发生的活动,从而进行分析,如图2-18所示:
在进行性能诊断的时候,我们会采用DTA工具来加载和分析跟踪文件。具体的我们后文讲述,
我们调试程序的时候,有些时候或者有些 IDE 可能不会提供详细的数据库异常信息。SQL Server Profiler 是 SQL 跟踪的图形用户界面,可以监视数据库引擎和 Analysis Services。在使用 Linq to SQL 或 ADO.NET Entity Framework 时可以用它清楚的知道它们对数据库执行了那些语句。同时我们也可以用它来跟踪数据库发生的异常。
SQL Server Profiler 通过 开始-->所有程序—>Microsoft SQL Server 2008-->性能工具—>SQL Server Profiler 打开。
单击文件-->新建跟踪,填写数据库连接的信息后选择连接。默认情况下使用Standard模板,它是不会跟踪异常的。如果要跟踪异常先切换到“事件选择”选项卡
单击右下的显示所有事件,再选中 Errors and Warnings 节下的 Exception 和 User Error Message 前的复选框。其他复选框可根据需要选择。选择完成后单机运行,此时开始跟踪。
然后执行要调试的程序并让它进行数据库操作。如果之前选择了 TSQL 下的一些复选框,那么 SQL Server Profiler 会记录下程序执行的 T-SQL 语句。查询发生异常时,SQL Server Profiler 会记录下异常的信息并显示,就像在 SQL Server Management Studio 中进行查询。
用此工具还可以跟踪在非调试环境下的数据库异常。