1 / 13

SQL Server Query Tuning Best Practices, Part 5 of 6

SQL Server Query Tuning Best Practices, Part 5 of 6. Aaron Bertrand SQL Sentry, Senior Consultant @AaronBertrand. Kevin Kline SQL Sentry, Dir of Engineering Services @ KEKline. New eBOOK Available!.

zanthe
Download Presentation

SQL Server Query Tuning Best Practices, Part 5 of 6

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. SQL Server Query Tuning Best Practices, Part 5 of 6 Aaron BertrandSQL Sentry, Senior Consultant@AaronBertrand Kevin KlineSQL Sentry, Dir of Engineering Services@KEKline

  2. New eBOOKAvailable! Check http://SQLSentry.TV for links to the video, slides, and demo code starting August 1st.

  3. Your chance to win one of 3 Rookie Experience packages and 3 Ride Along packages from the Richard Petty Driving Experience at Charlotte Motor Speedway on October 18, 2013.

  4. Agenda • Introductions • Patterns & Anti-Patterns • The “Kitchen Sink” stored procedure • SP_ExecuteSQLvs EXEC(…) • Execution Plan Self-Destruct • To MERGE or not to MERGE • Prizes! • Follow Up

  5. SQL Patterns and Anti-Patterns • Bad, Naughty Default Cursors • Correlated Subqueries • WHERE INversusWHERE EXISTS • UNION versus UNION ALL • WHERE {NOT IN | EXISTS} versus LEFT JOIN • Queries optimized for SELECT but not DML statements • Compound index columns • Covering indexes • The Transitive Property • Queries with IN (…)or OR • Queries with wildcard searches • Using functions in WHERE or JOINclauses • Specifying the schema • SP_xyz Prefix • Unwanted recompiles • The “Kitchen Sink” stored procedure • SP_ExecuteSQLvs EXEC(…) • Execution Plan Self-Destruct • To MERGE or not to MERGE

  6. The "kitchen sink" procedure • Many optional parameters to satisfy a variety of search conditions: • Dynamic SQL is often the best route here • Especially if Optimize For Ad Hoc Workloads is enabled • Could also use RECOMPILE, but that means you pay compile cost every time • DEMO

  7. sp_executesql vs. EXEC(…) • Can promote better plan re-use • Encourages strongly typed parameters instead of building up a massive string • DEMO

  8. Execution Plan Self-Destruct • Execution plans are intended to be reused as much as makes good sense. • There are a variety of hidden or inconspicuous reasons that execution plans will self-destruct and never be reused: • Contradictory language settings • Contradictory collation settings, especially with Unicode • Contradictory SET options • Users with different default schemas not using schema prefix

  9. Contradictions • When SQL Server is faced with contradictory settings, it will usually opt to generate a new execution plan: • If language settings are different on client and server, even at the Windows-level settings. • If collation settings differ between servers, server and client, or between a Transact-SQL batch and the objects they are operating upon. Especially problematic with Unicode.

  10. SET Option Hierarchy • SET statement in code • OLEDB/ODBC connection string • ODBC • Control Panel • SQLConfigDatasource • OLEDB/ODBC option auto set • Database Level Settings (Alter Database) • Server Wide Settings (sp_configure) s= required for index views or computed columns p = required for distributed / linked queries S, D, A = sp_configure, sp_dboption, Alter Database

  11. To MERGE or not to MERGE? • Unnecessarily complex syntax • Can complicate multi-operation trigger logic • By default, does *NOT* promise concurrency protection or prevent race conditions • In the end it is not really any more efficient than separate statements • Many unresolved bugs • More details at http://bit.ly/AB-vs-MERGE

  12. Summary • The “Kitchen Sink” stored procedure is bad. Specialize your stored procedures • SP_ExecuteSQLvs EXEC(…) have different strengths and weaknesses. Know when to use each. • Execution Plans can Self-Destruct due to hidden or inconspicuous settings. • To MERGE or not to MERGE? It’s not as clear cut as you might think.

  13. Follow Up • Engage with our community: SQL Sentry on Facebook, SQLSentry.Net, SQLPerformance.com • Share your tough query problems with us: http://answers.sqlperformance.com • Download SQL Sentry Plan Explorer for free: http://www.sqlsentry.net/plan-explorer/ • Check out our other award winning tools: http://www.sqlsentry.net/download

More Related