Transcript Real World Performance Design and Tuning

SQL SERVER CONFIGURATION
OPTIONS AND TRACE FLAG SECRETS
Kevin Kline
SQL Sentry, Director of Engineering Services
@KEKline on Twitter, FB, LI
Blogs at http://KevinEKline.com and http://ForITPros.com
FREE SWAG FROM SQL SENTRY!
•
Free Plan Explorer download:
http://www.sqlsentry.com/plan-explorer/
•
Free query tuning consultations:
http://answers.sqlperformance.com.
•
Free new ebook (regularly $10) to attendees.
Send request me at [email protected]
•
SQL Server educational videos, scripts, and
slides: http://SQLSentry.TV
•
Tuning blog: http://www.sqlperformance.com/
•
Monthly eNews tips and tricks:
http://www.sqlsentry.com/newsletter-archive.asp
AGENDA
• SP_Configure
o Red Herrings
o DBoptions
• Trace Flags
o The general details about trace flags
• Resources & Wrap up
SP_CONFIGURE SETTINGS
Bottom-line Behavior
• Min and Max SQL Server Memory
• Special considerations for Windows “Lock Pages in
Memory”
• Optimize for Ad hoc Workloads
• Fill Factor at 70-80% for OLTP applications
• Parallelism is not the boogyman:
o No kneejerk reactions – thinking about disabling MaxDOP (set to 1)?
o May prefer Cost Threshold for Parallelism, especially for OLTP
applications
SP_CONFIGURE RED HERRINGS
Don’t Use These Unless Testing Proves Otherwise
•
•
•
•
•
•
•
Boost SQL Server Priority
Max Worker Threads
Lightweight Pooling
CPU Affinity
Locks
Query Wait
Query Cost Governor Limit
SURPRISING DATABASE OPTIONS
• Parameterization:
o Simple or Forced
o For experienced DBAs only
• Database Read-Only
o Can help with locking
o Specialize!
• Compatibility Levels
o Might not do what you think it does…
WHAT ARE TRACE FLAGS?
• A directive used to “set specific server characteristics or
to enable/disable a particular behavior”
• Enabled at different scopes and contexts:
o Scopes are Global or Session.
o Contexts are Startup or Query.
• Documentation sources:
o Expected sourcs: BOL, KB articles / Service Pack & Cumulative
Update
o Unexpected: readme files, white papers, blogs / user groups / “water
cooler”
CAVEATS!
No legal action complaining if something goes wrong!
USING TRACE FLAGS IN T-SQL
• DBCC { TRACEON | TRACEOFF | TRACESTATUS }
o Use -1 to enable a trace flag globally
o Add -T to the SQLServer startup command to enable startup global trace flag
• Within a query using OPTION (QUERYTRACE n)
• Sometime trace flags seem to do “nothing”. To see their
output:
o DBCC TRACEON (3604): Send output to console
o DBCC TRACEON (3605): Send output to ERRORLOG
TF’ING–A!
SAFE* IN PRODUCTION
• T610 speeds up high volume data loads by writing less
information to the transaction log for minimally logged
inserts into indexed tables.
• T834 enables SQL Server, on 64-bit systems, to use
large-page allocations for the buffer pool.
• T835 enables “lock pages in memory” for Std Ed
-T1118: BLESSED BY ZEUS
• Tells SQL Server to allocate full extents to each tempdb
objects, rather than mixed extents.
o Less contention on internal structures such as SGAM pages
o Story has improved in subsequent releases of SQL Server
o So represents a “edge case”
ZEUS =
TF’ING
DEADLOCKS
• T1204 writes information about deadlocks to the
ERRORLOG in a “text format”. T1222 writes information
about deadlocks to the ERRORLOG in a “XML format”
o Resources
o Types of locks
• Duration of deadlocks are affected by enabling this trace
flag.
TF’ING
UP LOCKS
• T1200 returns locking information in real-time as your
query executes
o Use during development / testing phase
o Great for learning how SQL Server implements locking
• T1211 disables lock escalation based on memory
pressure or number of locks. T1224 disables lock
escalation until 40% of memory is used and then reenables escalation
o When enabled, MSSQL won’t escalate row or page locks to table
locks.
o T1211 takes precedence over T1224
o Microsoft recommends using T1224
• Trace flag 1211 prevents escalation in every case, even under memory
pressure and may help avoid "out-of-locks" errors when many locks are
being used.
• Warning! Can generate excessive number of locks, can slow performance,
even cause 1204 errors.
TF’ING
PARALLELIZED OPERATIONS
• T2528 disables parallel checking of objects during DBCC
CHECKDB, CHECKFILEGROUP and CHECKTABLE.
o Default leaves parallel DBCC checks enabled. DBCC operations can
dynamically change their degree of parallelism.
• T2562 perform the entire set of checks in a single ‘batch’
instead of multiple batches, and increase the efficient of
per-thread calls to get a new list of pages to read.
• T2549 treats each database file as if it’s on a separate
physical drive for the purposes of driving read-ahead for
DBCC CHECKDB
o Alternatives:
• MAXDOP option
• Resource Governor
TF’ING
DATA CONSISTENCY
• T806 enables DBCC audit checks to be performed on
pages to test for logical consistency problems.
o These checks try to detect when a read operation from a disk does
not experience any errors but the read operation returns data that is
not valid.
o Pages will be audited every time that they are read from disk.
o Affects performance! This should only be used in systems where
data stability is in question.
TF’ING
UP THE DATABASE FILES
• T3004 returns more information about instant file
initialization (IFI). Useful to see if SQL Server has been
configured to take advantage of IFI correctly. Not often
used
• T1117 tells SQL Server autogrow all files in a database
at the same time
TF’ING
UP THE TRANSACTION LOG
• T3422 enables log record auditing to troubleshoot log
file corruption
o Careful! It introduces overhead to each transaction log record write.
o Similarly to trace flag 806, you would only use this to troubleshoot corruption
problems
TF’ING
UP CHECKPOINTS
• T3502 enables detailed tracking of CHECKPOINTs to
the ERRORLOG
• T3505 disables automatic checkpoints:
o Setting trace flag 3505 may increase recovery time and can prevent log space
reuse until the next checkpoint is issued.
o Make sure to issue manual checkpoints on all read/write databases at
appropriate time intervals
• Microsoft recommends that you do not change the
recovery interval because it may affect data safety and
availability.
TF’ING
•
•
•
•
•
UP BACKGROUND
PROCESSES
T661 disables the ghost record cleanup process.
T8020 disables working set monitoring
T2330 disables all index usage stats gathering
T2371 enables proportional automatic update statistics
T2389 and T2390 enables auto-quick-statistics update
for ascending keys, whether known or unknown,
respectively
TF’ING
UP THE RING BUFFERS
• T818 enables the ring buffer for tracking the last 2,048
successful write operations, not including sort and
workfile I/Os.
• T8011 disables the ring buffer for Resource Monitor
• T8012 disables the ring buffer for schedulers
• T8018 disables exception ring buffer
• T8019 disables stack collections for the exception ring
buffer
TF’ING
UP THE OPTIMIZER
• T652 disables page pre-fetching scans
• T4199 enables all fixes that were previously made for
the query processor under many trace flags/hot fixes
based on “special” policy
• T2301 enables advanced optimizations specific for BI
and processing very large data sets
• T2312 and T9481 enables or disables the new SQL2014
cardinality estimator, respectively
• T2861 enables caching trivial plans
• T8744 disables pre-fetching ranges for nested loop
operations.
TF’ING
DEBUGGING QUERIES
The slide notes contain an exhaustive list of all query debugging trace flags
that I’m aware of. Many are undocumented. Use at your own risk.
• First, always enable T3604.
• T8615: Shows the final memo structure.
o Look for the ‘Good Enough Plan Found’ message
• T8675: Shows optimization stages and times.
o Simplification stage = trivial plan
o Search (0 … n) = transactional, quick, and full plans
A TRACE FLAG I ALWAYS WISHED FOR…
• T8780 allows you to “turn off” query optimizer time outs.
o Don’t use without plenty of testing.
o Allows optimizer differentiate between the optimal plan and the best
plan.
o My recommendation: Consider using only on batches that are used
bazillions of times per day.
RESOURCES
• Best blogs:
o http://sqlskills.com/blogs/paul
o http://sqlperformance.com, especially Paul White’s query tuning
posts
o http://www.somewheresomehow.ru/optimizer_unleashed_1/, a good
blog in Russian about advanced query tuning
• Trace Flags: http://www.victorisakov.com
24
WRAP UP
1. Engage with us on social media.
o
We’re thankful for your word of mouth promotions and endorsements!
2. Share your tough SQL Server problems with us:
http://answers.sqlperformance.net
3. Download SQL Sentry Plan Explorer for free:
http://www.sqlsentry.net/plan-explorer/sql-serverquery-view.asp
4. Check out our other award winning tools:
http://www.sqlsentry.net/download