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.
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.
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.
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.
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.
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.
With 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.
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.
About once a month, I get support ticket regarding SQL Server dropping an application’s or user’s connection. The problem is SQL Server does not just randomly drop a connection and continue to work normally. Some force outside the control of SQL Server breaks the connection. By default, SQL Server does not record when this event occurs.
In my history of working with SQL Server, only in extreme situations have I ever seen SQL Server drop its connections. The most common example is when SQL Server is in the process of shutting down.
Click through for the trace flag and details.
In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.
In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.
I’ve always felt a little icky about writing to the registry from SQL Server, but Wayne shows how to do it right.