When query plans go wrong Simon Sabin • Independent SQL Server Consultant and Trainer • Database design and development, Business Intelligence, Performance tuning.
Download ReportTranscript When query plans go wrong Simon Sabin • Independent SQL Server Consultant and Trainer • Database design and development, Business Intelligence, Performance tuning.
When query plans go wrong 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 Trace Flags Supportable WITH RECOMPILE Skewed Data OPTIMZE FOR Best Query Update statistics Compilation Multiple Code paths Out of date statistics Overview 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