SQL Server Profiler工具

最新推荐文章于 2022-08-01 11:49:00 发布

weixin_33699914

最新推荐文章于 2022-08-01 11:49:00 发布

阅读量83

点赞数

文章标签：数据库人工智能后端

一、SQL Profiler工具简单介绍

SQL Profiler是一个图形界面和一组系统存储过程，其作用例如以下：

图形化监视SQL Server查询。
在后台收集查询信息。
分析性能；
诊断像死锁之类的问题。
调试T-SQL语句；
模拟重放SQL Server活动。

也能够使用SQL Profiler捕捉在SQL Server实例上运行的活动。

这种活动被称为Profiler跟踪。

　　1、Profiler跟踪

　　从開始=》全部程序=》Microsoft SQL Server 2008=》性能工具打开Profiler工具。也能够打开SQL Server Management Studio=》工具=》SQL Server Profiler。

　　然后选择文件=》新建=》跟踪打开一个连接窗体。选择将要跟踪的server实例然后连接。打开例如以下“跟踪属性”对话框。

　　假设有很多跟踪。能够提供一个跟踪名称来帮助在以后进行分类。

不同的跟踪模板可帮助建立用于不同目的的跟踪。

　　打开跟踪属性窗体后，单击“事件选择”选项卡，为跟踪提供更具体的定义。

　　2、事件

　　一个事件表现SQL Server中运行的各种活动。这些活动能够简单地分类为事件类，游标事件，锁事件，存储过程事件和T-SQL事件是常见的事件类。

　　对于性能分析，主要对SQL Server上运行的各种活动的资源压力水平的事件感兴趣。资源压力主要包括例如以下内容：

SQL活动涉及哪一类的CPU使用？
使用了多少内存？
涉及多少I/0操作?
SQL活动运行了多长时间？
特定的查询运行的频率有多高？
查询面对哪类错误和警告？

　　以下给出跟踪查询结束的事件：

事件类	事件	说明
Stored Procedures	RPC:Completed	RPC完毕事件
	SP:Completed	存储过程完毕事件
	SP:StmtCompleted	在存储过程中一条SQL语句完毕事件
T-SQL	SQL:BatchCompleted	T-SQL批完毕事件
	SQL:StmtCompleted	一条T-SQL语句完毕事件

　　RPC事件表示存储过程使用远程过程调用(RPC)机制通过OLEDB命令运行。假设一个数据库应用程序使用T-SQL EXECUTE语句运行一个存储过程，那么存储过程将被转化为一个SQL批而不是一个RPC。RPC请求通常比EXECUTE请求快，由于它绕过了SQL Server中的很多语句解析和參数处理。

　　T-SQL由一条或多条T-SQL语句组成。语句或T-SQL语句在存储过程中也是单独和离散的。用SP:StmtCompleted或SQL:StmtCompleted事件捕捉单独的语句可能是代价非常高的操作，这取决于单独语句的数量。

如果系统中的每一个存储过程包括且仅仅有一条T-SQL语句。在这样的情况下。完毕的语句集合相当小。如今假定过程中有多条语句。并且这些过程中有些使用其它语句调用其它过程。收集全部这些额外的数据如今变成系统上非常厉害的负载。在生产机上一定要慎用。

　　如今回到那个事件选择面板。仅仅有已经被选择的事件才会被显示。

假设想显示全部可供选择的事件。则仅仅需选中“显示全部事件”单选框，要加入一个跟踪事件。在Event列中查找一个事件类下的事件。并单击其左边的检查框。要删除不须要的事件，取消选中的事件选择框。

　　光分类就有好多的说：

　　以下给出其它一些与性能诊断有关的事件:

