SQL SERVER CONFIGURATION OPTIONS AND TRACE FLAG SECRETS

SQL SERVER CONFIGURATION OPTIONS AND TRACE FLAG SECRETS Kevin Kline SQL Sentry, Director of Engineering Services @KEKline on Twitter, FB, LI Blogs at http: //Kevin. EKline. com and http: //For. ITPros. 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 sales@sqlsentry. net • SQL Server educational videos, scripts, and slides: http: //SQLSentry. TV • • Tuning blog: http: //www. sqlperformance. com/ Monthly e. News 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 Max. DOP (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 • T 610 speeds up high volume data loads by writing less information to the transaction log for minimally logged inserts into indexed tables. • T 834 enables SQL Server, on 64 -bit systems, to use large-page allocations for the buffer pool. • T 835 enables “lock pages in memory” for Std Ed

-T 1118: 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 • T 1204 writes information about deadlocks to the ERRORLOG in a “text format”. T 1222 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 • T 1200 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 • T 1211 disables lock escalation based on memory pressure or number of locks. T 1224 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 T 1211 takes precedence over T 1224 o Microsoft recommends using T 1224 • 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 • T 2528 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. • T 2562 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. • T 2549 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 • T 806 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 • T 3004 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 • T 1117 tells SQL Server autogrow all files in a database at the same time

TF’ING UP THE TRANSACTION LOG • T 3422 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 • T 3502 enables detailed tracking of CHECKPOINTs to the ERRORLOG • T 3505 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 T 661 disables the ghost record cleanup process. T 8020 disables working set monitoring T 2330 disables all index usage stats gathering T 2371 enables proportional automatic update statistics T 2389 and T 2390 enables auto-quick-statistics update for ascending keys, whether known or unknown, respectively

TF’ING UP THE RING BUFFERS • T 818 enables the ring buffer for tracking the last 2, 048 successful write operations, not including sort and workfile I/Os. • T 8011 disables the ring buffer for Resource Monitor • T 8012 disables the ring buffer for schedulers • T 8018 disables exception ring buffer • T 8019 disables stack collections for the exception ring buffer

TF’ING UP THE OPTIMIZER • T 652 disables page pre-fetching scans • T 4199 enables all fixes that were previously made for the query processor under many trace flags/hot fixes based on “special” policy • T 2301 enables advanced optimizations specific for BI and processing very large data sets • T 2312 and T 9481 enables or disables the new SQL 2014 cardinality estimator, respectively • T 2861 enables caching trivial plans • T 8744 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 T 3604. • T 8615: Shows the final memo structure. o Look for the ‘Good Enough Plan Found’ message • T 8675: 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… • T 8780 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