Transcript Major Myths About Microsoft SQL Server

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!
Check http://SQLSentry.TV for links to
the video, slides, and demo code
starting August 1st.
Drawing on July 31st for 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.
AGENDA
• Introductions
• Patterns & Anti-Patterns
o
o
o
o
Efficient ad hoc sets (Aaron)
Best practices for comma-delimited parameters (Aaron)
Implicit Conversions (Kevin)
Best practices in temporary structures (Kevin)
• Follow Up
SQL PATTERNS AND ANTI-PATTERNS
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Bad, Naughty Default Cursors
Correlated Subqueries
WHERE IN versus WHERE 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 JOIN clauses
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Specifying the schema
SP_xyz Prefix
Unwanted recompiles
The “Kitchen Sink” stored procedure
SP_ExecuteSQL vs 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
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
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
IMPLICIT CONVERSION RESOURCES
• Ian Stirk’s Column 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/findingimplicit-column-conversions-in-the-plan-cache.aspx.
• Jonathan Kehayias’ index scan study at
http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-causeindex-scans/
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
TEMPORARY STRUCTURES
• Which are better, temp tables or temp variables?
Temp Table
Temp Variable
Stored in?
Tempdb
Tempdb
Statistics?
Yes
No (1 row)
Indexs/Keys?
Yes
1 UK / PK only
Truncate?
Yes
No
Recompiles?
Yes
No
Parallelism?
Yes
No
Metadata Overhead?
Low
Lowest
Lock Overhead?
Normal
Lowest
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.
FOLLOW UP
1. Engage with our community: SQL Sentry on
Facebook, SQLSentry.Net, SQLPerformance.com
2. Share your tough query problems with us:
http://answers.sqlperformance.com
3. Download SQL Sentry Plan Explorer for free:
http://www.sqlsentry.net/plan-explorer/
4. Check out our other award winning tools:
http://www.sqlsentry.net/download