SQL Server monitoring series-2

Monitoring Overview

We mentioned MSSQL monitoring based on SQL event in the previous section, but sometimes we need more detailed monitoring that is suitable for tuning and troubleshooting. The visibility of running SQL Server is the possibility of query adjustment, optimization, and comprehensive troubleshooting! This section describes how to monitor the SQL server profile.

Use Cases

I remember when I was tuning for a company, the person in charge sent me a bunch of business T-SQL scripts, I am still calm in the face of massive scripts, although I do not understand the internal complex business, however, we have to focus on the key "slowness" of the problem. We will filter them out based on the "slowness" of the query and optimize them one by one. Won't the problem be solved quickly? Three days later, the person in charge held my hand with tears. The buddy worked hard and the query response was improved.

 

Tracking provider

SQL Server provides tracking methods for both of us: one is a physical file (which can be saved in a local or UNC network path) and the other is a row set. You should be familiar with the latter

This tool is available in SSMs> SQL Profile

For more information, see diffandsame (row set, file provider ).

• Both use buffer-like methods to store the current event data, obviously to reduce the IO pressure, so that the event data can be not blocked or omitted as far as possible, when the buffer reaches a certain amount, it may be flushed to the disk or sent to the network terminal (client) to display the monitoring row set.
• How to save the monitoring results for physical filesImportantIs not to miss any event, once Io speed down, may affect the overall T-SQL execution.

    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, and the IO situation of one of my customers:

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 have performed a lot of filtering, this value is acceptable, and the impact is not very big.

• In fact, we are most familiar with the method of row result set, which is directly presented to us using the SQL server profile monitoring GUI. However, I amNot recommendedFirst, if the buffer is full, there is a certain delay. It may discard the cleared cache and continue to accept the event. The event is not sent to the client, nor is it written to the physical file, naturally, it is lost. For example, the SQL server profile is monitored on the DB server. Because high-load machines are used for display, events may be lost. In addition, in fact, it is to accept a large enough buffer for large block write operations, the performance is better than the row set.

(Row set)

 

Confidentiality principles

SQL Server's security features automatically filter private data, such as passwords. I executed the following statement in my SSMs:

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

This is the system SP to change the SA account password, I opened the SQL server profile-> select the T-SQL monitoring template

Then execute the above stored procedure, and the monitoring result is:

Monitoring Result: -- * sp_password ----------------------------

 

SQL Server Profile

The SQL Server profile GUI tool has many advantages. First, it reduces the complexity of our monitoring and allows us to quickly establish monitoring. In tracking attributes, we can select templates provided by MSSQL, the commonly used t-SQL, T-SQL duration, T-SQL locks templates monitor all queries running on the current dB, the time consumed by all queries, and all the locked States, respectively.

In tracking properties-> select events, we can select the desired events, and all events are defined in msdn-> click the column filter to customize filtering and sort noise interference factors.

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

For other templates, you can refer to the msdn manual and try it yourself: SQL Server 2008 R2 local msdn

 

Server tracking and physical collection

SQL Server profile only encapsulates some stored procedures. I prefer to define common scripts and save the monitoring results on the local machine for a large amount of analysis and archiving.

Of course involves 4 stored procedures, although it is very troublesome to set the filtering script, but the SQL server profile can use the file-> export can export the monitoring script means that we do not need to write a complex T-SQL script, however, we recommend that you familiarize yourself with these stored procedures:

• Sp_trace_create defines a trail. The created trail is queried in SYS. traces.
• S_trace_setevent
• Sp_trace_setfilter
• Sp_trace_setstatus: sp_trace_setstatus @ traceid, 0 stop, sp_trace_setstatus @ traceid, and 2 remove the trace. This will cause SYS. traces to fail to query the trace.

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

It is used to monitor the batch processing and stored procedure statements that exceed the specified number of seconds and the database. (if the number of stored procedure statements exceeds 5 MB, A rolover is executed and _ 1, _ 2 is added according to the file name. TRC tracking result ):

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, including the database name, the number of seconds for executing the event, and the saved path.

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

Select * From fn_trace_gettable (N 'monitoring file path', 1 );

To view the results of the row method.

Similarly, creative readers can create their own monitoring locks and monitor deadlocks to save files. However, my advice is to reduce noise as much as possible, that is to say, what we want to achieve

What functions can be created to solve major problems in detail.

In microsfot SQL Server 2005 Technical Insider: T-SQL programming, there is a regular, used to combine all similar statements into, only the parameter form to replace the specific value

SQL clr, but I think there is still a bug in that regular expression. When I leave it empty, I will write one for you, and I will be able to use it more well.

Monitoring exception

In the previous series, exceptions captured by specific SQL events can be notified in a timely manner, but the specific exception information is not particularly detailed. Therefore, you can select

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

I created a tracking script, which is more than 5 MB rolover like the above tracking event script.

We need to perform this tracking on a regular basis. Although it is not recommended to enable it for a long time, it is advantageous for our system to operate for a longer period of time to regularly monitor and handle exceptions.

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, 33, 1, @on

EXEC sp_trace_setevent @TraceID, 33, 14, @on

EXEC sp_trace_setevent @TraceID, 33, 51, @on

EXEC sp_trace_setevent @TraceID, 33, 12, @on

EXEC sp_trace_setevent @TraceID, 11, 2, @on

EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setevent @TraceID, 11, 51, @on

EXEC sp_trace_setevent @TraceID, 11, 12, @on

EXEC sp_trace_setevent @TraceID, 13, 1, @on

EXEC sp_trace_setevent @TraceID, 13, 14, @on

EXEC sp_trace_setevent @TraceID, 13, 51, @on

EXEC sp_trace_setevent @TraceID, 13, 12, @on

DECLARE @intfilter int,@bigintfilter bigint;

EXEC sp_trace_setstatus @TraceID, 1

SELECT TraceID=@TraceID

GOTO finish

ERROR: 

SELECT ErrorCode=@rc

FINISH: 

Perform the task on a regular basis, Comrades. Look for exceptions...

Default trail and black box trail

In sys. trace of traceid = 1 in traces is the default trace of SQL Server. This trace is lightweight. Generally, the monitoring server enables and stops, and objects are created and deleted, automatic growth of logs and data files and changes in other databases. (It is the best to monitor those who have nothing to do with deleting the wrong table. Of course, do not use an account unless necessary !)

You can use

Exec sp_configure 'default trace enabled', 0;

Reconfigure with override;

To disable the default trail.

Black box tracking helps us diagnose database failures. We should also find it when searching sp_create_trace on msdn.

Then we can create a similar stored procedure to quickly create a black box tracking, to help us diagnose some exceptions!

CREATE PROCEDURE sp_trace_blackbox    @FilePath nvarchar(260)ASBEGIN    DECLARE @TraceID int,@MaxFileSize bigint    SET @MaxFileSize = 25;    EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize    EXEC sp_trace_setstatus @TraceID,1;END

Here I provide the @ filepath = NULL parameter, which is saved in the data folder of SQL Server by default.

 

End

This section describes the features of SQL Server trace in detail. If you are interested, you can go to msdn to study and monitor it, I also read the msdn and Microsoft SQL Server 2005 tuning books. The following monitoring may tell you about DDL trigger monitoring, c2 review and SQL Server Event Notification (the service broker involved will open a series to talk about service broker in detail). The final conclusion may be to talk about 2008 of data collection and monitoring, let's look forward to it. Break ~