SQL Server monitoring uses sp_trace_create

Monitoring preface

In the previous section we mentioned the SQL event-based monitoring of MSSQL, but there are times when we need to be more detailed and suitable for tuning debugging. Visibility of running inside SQL Server is a possibility for query tuning, optimization, and comprehensive troubleshooting! This section is mainly about some of the ways and means of monitoring SQL Server trace (SQL Server profile).

Usage Scenarios

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 ');

Wait_type

Waiting_tasks_count

Wait_time_ms

Max_wait_time_ms

Signal_wait_time_ms

Io_completion

66030898

24377499

3634

418960

Sqltrace_lock

12007

175943

1001

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.

(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 ';

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

(I randomly chose a time-consuming = 500 subtle filter condition)

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:

You can query all the tracking plans:

SELECT * from Sys.traces

Stop, delete, to stop before deleting:

EXEC sp_trace_setstatus 2,0-Stop, the first parameter is the ID column in the SELECT * from Sys.traces
EXEC sp_trace_setstatus 2,2--delete

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;

    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 abnormal situation!!!

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

    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

SELECT [email protected]

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) asbegin    DECLARE @TraceID int, @MaxFileSize bigint    SET @MaxFileSize =;    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.

End

The various features of SQL Server Trace are described in detail here, and interested friends can drill down to MSDN Research monitoring, which I've just taken, as well as the MSDN and Microsoft SQL Server 2005 Tuning book, The following monitoring may tell you about DDL trigger monitoring, C2 auditing, and SQL Server event notification (the Service Broker involved I'll open a series and talk to you in detail about Service Broker), and the final end might be to say 2008 of data collection monitoring, We look forward to it. Rest ~

Reference: http://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html

SQL Server monitoring uses sp_trace_create