事件类	事件	说明
Security Audit(安全审计)	Audit Login(登录审计)	记录用户连接到SQL Server或断开连接时数据库的连接
Security Audit(安全审计)	Audit Logout(注销审计)	记录用户连接到SQL Server或断开连接时数据库的连接
Sessions(会话)	ExistingConnection(现有连接)	表示全部在跟踪開始之间连接到SQL Server的用户
Cursors(游标)	CursorImplicitConversion(游标隐含转换)	表明创建的游标类型与所请求的类型个不同
Errors and Warnings(错误和警告)	Attention(注意)	表示因为client撤销查询或者数据库连接破坏引起请求中断
	Exception(异常)	表明SQL Server发生了异常
	Execution Warning(运行警告)	表明在查询或存储过程运行期间出现了警告
	Hash Warning(哈希警告)	表明hash操作发生了错误
	Missing Column Statistics(列统计丢失)	表明优化器要求的确定处理策略用的类统计丢失
	Missing Join Predicate(连接断言丢失)	表明查询在两个表没有连接断言情况下运行
	Sort Warning(排序警告)	表明像SELECT这种查询中运行排序操作没有合适的内存
Locks(锁)	Lock:Deadlock(死锁)	标志着死锁的出现
	Lock:Deadlock Chain(死锁链)	显示产生死锁的查询链条
	lock:Timeout(锁超时)	表示锁已经超过其超时參数，该參数由SETLOCK_TIMEOUT timeout_perious(ms)命令设置
Stored Procedures(存储过程)	SP:Recompile(重编译)	表明用于一个存储过程的运行计划必须重编译。原因是运行计划不存在。强制的重编译，或者现有的运行计划不能重用
Stored Procedures(存储过程)	SP:Starting(開始) SP:StmtStarting(语句開始)	分别表示一个SP:StmtStarting存储过程和存储过程中的一条SQL语句的開始。他们对于识别開始单由于一个操作导致Attention事件未能结束的查询非常实用
Transactions(事物)	SQLTransaction(SQL事务)	提供数据库事务的信息，包含事务開始/结束的时间、事务持续事件等信息

　　3、事件列

　　事件以不同的特性(被称为数据列)来表现。数据列表现一个事件的不通特性。如事件的类、用于该事件的SQL语句、事件的资源开销以及事件来源。

数据列	说明
EventClass(事件类)	事件类型。如SQL:StatementCompleted
TextData	事件所用的SQL语句，如SELECT * FROM Person
CPU	事件的CPU开销（以ms表示），如对一个SELECT语句，CPU=100表示该语句运行100ms
Reads	为一个事件所运行的逻辑读操作数量。比如对一个SELECT语句，Reads=800表示该语句须要800次逻辑读操作
Writes	为一个事件所运行的逻辑写操作数量
Duration	事件的运行时间(ms)
SPID	用于该事件的SQL Server进程标识符
StartTime	事件開始的时间

　　以上是经常使用的数据列，另外另一些不太经常使用的数据列：

　　BinaryData(二进制数据)
　　IntegerData(整数数据)
　　EventSubClass(事件子类)
　　DatabaseID(数据库标识符)
　　ObjectID(对象标识符)
　　IndexID(索引标识符)
　　TransactionID(事务标识符)
　　Error(错误)
　　EndTime(结束时间)

　　列数据能够又一次安排以符合你自己所喜欢的风格，要控制列数据的安放，单击组织列button，将打开例如以下对话框。能够单击Up和Downbutton改动列的位置，将列移入Groups意味着它将成为一个合计列。

　　4、列筛选器

　　除了为一个Profiler跟踪定义事件和数据列之外。还能够定义各种过滤条件。这些条件帮助缩小跟踪的输出，这往往是一个好主意。以下给出经常使用过滤条件列表。

事件	过滤条件实例	用处
ApplicationName(应用程序名称)	Not like:SQL Profiler	过滤Profiler生成的事件。这是默认的行为
DatabaseID(数据库标识符)	Equals:<ID of the database to monitor>	过滤特定数据库生成的事件。数据库ID:SELECT DB_IC('Northwind')
Duration(持续时间)	Greater than or equal:2	对于性能分析，常常会为一个大的工作负载捕捉跟踪。在大的跟踪中。很多事件日志具有比所感兴趣更小的持续周期(Duration)。过滤这个事件日志，由于差点儿没有可用于优化这些SQL活动的余地
Reads(读操作数)	Greater than or equal"2	过滤读操作较小的事件
SPID	Equals:<Database users to monitor>	定位由特定的数据库用户发送的查询

