1 / 15

SQL Server Query Tuning Best Practices

SQL Server Query Tuning Best Practices. Aaron Bertrand SQL Sentry, Senior Consultant @AaronBertrand. Kevin Kline SQL Sentry, Dir of Engineering Services @ KEKline. New eBOOK Available!.

anitra
Download Presentation

SQL Server Query Tuning Best Practices

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related