[转载]SQL Server性能调教系列(4)--Profiler(上)

[转载]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。

1

注:

  • 不要使用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:
<!– .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } –>

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

<!– .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } –> <!– .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } –> <!– .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } –>

执行之后会显示出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

<!– .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } –>2.4

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

捕获

接下篇:

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

>>>性能调校系列入口地址

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