Identify Queries causing High Average Wait time (SQL Server 2008) Recently we started to receive many alerts from SCOM : Alert: SQL 2008 DB Average Wait Time is too high.

When reviewed the graph for this counter, we found that the alert is always ranging between 1,000 (ms)  and  2,500  (ms) .

is there a specific way\query that can identify which queries are causing this locking which lead to the high wait time ?

Thanks
Osama Waly
You can probably find simpler queries to give you that information, but if you want something more robust, I'd suggest sp_BlitzCache. Full disclosure, I'm one of the authors. It's totally free, open source, and you can get it here.

How does it help you?

You can use the @SortOrder parameter to look for queries either with the most total duration accumulated in your plan cache:

EXEC sp_BlitzCache @SortOrder = 'duration'


Or you can look by long average duration:

EXEC sp_BlitzCache @SortOrder = 'avg duration'


That will average our duration over executions to find the longest running by average. 

You'll get back a ton of information about each query, including the query plan. If you have any questions about the results, you can ask them here or in the GitHub repo.

Hope this helps!