Transcript Major Myths About Microsoft SQL Server

SQL SERVER QUERY TUNING
BEST PRACTICES, PART 5 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
The “Kitchen Sink” stored procedure
SP_ExecuteSQL vs EXEC(…)
Execution Plan Self-Destruct
To MERGE or not to MERGE
• 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.
16.
17.
18.
19.
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
THE "KITCHEN SINK" PROCEDURE
• Many optional parameters to satisfy a variety of search
conditions:
o Dynamic SQL is often the best route here
o Especially if Optimize For Ad Hoc Workloads is enabled
o Could also use RECOMPILE, but that means you pay compile cost every time
• DEMO
SP_EXECUTESQL VS. EXEC(…)
• Can promote better plan re-use
• Encourages strongly typed parameters instead of
building up a massive string
• DEMO
EXECUTION PLAN SELF-DESTRUCT
• Execution plans are intended to be reused as much as
makes good sense.
• There are a variety of hidden or inconspicuous reasons
that execution plans will self-destruct and never be
reused:
o
o
o
o
Contradictory language settings
Contradictory collation settings, especially with Unicode
Contradictory SET options
Users with different default schemas not using schema prefix
CONTRADICTIONS
• When SQL Server is faced with contradictory settings, it
will usually opt to generate a new execution plan:
o If language settings are different on client and server, even at the Windows-level
settings.
o If collation settings differ between servers, server and client, or between a
Transact-SQL batch and the objects they are operating upon. Especially
problematic with Unicode.
SET OPTION HIERARCHY
SET Option
Requirements:
ON
Arithabort
s
S,D,A
Concat_null_yields_null
s
S,D,A
Quoted_Identifier**
s
S,D,A
Ansi_nulls**
sp
S,D,A
Ansi_Padding
s
S,D,A
Ansi_Warnings
sp
S,D,A
Numeric_roundabort
OFF
s
Set-able
By:
S,D,A
Forceplan
SET only
Ansi_Null_Dflt_On
S,D,A
Ansi_Null_Dflt_Off
S,D,A
s
= required for index views or computed columns
p
= required for distributed / linked queries
S, D, A = sp_configure, sp_dboption, Alter Database
1. SET statement in code
2. OLEDB/ODBC connection
string
3. ODBC
o
o
Control Panel
SQLConfigDatasource
4. OLEDB/ODBC option auto
set
5. Database Level Settings
(Alter Database)
6. Server Wide Settings
(sp_configure)
TO MERGE OR NOT TO MERGE?
• Unnecessarily complex syntax
• Can complicate multi-operation trigger logic
• By default, does *NOT* promise concurrency protection
or prevent race conditions
o In the end it is not really any more efficient than separate statements
• Many unresolved bugs
• More details at http://bit.ly/AB-vs-MERGE
SUMMARY
• The “Kitchen Sink” stored procedure is bad. Specialize
your stored procedures
• SP_ExecuteSQL vs EXEC(…) have different strengths
and weaknesses. Know when to use each.
• Execution Plans can Self-Destruct due to hidden or
inconspicuous settings.
• To MERGE or not to MERGE? It’s not as clear cut as you
might think.
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