1 / 18

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!. We’re giving away

omana
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!

  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 • Introducing Your Speakers • A Query Tuning Methodology – Kevin • Measuring Query Performance – Kevin • Query Tuning Patterns & Anti-Patterns – Aaron • Follow Up • Prizes!

  5. Meat-Head-Ology 

  6. 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.os_wait_stats • Yeah, ok. But which query DMVs?

  7. Periodic Table of SQL Server Dynamic Management Objects

  8. dm_os_wait_stats • “The Waiting is the Hardest Part”. • Bottlenecks. • Caveats for Relying on Wait Stats. • Querying methodology around “The” Wait Stats DMV.

  9. dm_exec_query_stats • Query Performance Information • System Resource Consumption: • CPU • Memory • IO

  10. dm_db_index_usage_stats • Reads by scans, seeks, lookups for both system and user activity. • Writes for both system and user activity. • Returns results for all databases, indexes so refine with predicates.

  11. dm_io_virtual_file_stats • IO activity breakdown for each SQL data and log file on the instance. • Provides file size information too. • It’s a function (DMF) so you must pass those parameters in!

  12. dm_exec_requests DEMO! • Active requests being serviced. • What users are doing on your instance. • Usually requires additional info from dm_exec_sessions and the SQLOS DMOs.

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

  14. Patterns and Anti-Patterns • 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 TEASE!

  15. Demos: Default Cursors • Cursors are usually unnecessary, but when they are, use the right options • The defaults are heavy-handed and guaranteed to be slow • Blog post: http://bit.ly/AB-cursors

  16. Demos: Correlated Subqueries • Coercing SQL Server to evaluate multiple times • Think about converting these to joins • Gives the optimizer a fighting chance

  17. Demos: NOT IN • Dangerous if source column is NULLable • LEFT OUTER JOIN is not always a good alternative • NOT EXISTS and EXCEPT are better (but can behave differently) • Blog post: http://bit.ly/AB-NOTIN

  18. Follow Up • Engage with our community: SQL Sentry on Facebook, SQLSentry.Net, SQLPerformance.com. • Share your tough SQL Server problems with us: http://answers.sqlperformance.net • Download SQL Sentry Plan Explorer for free: http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp • Check out our other award winning tools: http://www.sqlsentry.net/download

More Related