Analyze how SQL Server Profiler is monitored

Remember a time to a company tuning, the responsible person sent me a bunch of business of T-SQL script, I face a lot of script or calmly, although do not understand the internal complex business, but we have to focus on the issue of the key "slow", we based on the query "slow" to sift them out, one-mode optimization, not quickly solve the problem? Three days later, the person in charge of tears hold my hand, buddy hard, query response has been improved quality.

Tracking provider

SQL Server provides a way for us to track both: one is a physical file (which can be stored in a native or UNC network path) and a rowset. For the latter people should be more familiar with

　　

This tool in SSMS tool –> SQL profile

　　

Detailed I do not introduce, first talk about the difference between the two and the similarities point Diffandsame (rowset, file provider).

Both use buffer-like to save the current event data, it is clear to reduce the pressure of Io, so that the event data can not be blocked and as far as possible, when the buffer reaches a certain amount of time may be flush to the disk or send to the network terminal (client) display monitoring rowset.

The important guarantee of how physical files save monitoring results is that no events can be missed, and once the IO is slowed down, it can affect the execution of the entire T-SQL.

SELECT * from Sys.dm_os_wait_stats WHERE wait_type in (' Sqltrace_lock ', ' io_completion ');

I use this statement to monitor the impact of trace and IO completion on my current machine, the IO situation of one of my clients:

Wait_type Waiting_tasks_count Wait_time_msmax_wait_time_ms Signal_wait_time_ms

io_completion66030898243774993634 418960

sqltrace_lock12007 1759431001 1281

Because I do a lot of filtering, so this value is acceptable, the impact is not particularly large.

The way the result set is, in fact, we are most familiar with, is to use the SQL Server profile monitoring GUI directly shown to us. However, I am very not recommended to use, first if the buffer is full, it has a certain delay, it may discard the event has emptied the buffer to continue to accept the event, and the event is not sent to the client, nor write to the physical file, naturally lost. For example, SQL Server profile in the DB server monitoring, because the high-load machine again to show, it is likely to lose the event, in addition to the physical file mode, in fact, accept a large enough buffer, the bulk of the write operation, performance is better than the rowset.

　　

Privacy Principles

The security features of SQL Server automatically filter data that contains privacy, such as passwords. I executed the following statement in my SSMS:

EXEC sp_password ' pp ', ' pp1 ', ' sa ';

This is the system SP that modified the SA account password, I opened the SQL Server profile–> select the T-SQL Monitor template

Then execute the above stored procedure and monitor the results:

　　

Monitoring results:--*sp_password----------------------------

SQL Server Profile

There are many advantages of using the SQL Server Profile GUI tool, the first is to reduce the complexity of our monitoring, to quickly establish monitoring, in the tracking properties, we can choose the template that MSSQL provides us, including the common T-SQL, T-Duration, The T-SQL locks template monitors all queries running in the current DB, with time-consuming, all-locked status for all queries.

In the Trace properties –> Select Event Selection We can select the events we need, all events are defined in MSDN, click Column filters to customize filtering, sort noise interference factors

　　

Other templates you can take a look at the MSDN manual and try it Yourself: SQL Server page R2 native MSDN

Server-side tracking and physical collection

SQL Server profile is just encapsulation of some stored procedures, and I prefer to define my own commonly used scripts to store the monitoring results natively for a large number of analyses and archives.

Of course, there are 4 stored procedures involved, although setting up a filtered script is cumbersome, but SQL Server profile can export the monitoring script using file-based export, which means that we don't need to write complex T-SQL scripts, but we recommend that you familiarize yourself with these stored procedures:

Sp_trace_create defines the trace that is created by the trace in the Sys.traces query.

S_trace_setevent Setting up Monitoring events

Sp_trace_setfilter Setting Filter

Sp_trace_setstatus set tracking status commonly used is the sp_trace_setstatus @traceid, 0 stop function, sp_trace_setstatus @traceid, 2 remove Trace, This will cause Sys.traces to eventually query the trace

In fact, the whole tracking is relatively simple. I have a common script here:

Used to monitor batches and stored procedure statements that exceed a specified number of seconds and database (more than 5MB of files, execute rollover, and add trace results similar to _1,_2.TRC following the file name):

CREATE PROC [dbo]. [Sp_trace_sql_durtion]

@DatabaseName nvarchar (128),

@Seconds bigint,

@FilePath nvarchar (260)

As

BEGIN

DECLARE @rc int, @TraceID int, @MaxFileSize bigint;

SET @MaxFileSize = 5;

EXEC sp_trace_create @TraceID output,2, @FilePath, @MaxFileSize, NULL;

