【飞秋】SQL Server性能调教系列(4)--Profiler(上)

一：简介

在处理性能问题是，DBA倾向于关注系统技术层面，如资源队列，资源利用率，系统loading等。而用户只把性能问题认为是等待，他们从业务逻辑层面发出一个请求，等待返回结果，后台数据库就需要去响应这个请求。从用户角度来看，一般认为等待三秒才返回就属于性能问题（特殊的系统除外：比如需要大量的数据操作），他们并不关心系统的数据层，比如有多少个命令在等待处理，CPU利用率，RAM使用率等。在遇到这些问题之后，我们需要找到这个问题，请着手优化，找到合理的解决方案。

 

注：硬件方面的问题请参照系列（2）

SQL Server性能调教系列(2)--Server Performance Monitor(Perfmon)
 

二：理论

要做优化，首先要找出需要优化的部分（如找到效率低的SQL或SP等），引用SQL技术内幕中介绍的优化步骤：

1.分析实例级的等待

2.联系等待的队列

3.确定方案

4.细化到数据库/文件级

5.细化到进程级

6.优化索引/查询

 

三：方法

本章主要介绍Profiler工具来跟踪性能工作负荷。

1. Profiler简介

通过SQL Server—>Tools—>SQL Server Profiler启动

 

General页：跟踪的记录有两种保存方式:保存到文件和保存到表。通常选择保存到文件，因为保存到表会增加较多的额外系统开销。

Events Selection页：能够选择跟踪的事件。更多的跟踪事件请参考MSDN。

 

注：

不要使用GUI跟踪，应该使用T-SQL。因为使用GUI会有两个跟踪，一个把跟踪信息写入目标文件，一个把跟踪信息写入运行的GUI，会增加系统的额外开销。
不要把跟踪直接写入到表，这样会严重影响性能，把文件写到磁盘是最快的方案。
跟踪会产生大量和额外的IO操作。不要把跟踪文件放到包含数据库文件（如数据，日志和tempdb）的磁盘上。
选择事件类的数据列，只跟踪需要的信息。
使用跟踪筛选需要的事件。
2.启动跟踪

2.1  可以先在GUI中设置需要跟踪的事件类，然后在导出脚本(File—>Export-->Script Trace Definition)，通常筛选Duration数列大于某些值(比如3000毫秒)的事件来跟踪运行得比较慢的进程。

 

如:导出的脚本如下,这里把trace的脚本整理为一个存储过程,以方便执行.

CREATE PROC [dbo].[sp_perfworkload_trace_start]
  @dbid      AS INT,
  @tracefile AS NVARCHAR(254),
  @traceid   AS INT OUTPUT
AS
-- Create a Queue
DECLARE @rc          AS INT;
DECLARE @maxfilesize AS BIGINT;

SET @maxfilesize = 100;

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL
IF (@rc != 0) GOTO error;

-- Client side File and Table cannot be scripted

-- Set the events
DECLARE @on AS BIT;
SET @on = 1;
EXEC sp_trace_setevent @traceid, 10, 15, @on;
EXEC sp_trace_setevent @traceid, 10, 8, @on;
EXEC sp_trace_setevent @traceid, 10, 16, @on;
EXEC sp_trace_setevent @traceid, 10, 48, @on;
EXEC sp_trace_setevent @traceid, 10, 1, @on;
EXEC sp_trace_setevent @traceid, 10, 17, @on;
EXEC sp_trace_setevent @traceid, 10, 10, @on;
EXEC sp_trace_setevent @traceid, 10, 18, @on;
EXEC sp_trace_setevent @traceid, 10, 11, @on;
EXEC sp_trace_setevent @traceid, 10, 12, @on;
EXEC sp_trace_setevent @traceid, 10, 13, @on;
EXEC sp_trace_setevent @traceid, 10, 14, @on;
EXEC sp_trace_setevent @traceid, 45, 8, @on;
EXEC sp_trace_setevent @traceid, 45, 16, @on;
EXEC sp_trace_setevent @traceid, 45, 48, @on;
EXEC sp_trace_setevent @traceid, 45, 1, @on;
EXEC sp_trace_setevent @traceid, 45, 17, @on;
EXEC sp_trace_setevent @traceid, 45, 10, @on;
EXEC sp_trace_setevent @traceid, 45, 18, @on;
EXEC sp_trace_setevent @traceid, 45, 11, @on;
EXEC sp_trace_setevent @traceid, 45, 12, @on;
EXEC sp_trace_setevent @traceid, 45, 13, @on;
EXEC sp_trace_setevent @traceid, 45, 14, @on;
EXEC sp_trace_setevent @traceid, 45, 15, @on;
EXEC sp_trace_setevent @traceid, 41, 15, @on;
EXEC sp_trace_setevent @traceid, 41, 8, @on;
EXEC sp_trace_setevent @traceid, 41, 16, @on;
EXEC sp_trace_setevent @traceid, 41, 48, @on;
EXEC sp_trace_setevent @traceid, 41, 1, @on;
EXEC sp_trace_setevent @traceid, 41, 17, @on;
EXEC sp_trace_setevent @traceid, 41, 10, @on;
EXEC sp_trace_setevent @traceid, 41, 18, @on;
EXEC sp_trace_setevent @traceid, 41, 11, @on;
EXEC sp_trace_setevent @traceid, 41, 12, @on;
EXEC sp_trace_setevent @traceid, 41, 13, @on;
EXEC sp_trace_setevent @traceid, 41, 14, @on;

-- Set the Filters
DECLARE @intfilter AS INT;
DECLARE @bigintfilter AS BIGINT;
-- Application name filter
EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';
-- Database ID filter
EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

-- Set the trace status to start
EXEC sp_trace_setstatus @traceid, 1;

-- Print trace id and file name for future references
PRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))
  + ', Trace File: ''' + @tracefile + '''';

GOTO finish;

error:
PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish:

2.2 用以下T-SQL代码启动跟踪：

declare @dbid int,@traceid int;
set @dbid=DB_ID()---可以为默认的DB，或者DB_ID('Your_dbname')
EXEC sp_perfworkload_trace_start @dbid,'C:/test/performancetrace_20100802.trc',@traceid output

执行之后会显示出traceid，请记住这个traceid，会用它来停止和关闭追踪。

Trce ID: 2, Trace File: 'C:/test/performancetrace_20100802.trc'

 

2.3 停止和关闭追踪(sp_trace_setstatus,如果traceid是2，则停止和关闭的代码如下)：

EXEC sp_trace_setstatus 2,0
EXEC sp_trace_setstatus 2,2 2.4

如果忘记traceid，可以在查询试图sys.traces找到。

 

 

接下篇:

SQL Server性能调教系列(4)--Profiler(下)

关注技术文章飞秋：http://www.freeeim.com/，24小时专业转载。