Conga Lines: Some Misconceptions about SQL Server Updates.

There ain't no party like a SQL update party...

For SQL Server 2017 and above Microsoft stopped releasing Service Pack updates to the product and instead moved to a new servicing model; more information about that here.

Microsoft releases updates to SQL Server in two forms; Cumulative Updates (CU) and General Distribution Releases (GDR). The key difference is that cumulative updates contain all the goodies you could ever wish for; new and improvements to features, bug fixes and security patches whilst the General Distribution Releases contain just the "urgent fixes". 

This is Misconception #1 because this type of update isn't restricted to security patches only, if Microsoft need to urgently fix something (like that could ever happen!) then it can and probably will be included in a GDR as well, although most of the time it is the case technically it's not just security related.

Misconception #2, I should always install Cumulative Updates? This is only true if you want to take advantage of all the features and functionality that are included in them. Some organisations will make the decision to only install the GDR's on to their SQL Servers which is what is known as being on the GDR train, branch or conga line (as opposed to the CU train etc). 

Why would you not install Cumulative Updates? Well let's be honest, updates to SQL Server occasionally break stuff (and sometimes they really break stuff) and I don't know of a single DBA who enjoys applying updates to SQL Server and if you do, I really want to know why! Being on the GDR conga line means you have less updates to apply over a product lifecycle and also as they're not breaking/fixing as much stuff then in theory there's less chance of anything going spectacularly wrong.

Misconception #4, GDR's aren't cumulative. Although they don't contain the word cumulative, General Distribution Releases are cumulative as well. This means when you install a GDR (and I know I keep flicking between acronym and words but I'm lazy today and not in the mood for the grammar squad), where was we?? Ah yeah, when you install a GDR you also get all the fixes from previous GDR's as well, there's no need to install them one by one. It's exactly the same for the more precisely named Cumulative Updates.

Misconception #5, GDR's don't require testing. Honestly, I have heard this several times before and it's simply not true. Just because there's (in theory) less chance of something blowing up there's still a chance nonetheless. Cumulative updates might be a little different because there could be enhancements you want to test specifically sure but any update to SQL Server, whatever the type need full and thorough testing.

Misconception #6, GDR's only contain urgent fixes. Now I know I told you this earlier on in this very post BUT there's also different kind of GDR out there, the GDR-CU.

WTH is a GDR-CU?

OK, remember the conga lines? We have one line where only the GDR's are being installed and the other line that are installing all the goodies via cumulative updates (oooh, I should mention, you can only be in one of the conga lines because being in two is way too much fun for anyone to handle). 

If you're on the CU line and you install a GDR it doesn't just install the urgent fixes. It's like a CU in disguise and you'll also get any of the cumulative updates, security fixes and everything else you've missed to that point too, hence the term GDR-CU. 

If you're just installing GDR's then in this case they'll only contain the mega important stuff and a GDR is still just a GDR.

Misconception #7, I can jump between conga lines. Well in truth you can, once, and only if you're going from the ultra cautious GDR line to the all signing all dancing CU one. Once you go CU, you can't go...well you just can't change your mind go back to the GDR one. Here's why:

That link is the build list for SQL Server 2019 which is rather helpfully split into two parts; the Cumulative Update Train (conga line) and the GDR one. Take note of the version numbers for CVE-2022-29143 which at the time of writing is the very latest update for SQL 2019 and appears in both parts, for the CU party it's 15.0.4236.7 whilst over on the other side it's 15.0.2095.3 Once you install a CU...or GDR-CU you're on that line, you literally can't install an update from the GDR path anymore, it's a lower version number and those days are well and truly gone...unless you start removing stuff (and getting updates on is difficult enough).

[Sort of] Misconception #8, Automatic Updates make everything much easier. Up until now we've covered the two distinct conga lines, GDR and CU. But how do you join up? Well the update is your ticket; if you only install GDR's you're part of the GDR party and if you install a CU you're in that one. Automatic updates and their detection logic will mainly follow the same rules, if you have a CU installed you'll get the relevant updates for that path and it's the same with GDR's - if you ONLY have a GDR path update installed Windows won't put you on the CU train just for the hell of it.

But if you have a baseline version, that is one with no updates applied? This came up in a conversation the other day where a baseline install of SQL Server 2019 had automatically updated to the latest GDR but on the CU path (the GDR-CU) and some digging around in the Microsoft Update detection logic I found this:

• A WSUS / Catalog channel that uses the new detection logic that allows the CU branch version of the security release to be applied against a clean baseline instance. This is the channel of the release that will be presented to users who go to the Microsoft Update Catalog website.

It still has to be allowed, but there's still the potential for a baseline build of SQL Server to be put firmly on the CU conga line, so it's something to be aware of if your organisation prefers to be GDR release only.

Anyway, that's enough for this post, I've got a conga line to join :-)

SQL 2019: Physical reads are counted two times for read-aheads in sys.dm_exec_query_stats

Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see 

"Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values."

So if you're using these management views to look at performance metrics for your queries you're going to get incorrect results and you might be thinking the queries are doing way more work than what they're actually doing.

In order to test this in a before and after update type scenario I'm going to first force SQL Server to clean out it's buffer pool using DBCC DROPCLEANBUFFERS and the procedure cache with DBCC FREEPROCCACHE (btw, don't do this on anything else other than a sandbox environment). This will ensure the next time I run a query it will have no pages in memory and will have to retrieve the pages from disk, and I'm clearing the procedure cache as I'm going to be querying the sys.dm_exec_query_stats DMV.

Test query: 

SELECT * FROM Person.Person  

I've set SET STATISTICS IO ON so I can see after my query has ran the logical, and in this case more importantly the physical reads of my query and it shows 3 physical reads and 3866 read-ahead reads (which are still from disk):

Table 'Person'. Scan count 1, logical reads 3821, physical reads 3, page server reads 0, read-ahead reads 3866

SELECT [text] AS QueryText, last_physical_reads FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE [text] = 'SELECT * FROM Person.Person'

And I get the following results showing the last physical reads of my query was 7779, which is wrong:

I've now updated one of my test instances to CU15 and will run the same queries as before, and this time we have a much more accurate value returned from the DMV:

Now I should add there's still a difference between the statistics output and the sys.dm_exec_query_stats DMV for physical reads and to be totally honest I don't know exactly why but I've the question using #sqlhelp on Twitter and will update when I find out!