　　以下给出设置过滤列的方式：

　　5、跟踪模板

　　SQL Server Profiler能够用自己定义事件、数据列和过滤器创建一个跟踪模板，然后定义一个新的跟踪，然后重用跟踪个模板来捕捉一个跟踪。

定义新跟踪模板的过程类似于定义新跟踪，过程例如以下：

创建一个新的跟踪。
和前面一样定义事件，数据列和过滤器。
从文件=》另存为菜单将跟踪定义保存为跟踪模板。

　　SQL Server Profiler将自己主动将新的模板增加到其模板列表中。

　　新建模板：

　　保存模板:

　　查看：

　　6、跟踪数据

　　定义了跟踪以后。单击执行button将開始捕捉事件并将其显示在屏幕上。能够看到一系列滚动事件。能够在我们称之为SQL TV的屏幕上看到系统的执行，能够像DVD播放机一样或多或少地控制跟踪，能够使用工具栏上的button暂停、開始和停止跟踪。甚至能够在工作室暂停跟踪并改动它。

　　一旦完毕了SQL Server活动的捕捉。就能够将跟踪输出保存为一个跟踪文件或一个跟踪表。保存到跟踪文件的跟踪输出是一个原生的格式，能够由Profiler打开以分析SQL查询。

将跟踪的输出保存为一个表，也能够使Profiler在跟踪表上用SELECT语句来分析当中的SQL查询。

　　详细的操作为文件 =》另存为 =》跟踪表。

选择你希望存入的的数据库和表，然后你就能够像普通表一样运行各种SQL查询。

二、跟踪的自己主动化

　　Profiler GUI简化了Profiler跟踪的收集。

不幸的是，这样的简易性有其代价。Profiler工具捕捉的事件进入内存中的缓冲以便通过网络反馈给GUI。

GUI依赖网络，网络流量可能减少系统的速度并导致缓冲被填满。这将在较小的程度上影响server的性能。

进一步地，当缓冲被填满。server将開始丢弃事件以避免严重地影响server性能。

　　1、使用GUI捕捉跟踪

　　能够以两种方法两创建一个脚本化跟踪-手工或者使用GUI。在轻松地满足脚本的全部要求之间。最简易的方法就是使用Profiler工具的GUI,须要例如以下步骤：

定义一个跟踪；
单击文件=》导出=》脚本跟踪定义；
必须选择目标server类型， SQL Server2005/2008；
未文件命名，并保存它。

　　这些不走将生成全部步骤跟踪并将其输出到一个文件所需的全部脚本命令。

　　使用Management Studio手工启动新的跟踪:

打开文件；
使用系统的相关名称和路径替换InsertFileNameHere；
运行脚本，它将返回带有TraceId的单列结果集。

　　能够通过SQL Agent自己主动化这个脚本的执行。甚至能够使用sqlcmd.exe使用程序从命令行执行这个脚本。

无论使用哪种方法，这个脚本将启动跟踪。假设未定义跟踪停止时间，就必须使用TraceId手工停止跟踪。

　　2、使用存储过程捕捉跟踪

　　查看上一节中定义的脚本。会看到以特定顺序条用的一系列命令：

sp_trace_create:创建一个跟踪定义。
sp_trace_setevent:加入事件和事件列到跟踪中。
sp_trace_setfilter:将过滤器应用到跟踪；

　　一旦定义了SQL跟踪持续到跟踪被停止。

由于SQL跟踪作为一个后端进程持续执行，Managerment Studio会话不须要保持打开。能够使用SQL Server内建函数fn_trace_getinfo确定正在执行的跟踪。查询例如以下：

SELECT * FROM ::fn_trace_getinfo(default);

　　输出图：

　　fn_trace_getinfo函数的输出中，不同的traceid的数量表示SQL Server上活动跟踪的数量。

第三列(value)表示跟踪是否正在执行(value=1)或者停止(value=0)。能够通过执行存储过程sp_trace_setstatus停止特定的跟踪，如traceid=1，例如以下所看到的：

EXEC sp_trace_setstatus 1,0;

在跟踪停止之后。它的定义必须运行sp_trace_setstatus关闭而且从server中删除，例如以下所看到的:

EXEC sp_trace_setstatus 1,2;

　　为了验证跟踪成功地停止。又一次运行fn_trace_getinfo函数，并确定该函数的输出不包括该traceid。

　　这样的技术所创建的跟踪文件的格式与Profiler创建的跟踪文件同样。因此，这样的跟踪文件能够与Profiler创建的文件以同样的方式进行分析。

　　使用前一小节所概述的存储过程捕捉SQL跟踪。避免了与Profiler GUI相关的开销。并且还比Profiler工具提供了管理SQL跟踪计划的更大灵活性。

三、结合跟踪和性能监视器输出

　　假设自己主动化了性能监视器捕捉到文件，又自己主动化了Profiler数据捕捉到一个文件。它们覆盖同样的时间段，那么就能够在SQL Profiler GUI中一起使用它们。

确定跟踪有StartTime和EndTime数据字段。依照下面步骤进行：

打开跟踪文件(当然前提是你以前另存为=》跟踪文件)；
单击文件=》导入性能数据；
选择导入的性能监视器文件。

　　运行上面的操作将打开例如以下所看到的对话框，这里同意选择包括性能监视器计数器。

　　选择了想要包括的计数器之后，单击OKbutton将一起打开Profiler和性能监视器数据。如今，能够開始一起使用跟踪数据和性能监视器数据。

假设在顶部窗体选择一个时间，它将在性能监视器中放置一条红线，显示数据中事件发生的时间。相反。能够单击性能监视器数据，表示那段时间的事件将被选中。这些性能工作得非常好。将能够在调整过程中定时使用它们以确认瓶颈和压力点，并确定导致这些压力的特定查询。

四、SQL Profiler使用要点

　　SQL Profiler使用建议例如以下：

限制事件和数据列的数量；
抛弃用于性能分析的启动事件。
限制跟踪的输出大小。
避免联机数据列排序。
远程执行Proflier；

　　1、限制事件和数据列

　　在跟踪SQL查询时，能够通过过滤事件和数据列来决定哪些SQL活动应该被捕捉。选择很多其它的事件造成了大量的跟踪开销。数据列不会添加太多的开销，由于它们仅仅是一个事件类的特性。因此，知道每一个所希望跟踪事件的原因，并依据必要性来选择事件是非常重要的。

　　最小化捕捉的事件数量避免SQL Server浪费宝贵的资源带宽去生成全部的事件。捕捉像锁和运行计划这种事件时应该小心进行。由于这些事件会使跟踪输出变得很大并减少SQL Server的性能。

　　过滤分两个阶段：预过滤由SQL Server运行，后过滤由用户运行。预过滤是捕捉SQL Server活动的联机阶段。预过滤提供多种溢出：

减少了SQL Server的性能影响。由于生成有限数量的时间；
减少跟踪输出大小；
简化后过滤操作，首先由于要捕捉的事件更少了。

　　预过滤的唯一缺点是，可能丢失一些彻底分析中须要的重要信息。

　　2、丢弃性能分析所用的启动事件

　　所用于性能分析的信息环绕一个查询的资源开销。想SP:StmtStarting这样的启动事件不提供这样的信息。由于仅仅有在事件完毕之后。才干计算I/O量、CPU负载和查询的持续时间。所以。在跟踪执行缓慢的查询以进行性能分析时，不须要捕捉启动事件。这样的信息由相应的完毕事件来提供。

　　什么情况下适合捕捉启动事件呢？应该在预期某些SQL查询由于错误而不能结束执行。或者频繁发现Attention事件的时候捕捉启动事件。Attention事件一般表示用户中途撤销了查询或者查询超时，可能由于查询执行了太长时间。

