Query SYS. dm_ OS _wait_stats for database wait Information

 

How do you start the narrowdown problem when the user tells you that the database is slow? SQL Server provides SYS. dm_ OS _wait_stats to help us view the CPU, memory, or IO wait status. SQL
The wait information is recorded in this view during server execution.

 

With the following statement, we can capture the accumulated wait information of SQL Server for a period of time, and sort the information to identify the resource bottleneck.

 

Let's take a look at the proportion of each wait:

 

With waits

(

Select

Wait_type,

Wait_time_milliseconds/1000.
As wait_time_s,

100. * wait_time_ms
/Sum (wait_time_ms)
Over ()
As PCT,

Row_number ()
Over (Order
By wait_time_ms DESC)
As Rn

From SYS. dm_ OS _wait_stats

Wherewait_type

Not in

('Clr _ semaphore ',
'Lazywriter _ sleep ',
'Resource _ queue ',

'Sleep _ task ',
'Sleep _ systemtask ',
'Sqltrace _ buffer_flush ',
'Waitfor ',

'Clr _ auto_event ',
'Clr _ manual_event ', 'broker _ task_stop ')

) -- Filter out additional irrelevant waits

Select w1.wait _ type,

Cast (w1.wait _ time_s
As decimal (12, 2 ))
As wait_time_s,

Cast (w1.pct
As decimal (12, 2 ))
As PCT,

Cast (sum (w2.pct)
As decimal (12, 2 ))
As running_pct

From waits as W1

Inner join waits
As W2 on w2.rn
<= W1.rn

Group by w1.rn,

W1.wait _ type,

W1.wait _ time_s,

W1.pct

Having sum (w2.pct)
-W1.pct
<95; -- percentage threshold;

 

Because this view is cumulative, we need to collect statistical information on a regular basis and obtain the waiting status for this period of time through the comparison of the previous and second data.

 

-- Create Table topersist wait stats information:

 

Create Table colwaitinfo

(

[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,

[Capture_time] [datetime] Not
Null,

[Increment_id] [int] notnull

);

 

Alter table colwaitinfo add default
(Getdate ())
For [capture_time];

 

-- Insert waitstats info in a datestamped format for later querying:

Declare @ DT datetime
;

Set @ dt =
Getdate ();

Declare @ increment_id
Int;

 

Select @ increment_id =
Max (increment_id)
+ 1 from colwaitinfo

Select @ increment_id =
Isnull (@ increment_id, 1)

 

Insert into colwaitinfo

([Wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],

[Signal_wait_time_ms], [capture_time], [increment_id])

Select [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],

[Signal_wait_time_ms], @ DT, @ increment_id

From SYS. dm_ OS _wait_stats;

 

 

The following statement is used to query the cumulative information during the interval:

Declare @ max_increment_id
Int

 

------------------------------------------------------------------

-- Determinemost-Recent increment_id

------------------------------------------------------------------

Select @ max_increment_id
= Max (increment_id)

From colwaitinfo

------------------------------------------------------------------

-- Present waitsresults for period

------------------------------------------------------------------

Select dows1.wait _ type,

(Dows1.waiting _ tasks_count
-Dows2.waiting _ tasks_count)
As [waiting_tasks_count],

(Dows1.wait _ time_ms
-Dows2.wait _ time_ms)
As [wait_time_ms],

Dows1.max _ wait_time_ms,

(Dows1.signal _ wait_time_ms
-Dows2.signal _ wait_time_ms)
As [signal_wait_time_ms],

Datediff (MS, dows2.capture _ time, dows1.capture _ time)
As [elapsed_time_ms],

Dows1.capture _ timeas [last_time_stamp], dows2.capture _ time
As [previus_time_stamp]

From

(

Select wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,

Signal_wait_time_ms, capture_time, increment_id

Fromcolwaitinfo

Whereincrement_id = @ max_increment_id

) As dows1

Inner join

(

Select wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,

Signal_wait_time_ms, capture_time, increment_id

Fromcolwaitinfo

Whereincrement_id = (@ max_increment_id
-1)

) As dows2
On dows1.wait _ type
= Dows2.wait _ type

Where (dows1.wait _ time_ms
-Dows2.wait _ time_ms)
> 0

And dows1.wait _ type
Not in

('Clr _ semaphore ',
'Lazywriter _ sleep ',
'Resource _ queue ',

'Sleep _ task ',
'Sleep _ systemtask ',
'Sqltrace _ buffer_flush ',
'Waitfor ',

'Clr _ auto_event ',
'Clr _ manual_event ', 'broker _ task_stop ')
-- Filter out additional irrelevant waits

Order by (dows1.wait _ time_ms
-Dows2.wait _ time_ms)
DESC;