Collecting Wait Stats

We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. Below are the wait stats by number of tasks waiting and also wait time per task.

We have users complaining of slow down in the system, yet the metrics for server seem fine in terms of Disk IO, memory and CPU. Does anyone know if the PREEMPTIVE waits are an issue?

Number of waiting tasks
SOS_SCHEDULER_YIELD
PAGELATCH_EX
PAGELATCH_SH
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT

Average wait per task
PAGEIOLATCH_SH
PREEMPTIVE_XE_GETTARGETSTATE

Update:

I ran a query from Paul Randal similar to what you posted and got the following:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
PREEMPTIVE_XE_GETTARGETSTATE    9704.81 9704.81 0.00    604647  44.60   0.0161  0.0161  0.0000

I know this doesn't line up very well, but basically this wait type has accounted for %44.60 of all wait types. Also as there has been no Signal Waits on this type then this indicates no CPU pressure but instead a wait on some other resource. Not sure how I deduce what that resource is however.

Also this is SQL 2012 SP1

Update2
AS requested here is results from your query. In regards extended events the only sessions running is the default system_health one and 2 SharePoint ones I have just noticed, they must have been put there by default. I may switch these off I wonder if these are causing an issue.

Its interesting that my PREEMPTIVE_XE_GETTARGETSTATE doesn't seem to be in this list.

```
wait_type wait_time_ms signal_wait_time_ms resource_wait_time_ms percent_total_waits percent_total_signal_waits percent_total_resource_waits
SP_SERVER_DIAGNOSTICS_SLEEP 300014 355508314 0 24.621089361251698 99.883069863550302 0.000000000000000
MSQL_XP 96782 0 4268999 0.295653861591811 0.000000000000000 0.295653861591811
ASYNC_IO_COMPLETION 56193

The preemptive_xe_* wait types are associated with Extended Events from what I understand and can find. Considering that and your first sentence:


We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time.

I would start looking at your monitoring tools as the culprit. However being that your data from Paul's script shows average wait is low I would not put to much concern into it right now.

Your main challenge here that I think you may be overlooking is your user's told you there was a slow down in the system/application. Most will always point to the database/server as the culprit. My general next question when someone tells me that is "what portion of the system is slow". If they tell me things like pages loading slowly, and they are not actually executing any ad-hoc reports or queries, I ask them to check with the server folks or application admins to ensure nothing else is messed up. Then while they are doing that I will go check the database server performance. However as you have shown if the XE wait type is the only one above 10% or so I don't think the issue is with the database server.

If you want to find the culprit of that wait type you can begin querying sys.dm_os_waitting_tasks, this will show you the session_id and wait that is occurring. You can also use sp_WhoIsActive to get similar information a bit more easily. I would bet you are going to find it to be sessions associated with your monitoring tool.

StackExchange Database Administrators Q#71965, answer score: 5