　　3、限制跟踪输出大小

　　除了预过滤事件和数据列，其它过滤条件也会限制跟踪输出的大小。相同，大小限制可能丢失所关注的整体系统状态中感兴趣的事件。可是，假设关注于开销较大的查询，过滤器是有帮助的。

　　通过过滤器。可以筛选运行事件》=2或逻辑读数量》=100的查询，由于消耗太低的查询基本上不须要优化。

　　4、避免在线数据列排序

　　在性能分析期间，一般在不同的数据列(如Duration、CPU、Reads)上排序以确定对应数字最大的查询。假设脱机排序，就能减少在与SQL Server交互时必须进行的Profiler活动。排序捕捉到的SQL跟踪输出的方法例如以下：

捕捉跟踪。不做不论什么排序或分组；
另存为跟踪输出到一个跟踪文件；
打开跟踪文件并依照须要在特定的数据列上排序或分组跟踪文件输出；

　　5、远程执行Profiler

　　直接在生产server上执行測试工具一般不是一个好办法。Profiler有一个大型的用户界面，因此，在其它机器上执行它更好。与系统监视器相似。Profiler不应该通过终端服务会话来执行。由于这样工具的主要部分仍然在server上执行。

在直接将跟踪输出收集到一个文件时，保存在Profiler执行的本地文件上。这仍然是比通过系统存储过程将Profiler作为server端跟踪来执行更加资源密集的操作。

使用系统存储过程仍然是最好的选择。

　　6、限制使用某些事件

　　某些事件的开销比其它的事件大。因为生成的查询的特性，语句完毕事件的开销可能很大。须要慎重地使用，特别是在已经遇到压力的系统上，必须慎重使用的事件有：Showplan XML事件，Performance:Showplan XML、Performance:Showplan XML for Query Compile和Performance:Showplan XML sTATISTICS Prifile。尽管这些事件可能实用，可是不要在生产机器上使用它们。

五、没有Profiler的情况下查询性能度量

　　建立一个跟踪能收集很多数据供以后使用。可是这样的收集可能代价非常大，必须等待结果。

　　假设要马上捕捉系统的性能度量，特别是关于查询性能的度量，那么动态管理视图sys.dm_exec_query_stats正式所须要的。

假设还须要查询执行及其单独开销的历史记录，那么跟踪仍然是更好的工具。

可是。假设仅仅须要知道这时候执行时间最长的查询或者最多的物理读操作，则能够从sys.dm_exec_query_stats得到这些信息。

　　由于sys.dm_exec_query_stats仅仅是一个视图，能够简单地对其进行查询并获得server上查询计划统计的信息。

列	描写叙述
Plan_handle	引用运行计划的指针
Creation_time	计划创建的时间
Last_execution time	查询最后一次使用的计划时间
Execution_count	计划已经使用的次数
Total_worker_time	从创建起计划使用的CPU时间
Total_logical_reads	从创建器计划使用的读操作数量
Total_logical_writes	从创建器计划使用的写操作数量
Query_hash	可用于识别有相似逻辑的查询的一个二进制hash
Query_plan_hash	可用于识别有相似逻辑的计划的一个二jinzhihash

　　为了过滤从sys.dm_exec_query_stats返回的信息，须要将其连接到其它动态管理函数上。如sys.dm_exec_sql_text能够显示与计划相关的查询文本，sys.dm_query_plan显示用于查询的运行计划。一旦连接到其它DMF。能够限制希望过滤得数据库或过程。

weixin_33699914

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server Profiler工具

一、SQL Profiler工具简单介绍SQL Profiler是一个图形界面和一组系统存储过程，其作用例如以下：图形化监视SQL Server查询。在后台收集查询信息。分析性能；诊断像死锁之类的问题。调试T-SQL语句；模拟重放SQL Server活动。也能够使用SQL Profiler捕捉在SQL Server实例上运行的活动。这种活动被称为Profiler...
复制链接

扫一扫