Transcript query test harness

SQL SERVER QUERY
TUNING BEST PRACTICES
Aaron Bertrand
SQL Sentry, Senior Consultant
@AaronBertrand
Kevin Kline
SQL Sentry, Dir of Engineering Services
@KEKline
NEW eBOOK
AVAILABLE!
Kindle promo code and links to the
video, slides, and codes will be emailed
to all attendees after the webcast.
We’re giving away
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
Don’t forget the test harness – Kevin
Quick tips on assessment – Kevin
Patterns & Anti-Patterns – Aaron
o Queries optimized for SELECT statements but not DML
statements
o Using functions in WHERE and JOIN clauses
o Queries with wildcard searches
• Prizes!
• Follow Up
TEST HARNESS
• Your query test harness should include some stuff.
• Code to clear the caches: *
o DBCC [FreeProcCache | FreeSystemCache |
FlushProcInDB(<dbid>) ]
o DBCC DropCleanBuffers
• Code to set measurements:
o SET STATISTICS TIME
o SET STATISTICS IO
o SET SHOWPLAN [TEXT | XML]
• Code for Dynamic Management Views (DMV) checks.
o System info – sys.dm_os_performance_counters and
sys.dm_os_wait_stats
ASSESSING THE FINDINGS
•
Red Flags Query Operators:
o
o
o
o
•
Red Flags Elsewhere:
o
o
o
o
o
•
Lookups
Scans
Spools
Parallelism Operations
Dissimilar estimated versus actual row counts
High physical reads
Missing statistics alarms
Large sort operations
Implicit data type conversions
Using live demo, we’ll show you patterns to use and anti-patterns to
beware.
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
OPTIMIZING FOR SELECT VS. DML
• Big differences between a SELECT and a DML
statement that effects the same rows.
• Shouldn’t blindly create every index the Tuning
Advisor or execution plan tells you to.
• Blog post - http://bit.ly/AB-BlindIndex
READS & INDEX STRUCTURE
• 8K pages
• Leaf pages ARE the data.
• Non-leaf pages are pointers.
Root Page
Intermediate
Pages
Leaf Pages
Level 2
Level 1
Level 0
WRITES & INDEX STRUCTURE
• Each change to the leaf pages requires all index
structures be updated.
Root Page
Intermediate
Pages
Leaf Pages
Level 2
Level 1
Level 0
DML
Page
Split
Actual
placement
WHAT’S THE USAGE OF AN INDEX?
• Easy to answer with two DMVs!
o sys.dm_db_index_physical_stats
o sys.dm_db_index_usage_stats
FUNCTIONS IN THE WHERE AND JOIN CLAUSES
• Functions / calculations on columns of a WHERE or
JOIN clause can make SQL Server ignore indexes:
o WHERE qty * 12 > 10000
• Instead, move function / calculation to SARG:
o WHERE qty > 10000/12
DEMO
WILDCARD SEARCHES USING ‘%’
• Queries that use the LIKE clause have two simple
rules:
o LIKE can use indexes if the pattern starts with a character
string, such as WHERE lname LIKE ‘w%’
o LIKE cannot use an index if the pattern starts with a leading
wildcard, such as WHERE lname LIKE ‘%alton’
SUMMARY
• Queries optimized for SELECT statements but
not DML statements
o Know your whole workload before you create your indexes
• Using functions in WHERE and JOIN clauses
o Negate the use of indexes
• Queries with wildcard searches
o Negate the use of indexes
o Make an index less effective
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