when query plans go wrong
DESCRIPTION
When query plans go wrong. SQL. Simon Sabin. Independent SQL Server Consultant and Trainer Database design and development, Business Intelligence, Performance tuning and troubleshooting SQL Server since 6.5 Email: [email protected] Blog: http://Sqlblogcasts.com/blogs/simons - PowerPoint PPT PresentationTRANSCRIPT
When query plans go wrong
SQL
Simon Sabin
• Independent SQL Server Consultant and Trainer• Database design and development, Business
Intelligence, Performance tuning and troubleshooting
• SQL Server since 6.5• Email: [email protected]• Blog: http://Sqlblogcasts.com/blogs/simons• Twitter: simon_sabin
Car crash
Works on my computer
Parameter Sniffing
@
SQL tries to be clever
A bit like
Statistics
So what are the root causes
Out of date statistics
Best Before: 1/4/1999
Skewed data
Multi purpose queries
Solutions
• Selective code paths– Careful as SP is compiled as one batch
• WITH RECOMPILE– Compilation hit, plan cache bloat
• OPTIMIZE FOR– Results in a consistent plan
• PLAN guides– Results in a consistent plan
Update Statistics
• Update Statistics– Can be a performance hit
• Trace flag 2388, 2389 and 2390
OverviewOut of date statistics
Compilation
Best Query
Skewed Data
Supportable
Multiple Code paths Update statistics OPTIMZE FOR WITH RECOMPILE Trace Flags
Summary
• You will only know if you monitor• Baseline your system• Identify changes in read, writes and cpu– Not duration
• Consider the options for your situation
• You can win this battle
Q&A
• Now - Just ask• Afterwards – I’ll be around• Much Later– [email protected]– @simon_sabin– http://sqlblogcasts.com/blogs/simonsabin
Please fill in feedback forms