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!

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

Meat-Head-Ology 

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?

Periodic Table of SQL Server Dynamic Management Objects

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

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

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.

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!

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.

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.

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!

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

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

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

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

SQL Server Query Tuning Best Practices