1 / 12

SQL Server Query Tuning Best Practices, Part 6 of 6

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

duncan
Download Presentation

SQL Server Query Tuning Best Practices, Part 6 of 6

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, Part 6 of 6 Aaron BertrandSQL Sentry, Senior Consultant@AaronBertrand Kevin KlineSQL Sentry, Dir of Engineering Services@KEKline

  2. New eBOOKAvailable! Check http://SQLSentry.TV for links to the video, slides, and demo code starting August 1st.

  3. Drawing on July 31stfor a chance to win one of 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 • Patterns & Anti-Patterns • Efficient ad hoc sets (Aaron) • Best practices for comma-delimited parameters (Aaron) • Implicit Conversions (Kevin) • Best practices in temporary structures (Kevin) • Follow Up

  5. SQL 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 SELECT but 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 • Specifying the schema • SP_xyz Prefix • Unwanted recompiles • The “Kitchen Sink” stored procedure • SP_ExecuteSQLvs EXEC(…) • Execution Plan Self-Destruct • To MERGE or not to MERGE • Efficient ad hoc sets • Implicit conversions • Best practices for comma-delimited parameters • Best practices in temporary structures

  6. Generate ad-hoc sets efficiently • Example: a set of days in a range • Recursive CTEs can do this, but… • Create a permanent number/calendar table instead • DEMO

  7. implicit conversions • SQL Server has to do a lot of extra work / scans when conversion operations are assumed by the SQL programmer. • Happens all the time with data types you’d think wouldn’t need it, e.g. between date types and character types. • Very useful data type conversion chart at http://bit.ly/15bDRRA. • Data type precedence call also have an impact: http://bit.ly/13Zio1f. • DEMO

  8. Implicit conversion resources • Ian Stirk’sColumn Mismatch Utility at http://www.sqlservercentral.com/articles/Administration/65138/. • Jonathan Kehayias’ plan cache analyzer at http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx. • Jonathan Kehayias’ index scan study at http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

  9. Comma-Delimited parameters • Example: pass a comma-separated list of OrderIDs • String splitting is expensive, even using CLR • Table-valued parameters are typically a better approach • DEMO

  10. Temporary structures • Which are better, temp tables or temp variables?

  11. Summary • Comma-delimited parameters may be better with TVPs than splitting strings. • Implicit conversions can cause extra CPU work and/or index and table scans. • Efficient ad hoc sets may not be best in recursive CTEs. Test your alternatives. • Temporary tables and temporary variable each have their uses. Know when to use each.

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