performance tuning

JanisGriffin@confio.com, CONFIO webinar on EE, PASS presentation on PT.

SQL Server very slow:  very busy and improperly sized, or bad queries and not running properly

using RTA (response time analysis). focus on slow responding queries.  Use Wait types of define what is happening (7 wait time tables).  DVMs are used but don’t do periodic determinations (cumulative).  Basic monitoring query.

Make sure you are working on the right query.

INSERT INTO SessionWaitInfo
SELECT r.session_id, r.sql_handle, r.statement_start_offset,
r.statement_end_offset, r.plan_handle, r.database_id,
r.blocking_session_id, r.wait_type, r.query_hash,
s.host_name, s.program_name, s.host_process_id,
s.login_name, CURRENT_TIMESTAMP cdt
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
WHERE r.status <> 'background'
AND r.command <> 'AWAITING COMMAND'
AND s.session_id > 50
AND s.session_id <> @@SPID

get baseline stats and wait_type info.  review execution plan, check table information and indexes

monitor before and after so we know it made a difference

Advertisements