The Basics of SQL Server Performance

When you first hit a SQL Server that cannot server even another single hit you should start with following basic methods:

1. Analyze the application and understand who are the most massive business processes, and analyze their performance. High odds that the problem lies there.
2. Detect open connections using the Activity Monitor
3. 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).
4. Implement profiling and check the queries are most frequent and  most time consuming.
5. Detect dead locks (a good sign for that this is the case is low CPU utilization).
   
6. Detect slowdown source by using the following query  (thanks to Henk van der Valk):
   

-- Uncomment the reset or capture the wait stats before you start the batch to investigate:
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
-- DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR)

-- show the sql waitstatistics:
SELECT wait_type
, 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.'

FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
'SQLTRACE_BUFFER_FLUSH'
, 'LAZYWRITER_SLEEP'
, 'CHECKPOINT_QUEUE'
, 'BROKER_TO_FLUSH'
)
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
WITH ROLLUP
ORDER BY wait_time_ms DESC

After doing these initial steps, you finally gathhered enough data to solve the issue...

Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.
moshe at rocketier.com