SQL Drip 13-collects SQL Server thread wait information

Source: SQL Drip 13-collects SQL Server thread wait information

To know that thread wait time is an important reason for SQL Server efficiency, this essay will learn how to collect information such as thread wait time, type, etc. in SQL Server, which is the basis for database optimization.

　　

Sys.dm_os_wait_stats

This is a system view, which stores all the waiting information that the thread encounters, the following table is the specific column

Column Name	Data type	Description
Wait_type	Nvarchar (60)	Wait type name
Waiting_tasks_count	Bigint	Wait-type number of waits. The counter increases every time a wait is started.
Wait_time_ms	Bigint	The total wait time for the wait type.
Max_wait_time_ms	Bigint	The maximum wait time for the wait type.
Signal_wait_time_ms	Bigint	The time difference between waiting threads from receiving a signal notification to starting to run.

Note that the information for this view is automatically cleared every time the SQL Server is closed, and the next time you open SQL Server, the statistics will start again.

　　

New Thread waiting Information table

If you want continuous information, collect information for a fixed time interval such as one hours, so that you can analyze the waiting time allocated by the system and identify the peak time period. Here we collect this information in a data table to save and analyze. Create a new table using the following statement:

 UseAdventureWorks

CREATE TABLEdbo. Waitstats

(

DTDATETIME  not NULL DEFAULT (Current_timestamp),

Wait_typeNVARCHAR( -)  not NULL,

Waiting_tasks_countBIGINT  not NULL,

Wait_time_msBIGINT  not NULL,

Max_wait_time_msBIGINT  not NULL,

Signal_wait_time_msBIGINT  not NULL

);

CREATE UNIQUE CLUSTERED INDEXIdx_dt_type ondbo. Waitstats (DT, wait_type);

CREATE INDEXIdx_type_dt ondbo. Waitstats (wait_type, DT);

　　

New Job Populating Data

The best way to gather information is to use a job to regularly populate the data with an INSERT statement, as described in the steps below

2. Click SQL Server Agent in the database, expand, right-click the Jobs folder, right-click New. 1
   
   
   Figure 1
4. In the new job interface, complete the job name in the General tab, description, 2
   
   Figure 2
6. Click the Setps tab and click the New button to set the step Name,type,database,command properties such as 3 in the new step interface. These attributes know what to do at a glance, and they don't explain it all. What's more, it's a built job, if you do it yourself. The dialog box may display a different caption. The
   
   
   Figure 3
   Command property is the set of SQL statements or stored procedures to execute, and so on, which is set in SQL Script:
   

    insert     into   (Wait_type, Waiting_tasks_count, Wait_time_ms, 
    Max_wait_time_ms, Signal_wait_time _ms) 
     select   
    Wait_type, Waiting_ Tasks_count, Wait_time_ms, 
    Max_wait_time_ms, Signal_wait_time_ms 
     from   Sys.dm_os_wait_stats  

8. Click the Shedules tab to set the job execution plan, which is set to execute every 5 minutes every day 4
   
   Figure 4

The above steps contain the main settings for creating a new job, the other details are not included, and the specific problem is analyzed.

Collect waiting information data

After some time in the table waitstats there will be some data, every 5 minutes will be written in this table some data, the data will continue to increase. Here, in order to get a change in the thread waiting time between adjacent intervals, the connection condition is the same as the wait type, the current line number is equal to the previous line number plus 1, then you can use the last wait time minus this time to get the change value, the following use a function to implement this logic:

IF object_id('dbo. Intervalwaits', 'IF')  is  not NULL
DROP FUNCTIONdbo. Intervalwaits;
GO
CREATE FUNCTIONdbo. Intervalwaits
(@fromdt  as DATETIME, @todt  as DATETIME)
RETURNS TABLE
 as
RETURN
 withWaits as
(
SELECTDT, Wait_type, Wait_time_ms,
Row_number () Over(PARTITION byWait_type
ORDER  byDT) asRN
 fromdbo. Waitstats
)
SELECTPrv.wait_type, Prv.dt asStart_time,
CAST((Cur.wait_time_ms-Prv.wait_time_ms)
/  +.  asNUMERIC ( A, 2))  asinterval_wait_s
 fromWaits asCur
JOINWaits asPRV
 onCur.wait_type=Prv.wait_type
 andCur.rn=Prv.rn+ 1
 andPrv.dt>= @fromdt
 andPrv.dt< DATEADD( Day, 1, @todt)
GO

This function takes two parameters, starts to count the time, ends the statistical time, returns the time to wait for the change, and sorts by type. Call this function as follows:

SELECT Wait_type, Start_time, interval_wait_s

FROM dbo. Intervalwaits (' 20110509 ', ' 20110510 ') as F

ORDER by SUM (interval_wait_s) through (PARTITION by Wait_type) Desc,wait_type,start_time;

But we can't call this function every time, so we can put this query in a view, and the external only needs to use the view to query the data:

IF object_id('dbo. Intervalwaitssample', 'V')  is  not NULL
DROP VIEWdbo. Intervalwaitssample;
GO
CREATE VIEWDbo. Intervalwaitssample
 as
SELECTWait_type, Start_time, interval_wait_s
 fromdbo. Intervalwaits ('20090212', '20090215')  asF;
GO

The data we get from the view is the data we want to get. But these are not obvious, first write here, the next essay I will be in the execl to make this data into a histogram or line diagram, the horizontal axis is the time, vertical axes is the waiting interval time. This makes it more intuitive to see the time when SQL Server threads wait the longest, that is, the busiest time.

SQL Drip 13-collects SQL Server thread wait information