SQL Server Query Tuning Best Practices

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

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

3. 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.

4. Agenda • Introductions • Don’t forget the test harness – Kevin • Quick tips on assessment – Kevin • Patterns & Anti-Patterns – Aaron • Queries optimized for SELECT statements but not DML statements • Using functions in WHERE and JOIN clauses • Queries with wildcard searches • Prizes! • Follow Up

5. Test Harness • 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

6. 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 statistics alarms • Large sort operations • Implicit data type conversions • Using live demo, we’ll show you patterns to use and anti-patterns to beware.

7. 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 SELECTbut 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

8. OPTIMIZING FOR SELECT vs. DML • Big differences between a SELECT and a DML statement that effects the same rows. • Shouldn’t blindly create every index the Tuning Advisor or execution plan tells you to. • Blog post - http://bit.ly/AB-BlindIndex

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

10. Writes & Index Structure • Each change to the leaf pages requires all index structures be updated. Root Page Level 2 Intermediate Pages Level 1 Actual place- ment Leaf Pages Level 0 DML Page Split

11. What’s the Usage of an index? • Easy to answer with two DMVs! • sys.dm_db_index_physical_stats • sys.dm_db_index_usage_stats

12. Functions in the WHERE and JOIN Clauses • Functions / calculations on columns of a WHERE or JOIN clause can make SQL Server ignore indexes: • WHERE qty * 12 > 10000 • Instead, move function / calculation to SARG: • WHERE qty > 10000/12 DEMO

13. WILDCARD SEARCHES USING ‘%’ • Queries that use the LIKE clause have two simple rules: • LIKE can use indexes if the pattern starts with a character string, such as WHERE lname LIKE ‘w%’ • LIKE cannot use an index if the pattern starts with a leading wildcard, such as WHERE lname LIKE ‘%alton’

14. Summary • Queries optimized for SELECT statements but not DML statements • Know your whole workload before you create your indexes • Using functions in WHERE and JOIN clauses • Negate the use of indexes • Queries with wildcard searches • Negate the use of indexes • Make an index less effective

15. 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