Transcript Slide 1

Danette Dineen Riviello
Magellan Health
March 17, 2015
1




What triggers an Investigation?
Emergent Performance Issues
Chronic Performance Problems
Solving the Case
2
Increase in User Complaints
Application Timeouts
Long-running queries
 Open Transactions
 Chain of blocking



3

Look at all running processes
sp_who2 active

Look for one login or one database:
SELECT spid, [status],
loginame [Login],hostname,
blocked BlkBy,
Db_name(dbid) DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses
where [status] not in ('sleeping')
and loginame like '%login%'
And Db_name(dbid) = 'DBName'
ORDER BY dbname
4
Look for the lead of a blocking chain
SELECT spid,sp.STATUS
,loginame
= SUBSTRING(loginame, 1, 12)
,hostname
= SUBSTRING(hostname, 1, 12)
,blk
= CONVERT(CHAR(3), blocked)
,open_tran ,dbname
= SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd,waittype,program_name
,waittime ,last_batch
,SQLStatement
=
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
5

Look at object locks
SELECT resource_type,
object_name(resource_associated_entity_id),
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
6

Look for open transactions
SELECT spid, [status],
loginame [Login],hostname,
blocked BlkBy,
Db_name(dbid) DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses
WHERE open_tran>0
ORDER BY spid
7

Find the line of code that is running:
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id = @SPID
-- where @SPID is the one in Question
ORDER BY 1, 2
8


What has changed?
Look at default system trace:
9

Look for recent changes

Look at the Log directory for prior files
10

To find most expensive stored procedures:
SELECT TOP 100 d.object_id, d.database_id,
OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
11

Most expensive Stored procedure runs
12





Less impact than an interactive trace
Can load trace data on an alternate server
Can load trace data at a different time of day
Capture specific parameters passed
Compare same time of day on different days
13
14
15

Load the trace file to another server
select * into dbo.tmp_loadtraceFile_ServerA_20150201_8
FROM ::fn_trace_gettable('d:\trace_20150201_8.trc', 1)

Query trace file to find commands that are
calling the suspected stored procedure
select top 25 textdata, loginname, spid, duration,
starttime, endtime, reads, cpu
From dbo.tmp_loadtraceFile_ServerA_20150201_8
Where textdata like '%offendingproc%'
Order by duration desc
16
17



Use “Display Estimated Execution Plan”
Use “Include Actual Execution plan”
Query to get query plans from DMV:
select top 25 st.text, qp.query_plan, qs.plan_handle
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
18





Look at the query plan
Missing index or wrong index chosen?
Look at the parameters sent in
Check for other runs that perform better
Could it be a parameter sniffing issue?
19
Query plan developed based on the first values
passed to the procedure
 Pros:
 Saves time: only one compile needed
 Cons:
 Wrong query plan chosen

20



Look at Query plans
If one procedure performs well in one case
and not others
Do the index choices make sense?
21
22





Do Nothing
Force Recompile each run (expensive!)
Query Hints (OPTIMIZE FOR)
Break down stored procedures to handle
specific cases
Education users on best parameter choices
23

Check for table scans caused by:
 Missing index
 Broad “where” clause



Check for improper join (many-to-many)
Check for too many tables in one join
Use of a function in a large query result set
24
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar,
mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND
mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS
create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle =
mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact *
(migs.user_seeks + migs.user_scans) DESC
25

Solution may change over time
 Tables grow
 Statistics out of date
 Parameter Sniffing


Some problems result from multiple issues
Do least disruptive changes first:
 Add an index
 Close open connections
26

Thank you for attending!

Further questions:
[email protected]
27