SQL Server Performance Troubleshooting - Wait Stats

Question

SQL Server Performance Troubleshooting - Wait Stats

SQLSACT

SSC-Insane

Points: 21838
More actions
July 11, 2012 at 7:06 am

#259447

Hi All
A question regarding the following DMV:
sys.dm_os_wait_stats
Does this DMV indicate processes that are currently in a waiting state or processes that have had to wait for resources?
What information regarding performance issues can I pull from this?
Please assist is determining a performance troubleshooting methodology using this and other related DMV's.
I haven't done much in the way of Troubleshooting Performance issues in SQL Server so any assistance will be helpful.
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

The single best source for understanding how to deal with wait states is this excellent white paper from Microsoft. It should answer most of your questions in detail.

In a nutshell, no, looking at the DMO doesn't get you active waits. It's the accumulation of wait statistics on the server. It will give you indications where your primary issues are. You'll still need to collect other metrics (outlined well in the white paper) to determine what is causing those waits.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Grant Fritchey (7/11/2012)
The single best source for understanding how to deal with wait states is this excellent white paper from Microsoft. It should answer most of your questions in detail.
In a nutshell, no, looking at the DMO doesn't get you active waits. It's the accumulation of wait statistics on the server. It will give you indications where your primary issues are. You'll still need to collect other metrics (outlined well in the white paper) to determine what is causing those waits.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SQLSACT (7/11/2012)
Hi All
A question regarding the following DMV:
sys.dm_os_wait_stats
Does this DMV indicate processes that are currently in a waiting state or processes that have had to wait for resources?

That is cumulative wait stats of all sessions since the DMV was cleared or SQL started last.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster (7/11/2012)
SQLSACT (7/11/2012)
Hi All
A question regarding the following DMV:
sys.dm_os_wait_stats
Does this DMV indicate processes that are currently in a waiting state or processes that have had to wait for resources?
That is cumulative wait stats of all sessions since the DMV was cleared or SQL started last.

Thanks

I'm very new to the performance side of SQL Server so please bear with me

Regarding the Wait Type column of the sys.dm_os_wait_stats DMV - Does that column represent the cause of the wait or the process that is waiting?

I'm looking through the documentation on BOL for that DMV and it seems that the LCK* wait types is the actual process that is doing the waiting and the PAGEIO* wait types is what caused the wait

I'm a bit puzzled by this

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It's the type of the wait in all cases.

When a process has to wait for a resource, it gets a matching wait type (waiting for locks gets a lock wait, waiting for a latch gets a latch wait, etc), those wait types are the 'Wait Type' column in that DMV.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

GilaMonster (7/11/2012)
It's the type of the wait in all cases.
When a process has to wait for a resource, it gets a matching wait type (waiting for locks gets a lock wait, waiting for a latch gets a latch wait, etc), those wait types are the 'Wait Type' column in that DMV.

Thanks

From BOL:

LCK_M_S:

Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

So this is saying that a process wanted to acquire a Shared Lock on an object but something else already had an exclusive lock on the same object?

So the wait_type column doesn't tell us what caused the wait, it's just pointing out which type of processes has done some waiting?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLSACT (7/12/2012)
So this is saying that a process wanted to acquire a Shared Lock on an object but something else already had an exclusive lock on the same object?

Not necessarily exclusive, just some incompatible lock.

So the wait_type column doesn't tell us what caused the wait, it's just pointing out which type of processes has done some waiting?

No, it has nothing to do with the type of the process, just what wait that process is incurring. That process (whatever it is), is waiting to acquire a

shared lock, hence it gets a LCK_M_S. If it was waiting to acquire an exclusive lock, it would get a LCK_M_X. If it was waiting to acquire a page latch, it would get one of the PAGELATCH wait types, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 8

GilaMonster (7/12/2012)
SQLSACT (7/12/2012)
So this is saying that a process wanted to acquire a Shared Lock on an object but something else already had an exclusive lock on the same object?
Not necessarily exclusive, just some incompatible lock.
So the wait_type column doesn't tell us what caused the wait, it's just pointing out which type of processes has done some waiting?
No, it has nothing to do with the type of the process, just what wait that process is incurring. That process (whatever it is), is waiting to acquire a
shared lock, hence it gets a LCK_M_S. If it was waiting to acquire an exclusive lock, it would get a LCK_M_X. If it was waiting to acquire a page latch, it would get one of the PAGELATCH wait types, etc.

Thanks - Much Clearer now

Is there anyway of telling which process did the actual waiting?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

From os_wait_stats, no, that's the aggregated totals since SQL started.

For process level, sys.dm_os_waiting_tasks or extended events

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

GilaMonster (7/12/2012)
From os_wait_stats, no, that's the aggregated totals since SQL started.
For process level, sys.dm_os_waiting_tasks or extended events

Thanks

Is sys.dm_os_waiting_tasks current tasks or also tasks that have waited?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Given it's name, what do you think? "waiting tasks"

Also, what does Books Online say?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 12

GilaMonster (7/12/2012)
Given it's name, what do you think? "waiting tasks"
Also, what does Books Online say?

Apologies

I realised the mootness of the question the moment I hit "Post Quoted Reply"

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

SQLSACT (7/12/2012)
GilaMonster (7/12/2012)
Given it's name, what do you think? "waiting tasks"
Also, what does Books Online say?
Apologies
I realised the mootness of the question the moment I hit "Post Quoted Reply"
Thanks

🙂

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass