A Fine Slice Of SQL Server
Taiob 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 closedMonica Rathbun points out some of the trace flags which are no longer important in SQL Server:
If you have ever attended one of my performance tuning sessions, you know I tend to talk about trace flags. Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents. I have also included a brief commentary on each one. As with any change, you should be sure to thoroughly test before implementing these trace flags into any production environment.
Read the whole thing, especially because at least one of them is still optional and defaulted to off (but able to change at a different scope).
Comments closedDavid Klee talks us through large memory pages:
SQL Server Enterprise Edition can leverage large memory pages to reduce the amount of memory pointers required for larger SQL Server deployments. Reducing the number of pointers makes the database engine more efficient, especially for SQL Servers with greater than 32GB of RAM. A normal memory block is 4KB, and many thousands of pointers are required to manage the memory underneath a larger SQL Server. Large memory pages can change the block size to 2MB, greatly reducing the number of pointers required for memory management.
Read on to see what effect this has, as well as when to use them and—more importantly—when not to use them.
Comments closedPam Lahoud explains the purpose of trace flags and talks about a very important trace flag, 4199:
Some trace flags are used to enable enhanced debugging features such as additional logging, memory dumps etc. and are used only when you are working with Microsoft Support to provide additional data for troubleshooting. These trace flags are not ones you want to leave turned on in a production system as they may have a negative impact on your workload. An example of one of these flags would be TF 2551 which is used to trigger a filtered memory dump whenever there is an exception or assertion in the SQL Server process. These trace flags are only used for a short period of time and typically only at the recommendation of Microsoft Support, so they will likely always be around.
If you are a DBA and are not extremely familiar with trace flags, you really want to read this article.
Comments closedAndy Galbraith recommends that you enable trace flag 4199 in SQL Server:
The session was titled “Modernize Your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers” and it…was…awesome!One of the presenters was Pedro Lopes (blog/@SQLPedro), a Senior PM for the Relational Engine. In his part of the day he talked about several features of the engine and the optimizer, but the “What…what did he say?” moment for me was when he talked about trace flag 4199…and how we should have it turned on pretty much everywhere.Wait…what?
If you aren’t aware of trace flag 4199 and are running an edition of SQL Server prior to 2016, this is big. One of our user group members called it out specifically at our last meeting. As far as 2016+ instances go, Andy covers how that behavior is a little different, so check it out.
Comments closedRob Sewell points out an addition to dbachecks:
This will show you
- the UniqueTag which will enable you to run only that check if you wish
- AllTags which shows which tags will include that check
- Config will show you which configuration items can be set for this check
The trace flag checks require the app.sqlinstance configuration which is the list of SQL instances that the checks will run against. You can also specify the instances as a parameter for Invoke-DbCheck as well.
Click through for an example.
Comments closedGrant Fritchey shows a lightweight way of capturing plan metrics:
I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don’t need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan.
Very interesting. But as Grant points out, this is not a cost-free operation, so use as needed but not willy-nilly.
Comments closedMonica Rathbun explains that just upgrading a SQL Server database doesn’t enable optimizer changes:
When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software.
To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable or disable the updated fixes. To take advantage of optimizer fixes or improvements you would have enable trace flag 4199 after applying each hot fix or update or set it up as a startup parameter. Did you know this? This was something I learned while working with an existing system, years into my career. I honestly assumed it would just apply any applicable changes that were in the patch to my system. Trace flag 4199 was introduced in the SQL Server 2005-era. In SQL Server 2014, when Microsoft made changes to the cardinality estimator they protected the changes with trace flags as well, giving you the option to run under compatibility level 120 and not have the cardinality estimator changes in effect.
Things changed starting with SQL Server 2016.
Click through to see how SQL Server 2016 made it a bit easier.
Comments closedWith the release of SQL Server 2016, these trace flags were rumored to be a thing of the past and hence completely unnecessary. That is partially true. The trace flag is unneeded and SQL 2016 does have some different behaviors, but does that mean you have to do nothing to get the benefits of these Trace Flags as implemented in 2016?
As it turns out, these trace flags no longer do what they did in previous editions. SQL Server now pretty much has it baked into the product. Buuuuut, do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was automatically enabled. So let’s create a script that checks these things for me.
Click through for the script and a summary of his findings.
Comments closedJoe Obbish has a list of 45 undocumented trace flags:
Below is a list of trace flags which, as far as I can tell, have never been publicly documented. I did not fully investigate many of them and many of the descriptions are just guesses. I make no guarantees and none of these should be used in production. All tests were performed on SQL Server 2017 CU2 with trace flags enabled at the global level.
This is combining a bit of database archaeology and database anthropology.
Comments closed