When Your Options Are Limited

In my role I am still on-call several times each month, and many of the escalations that come are simply things that newer DBA's and Help Desk staff have yet to run into - things that many employees at their level *can* handle, once they have experienced it.

This is one of those stories.

https://memegenerator.net/img/instances/57630989/with-a-one-way-ticket-to-the-twilight-zone.jpg

Our monitoring alerted the Help Desk because a client SQL Server was down (in this case, SQL services were stopped but Windows was not down).  In most cases this is handled between our Help Desk and if needed, the client, but in this case it was paged out to me with this note from the help desk technician:

"Upon checking on server Generic SQL Server, SQL Server Agent and SQL Server are failed - these services have automatic startup type but I can't start the services because the start option is disabled  "

And they added a screen shot:

There are multiple reasons why you can't start a service, most of them around permissions (role memberships, explicit permissions, etc.)  With what we do as a managed services provider (MSP) we usually have extensive administrative privileges on the servers on which we work, so true permissions aren't usually a problem.

But UAC is.

https://pics.ballmemes.com/curses-foiled-again-6891606.png

User Account Control (UAC) was introduced in Windows Vista and has since been expanded across multiple Windows desktop and server platforms.  At a basic level, it is an extra layer of security to make sure that changes to the operating system and it's components are only performed by administrators, and only when actually intended - think of it as an "Are You Sure You Want To Do That?"

https://memegenerator.net/img/instances/55831706/is-that-your-final-answer.jpg

UAC is an important security feature but can also be pretty annoying when you perform administrative tasks all of the time.  When I was looking for a good descriptive link to reference in the paragraph above, five of the top ten items were some variant of "here's an easy way to turn off UAC" (and three of the other five were "why you should never turn off UAC!")

The functional gotcha is the screen shown above.  The technician had not run the services console "as administrator" and as such did not have the token authority to start/stop Windows services.

This is an easy fix, however...instead of double-clicking on the services icon, simply right-click and select "Run As Administrator"

If your login is a Windows administrator. this then runs the services console with the administrator token, and you can then start/stop/manage services (as well as break/delete/befoul services - be careful!) to your heart's content.

If your login is *not* a Windows Admin, you will receive a prompt which will allow you to enter administrative credentials which will be used solely to run the application in question - it isn't a full context switch to that login for your whole session.

Note - you will often also run into this on some systems with SQL Server Management Studio and other SQL Server tools if you are running them locally on a server - if you run the tool and cannot connect to your local SQL Server instance, it may be that you haven't run the tool "as administrator."  Simply right-click the icon for the tool and, as above, select "Run as Administrator."

--

Hope this helps!

Why I Believe In In-Person Training

I was recently asked a question from a manager regarding someone on my team when I requested to send them to a training experience: 

Are there CBT based alternatives that may be more affordable that [the team member] can take in his down time?

This question is definitely a tricky one.  The question is not really “is there an alternative?” – yes, in the current age there is always an online alternative.

Are they comparable alternatives?  No.

I have done many hours of online webinars, Pluralsight courses, and remote learning in my almost twenty years as a DBA.  I would never say online learning can’t work for a specific purpose when properly applied.  (Of course we often don’t properly apply it because we try to horn it in between all of the other distractions of work and life; this is one of the benefits to going away to training, even though travel costs money – it removes the learner from the distractions of not only work but also the rest of their lives when they don’t have to go home at night.)

https://i0.wp.com/yourdogsfriend.org/wp-content/uploads/2013/11/squirrel.jpg

I still think we need to pursue online courses, such as Pluralsight subscriptions.  I have written many times on this blog about Pluralsight, PASS Virtual Chapters, and other online experiences.

Having said that, the *most useful* training experiences I have ever had come from being present in-person at an event (a conference or a class), and interacting with the instructors *and attendees* at the event, both during the event time and before and after.

