0

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

closed as too broad by spaghettidba, mustaccio, SqlWorldWide, LowlyDBA, joanolo Jul 5 '17 at 18:30

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

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!

1

You can query sys.dm_exec_query_stats DM object. Top query is the query that had most waits. This is only for queries that are still cached.

select
    qs.total_elapsed_time / 1000000. as ElapsedSec
    , qs.total_worker_time / 1000000. as WorkerSec
    , st.text as Query
    , *
from sys.dm_exec_query_stats qs
    outer apply sys.dm_exec_sql_text(qs.sql_handle) st
order by qs.total_elapsed_time - qs.total_worker_time desc
  • total_elapsed_time=Total elapsed time, reported in microseconds (but only accurate to milliseconds), for completed executions of this plan. total_worker_time= Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled. You will need to subtract these 2 and divide by execution_count to find average high wait time. – SqlWorldWide Jul 5 '17 at 12:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.