130 likes | 223 Views
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!.
E N D
SQL Server Query Tuning Best Practices, Part 5 of 6 Aaron BertrandSQL Sentry, Senior Consultant@AaronBertrand Kevin KlineSQL Sentry, Dir of Engineering Services@KEKline
New eBOOKAvailable! Check http://SQLSentry.TV for links to the video, slides, and demo code starting August 1st.
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.
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
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
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
sp_executesql vs. EXEC(…) • Can promote better plan re-use • Encourages strongly typed parameters instead of building up a massive string • DEMO
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
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.
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
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
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.
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