I cannot list the number of times that I have gained extra knowledge or solved real-world problems by interacting with classmates during downtime between lectures or exercises or at breakfast/lunch/dinner around the event.  I had an experience at my last employer before Ntirety (actually at a SQLskills class) where something went wrong at a client while we were at class, and the class including the instructors worked with me to brainstorm and ultimately resolve this issue.

https://pics.me.me/death-brainstorm-ing-some-of-the-best-ideas-were-found-16511986.png

Standing in a classroom or conference center with other professionals gives opportunities for interactive learning that just don’t – can’t – exist with a CBT.  The CBT can spit knowledge at you, but it can’t answer your responsive questions, and it definitely can’t take meaningful detours into related topics.

…and that’s what real high-end training and learning is all about, Charlie Brown.

https://www.faithstrongtoday.com/wp-content/uploads/2016/12/Thats-What-Christmas-Is-All-About-1600x600.jpg

On the subject of cost, there are less expensive in-person Microsoft Official Curriculum (MOC) classes about very broad topics offered by instructors at dedicated training facilities (such as New Horizons) who are usually certified *instructors* (material deliverers) but not certified *database professionals* and this greatly limits the value of the interaction as well as the quality of the material itself.  Training at higher-end classes and conferences are created and delivered by persons who work with the product daily as part of their lives, not people who just talk about the product M-F 8-5.

As always, this is my $.02.

Hope this helps!

Toolbox - Exporting SSMS Results to Excel

I was working on another post when I found myself needing to dump out query results to a grid format to include in the post.  This is a very normal situation - even in my day-to-day job I send emails with grids of results (we all do right?)

The easiest thing to do is to copy-paste from the SSMS grid results, and this is what I do 90%+ of the time just as I'm sure most of you do:

Execute with Results to Grid (usually the default), and then right-click in the upper-left of the the resultset and click "Copy With Headers" and then paste the data into Excel:

Easy right?

--

The gotcha is when you are returning something more interesting, something with punctuation like a query.  When you copy-paste that your Excel turns out like this:

https://imagessure.com/thumbs/jziYuTJbBMxlZLZRfveokk5JIwpvVzi7NMi2yjIJGioHL02jChLnoqGXf-dlGY9gDVCQVMvX-DPD-BV9R4Mnjg.jpg

The ugliness is usually caused by carriage return/line feed (CR/LF) in your query - we all like nice pretty TSQL, so we use lots of newlines along the way.  For a normally delimited resultset, this results in lots of new rows as seen above.

The easiest way to deal with this is using the REPLACE function to replace the offending characters.  Carriage Return and Line Feed are two different ASCII characters, CHAR(13) and CHAR(10) respectively.

To remove them with REPLACE, your code turns out like this:

--

SELECT REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ')  /* Change LF to Space */
as QueryText
FROM <whatever DMV>

--

The code first replaces any carriage returns with a space, and then uses replace a second time to replace any line feeds with a space.  

Using the REPLACEs without any other changes results into an Excel sheet that look like this:

Note the extra columns. When copy-pasting into Excel the presence of tabs causes the column breaks seen here.

Luckily, tabs are also replaceable as ASCII characters - CHAR(9):

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

Which now looks like this:

SELECT @DownloadCount=COUNT(*)                   FROM (                   SELECT DISTINCT ma.Material_Attachment_ID                   FROM Topic_Main tm WITH (NOLOCK)                  INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                   INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                   WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                   INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID    WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                              -->Query to calculate Experts based on Topic ID (PA)

Getting closer.  Note that for some code, this may be the extent of what you need - you may not have all of that ugly whitespace.

To fix this last piece, let's try one more REPLACE to remove double spaces for single spaces:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
, '  ', ' ')  /* Change Two Spaces to Space */
as QueryText
FROM <whatever DMV>

--

Which now looks like this:

