Transcript queries with in

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!
Check http://SQLSentry.TV for links to
the video, slides, and demo code
starting August 1st.
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.
AGENDA
• Introductions
• Patterns & Anti-Patterns
o
o
o
o
o
Specifying the schema
SP_xyz Prefix
Queries with IN (…) / OR
Unwanted recompiles
Transitive property of indexes
• Prizes!
• 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.
Specifying the schema
SP_xyz Prefix
Unwanted recompiles
SPECIFYING THE SCHEMA
• Always - when creating, altering, referencing objects
• Even if today everything is dbo
o Object resolution works harder
o Can yield multiple cached plans for the same query
o DEMO
BONUS REASON: SECURITY
dbo.sptest
(Aaron) Exec sptest
select * from test
Aaron.test
dbo.test
dbo stuff
Aaron stuff
THE DREADED SP_ PREFIX
• Stored procedures with the SP_ prefix can:
o Cause metadata overhead
o Induce needless SP:CacheMiss events
• About 10% performance hit (duration) in my tests
• Blog post: http://sqlperformance.com/sp_prefix
QUERIES WITH IN (…) / OR
• Meaning:
o column IN (a,b,c)
o 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
UNWANTED RECOMPILES
Execution
In Memory?
NO
Read from
system table
YES
compile
ReComp optimize
Execute
CAUSES OF RECOMPILE
• Expected: Because we request it:
o CREATE PROC … WITH RECOMPILE or EXEC myproc … WITH RECOMPILE
o SP_RECOMPILE foo
• Expected: Plan was aged out of memory
• Unexpected: Interleaved DDL and DML
• Unexpected: Big changes since last execution:
o Schema changes to objects in underlying code
o New/updated index statistics
o Sp_configure
INTERLEAVED DDL AND DML
•
•
•
•
•
•
•
•
•
•
•
CREATE PROC testddldml AS … ;
CREATE TABLE #testdml;
<some T-SQL code here>
INSERT INTO #testdml;
<some T-SQL code here>
ALTER TABLE #testdml;
<some T-SQL code here>
INSERT INTO #testdml;
<some T-SQL code here>
DROP TABLE #testdml;
<some T-SQL code here>
-- (DDL)
-- (DML + RECOMPILE)
-- (DDL)
-- (DML + RECOMPILE)
-- (DDL)
SCHEMA CHANGES TO OBJECTS
• Schema changes:
o
o
o
o
Column additions, deletions
Data type changes
Constraint additions, deletions
Rule/Default bindings
• Index used by query is dropped
NEW/UPDATED INDEX STATISTICS
• SQL Server recompiles to code to take advantage of
new statistics for both manually and automatically
created statistics:
o Auto_update statistics
o Auto_create statistics
o Update statistics
TRANSITIVE PROPERTY OF INDEXES
• In algebra:
o when A = B and B = C, then …
o A=C!
• Some older versions of SQL Server do not know this.
• Incorporate the transitive property into JOIN and
WHERE subclauses when appropriate:
o SELECT … FROM table1 AS t1
o JOIN table2 AS t2 ON t2.my_id = t1.my_id
o JOIN table3 AS t3 ON t3.my_id = t1.my_id
• AND t3.my_id = t2.my_id
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.
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