1 / 18

SQL Server Query Tuning Best Practices, Part 4 of 6

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

gavin
Download Presentation

SQL Server Query Tuning Best Practices, Part 4 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 4 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. Your 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 • Specifying the schema • SP_xyz Prefix • Queries with IN (…) / OR • Unwanted recompiles • Transitive property of indexes • Prizes! • 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

  6. SPECIFYING THE SCHEMA • Always - when creating, altering, referencing objects • Even if today everything is dbo • Object resolution works harder • Can yield multiple cached plans for the same query • DEMO

  7. dbo.sptest select * from test Aaron.test dbo.test Aaron stuff dbo stuff Bonus reason: Security (Aaron) Exec sptest

  8. The dreaded sp_ prefix • Stored procedures with the SP_ prefix can: • Cause metadata overhead • Induce needless SP:CacheMiss events • About 10% performance hit (duration) in my tests • Blog post: http://sqlperformance.com/sp_prefix

  9. QUERIES WITH IN (…) / OR • Meaning: • column IN (a,b,c) • column = a OR column = b OR column = c • These optimize to the exact same plan • IN is my personal preference (brevity) • Do *not* replace with UNION or UNION ALL • Can use TVPs to replace CSV/XML or dynamic SQL • DEMO

  10. Unwanted Recompiles Execution Read from system table In Memory? NO compile YES optimize ReComp Execute Execute

  11. Causes of Recompile • Expected: Because we request it: • CREATE PROC … WITH RECOMPILE or EXEC myproc … WITH RECOMPILE • SP_RECOMPILEfoo • Expected: Plan was aged out of memory • Unexpected: Interleaved DDL and DML • Unexpected: Big changes since last execution: • Schema changes to objects in underlying code • New/updated index statistics • Sp_configure

  12. Interleaved DDL and DML • CREATE PROC testddldml AS … ; • CREATE TABLE #testdml; -- (DDL) • <some T-SQL code here> • INSERT INTO #testdml; -- (DML + RECOMPILE) • <some T-SQL code here> • ALTER TABLE #testdml; -- (DDL) • <some T-SQL code here> • INSERT INTO #testdml; -- (DML + RECOMPILE) • <some T-SQL code here> • DROP TABLE #testdml; -- (DDL) • <some T-SQL code here>

  13. Schema Changes to Objects • Schema changes: • Column additions, deletions • Data type changes • Constraint additions, deletions • Rule/Default bindings • Index used by query is dropped

  14. New/Updated Index Statistics • SQL Server recompiles to code to take advantage of new statistics for both manually and automatically created statistics: • Auto_update statistics • Auto_create statistics • Update statistics

  15. Transitive property of indexes • In algebra: • when A = B and B = C, then … • A = C ! • Some older versions of SQL Server do not know this. • Incorporate the transitive property into JOIN and WHERE subclauses when appropriate: • SELECT … FROM table1 AS t1 • JOIN table2 AS t2 ON t2.my_id = t1.my_id • JOIN table3 AS t3 ON t3.my_id = t1.my_id • AND t3.my_id = t2.my_id

  16. Summary • Specify the schema, even if you only have dbo. • Don’t use the SP_xyz Prefix. • Understand queries with IN (…) / OR clauses. • Remember unwanted recompiles. • Don’t expect SQL Server to know the transitive property of indexes.

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