SELECT @DownloadCount=COUNT(*)          FROM (          SELECT DISTINCT ma.Material_Attachment_ID          FROM Topic_Main tm WITH (NOLOCK)         INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID          INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id          WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que          INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID  WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())               -->Query to calculate Experts based on Topic ID (PA)

--

http://www.quickmeme.com/img/98/98dd84943a5bcb086e5ec689072c0e6caa04bcc9314a37ae721268b5b798d533.jpg

Better, but why didn't it solve our problem?

Replacing two spaces with one space does *not* replace three spaces with one space, or four spaces with one space, etc.  Using this REPLACE simply turns every two spaces into one (or four spaces into two, or six spaces into three) - it *doesn't* clean up all the white space.

There are two ways to handle this - the first is a brute force method of using lots of REPLACE statements to repetitively replace two spaces with one as many times as you think is important:

--

SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(<MyStringWithLotsOfSpaces> , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') as MyStringWithHopefullyNoMoreSpaces

--

In most cases with enough REPLACE statements this will work, but it is ugly.

The second, and more elegant, method is described by Jeff Moden (blog) in an article on SQLServerCentral and leverages the ability to REPLACE multiple characters at once.

For this method, instead of replacing a double space with a single space, we will replace it with what Jeff calls an "unlikely character" such as the Backspace (ASCII CHAR(8)):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
as MyStringWithLotsOfSpacesAndNowBackSpaces

--

This makes a string that had multiple spaces now have a CHAR(8) as every other character.

Next, now that your former rows of strings ooooooooo instead looks like oxoxoxoxo, we will replace the flipped pattern, CHAR(8)+' '  with just an empty string - this removes all of the intermediate patterns:

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
as MyStringWithAtMoseOneSpaceAndAtMostOneBackSpace

--

This removes all of the "xo" pairs so now the oxoxoxoxo becomes just o (a space).   If there were originally an even number of spaces you would have had oxoxoxox and after the REPLACE you would end up with just ox (space+CHAR(8)) - this means you need one more REPLACE to strip off any remaining CHAR(8):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as MyStringWithAtMostOneSpaceBetweenEachWord

--

As Jeff shows in his article, the result play out like this:

Original String (Odd Number)	ooooooooo
Step 1	oxoxoxoxo
Step 2	oxoxoxoxo
Step 3	o
Final	o
Original String (Even Number)	oooooooo
Step 1	oxoxoxox
Step 2	oxoxoxox
Step 3	ox
Final	o

--

Now that we know how to strip out those offending whitespaces, let's go back to our original query:

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

--

We now need to wrap this in our space-remover REPLACES like this:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as QueryText
FROM <whatever DMV>

--

Is this ugly?  heck yeah!

Does it work?  HECK YEAH!

--

Original QueryText:

SELECT @DownloadCount=COUNT(*)                     
            FROM (                     
            SELECT DISTINCT ma.Material_Attachment_ID                     
            FROM Topic_Main tm  WITH (NOLOCK)                   
            INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                     
            INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                     
            WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                     
            INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID
WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                   
                     
           -->Query to calculate Experts based on Topic ID (PA)                        

Repaired QueryText:

SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID= dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA) 

--

This "repaired" text is easily paste-able into Excel into a single column, giving us an easily manageable spreadsheet.

...and all it takes is six REPLACEs!

Obviously you can *NOT* simply copy the "repaired" text into a query window and hit execute - if nothing else this method breaks inline comments which makes the code unexecutable.  It is useful now for manual analysis and comparison - for example looking at expensive queries, or pattern matching for certain object names in the code - but not for execution.

--

Do I advise you to wrap all of your text fields in six REPLACES?

https://i.imgflip.com/t362n.jpg

This is definitely an "edge" case - as mentioned above 90%+ of the time you will simply right-click, copy with headers, paste into Excel, and go on your merry way.

...but save this set of REPLACEs into your script repository - because sooner or later...you will need it.

I guarantee it.

https://memegenerator.net/img/instances/75015382/thats-the-fact-jack.jpg

Hope this helps!

T-SQL Tuesday #111 - Why Do You Do What You Do?

This month's T-SQL Tuesday is hosted by Andy Leonard (blog/@AndyLeonard) and his topic was this:

That’s the question this month: Why do you do what you do?

For me this was the easiest T-SQL Tuesday I have ever seen.  Some of you may consider this a cop-out, but here is my answer:

Why Can't I Shrink TempDB?

I know, I know....#OMGShrinkingIsBadDontEverDoThatOrElseEtcEtcEtc

We all know that there are simply times you have to shrink some files.  There are risks - blocking, significant I/O, fragmentation, and more - all of which mean you should not shrink a file willy-nilly without considering the impacts...but sometimes you are having a production issue and don't have any choice.  Similarly, sometimes you are in Dev/Test and it is simply more practical to shrink a file than to add drive space or re-architect the full solution.

In many cases it comes down to that unfortunate reality that there's a way the book/class does it, and another way we have to do it "in the field."

http://twitchlol.com/wp-content/uploads/2013/09/Battle-Plan-for-kids.jpg

Of course you also have to remember Law #9842 of being a DBA - all database systems are Production to someone.  It may be a developer or a QA team rather than an end user, but it is still PROD to them!)

--

In this story, TempDB DATA files were using almost all of the space on the drive, meaning TempLog couldn't grow, but the DATA files were mostly empty by the time it escalated to me.

Even though the files were mostly empty, my attempts to shrink them were throwing an error and the files were not shrinking:

--

Msg 5054, Level 16, State 1, Line 1
Could not cleanup worktable IAM chains to allow shrink or remove file operation.  Please try again when tempdb is idle.

--

www.deviantart.com/ieatatwaffenhouse/art/Skeletor-553757222

Googling the message led me to one of my top five authoritative sources, Paul Randal of SQLskills.com (@PaulRandal/blog).  The relevant blog post is https://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/

While my error message isn't featured in the body of the blog post, it *is* in the final comment and reply in the article.  By that information, it appears that this error reflects that certain system structures are on pages that can't be moved without system restart, which means TempDB can't be manually shrunk on this instance w/o SQL service restart.

(Paul does describe in his article the old-school fear that "shrinking TempDB leads to corruption" and how his extensive experience leads him and Microsoft to now believe that is not true *on modern versions of SQL Server (2005+)*)

--

Hope this helps!

Things I Learned at Summit v20 - Visual Studio Code

Part of a series of posts on cool stuff I learned at PASS Summit v20 (2018) - in this post we'll look at a code editing tool that I hadn't seen before - Visual Studio Code.

--

When I attend a session at a technical event - Summit/SQL Saturday/etc. - the question inevitably comes:

ScaryDBA and current PASS President Grant Fritchey at SQL Saturday Iowa City 2018

I always answer that I'm a DBA, but really we're all Developers as well - it's just what language we use.

I have been developing in T-SQL for almost 20 years, and for much of that I have been using the ultimate editing tool...Microsoft Notepad.

Yup...Notepad.

In the early days I developed in Query Analyzer (Yes, I'm old) and then Management Studio, but over time most of the work I have done is now at client sites on client servers, so I don't run Management Studio on my laptop very often (pretty much just when I write blogs and work on presentations actually).

I write my code in Notepad and then copy-paste into the client window and execute...and it has worked for me for some time.

Sure there's no Intellisense, but I lived with it.  I have had times in the past (although I haven't re-tested in some time) where Management Studio became a resource hog - especially with multiple sessions open - and made my laptop unhappy, so running little tiny Notepad was the trade-off and it worked for me.

https://i.chzbgr.com/full/1300811520/h8F002C7D/

As a "TSQL Developer" I never had the need for full-on MS Visual Studio to justify the cost to myself or my employers.

--

In several sessions at Summit v20 I saw the presenters open a different tool; you could tell from the appearance that it wasn't commercial MS Visual Studio, but it wasn't Management Studio either.

The second time I saw it, I asked the speaker what they were running, and they gave a three minute demo of an open source tool called Visual Studio Code.

https://code.visualstudio.com/

It is an open-source tool...

https://memegenerator.net/img/instances/68516476/i-felt-a-great-disturbance-in-the-force-as-if-millions-of-voices-suddenly-cried-out-in-terror-and-we.jpg

Yes, yes...OPEN SOURCE!  #TheWaveOfTheFuture

As I was saying, it is an open-source tool, and because of that it can run on multiple operating systems, including Windows, MacOS, and Linux.

It turned out that was why the speakers I saw were running it - it was a tool they could run on the MacBooks they use to present.

(Not going to get into a philosophical MS vs Mac argument here, but the next time you are at a SQL Server event, watch how many high-end presenters are using Macs - it may surprise you!)

I have played with it some since I returned from Summit and it is growing on me - the first time I opened a TSQL file it prompted me to install a SQL extension, which wasn't an issue.

I can see how this tool will be useful to all DBA's, but especially those that also developer in some other language - Visual/Python/etc. - as well as those that like Mac or Linux.

--

Hope this helps!

Things I Learned at Summit v20 - Trace Flag 4199

Part of a series of posts on cool stuff I learned at PASS Summit v20 (2018) - in this first post we'll look at a trace flag I had never even heard of, let alone deployed - 4199.

https://i.imgflip.com/15frvt.jpg

What is a Trace Flag?  According to Microsoft:

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. 

At the most basic, you can set or unset a trace flag in order to modify how SQL Server works.  This can be done globally (instance-wide) or for a particular session.

There are a LOT of trace flags, and several are widely used, such as 1204 and 1222 (Deadlock Info) and 1118 (Disable Single Page Allocations), but I haven't heard a Microsoft employee tell me I should "almost always" turn any trace flag on - until now.

--

On Tuesday of Summit week, I was in the pre-conference session put on by the Microsoft MSSQL Tiger Team - the Senior Product Managers of SQL Server Engineering (blog/@mssqltiger).

https://fico.i.lithium.com/t5/image/serverpage/image-id/31298i0D30CC780E2784AD?v=1.0

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?

https://vignette.wikia.nocookie.net/halofanon/images/d/de/BEAN-say-what.jpg/revision/latest?cb=20130302200428

Trace Flag 4199 enables query optimizer fixes that are included in CUs (and the CU breakpoint releases formerly known as "service packs") after RTM.

What does this mean?  If you aren't running with TF 4199, then your Query Optimizer (QO) is running the same as RTM, regardless of what CU level you have applied to your engine.

For example, if you are running SQL Server 2012 SP4 (11.0.7001.0) but you aren't running with TF 4199, then your QO is running the same as someone whose instance is running 2012 RTM (11.0.2100.60).  There are plenty of other things changed by applying the CU, but Query Optimizer function isn't one of them...without 4199.

That melted my mind a little, and I could tell the same was true for the attendees sitting near me - we can religiously apply CUs and fixes but unless we're running this TF (which none of us had ever even *heard* of) then you aren't fixing (improving) your Query Optimizer.

https://i.chzbgr.com/full/3519512832/h0B54C8A3/

Query Optimizer fixes are shipped as OFF by default in order to maintain compatibility and performance when CUs are deployed (so there aren't plan regressions).  Originally there was a new trace flag for each fix, so to turn on multiple fixes you would have to enable multiple trace flags.

As described at https://support.microsoft.com/en-us/help/974006/sql-server-query-optimizer-hotfix-trace-flag-4199-servicing-model, starting way back in SQL2005-SP3-CU6, SQL2008-SP1-CU7, and SQL2008R2 RTM, the ability to enable query optimizer fixes was rolled into one switch, TF 4199. 

While reading the article I tripped over one sentence:

Because trace flag 4199 is recommended only for customers who are seeing specific performance issues, customers are advised to remove trace flag 4199 after they migrate their databases to the latest compatibility level because trace flag 4199 will be reused for future fixes that may not apply to your application and could cause unexpected plan performance changes on a production system

This was interesting to see, because it ran contrary to the advice from this session and several other places I found references to enabling 4199, but this sentence reads like standard lawyerly boilerplate which is enough to make me treat it with healthy distrust.

(For this blog post I took a step I never have for a blog post before - I reached out to Pedro directly to verify my interpretation of his message to make sure I wasn't misrepresenting his content.  He graciously responded with a few comments, including the fact that he is trying to get the above highlighted sentence updated to reflect changes in how TF 4199 works under SQL 2016+ (see below)).

--

At the pre-conference session, Pedro's comments were basically that while there was a time when we all "waited for SP1" to let everyone else test, that isn't how the modern SQL Server development model works.  With the extensive testing that occurs these days as well as the "Cloud First" model that has everything live in production in Azure long before it gets to the on-premises product, there simply isn't that much risk to deploying RTM software or to deploying CUs as they come out.

IMPORTANT NOTE - There is still always (*always*) due diligence - patch DEV before PROD (we all do *that* at least, right?) - but by the time we get a new CU for an on-prem SQL Server it has already been used by thousands (hundreds of thousands) of users in Microsoft testing and on Azure.

In effect, we are already waiting for other people to test it, even without waiting at all.

https://ih0.redbubble.net/image.400523286.2984/flat,550x550,075,f.u3.jpg

While researching this topic I found another well-put opinion in one of the forum answers here by SQL MVP Gianluca Sartori (blog/@spaghettidba):

--

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.

The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the differences between old and new version are a concern on their own and having to deal with plan regression is expected anyway, so I prefer fighting with it with TF4199 enabled. <emphasis mine>

AS far as existing databases is concerned, there is only one way to know: test it. You can capture a workload on the existing setup and replay it after enabling the flag. RML Utilities can help you automate the process, as described in this answer.

Obviously, the flag affects the whole instance, so you'll have to test all the databases sitting there.

--

Another piece to the TF 4199 story is how it relates to database compatibility level.  Prior to SQL 2016, 4199 was an all or nothing - if you were running SQL 2014 SP1 with 4199 enabled, you got the Query Optimizer fixes included in 2014 SP1, regardless of the DB Compat level.

Starting in SQL 2016, DB Compat Level is relevant with 4199 enabled:

https://sqlbits.com/Downloads/550/SQLBits%20-%20Enhancements%20that%20will%20make%20your%20SQL%20database%20engine%20Part%202.pdf

On a SQL Server 2016 server, running in 130 compatibility automatically enables fixes from previous compatibility levels, even without running 4199; the purpose of 4199 in SQL 2016 is to enables fixes *after 2016 RTM* for databases in compatibility 130 - fixes before 2016 RTM are already enabled in compatibility 130.

This model is continued in 2017+ as described here.

This is consistent with another piece of info I learned at Summit v20 from the Tiger Team - start certifying for Compatibility Level, not SQL Server version.  When developing and testing, verify your code for DBCompat 130, *not* SQL Server 2016. (I may write more about that later.)

--

One other item Pedro highlighted in his email response to me was that there are multiple trace flags that Microsoft recommends for various systems, especially high-traffic/high-performance systems.  These still aren't "turn on everywhere" but are frequently useful.  

They are laid out in a pair of MS support articles:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads

Many of these items and other related high-performance configurations are detailed at:

http://aka.ms/sqlperfcenter

Thanks Pedro for your response - and thanks Tiger Team for your amazing pre-con!

https://sayingimages.com/wp-content/uploads/dont-try-to-deny-it-awesome-meme.jpg

--

Hope this helps!