SQL Server Query Tuning Best Practices, Part 5 of 6

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