IF @rc! = 0

RETURN;

DECLARE @On bit;

SET @On = 1;

EXEC sp_trace_setevent @TraceID, 10,35, @On;

EXEC sp_trace_setevent @TraceID, 10,1, @On;

EXEC sp_trace_setevent @TraceID, 10,13, @On;

EXEC sp_trace_setevent @TraceID, 41,35, @On;

EXEC sp_trace_setevent @TraceID, 41,1, @On;

EXEC sp_trace_setevent @TraceID, 41,13, @On;

SET @Seconds = @Seconds * 1000000;

EXEC sp_trace_setfilter @TraceID, 13,0,4, @Seconds;

IF @DatabaseName is not NULL

EXEC sp_trace_setfilter @TraceID, 35,0,0, @DatabaseName

EXEC sp_trace_setstatus @TraceID, 1

SELECT traceid = @TraceID;

END

The parameters are very clear, the database name, how many seconds to execute the event, and the path to save.

When we run this script for an event, we can quickly find a lot of time-consuming T-SQL that we can

SELECT * from fn_trace_gettable (N ' monitoring file path ', 1);

To see the results of the row style.

The same creative readers can create their own monitoring lock, monitoring deadlocks and other ways to save files, but my advice is to reduce noise as much as possible, that is, we have to achieve what the target

What functions are built so that the big problem can be refined and solved.

There is a regular in the Microsfot SQL Server 2005 Technology Insider: T-SQL programming to combine similar statements in all, with only the parameter form replacing the specific value

The SQL CLR, but I think that there is a bug, and so I empty to everyone to write one, you can use more perfect.

Monitoring exceptions

In the last series, the specific SQL event caught exception, can be notified in a timely manner, but the specific exception information is not particularly detailed. So we can select the events in the

Error to add all the exceptions to the T-SQL batch and SP for analysis, this trace is very helpful for us to monitor some anomalies!!!

I created a tracking script that is more than 5MB RollOver, just like the script for tracking events above.

We want to perform this tracking on a regular basis, although it is not recommended to be open for a long time, but regular monitoring and handling of exceptions is beneficial to our systems for longer operations.

CREATE PROC [dbo]. [Sp_trace_sql_exception]

@FilePath nvarchar (260)

As

DECLARE @rc int, @TraceID int, @Maxfilesize bigint

SET @maxfilesize = 5

EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL

IF (@rc! = 0)

RETURN;

DECLARE @on Bit

SET @on = 1

EXEC sp_trace_setevent @TraceID, 1, @on

EXEC sp_trace_setevent @TraceID, @on

EXEC sp_trace_setevent @TraceID, @on

EXEC sp_trace_setevent @TraceID, @on

EXEC sp_trace_setevent @TraceID, one, 2, @on

EXEC sp_trace_setevent @TraceID, one, one, @on

EXEC sp_trace_setevent @TraceID, one, Wuyi, @on

EXEC sp_trace_setevent @TraceID, one, one, @on

EXEC sp_trace_setevent @TraceID, 1, @on

EXEC sp_trace_setevent @TraceID, @on

EXEC sp_trace_setevent @TraceID, Wu, Wuyi, @on

EXEC sp_trace_setevent @TraceID, @on

DECLARE @intfilter int, @bigintfilter bigint;

EXEC sp_trace_setstatus @TraceID, 1

SELECT [email protected]

GOTO Finish

ERROR:

SELECT [email protected]

FINISH:

Do it regularly, comrades, looking for something unusual ...

Default tracing and black-box tracking

The trace of Traceid = 1 in sys.traces is the SQL Server default trace, which is lightweight, generally monitors the server's enable stop, object creation and deletion, log and data file autogrow, and other database changes. (Monitoring those who have nothing to delete the wrong table, is the best, of course, not all use an account!)

can be done by

EXEC sp_configure ' default trace enabled ', 0;

RECONFIGURE with OVERRIDE;

To turn off default tracing.

Black box tracking, is to help us diagnose the database is nothing from a run of the exception, in the MSDN search Sp_create_trace should also find out

　　

option, we can also create a similar stored procedure to quickly create a black box trace to help us diagnose some exceptions!

CREATE PROCEDURE Sp_trace_blackbox

@FilePath nvarchar (260)

As

BEGIN

DECLARE @TraceID int, @MaxFileSize bigint

SET @MaxFileSize = 25;

EXEC sp_trace_create @TraceID output,8, @FilePath, @MaxFileSize

EXEC sp_trace_setstatus @TraceID, 1;

END

I am providing the @filepath = NULL parameter, which is saved in the SQL Server data folder by default.

Analyze how SQL Server Profiler is monitored