High Average Wait Time

Question

High Average Wait Time

VastSQL

SSC-Dedicated

Points: 36124
More actions
February 13, 2016 at 10:40 pm

#304668

Hi Experts,
We are getting multiple SCOM alert
The Average Wait Time of SQL instance "INT1" on computer "ROCPRDSQL01" is too high.
How can i find and eliminate this?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Is there a Wait Type listed? What's the full message?

A "waiting" thread is simply one that isn't doing anything. For example, the lazywriter thread is usually waiting for something to do. That's not a problem. However, if threads are waiting because they are blocked on a resource, that's a problem.

The 'Wait Type' is the reason the Wait Time is high - blocking, flooding I/O, overloading the network, low memory, and so on. It's usually a code, like PAGEIOLATCH_SH or LCK_M_IX.

-Eddie

Eddie Wuerch
MCM: SQL

VastSQL SSC-Dedicated Points: 36124 More actions · Answer 2

Eddie Wuerch (2/14/2016)
Is there a Wait Type listed? What's the full message?
A "waiting" thread is simply one that isn't doing anything. For example, the lazywriter thread is usually waiting for something to do. That's not a problem. However, if threads are waiting because they are blocked on a resource, that's a problem.
The 'Wait Type' is the reason the Wait Time is high - blocking, flooding I/O, overloading the network, low memory, and so on. It's usually a code, like PAGEIOLATCH_SH or LCK_M_IX.
-Eddie

Thanks Eddie.

Unfortunately thats the only message i got from SCOM. How can i dig more on that?

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

VastSQL (2/14/2016)
Unfortunately thats the only message i got from SCOM. How can i dig more on that?

That's a meaningless alert, in my opinion. Unless it has some description documented elsewhere, it should be disabled. You may have some performance issue with the instance mentioned, so you should peek at current activity to see if things are running normally or not.

-Eddie

Eddie Wuerch
MCM: SQL

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I agree with Eddie (big surprise - Hi Eddie!!). 🙂

Waits are cumulative in SQL Server, so they are pretty useless just looking at them in isolation. What you need to do it a differential wait stats analysis (and same for file IO stalls too). Take a snapshot, wait for a period (3 minutes, 1 hour, whatever), then take another snapshot and join the two together and divide by time to get waits per millisecond for each of the different types of waits. THAT gives you actionable information you can start pursuing.

Note that it won't tell you which queries caused slowness. You can use sp_whoisactive for real-time analysis of what is going on with SQL Server. And I ALWAYS do aggregate profiler analyses at clients to find queries that need tuning.

ALL of the above form a core part of my performance reviews and health checks for clients, both new and existing.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

VastSQL SSC-Dedicated Points: 36124 More actions · Answer 5

TheSQLGuru (2/14/2016)
I agree with Eddie (big surprise - Hi Eddie!!). 🙂
Waits are cumulative in SQL Server, so they are pretty useless just looking at them in isolation. What you need to do it a differential wait stats analysis (and same for file IO stalls too). Take a snapshot, wait for a period (3 minutes, 1 hour, whatever), then take another snapshot and join the two together and divide by time to get waits per millisecond for each of the different types of waits. THAT gives you actionable information you can start pursuing.
Note that it won't tell you which queries caused slowness. You can use sp_whoisactive for real-time analysis of what is going on with SQL Server. And I ALWAYS do aggregate profiler analyses at clients to find queries that need tuning.
ALL of the above form a core part of my performance reviews and health checks for clients, both new and existing.

Thanks alot Kevin & Eddie

VastSQL SSC-Dedicated Points: 36124 More actions · Answer 6

Alert Summary:

Average Wait Time monitor for SQL 2014 DB

The average amount of wait time (milliseconds) for each lock request that resulted in a wait. Based on the DB engine counter, has identical values for all databases on a SQL instance.

Causes:

Lock requests occurred

Resolutions:

Use system DMVs such as sys.dm_exec_requests or SQL Server Management Studio Activity Monitor to identify processes which are blocking other processes.