SQL SERVER QUERY TUNING BEST PRACTICES Aaron Bertrand

SQL SERVER QUERY TUNING BEST PRACTICES Aaron Bertrand SQL Sentry, Senior Consultant @Aaron. Bertrand Kevin Kline SQL Sentry, Dir of Engineering Services @KEKline

NEW e. BOOK AVAILABLE! 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 o o UNION versus UNION ALL – Aaron Index Structures – Kevin Compound Indexes – Aaron Covering Indexes – Kevin • Follow Up

TEST ENVIRONMENT • Your query test harness should include some stuff. • Code to clear the caches: * o DBCC [Free. Proc. Cache | Free. System. Cache | Flush. Proc. In. DB(<dbid>) ] o DBCC Drop. Clean. Buffers • Code to set measurements: o SET STATISTICS TIME o SET STATISTICS IO o SET SHOWPLAN [TEXT | XML] • Code for Dynamic Management Views (DMV) checks. o System info – sys. dm_os_performance_counters and sys. dm_os_wait_stats

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

PATTERNS 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. Bad, Naughty Default Cursors Correlated Subqueries WHERE IN versus WHERE 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 o Can require worktable, additional sort operators o Can have effect on performance and tempdb o 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 • 8 K pages • Leaf pages ARE the data. • Non-leaf pages are pointers. Root Page Intermediate Pages Leaf Pages Level 2 Level 1 Level 0

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: o WHERE a = @a AND b = @b The following WHERE clauses may access only part of NDX_FOO: o WHERE a = @a AND d = @d o WHERE a = @a AND c = @c AND b = @b The following WHERE clauses will usually ignore NDX_FOO: o 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 1. Engage with our community: SQL Sentry on Facebook, SQLSentry. Net, SQLPerformance. com 2. Share your tough query problems with us: http: //answers. sqlperformance. com 3. Download SQL Sentry Plan Explorer for free: http: //www. sqlsentry. net/plan-explorer/ 4. Check out our other award winning tools: http: //www. sqlsentry. net/download