- Analyze the application and understand who are the most massive business processes, and analyze their performance. High odds that the problem lies there.
- Detect open connections using the Activity Monitor
- Check objects execution time using the SQL Server reports (of course assuming you are lucky enough and all SQL Server access is done using stored procedures).
- Implement profiling and check the queries are most frequent and most time consuming.
- Detect dead locks (a good sign for that this is the case is low CPU utilization).
- Detect slowdown source by using the following query (thanks to Henk van der Valk):
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
-- DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR)
-- show the sql waitstatistics:
, SUM(waiting_tasks_count) AS waiting_tasks_count
, SUM(wait_time_ms) AS wait_time_ms
, SUM(max_wait_time_ms) AS max_wait_time_ms
, SUM(signal_wait_time_ms) AS signal_wait_time_ms
, SUM(wait_time_ms/NULLIF(waiting_tasks_count,0)) as 'Avg_wait_time_ms per waittype req.'
WHERE wait_type NOT IN
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT LIKE 'PREEMPT%'
AND wait_type NOT LIKE 'SLEEP%'
AND wait_type NOT LIKE '%REQ%DEAD%'
AND waiting_tasks_count > 0
GROUP BY wait_type
ORDER BY wait_time_ms DESC
After doing these initial steps, you finally gathhered enough data to solve the issue...
Moshe Kaplan. RockeTier. The Performance Experts.
moshe at rocketier.com