SQL Server Performance Tuning series (4)-Profiler (I)

I. Introduction

In terms of processing performance, DBA tends to focus on the system technical level, such as resource queue, resource utilization, and system loading. However, users only consider performance issues as waiting. They send a request from the business logic layer and wait for the returned results. The background database needs to respond to the request. From the user's point of view, it is generally considered that it is a performance problem to wait for three seconds to return (except for special systems: for example, a large amount of data operations are required). They do not care about the data layer of the system, for example, how many commands are waiting for processing, CPU utilization, and RAM usage. After encountering these problems, we need to find this problem. Please optimize it and find a reasonable solution.

 

Note: For hardware problems, see series (2)

SQL Server Performance Tuning series (2) -- server Performance Monitor (perfmon)

 

Ii. Theory

To optimize the SQL statements, you must first find the optimization items (such as SQL statements or SP statements with low efficiency) and reference the optimization steps described in SQL technology:

1. Analyze instance-level waiting

2. Contact the waiting queue

3. Determine the solution

4. refine to database/file level

5. refine to the Process Level

6. Optimize index/Query

 

Iii. Method

This chapter describes the profiler tool to track performance workloads.

1. Introduction to profiler

Start with SQL Server-> Tools-> SQL Server Profiler

General page: trace records can be saved to files or tables. Generally, you can choose to save to a file, because saving to a table adds a lot of additional system overhead.

Events selection page: events that can be tracked. For more tracking events, see msdn.

Note:

• Do not use GUI tracking. Use T-SQL. Because there are two traces in the GUI, one is to write the trace information into the target file, and the other is to write the trace information into the running GUI, which will increase the additional overhead of the system.
• Do not write the trail directly to the table. This will seriously affect the performance. Writing files to the disk is the fastest solution.
• Tracing will generate a large number of and additional Io operations. Do not place trace files on disks that contain database files (such as data, logs, and tempdb.
• Select the data column of the event class and track only the required information.
• Use a trail to filter events.

2. Start tracing

2.1 you can first set the event class to be tracked in the GUI, and then export the script (file-> export --> script trace definition ), generally, events with a duration series greater than some values (such as 3000 ms) are filtered to track slow processes.

 

For example, the exported script is as follows. Here we organize the trace script into a stored procedure for convenient execution.

CREATE PROC [dbo].[sp_perfworkload_trace_start]  @dbid      AS INT,  @tracefile AS NVARCHAR(254),  @traceid   AS INT OUTPUTAS-- Create a QueueDECLARE @rc          AS INT;DECLARE @maxfilesize AS BIGINT;SET @maxfilesize = 100;EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULLIF (@rc != 0) GOTO error;-- Client side File and Table cannot be scripted-- Set the eventsDECLARE @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 FiltersDECLARE @intfilter AS INT;DECLARE @bigintfilter AS BIGINT;-- Application name filterEXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';-- Database ID filterEXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;-- Set the trace status to startEXEC sp_trace_setstatus @traceid, 1;-- Print trace id and file name for future referencesPRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))  + ', Trace File: ''' + @tracefile + '''';GOTO finish;error:PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));finish:

 

2.2 start the trail with the following T-SQL code:

Declare @ dbid int, @ traceid int; Set @ dbid = db_id () --- it can be the default dB, or db_id ('your _ dbname') exec sp_perfworkload_trace_start @ dbid, 'c: \ test \ performancetrace_20100802.trc ', @ traceid output

 

Traceid is displayed after execution. Remember this traceid and use it to stop and disable tracing.

Trce ID: 2, trace file: 'C: \ test \ cetcetrace_20100802.trc'

 

2.3 stop and close tracing (sp_trace_setstatus. If traceid is 2, the STOP and close code is as follows ):

EXEC sp_trace_setstatus 2,0 EXEC sp_trace_setstatus 2,2

2.4

If you forget the traceid, you can find it in the query attempt SYS. traces.

 

Next article:

SQL Server Performance Tuning series (4) -- profiler (II)

 

>>> SQL server performance tuning portal address