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. If you look under database properties, you will notice an option called Query Optimizer Fixes which is defaulted to OFF. Unlike trace flags, I think this option brings this to the forefront, as many of us like to dig through properties and configurations which lead us to find out information like this. This defaulted to OFF still provides systems with the same safe guard as trace flag 4199. Now it can be controlled and enabled on a database level for all hot fixes, cumulative updates, and upgrades.
Query Optimizer Fixes must be set to ON for to be enabled automatically for compatibility levels less than 130 (SQL Server 2016). As you move from level to level you can enable all optimizer changes from previous updates just by changing to a higher compatibility level. There is no longer a need to use the trace flag to take advantage.
So, what’s the point of enabling this option if I can take advantage of optimizer changes just by changing compatibility levels. Well, the flag still applies for any future updates to SQL Server or potentially you are stuck at a lower compatibility level due to a third-party application or code that cannot be upgraded, but you still want to take advantage of optimizer fixes post updates. If you enable this option, you will no longer have to remember to enable trace flag 4199 post install and or have it enabled at start up. Now of course if you have this option set in production I hope you have already tested fixes in development and are free to have these optimizer changes implemented.
You can read more about the history of trace flag 4199 here.
One of my goals for 2018 is to expand the reach of my blog.
As a blogger starting out I had my own site I posted to, but my blogs only reached those who followed me on Twitter, Facebook, Google + and LinkedIn. My blog automatically posts to these three sites using a WordPress Jetpack Plugin called Publicize.
After a few months, I discovered that syndication could be setup on other sites such as SQL Server Central. This allowed more people the opportunity to read my writing and expand my reach. Many sites, such as these will even send your blog out in newsletters like Database Weekly.
Starting last week, I wrote my first article on Simple Talk and I hope to have more in the future. This week some of you may have noticed my tweets regarding C# Corner. I want to thank to Mahesh Chand (T), Microsoft Regional Director and MVP for introducing me to it.
C# Corner, founded in 2000 is one of the largest software professionals (developers, IT pros, database) online community with 5.5 million monthly visitors and 2.4 million registered members. C# Corner contributors are industry influencers, MVPs, authors, and trainers from around the globe. C# Corner is always looking for industry experts for their opinions, contributions, and expertise to share with its millions of users. Since posting to their site, I have already had over 7,000 views and exposed to millions of readers worldwide.
Websites such as SQLServerCentral.com and C# Corner are just two examples of ways to share what you have written beyond your own website. There are thousands of sites and readers out there looking to learn what you have to share.
If you’re looking to expand the reach of your writing, I highly encourage you to check out this site.
I am so thrilled to have the opportunity to expand my writing with my first Simple-Talk article posted. Simple- Talk is technical journal and community hub from Redgate. It in I discuss my passion for my work as a Lone DBA.
You can find the full article using the link below.
Here is a quick blog to show you something I discovered that I didn’t know existed.
I was on a Skype call and noted that the presentation I had been watching was no longer audible. I tried messing with my volume, I made sure my sounds weren’t muted and played around with my speakers and still nothing. I could hear the skype call without issues, but I wanted to multitask and continue to listen to the presentation.
Finally, a light bulb went off and I thought maybe windows was “doing me a favor” and turning off all noises why I am on Skype. Sure, enough it was. If you look under your computer audio settings, you will notice a tab call Communications. Based on the setting windows will turn off all sounds, lower the volumes or do nothing when it notes you are placing or receiving a call. Pretty nifty if you ask me.
Mine was set to the second option and had reduced my volume of other sounds to 80%. This made sense because I was still hearing some sound, just not much. I found it frustrating when trying to figure out what was “wrong” with my volume. Hopefully this quick blog post will help prevent any frustrations for you!