sql server08 查询优化系列 3-1 sql 查询性能分析 sql profiler

sql profiler 工具

此工具 是 可用于 一下目标 的一个 GUI和一组系统存储过程.

• 图形化坚持sql server 查询
• 在后台收集查询信息
• 分析性能
• 诊断像死锁这样的问题
• 调试Transact-SQL语句
• 模拟重放SQL Server活动

 

下面新建一个跟踪:

打开 sql server profiler

新建一个跟踪:

可以把检测的结果 保存到文件或者选择到 数据库中的表里,

这里面有很多的事件 供我们选择  来跟踪.

 

事件

• 游标事件
• 锁事件
• 存储过程事件
• T-Sql事件

对于性能分析,主要对帮助 判定 sql server上执行的各种活动的资源压力水平的 事件 倍感兴趣. 

那么资源压力主要是指:

• sql server 活动涉及哪一类的cpu使用
• 使用了多少内存
• 涉及多少I/O操作
• Sql 活动执行了多长时间
• 特定的查询执行的频率多高
• 查询面对哪类错误和警告

可以再事件完成后 计算 sql活动的资源压力,所以用于性能分析的主要时间 是 那些标示 sql 活动完成的事件. 如下:

事件类	事件	描述
Stored Procedures	RPC:Completed	RPC完成事件
	SP:Completed	存储过程完成事件
	SP: StmtCompleted	在存储过程内一条sql语句完成事件
TSQL	SQL: BatchCompleted	T-Sql批完成事件
	SQL: StmtComleted	一条sql语句完成事件

 

RPC事件表示 存储过程 使用远程调用(RPC)机制 通过 OLEDB命令执行.

如果应用程序 用T-SQL Execute执行存储过程,那么存储过程会被转换一个 sql 批 而不是一个RPC.

数据列:

事件 以不同的特性(被称为数据列) 来表现.下面看一下部分 数据列:

下面看一下数据存的真实数据:

 

过滤器:

可以在跟踪时  定义过滤,这样可以帮助 缩小跟组的的输出.

可以 列名,在右面 可以选择 过滤的条件. 就是这么简单.

 

上面讲的都是 使用GUI来跟踪,那么这样会有弊端.

profiler工具 捕捉的事件 进入内存中的缓冲 以便通过 网络反馈给 GUI.GUI依赖网络,网络流量会降低系统的速度

并导致缓冲区填满.当缓冲区没填满后,服务器将开始 丢弃事件已避免严重的影响服务器的性能.

 

那么我们就可以使用存储过程来 捕捉跟踪.

下面就用 这几个 拓展存储过程来完成这个操作.

1创建一个跟踪: (sp_trace_create )http://msdn.microsoft.com/zh-cn/library/ms190362(v=sql.105)

DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 0, N'd:\SampleTrace1.trc' 

2。给跟踪添加事件(sp_trace_setevent )http://msdn.microsoft.com/zh-cn/library/ms186265(v=sql.105)

SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 45, 1, @on -- 45 是代表SQL: StmtComleted

3.添加过滤器 (sp_trace_setfilter )  http://msdn.microsoft.com/zh-cn/library/ms174404(v=sql.105)

4.执行 (sp_trace_setstatus )http://msdn.microsoft.com/zh-cn/library/ms176034(v=sql.105)

EXEC @RC = sp_trace_setstatus @TraceID, 1   --1开始 0结束 2 结束并删除

 

整体代码:

DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 0, N'd:\SampleTrace1'

-- Select the return code to see if the trace creation was successful.
SELECT RC = @RC, TraceID = @TraceID

-- Set the events and data columns you need to capture.
SELECT @on = 1

--添加事件
EXEC sp_trace_setevent @TraceID, 45, 1, @on 

--开始跟踪

EXEC @RC = sp_trace_setstatus @TraceID, 1
GO

执行后看如下信息:

看到traceId=2

那么查看 这个跟踪的详细信息

select * from sys.fn_trace_getinfo(2)

结果如下图:

以供五行数据.正好对应 创建跟踪的那个后面五个参数.

如果想 停止 该跟踪;

EXEC @RC = sp_trace_setstatus @TraceID, 0
删除该跟踪：

EXEC @RC = sp_trace_setstatus @TraceID, 2

下面 把 生成的 跟踪文件 导入到数据表中:

select * into test from sys.fn_trace_gettable('d:\SampleTrace2.trc',default)

表的数据如图:  因为只加了一列 textdata 所以 后面的都是null