More ways to discover changes in new versions of SQL Server

Back in December, I published a post entitled, “How I spot not-yet-documented features in SQL Server CTPs.” In that post, I revealed a few of the ways that I get an early jump on what’s changed between CTP and/or RC builds of SQL Server. You can do those same things if you want to see what new objects or columns have been created, or which system modules have changed, between – say – SQL Server 2014 and SQL Server 2016. This will likely be a bigger list than any of the individual sets of items I’ve posted about in our SQL Server 2016 builds post, but the same concepts apply – create a linked server to the older instance, optionally create some synonyms for easy adaptation, and go to town.

There are a few other things I check as we get closer to the final release, and they can be quite revealing about what features have made it into the product. We can also get some insight into things they tried to get in but couldn’t (for example, there are error messages and Intellisense verbiage for STRING_AGG(), which does not seem to be in the cards for RTM). I’m going to point out a few, but I’m not going to iterate through all of the things I’ve learned – this is more to serve as as a starting point so you can experiment on your own. 

Extended Events definitions

The Extended Events dialogs in Management Studio can serve as useful interfaces to poke around and find specific events or actions you’re looking for, but there isn’t a good way to use those dialogs to find new items (unless you’re Rain Man). Management Studio gets its information – as far as I can tell – from a Management Object file, XESQLPKG.MOF, which you can find in the Binn folder:

C:\Program Files\Microsoft SQL Server\<instance name>\MSSQL\Binn\

Now, what I do in this case, is I find the equivalent file from a SQL Server 2014 instance, and then compare them using just about any diff tool (my current preference is DiffMerge). There were a lot of differences between versions for this file (407!); here is one, which demonstrates a new “database dropped” event (click to enlarge):

Diffing xesqlpkg.mof

The system_health session

I’ve always liked to keep track of diagnostic information you can get from SQL Server by default, so I’ve always paid close attention to things like the system_health session to see if they evolve over time and between versions. We can use a simple EXCEPT query to see if we find any events in SQL Server 2016 that did not exist in prior versions:

-- on newer version
CREATE SYNONYM dbo.OlderVersion_Sessions 
    FOR [.\OlderVersion_LinkedServer].master.sys.server_event_sessions;
 
CREATE SYNONYM dbo.OlderVersion_SessionEvents 
    FOR [.\OlderVersion_LinkedServer].master.sys.server_event_session_events;
GO
 
SELECT e.package, e.event_id, e.name --, e.[predicate
  FROM sys.server_event_sessions AS s
  INNER JOIN sys.server_event_session_events AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
EXCEPT
SELECT e.package, e.event_id, e.name --, e.[predicate
  FROM dbo.OlderVersion_Sessions AS s
  INNER JOIN dbo.OlderVersion_SessionEvents AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
ORDER BY package, name;

In this case, system_health now captures two additional events that weren’t captured in SQL Server 2014:

You can perform similar comparisons for other Microsoft-supplied sessions, like AlwaysOn_health. Probably the default trace too, even though that should have been discontinued about 17 versions ago…

Showplan differences

SQL Server defines its entire set of execution plan semantics in a file called ShowPlanXML.xsd. Similar to the XE MOF file above, it is trivial to obtain a copy from a SQL Server 2016 installation and compare it to a 2014 version, to see what new things you might look forward to seeing in execution plans in the near future. The file is located in:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\
;For SQL Server 2014, you'll need to change 130 to 120.

This diff highlights three new logical operators – foreign key references check, put, and window aggregate (click to enlarge):

Diffing ShowPlanXML.xsd

These are obviously not the only differences (looks like 38 total), but they were the most significant to me.

You can also check for new operator icons (and probably a host of other things, too) by cracking into the SqlMgmt.dll file, located here:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\
;Again, for SQL Server 2014, you'll need to change 130 to 120.

I looked for resources with “ShowPlan” in the name using JetBrains dotPeek:

Sniffing around SqlMgmt.dll

I’m not going to give away too much here – you can probably figure out how I performed a diff between 2014 and 2016 versions of the DLL, which led me to discover three new icons that matched the three new logical operators I found above by diffing the ShowPlan XSD.

New Performance Counters

Another way to get some insight into new information that will be exposed by SQL Server is to take a look at any new rows returned by sys.dm_os_performance_counters. This query takes a look at the new version and excludes any that match. To avoid a slew of duplicates or false positives due to database-specific counters, it also filters out any instance_name that matches a database name on each respective instance (but note that if you have a database named _Total or Deprecated Hash Algorithm, you might miss some new counters).

-- on new version
CREATE SYNONYM dbo.OlderVersion_PerfCounters 
    FOR [.\OlderVersion_LinkedServer].master.sys.dm_os_performance_counters;
 
CREATE SYNONYM dbo.OlderVersion_Databases    
    FOR [.\OlderVersion_LinkedServer].master.sys.databases;
GO
 
SELECT 
  name = SUBSTRING([object_name], CHARINDEX(N':', [object_name])+1, 128),
  counter_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
  instance_name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.dm_os_performance_counters
WHERE instance_name NOT IN (SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM sys.databases UNION ALL SELECT N'mssqlsystemresource')
EXCEPT 
SELECT 
  name = SUBSTRING([object_name], CHARINDEX(N':', [object_name])+1, 128),
  counter_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
  instance_name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM dbo.OlderVersion_PerfCounters
WHERE instance_name NOT IN (SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM dbo.OlderVersion_Databases UNION ALL SELECT N'mssqlsystemresource')
ORDER BY name, counter_name, instance_name;

For me, this returned 157 rows, including a few interesting ones (you may find others more interesting):

New Error Messages

Probably one of the most revealing sources for information about changed or new features is sys.messages. This view contains error messages for features that don’t exist yet, limitations that haven’t been documented so far, and even limitations that might never be documented. For this, a simple NOT IN will suffice:

CREATE SYNONYM dbo.OlderVersion_Messages 
    FOR [.\OlderVersion_LinkedServer].master.sys.messages;
GO
 
SELECT message_id, [text]
FROM sys.messages
WHERE language_id = 1033 -- US English
AND message_id NOT IN
(
  SELECT message_id FROM dbo.OlderVersion_Messages
);

I get 1,226 rows, and I am not about to try to reproduce a set that large here. There is also a lot of garbage, like Msg 870, BPE feature switch is on!. But there is some interesting information hidden in there too. For example, if you want to find out the limitations and verbose logging messages for Stretch Database, you can add a simple filter:

AND LOWER([text]) LIKE N'%stretch%';

Now I get 20 rows that are a pretty quick read:

There is a lot more information available to you than what’s described in the marketing events and the What’s New document – you just have to know where to look, and be willing to roll up your sleeves a little. I hope I’ve given you some ammunition to do a little hunting and gathering of your own. 🙂

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.