Most of the time, when you need to change SQL Server’s behavior – like how it handles memory or parallelism – there are easy-to-use switches to get the job done. In SQL Server Management Studio, just right-click on the server’s name, go into Properties, and there are all kinds of things you can mess with. Most of the time, you’re going to make things worse, but whatever.
Every now and then, when you work with Microsoft support, you might hit a specialized situation where you need a very unusual change in SQL Server’s behavior. These settings aren’t well-documented, nor are they easy to enable via the GUI. These settings are typically implemented via trace flags.
Trace flags are edge cases. At least, that’s the idea. If you read through Konstantin Taranov’s excellent open source trace flag documentation, you’ll see why.
Back in the 2000s, a few settings turned out to be fairly important, like how TempDB handles page allocations. For a while back then, folks would recommend that everybody implement trace flags like 1117 and 1118. Microsoft took note of that, and eventually changed SQL Server’s normal behavior so that those trace flags weren’t necessary for TempDB anymore.
Konstantin’s documentation has a list of recommended trace flags, but I’m curious: if you’re running a trace flag that is NOT in Konstantin’s list of defaults, which one is it, and what prompted you to turn it on?
13 Comments. Leave new
Hey Brent I’m using TF: 9567 (compression improves the throughput of Automatic Seeding operations) on all our AO/AG replicas. We have a 3 primary – 2 secondary setup!
WHY: We found the CPU overhead did not impact our servers greatly and the seeding operation time to complete was cut by 50% or more 🙂
From the article…
“For a while back then, folks would recommend that everybody implement trace flags like 1117 and 1118. Microsoft took note of that, and eventually changed SQL Server’s normal behavior so that those trace flags weren’t necessary for TempDB anymore.”
While I appreciate Microsoft’s good intentions, that change screwed me because they don’t allow the new “feature” to be even temporarily disabled on TempDB. You’ll be screwed as well if you have a large table that you need to copy on the same instance and you need to use SET IDENTITY INSERT ON because that known-by-MS-to-be-a-problem writes the ENTIRE clustered index to TempDB for “sorting”.
So, have a nice tight TempDB of 8 files and you decided to copy a 250GB table and it decides to go single threaded… it tries to increase the size of all 8 files to 250GB making for a 2 TB TempDB. AND, that occurs even if you do achieve “Minimal Logging” for the copy.
I’ve always liked 3042 so that backups don’t write out the full size before being compressed at the end and saves disk space. This trace flag compresses the backup a number of times during the backup.
Definitely a very useful trace flag. I’ve been using it for a long time. But do you have any other trace flags suggestions for the prod environment? ( I have an always-on environment with 3 nodes )
I use 9567, 3042 which are mentioned here too. Also use 9481 which is the one that forces to use the legacy Cardinal Estimator even if your user databases are in the latest compatibility level. There is a long long story on the WHY
TF 1222 Vendor asked us to turn it on, to figure out blocking. “Function: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Write deadlock information to sql server error log.”
In these cases I Usually tell the vendor no, and will send them the following link:
https://docs.microsoft.com/en-us/answers/questions/151238/query-for-deadlocks-from-system-health-empty-resul.html
We have the following trace flags enabled:
4199 – Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.
We enable this because I want fixes and changes from CUs and hot-fixes to take affect immediately.
3226 – Suppress successful backup logging
Who wants all that extraneous logging?
2861 – Zero cost plans
SolarWinds DPA likes this enabled.
1448 – Enables log reader to move forward even if the async secondaries have not acknowledged the reception of a change.
While migrating SQL 2012 -> SQL2019 there was a window of time when we our AG secondary was not online. Setting the secondary to async and enabling this flag allowed CDC on our primary to continue to capture changes during the seeding and build-out of the secondary.
Most 2016 SQL Servers:
460 String or binary data would be truncated more useful information.
3266 Got a job to keep tract of all backups.
2014 SQL Servers for Microsoft SCMS: 834, 1117, 1118, 8295. Microsoft setup.
2016 SQL Server Vendor application: 652 Disable page pre-fetching scans. Vendor setup.
3042 – Backup pre-allocation turned off so the backup files grow as needed. It seems even differential backups want to pre-allocate way too much. There is no point of using compression to reduce the storage footprint if a backup always wants to pre-allocate more than what’s reasonable to maintain.
We use this by default when taking any backup whether it needs it or not. Red Gate backup did this I believe as a default. Then we switched to native compressed SQL backups and ran into this issue. Now we’re switching to BOOST-FS native backups so we may be able to get away without it sooner or later.
Note that if your storage admins force you into DDBOOST because they got sold on data deduplication, make sure they meet you in the middle and they also configure BOOST-FS (FILESHARE) with it (you can still make use both if you want to). No cryptic backup and restore syntax, just keep your native SQL backups and simply write them to the BOOST-FS file share. Be aware the Windows file shares are not case-sensitive while the appliances file share part IS. Just don’t plan on changing the case of your database names or backup subfolders within these file shares or you may be scratching your head trying to figure out why files/folders aren’t found but you can see them. (Case changes can slip by someone for example when restoring databases and over-typing in the database name part.)
3226 – suppresses successful backup messages in the error log.
3625: to reduce the amount of database information that might be returned in an error message to users.
3226: suppress successful backups. Less information to go through when reading ERRORLOG.
7745: For Query Store, do not flush data to disk on database shutdown. I would rather lose some Query Store data than wait for this operation during shutdown.
7752: Asynchronous load of Query Store. I would rather get databases up faster than wait for Query Store to finish loading.