SQL bit by bit collection SQL Server thread wait Information

You need to know that the thread wait time is an important factor restricting the efficiency of SQL Server. This article will learn how to collect information such as the thread wait time and type in SQL Server, this information is the basis for database optimization.

Sys. dm_ OS _wait_stats

This is a system view that stores all the waiting information encountered by the thread. The specific columns are shown in the table below.

Column name	Data Type	Description
Wait_type	Nvarchar (60)	Wait type name
Waiting_tasks_count	Bigint	Number of waiting types. This counter increases every time a wait is started.
Wait_time_ms	Bigint	The total waiting time of this type.
Max_wait_time_ms	Bigint	The maximum waiting time of this type.
Signal_wait_time_ms	Bigint	The time difference between the waiting thread and the start of running.

Note that the view information is automatically cleared every time the SQL Server is disabled, and the statistics will be resumed the next time the SQL Server is opened.

Create thread wait info table

If you want to collect continuous information, such as once an hour, at a fixed interval, you can analyze the wait time allocated by the system and identify the busy time periods. This information is collected to a data table and saved for analysis. Use the following statement to create a table:

 
 

  
  
use AdventureWorks  
  
  
CREATE TABLE dbo.WaitStats  
  
  
(  
  
  
dt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),  
  
  
wait_type NVARCHAR(60) NOT NULL,  
  
  
waiting_tasks_count BIGINT NOT NULL,  
  
  
wait_time_ms BIGINT NOT NULL,  
  
  
max_wait_time_ms BIGINT NOT NULL,  
  
  
signal_wait_time_ms BIGINT NOT NULL 
  
  
);  
  
  
CREATE UNIQUE CLUSTERED INDEX idx_dt_type ON dbo.WaitStats(dt, wait_type);  
  
  
CREATE INDEX idx_type_dt ON dbo.WaitStats(wait_type, dt); 
 
 

Create jobFill data

To collect information, it is best to use a job to periodically execute the insert statement to fill in data. The following describes the steps

Click SQL Server Agent in the database, expand, right-click the jobs folder, and right-click New. 1

On the create job page, enter the job name, description, and 2 in the General tab.

Click the Setps tag and click the new button. On the new step page, set properties such as Step Name, Type, DataBase, and Command, as shown in figure 3. These attributes will know what to do at a glance, and they will not be explained in detail. We also need to talk about the created job. If you do it yourself, the title displayed in the dialog box may be different.

The Command attribute is used to set the SQL statement or stored procedure to be executed. The following SQL Script is set here:

 
 

  
  
INSERT INTO Performance.dbo.WaitStats  
  
  
(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 
 
 

Click the Shedules tab to set the job execution plan. Here, the task is executed every 5 minutes every day.

The above steps contain the main settings for creating a job, and other details are not included. If you encounter any specific problems, analyze them in detail.

Collect waiting information data

After a period of time, some data will be generated in the table WaitStats, and some data will be written into the table every five minutes, which will continue to increase. Here, we need to use a self-connection to obtain the change of thread wait time between adjacent intervals. The connection condition is that the waiting type is the same, and the current row number is equal to the previous row number plus 1, then you can get the variable value by using the last wait time minus the wait time. The following uses a function to implement this logic:

 
 

  
  
IF OBJECT_ID('dbo.IntervalWaits', 'IF') IS NOT NULL 
  
  
DROP FUNCTION dbo.IntervalWaits;  
  
  
GO  
  
  
CREATE FUNCTION dbo.IntervalWaits  
  
  
(@fromdt AS DATETIME, @todt AS DATETIME)  
  
  
RETURNS TABLE 
  
  
AS 
  
  
RETURN 
  
  
WITH Waits AS 
  
  
(  
  
  
SELECT dt, wait_type, wait_time_ms,  
  
  
ROW_NUMBER() OVER(PARTITION BY wait_type  
  
  
ORDER BY dt) AS rn  
  
  
FROM dbo.WaitStats  
  
  
)  
  
  
SELECT Prv.wait_type, Prv.dt AS start_time,  
  
  
CAST((Cur.wait_time_ms - Prv.wait_time_ms)  
  
  
/ 1000. AS NUMERIC(12, 2)) AS interval_wait_s  
  
  
FROM Waits AS Cur  
  
  
JOIN Waits AS Prv  
  
  
ON Cur.wait_type = Prv.wait_type  
  
  
AND Cur.rn = Prv.rn + 1  
  
  
AND Prv.dt >= @fromdt  
  
  
AND Prv.dt < DATE 
  
  
ADD(day, 1, @todt)  
  
  
GO 
 
 

This function takes two parameters: Start Time and End Time, and return the waiting time for change, sorted by type. Call this function as follows:

SELECT wait_type, start_time, interval_wait_s

FROM dbo. IntervalWaits ('20140901', '20160901') AS F

Order by sum (interval_wait_s) OVER (partition by wait_type) DESC, wait_type, start_time;

However, we cannot call this function every time, so you can put this query in a view. You only need to use the view to query data externally:

 
 

  
  
IF OBJECT_ID('dbo.IntervalWaitsSample', 'V') IS NOT NULL 
  
  
DROP VIEW dbo.IntervalWaitsSample;  
  
  
GO  
  
  
CREATE VIEW dbo.IntervalWaitsSample  
  
  
AS 
  
  
SELECT wait_type, start_time, interval_wait_s  
  
  
FROM dbo.IntervalWaits('20090212', '20090215')   
  
  
AS F;  
  
  
GO 
 
 

The data obtained from the view is the data we want. But this is not obvious. Write it here first. Next, I will make the data into a histogram or line chart in EXECL. the horizontal axis is time, and the vertical axis is the waiting interval. In this way, the SQL Server thread waits for the longest time, that is, the busiest time.