A Fine Slice Of SQL Server
Chad Callihan looks at a pair of options:
Just because something works doesn’t mean it’s the right thing to do. I had that type of challenge to my database morals recently when facing a query that refused to go parallel.
Read on to learn more. Note that neither of these relates to MAXDOP because that doesn’t determine whether a plan will go parallel (though you can use it to prevent a plan from going parallel).
Chad Callihan enables a trace flag:
In SQL Server 2016 and 2017, trace flag 460 can be used to gather additional details about string truncation errors. You may be familiar with the “String or binary data would be truncated” error message. Have you been left wondering what would be truncated? This is where trace flag 460 comes in. When enabled, the error message will include details on where exactly the potential truncation is taking place.
Read on to see if this is something you might benefit from enabling.
Comments closedJonathan Kehayias helps me understand that Trace Flag 460 is one of the best trace flags ever:
For most people that are reading this post, I’d venture to guess that you have no idea what Trace Flag 460 is or when you would use it. Well first off, let me tell you it’s a fully documented and supported trace flag, and it’s totally safe. In fact, on SQL Server 2019 and higher it is the default behavior when you create a new database. What does it do? It makes troubleshooting string or binary truncation issues easier by changing the error message that is returned from message ID 8152 and replaces it instead with message ID 2628. The trace flag is also available in SQL Server 2016 SP2 CU6+ and SQL Server 2017 CU12.
Read on to learn more about it and to remove a potential bit of confusion in the documentation.
Comments closedErik Darling takes us through trace flag 3608:
According to the docs:
Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Move System Databases and Move User Databases.
Note: Do not use during normal operation.
Scope: global only
But it turns out it can do quite a bit of harm. It seems that many things stop working when it’s in use, though, including statistics getting automatically created.
Click through to see what kinds of things fail to work as a result of this trace flag.
Comments closedRobert Sheldon performs some level-setting:
SQL Server includes a set of configurable options known as trace flags. You can use trace flags to set server characteristics and control different types of operations. SQL Server offers a wide range of trace flags that let you modify the platform’s default behavior to meet specific requirements. Trace flags can help you when performing such tasks as testing stored procedures, diagnosing performance issues, or debugging complex computer systems. Microsoft Support might also recommend using certain trace flags to address behavior that’s impacting specific workloads. This article explains how to enable SQL Server trace flags.
Click through for the article.
Comments closedGarry Bargsley has a solution to an annoyance:
Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence. This behavior causes a bloated Error Log that can make it hard to find what you need quickly.
Luckily, that same SQL Server team built in a solution to this situation.
Read on to see what the solution is, as well as how to use it.
Comments closedTaiob Ali has a set of hints to use instead of setting trace flags:
Introduced in SQL Server 2016 (13.x) SP1 and Azure SQL Database is the USE HINT option. At the time of writing this article, there are 23 of those. You can see an up to date list by running Dynamic Management View sys.dm_exec_valid_use_hints.
With the introduction of this feature, we can replace some of the trace flags with hints. I personally like hints over trace flags (if I have to choose between two devils). That way someone reading the code have some idea what the hint is about instead of remembering the trace flag numbers.
I agree with Taiob on that sentiment.
Comments closedPaul Randal takes us through a change to SQL Server 2019:
After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.
Read on to see the answer.
Comments closedLonny Niederstadt has a public service announcement:
Trace flag 2549 is no longer required for checkdb or checktable in SQL Server 2016++.
Trace flag 2562 may still benefit checkdb on a system (depends on disk characteristics and tempdb) in SQL Server 2016++, but is not expected to benefit checktable due to the already-limited scope of checktable activity.
Click through for the full story.
Comments closedTaiob Ali creates a process to track ensure specific trace flags are in use:
Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.
My team members fixed this issue by leveraging startup stored procedures.
Click through for the explanation.
Comments closed