SQL Server Query Tuning Best Practices

SQL Server Query Tuning Best Practices Aaron BertrandSQL Sentry, Senior Consultant@AaronBertrand Kevin KlineSQL Sentry, Dir of Engineering Services@KEKline

New eBOOKAvailable! Kindle promo code and links to the video, slides, and codes will be emailed to all attendees after the webcast.

We’re giving away 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 • Don’t forget the test harness – Kevin • Quick tips on Assessment – Kevin • Patterns & Anti-Patterns – Aaron • UNION versus UNION ALL – Aaron • Index Structures – Kevin • Compound Indexes – Aaron • Covering Indexes – Kevin • Follow Up

Test Environment • Your querytest harnessshould include some stuff. • Code to clear the caches: * • DBCC [FreeProcCache | FreeSystemCache | FlushProcInDB(<dbid>) ] • DBCC DropCleanBuffers • Code to set measurements: • SET STATISTICS TIME • SET STATISTICS IO • SET SHOWPLAN [TEXT | XML] • Code for Dynamic Management Views (DMV) checks. • System info – sys.dm_os_performance_counters and sys.dm_os_wait_stats

Assessing the Findings • Red Flags Query Operators: • Lookups • Scans • Spools • Parallelism Operations • Red Flags Elsewhere: • Dissimilar estimated versus actual row counts • High physical reads • Missing index/statistics alarms • Large sort operations • Implicit data type conversions • Using live demo, we’ll show you patterns to use and anti-patterns to beware.

Patterns and Anti-Patterns • Bad, Naughty Default Cursors • Correlated Subqueries • WHERE INversusWHERE EXISTS • UNION versus UNION ALL • WHERE {NOT IN | EXISTS} versus LEFT JOIN • Tuning for SELECT versus INSERT, UPDATE, and DELETE • Compound index columns • Covering indexes • The Transitive Property • Queries with IN (…) or OR • Queries with LIKE ‘%’ • Functions and calculations in WHERE or JOIN

UNION vs. UNION ALL • UNION ALL performs better than UNION because of an unpublicized side-effect • UNION eliminates duplicates by sorting • Can require worktable, additional sort operators • Can have effect on performance and tempdb • Can change semantics of query results DEMO

Bonus Tip: SET NOCOUNT ON • SET NOCOUNT ON suppresses all DONE_IN_PROC messages until enabled for the session • Can add up to very big savings when included in any procedural code such as procedures, functions (UDFs), triggers, and batches DEMO

Index Structure • 8K pages • Leaf pages ARE the data. • Non-leaf pages are pointers. Root Page Level 2 Intermediate Pages Level 1 Level 0 Leaf Pages

Compound Indexes • Compound indexes are most useful from the leftmost column to the rightmost column, in the order they appeared in the CREATE INDEX statement. Example: CREATE NONCLUSTERED INDEX ndx_foo ON foo(a, b, c, d) • The following WHERE clauses will access the NDX_FOO: • WHERE a = @a • WHERE a = @a AND b = @b • The following WHERE clauses may access only part of NDX_FOO: • WHERE a = @a AND d = @d • WHERE a = @a AND c = @c AND b = @b • The following WHERE clauses will usually ignore NDX_FOO: • WHERE b = @b AND c = @c DEMO

Covering Indexes • A covering index is one that answers a query entirely from its own intermediate pages, rather than going all the way down the tree to leaf pages. • All columns referenced in the query need to be in the index. • Don’t confuse a covering index with an included columns index DEMO

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

SQL Server Query Tuning Best Practices