WEB开发网
开发学院数据库MSSQL Server SQL Server性能调教系列(3)--Profiler(上) 阅读

SQL Server性能调教系列(3)--Profiler(上)

 2010-09-27 00:00:00 来源:WEB开发网   
核心提示: 查看原图(大图)注:不要使用GUI跟踪,应该使用T-SQL,SQL Server性能调教系列(3)--Profiler(上)(2),因为使用GUI会有两个跟踪,一个把跟踪信息写入目标文件,Trce ID: 2, Trace File: 'C:\test\performancetrace_

SQL Server性能调教系列(3)--Profiler(上)

查看原图(大图)

注:

不要使用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性能调教系列(3)--Profiler(上)

查看原图(大图)

上一页  1 2 

Tags:SQL Server 性能

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接