1 / 18

When query plans go wrong

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: Simon@onarc.com Blog: http://Sqlblogcasts.com/blogs/simons

frieda
Download Presentation

When query plans go wrong

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. When query plans go wrong

  2. SQL

  3. Simon Sabin • Independent SQL Server Consultant and Trainer • Database design and development, Business Intelligence, Performance tuning and troubleshooting • SQL Server since 6.5 • Email: Simon@onarc.com • Blog: http://Sqlblogcasts.com/blogs/simons • Twitter: simon_sabin

  4. Car crash

  5. Works on my computer

  6. Parameter Sniffing @

  7. SQL tries to be clever

  8. A bit like

  9. Statistics

  10. So what are the root causes

  11. Out of date statistics Best Before: 1/4/1999

  12. Skewed data

  13. Multi purpose queries

  14. 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

  15. Update Statistics • Update Statistics • Can be a performance hit • Trace flag 2388, 2389 and 2390

  16. Overview

  17. 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

  18. Q&A • Now - Just ask • Afterwards – I’ll be around • Much Later • Simon@SQLKnowHow.com • @simon_sabin • http://sqlblogcasts.com/blogs/simonsabin Please fill in feedback forms

More Related