Keep your eyes open and watch SQLServer

I 've watched Fan Wei's<Tracking Kong lingxue>, once again, I enjoyed Fan Wei's exquisite acting skills, especially the cool expression. After reading this, I think of the tracking and anti-tracking technologies in SQLServer. I think this part is worth writing an article to share with you. Understanding SQLServer tracking technology allows us to easily obtain the internal operation of SQLServer in real time at runtime. This acquisition method is much easier than using the tracking mark and dynamic management view. When it comes to tracking, many people will think of SQL Profiler. SQL Profiler is just a GUI, and SQL Trace is the essence. SQL Trace is the basis for building server tracking and Profiler. If you understand this, you will not hesitate to use server tracking in the production environment. The following describes SQL Trace from three aspects: 1. Cost of SQL Trace tracking 2. SQL Trace architecture 3. Specific tracking Example 4. How to backtrack v. How to follow SQL Trace principles

 I. Cost of SQL Trace tracking

It must be noted that tracing will affect system performance, which is inevitable. Of course, we can minimize this cost in some ways. Many people often refuse to trace because the tracing will affect the current network performance. In fact, this is not correct. Some people also do tracking at ordinary times, but they like tracking when the system is not busy. This is a problem. The former often has a sudden problem with your system, and you do not have any warning, the latter often has the best time you miss the problem capture, so tracking in busy hours is wasted. When can we track the production environment? The correct method should be to collect system information every moment to provide information sources for the overall analysis of system performance.

Ii. SQL Trace Architecture

If you want to understand SQL Trace, the best way is to compare it with your own system. In general, we will record some logs in the system and identify the log level based on the points of attention. A typical log component is

Log components such as Log4net. In this way, we can analyze the running status of the system through logs. Knowing this makes it easy to understand SQL Trace. In SQLServer, the trace information is composed of a series of events. Who triggers the event since there is an event. All components in the database engine are event producers. The architecture of SQL Trace is as follows:

 

As shown in: the entire SQL Trace architecture consists of three parts: Database Engine, Trace controller, and Trace session. The database engine is the event generator. The Tracking Controller is responsible for event distribution and Event Filtering. The tracking session is responsible for filtering event columns and tracking event endpoints. The following describes the entire process. The tracking controller uses a bitmap to let other components of the Database Engine know what events the tracker requested. This bitmap is a collection of all the tracked events. Once the database engine generates an event, it stores the event information in the queue in the tracking controller. The Tracking Controller then transmits the complete event information to each tracing session that requires this event. When the tracing session receives the event information that it is concerned with, it first goes through the filter (mainly to filter out columns and rows that are not interested), filters out the information and sends it to the tracking I/O provider. The queue here serves as a buffer. There are many types of I/O providers, such as Profiler, server trace, and SQLServer's own trace.

Iii. Specific tracking examples

The example here does not require the SQL Profiler for example, because I think it is just convenient for us to track. However, when tracing, it writes the output to the target file or table (and then saves the table in the saved file) and the tracking information to the client running Profiler. Writing tracing information to the running Profiler client is often slower than directly writing data to files. You can think about why? However, you can use Profiler to define a trail graphically and then export the generated trace SQL. The details are as follows:

 

Once you have enabled tracking, you can use the following methods:

Select * from sys. traces to view the session you are tracking.

 

4. Anti-tracking

Sometimes, we do not want our SQL to be tracked. For example, we do not want others to see the SQL statements written in our program. There are many methods. Here is a simple method. The idea is to force SQLServer to stop tracking. The specific stored procedure is as follows:

/* + Certificate ---------------------------------------------------------------------------------------------------------------------------------------
* Name: [DBO]. [Performance_Trace_StopAll]
* Function: prevents anti-tracking
* Author: junling
* Creation Time:
* Project name: XXXX
* Certificate *-----------------------------------------------------------------------------------------------------------------------------------------
* History
* Date Author remarks
* 1.0 create junling on
Certificate ----------- + */
Create proc [dbo]. [Performance_Trace_StopAll]

AS
Declare traceCursor cursor for select id from sys. traces where id <> 1
Open traceCursor
Declare @ curid int
Fetch next from traceCursor into @ curid
While (@ fetch_status = 0)
Begin

Exec sp_trace_setstatus @ curid, 0

Exec sp_trace_setstatus @ curid, 2

Fetch next from traceCursor into @ curid
End
Close traceCursor
Deallocate traceCursor

When to call it depends on your actual situation.

V. SQL Trace tracking principles

Here we mainly list the things we should pay attention to during tracking, or follow the following principles to reduce the impact of tracking on the production environment.

1. Do not use Profiler GUI tracking. If you use Profiler GUI, do not run it on the server where the SQL server is located;

2. Do not directly write the tracking data into the table. We can import the tracking information into the table when the system is not busy (unless you want to analyze the data immediately );

3. There will be a large number of I/O operations for tracking. Try to put the tracking file on a physical disk separately;

4. select only the events you are interested in. Selecting one event will lead to overhead (unless you do not select multiple events, there is no need to choose;

5. filter your tracking information. For example, if you are only interested in a database, you are only interested in certain columns. (Note that this only reduces the overhead of the I/O provider in the architecture diagram, think about why );

6. Events such as XXXXXXStarting often have little significance;

7. Check whether the SQL you are tracking uses scalar functions. Tracking these SQL statements will seriously affect performance. Each scalar function will trigger an event for each row (if the table is large, this is a terrible event );

8. You only need to specify the trail permission for the user to be tracked.

6. End

Today, I mainly discussed the tracking knowledge of SQLServer. There is still a lot of knowledge worth exploring, for example, event classification, meaning of each column in the SQL Trace directory view, how to import trc format files into the table for analysis and statistics, tracking security issues, and tracking performance optimization. In these aspects, you will have a better understanding of SQLServer.

This is the end of today's analysis. If your description is inappropriate, please note that. Common progress is the final principle.