Problem
As a DBA, we may be asked by management or other stakeholders, like developers and application architects whether a new SQL Server version is stable or when a new of version of SQL Server can be considered mature and stable. Is there a way we come up with a solid approach to answer this question?
Solution
This is actually a very interesting question, we may not be able to predict the future, but we can at least look back and do some analysis of previous SQL Server releases and try to get some insights to make informed recommendations.
What I propose is that we look at the SQL Server Cumulative Updates (CUs) and see how many fixes each CU contains and normalize the number of fixes by day. For example, we can check that after SQL Server 2012 RTM was released, within about 100 days, how many CUs are released and sum the hotfixes in these CUs, and then average out the hotfixes within 100 days, so we can calculate average daily hotfixes. We can do this to SQL Server 2014/2016/2017 as well. This applies to hotfixes after each Service Pack as well (there are no more Service Packs for SQL Server 2017, all updates are done using CUs).
I am excluding SQL Server 2008R2 and earlier versions because Microsoft started to taking the Cumulative Update approach only since SQL Server 2012. Previously, SQL Server mainly used Service Packs, which aggregates all hotfixes since the last SP/RTM, as a standard patch approach. (Of course, Hotfixes are available based on needs).
Solution Algorithm
We will first retrieve SQL Server CU release history by using my previous tip “Creating a SQL Server Build and Patch Release Table”, and then we will retrieve all hotfix information using another tip “Extract Hot Fix Details from a SQL Server CU KB Articles”. Once we get all the information, we can start to do some interesting calculations, such as:
- Total / Average daily hotfixes in first [X] days since RTM (let’s say X=100 or 365)
- Total hotfix numbers for the life cycle of each Service Pack (including RTM)
- In SQL Server 2017 (the latest SQL Server), with each CU releases (every month), calculate the total # of hotfixes in each CU, see whether there is any trend.
Repository and Analysis Tables
We first create two repository tables, one is for the CU release list and another is hotfix list in each CU, and we then we create another table for analysis purpose. The two repository tables are a direct copy from the previous two tips (I put them together here for easy reading).
-- inventory table for SQL Server Version build numbers and the web URL to the each release
use MSSQLTips -- change it to your own db
drop table if exists dbo.SQLVer;
create table dbo.SQLVer (
Build varchar(32)
, Description varchar(256)
, ReleaseDate varchar(30)
, WebLink varchar(512) -- web url to the MS CU/SP release
);
-- store the fix information in CU KB article
if object_id('dbo.SQLCU_Detail', 'U') is not null
drop table dbo.SQLCU_Detail;
create table dbo.SQLCU_Detail (
VSTSBugNumber varchar(50)
, KBNumber varchar(12)
, [Description] varchar(1024)
, [FixArea] varchar(50)
, [Platform] varchar(50)
, KB_URL varchar(256)
, SQLCU_URL varchar(256)
, LogDate datetime default current_timestamp
, id int identity primary key
);
The analysis table is like the following:
use MSSQLTips
if object_id('dbo.SQLVer_Analysis', 'U') is not null
drop table dbo.SQLVer_Analysis;
create table dbo.SQLVer_Analysis (
id int identity primary key
, build varchar(20)
, [Description] varchar(200)
, ReleaseDate datetime
, [Name] varchar(100)
, ParentID int -- indicates the basis of the CU, such as RTM or SP, because we say CU1 for SP2 or CU1 for RTM,
, RootID int -- indicate original SQL Version
);
Populate Report Tables
We can easily populate the two repository tables using the PowerShell scripts in the previous two tips.
#assume the latest SQL Server PS module is installed, if not #go to https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module # first populate CU repository table Import-Module sqlserver -DisableNameCheckingfor ($i=1$i -le$i++) #extract sql server 2017 to SQL Server 2012 build history { $t = Get-SQLBuildHistory -TableNumber $i -IncludeLinkWrite-SqlTableData -ServerInstance localhost -DatabaseName MSSQLTips ` -SchemaName dbo -TableName SQLVer -InputData $t
Once we get dbo.SQLVer table populated, we need to process a few specific entries as there are two items in one column as shown below.
select * from dbo.SQLVer
where weblink is not null
and charindex(';', weblink) > 1;
We are only interested in the CU links not the Security update links, so we will do the following cleanup by removing the non-CU parts from both the [Description] and [WebLink] columns.
use mssqltips
set nocount on;
declare @build varchar(30), @description varchar(600), @ReleaseDate varchar(30), @weblink varchar(600);
declare @loc int, @loc2 int, @crlf char(2) = char(0x0d) + char(0x0a);
declare @cur cursor;
set @cur = cursor for
select * from dbo.sqlver
where charindex(';', weblink) > 0;
open @cur;
fetch next from @cur into @build, @description, @releasedate, @weblink;
while @@FETCH_STATUS = 0
begin
if @description like 'CU%'
begin
select @loc = charindex(@crlf, @description), @loc2 = charindex(';', @weblink);
update dbo.sqlver--dbo.SQLVer
set [Description] = SUBSTRING([Description], 1, @loc-1), WebLink = substring(Weblink, 1, @loc2-1)
where current of @cur;
insert into dbo.sqlver (build, [Description], ReleaseDate, WebLink)
select @build, substring(@description, @loc+2, 200), @ReleaseDate, substring(@weblink, @loc2+1, 200)
end
else
begin
select @loc = charindex(':', @description), @loc2 = charindex(';', @weblink);
update dbo.sqlver --dbo.SQLVer
set [Description] = SUBSTRING([Description], 1, @loc-1), WebLink = substring(Weblink, 1, @loc2-1)
where current of @cur;
insert into dbo.sqlver (build, [Description], ReleaseDate, WebLink)
select @build, substring(@description, @loc+2, 200), @ReleaseDate, substring(@weblink, @loc2+1, 200);
end
fetch next from @cur into @build, @description, @releasedate, @weblink;
end
close @cur;
deallocate @cur;
-- cleanup some depreciated (or replaced) CUs
delete from dbo.sqlver
where description like '%(replaced)%'
Now we will extract the hotfix list in each CU with this code:
import-module sqlserver -DisableNameChecking; $qry = @" select WebLink from dbo.SQLVer where Description like 'cu%' and weblink is not null; "@$links = invoke-sqlcmd -ServerInstance localhost -Database MSSQLTips -Query $qry$repo_dt = new-object system.data.datatable$col = new-object System.Data.DataColumn ('bugnum', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('kbnum', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('description', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('fixarea', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('platform', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('kb_url', [system.string]$repo_dt.Columns.Add($col$col = new-object System.Data.DataColumn ('cu_url', [system.string]$repo_dt.Columns.Add($colforeach ($url in $links.weblink) #{$url} { try { Write-Host $url -ForegroundColor Yellow$dt=Get-SQLCUDetail -SQLCU $url -IncludeLink; if ($dt.Columns.count -eq 7) #contains [platform] column { foreach($r in $dt.Rows) { $dr = $repo_dt.NewRow(); $dr[0]=$r[0]$dr[1]=$r[1]$dr[2]=$r[2]$dr[3]=$r[3]$dr[4]=$r[4] $dr[5]=$r[5]$dr[6]=$r[6]$repo_dt.Rows.add($drelseif ($dt.columns.Count -eq 6) #deos not contain [platfrom] but contains [fix area] col { foreach ($r in $dt.rows) { $dr = $repo_dt.NewRow(); $dr[0]=$r[0]$dr[1]=$r[1]$dr[2]=$r[2]$dr[3]=$r[3]$dr[5]=$r[4]$dr[6]=$r[5]$repo_dt.Rows.add($dr#elseif else #only 5 columns, no [platform] and [fix area] { foreach ($r in $dt.rows) { $dr = $repo_dt.NewRow(); $dr[0]=$r[0]$dr[1]=$r[1]$dr[2]=$r[2]$dr[5]=$r[3]$dr[6]=$r[4]$repo_dt.Rows.add($dr#else # Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips -SchemaName dbo -TableName SQLCU_Detail -InputData $dt; } #try catch { Write-Error $_#write to sql table dbo.SQLCU_Detail Write-SqlTableData -ServerInstance localhost -DatabaseName mssqltips -SchemaName dbo -TableName SQLCU_Detail -InputData $repo_dt
Now we need to manipulate the collected data, and populate our analysis table dbo.SQLVer_Analysis.
-- populate dbo.SQLVer_Analysis
use MSSQLTips;
insert into dbo.SQLVer_Analysis (build, [Description], ReleaseDate, [Name])
select build, [description], [ReleaseDate]
, brief= case when [Description] like 'CU%' then replace(substring([description], 1, charindex(' (KB', description)), ' Microsoft ', ' ')
when [Description] like '%RTM' then [Description]
when [Description] like '% Service Pack [1-9]%' then substring(replace([Description], 'Microsoft ', ''), 1, iif(charindex(' (',replace([Description], 'Microsoft ', ''))=0, 100,charindex(' (',replace([Description], 'Microsoft ', '')) ))
else substring([description], 1, charindex(' (KB', description)) end
from dbo.SQLVer
where (Description like '%CU[1-9]%' or [Description] like '%RTM' or [Description] like '%Service Pack [1-9]%')
and convert(decimal(2,0),left(build,2)) >=11
and [Description] not like 'MS%' and [Description] not like 'security%' and [Description] not like 'fix%' and [Description] not like 'on%'
order by build --convert(decimal(2,0),left(build,2)) asc--, -- cast(Releasedate as datetime) asc
-- a small cleanup
update dbo.SQLVer_Analysis set Name=replace(name, ' RTM', '')
where name like 'CU% 2016 RTM'
go
-- populate ParentID and RootID
declare @cur cursor;
declare @id int, @name varchar(100);
declare @first_id int, @last_id int
select top 1 @first_id = id from dbo.SQLVer_Analysis
where name like '%rtm'
order by id asc
set @cur = cursor for
select id
from dbo.SQLVer_Analysis
where id > @first_id
and name like '%rtm'
order by id
open @cur;
fetch next from @cur into @id
while @@fetch_status = 0
begin
set @last_id = @id
update dbo.SQLVer_Analysis
set RootID = @first_id
where id >= @first_id and id < @last_id;
set @first_id = @last_id
fetch next from @cur into @id
end
select @last_id = max(id) + 1 from dbo.SQLVer_Analysis;
update dbo.SQLVer_Analysis
set RootID = @first_id
where id >= @first_id and id < @last_id;
close @cur;
-- now we need to update parentid
select top 1 @first_id = id from dbo.SQLVer_Analysis
where name like '%rtm'
order by id asc
set @cur = cursor for
select id
from dbo.SQLVer_Analysis
where name like '%rtm' or name like '% Service Pack [1-9]'
and id > @first_id
order by id;
open @cur;
fetch next from @cur into @id
while @@fetch_status = 0
begin
set @last_id = @id
update dbo.SQLVer_Analysis
set ParentID = @first_id
where id > @first_id and id < @last_id;
set @first_id = @last_id
fetch next from @cur into @id
end
select @last_id = max(id) + 1 from dbo.SQLVer_Analysis;
update dbo.SQLVer_Analysis
set ParentID = @first_id
where id > @first_id and id < @last_id;
close @cur;
go
With all data collected, we can do some analysis.
We first check the total # of hotfixes within around 100 days after RTM release. Here “around 100 days” means finding the CU whose release date (T day) is closest to 100 days after RTM release date and calculate all hotfixes inside CUs up to (T day).
-- find the total # and avg daily # of hotfixes within [X] days after RTM release
use MSSQLTips;
declare @X int = 100;
; with c as (select id, rtmdate = ReleaseDate from dbo.SQLVer_Analysis where name like '%rtm')
, c2 as (select a.build, a.Description, a.releasedate, a.RootID, dist= datediff(day, c.rtmdate, a.ReleaseDate)
from dbo.SQLVer_Analysis a
inner join c
on a.parentid = c.id
)
, c3 as (select rootid, mindist=min(abs(@X-dist))
from c2
group by rootid
)
, c4 as (select c3.rootid, c2.build, c2.Description, c2.ReleaseDate, c2.dist
from c3
inner join c2
on c3.RootID = c2.RootID
and abs(@X-c2.dist) = c3.mindist ) -- c4 give out the closest CU build which was release about [X] days after RTM
, c5 as ( -- c5 should give out all builds before c4.release but after rtm
select a2.*, c4.dist
from dbo.SQLVer_Analysis a1
inner join c4
on a1.build = c4.build --) select * from c5
inner join dbo.SQLVer_Analysis a2
on a1.ParentID = a2.ParentID
and a2.build <= a1.build
)
select [Version]=case left(v.build, 2)
when '11' then 'SQL2012'
when '12' then 'SQL2014'
when '13' then 'SQL2016'
when '14' then 'SQL2017'
else 'Other'
end
, Daily_Avg_Bugs= cast(count(*)*1.0/c5.dist as decimal(5,2))
, Total_Bugs = count(*)
, Days_Since_RTM=c5.dist
from dbo.SQLCU_Detail d
inner join dbo.SQLVer v
on d.SQLCU_URL = v.WebLink
inner join c5
on c5.build = v.Build
and v.Description like 'CU%'
group by left(v.build, 2), c5.dist
order by 1
go
The result is:
Find the Average Daily Error Rate for each Service Pack (including RTM):
--2. find the avg error rate among each SPs
use MSSQLTips;
select p.Build, p.Description, count(*) as Total_Errors, max(datediff(day, p.releasedate, a.releasedate)) as [Days]
, Daily_Errors = cast(count(*) *1.0 / max(datediff(day, p.releasedate, a.releasedate)) as decimal(5,2))
from dbo.SQLVer_Analysis a
inner join dbo.SQLVer_Analysis p
on a.ParentID = p.id
inner join dbo.sqlver v
on v.Build = a.build
and a.Description = v.Description
inner join dbo.SQLCU_Detail d
on d.SQLCU_URL = v.WebLink
group by p.build, p.Description
order by p.build;
The result is:
If we use the daily errors for post-RTM hotfixes, we can see, SQL Server 2012 is lowest at 0.41 while SQL Server 2017 seems to be highest at 1.18.
I have two personal explanations for this trend:
- SQL Server becomes more complex with more features, thus likely more bugs.
- The more quickly a new SQL Server version is released, more errors it may contain. SQL Server 2014 and SQL Server 2016 have similar daily error rates because they were each released two years after the previous version. SQL Server 2017 has more daily errors when it has only a 1-year gap from the previous version.
Calculate the number of hotfixes in each CU of SQL Server 2017:
use MSSQLTips;
select CU=substring(v.Description,1,3), Total_Hotfix=count(*)
from dbo.SQLCU_Detail d
inner join dbo.SQLVer v
on v.WebLink = d.SQLCU_URL
and v.Build like '14%'
group by substring(v.Description,1,3)
order by [CU]
go
The result is:
No surprise, CU1 has the most hotfixes, doesn’t this conform to people’s “superstition” that it is better to wait for the first CU (or SP) before putting the new version into production?
Summary
In this tip, we have explored a way to do some SQL Server CU / Hotfix release history analysis based on public data from Microsoft.
Different calculations with different perspectives may give us different insights into our SQL Server stability concerns. But generally speaking, the later you adopt a new version, the more stable the product is as this can be shown in the daily average bugs data (Figure 3).
Of course, this method of calculation is not necessarily satisfactory, for example, security hotfixes are not counted, hotfixes inside Service Pack are not calculated, etc. Also, the hotfixes do not necessarily carry the same weight of importance, for example, some hotfixes are just about adding a new feature instead of really fixing an issue.
Next Steps
You can build your own questions and try to get the answer from the data. For example, you can count the hotfixes of each FixArea (i.e. SSRS, SSAS, SQL Server Database Engine, etc.) and compare them between different Service Packs or SQL Server versions.
This tip is based on the previous tips and you may review them here.
