More ways to discover changes in new versions of SQL Server

Aaron Bertrand

Published On: March 30, 2016

Categories: Execution Plans, SQL Server 2016, Extended Events 0

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):

xesqlpkg_mof_-1Diffing 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;
SELECT e.package, e.event_id, --, 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 = N'system_health'
SELECT e.package, e.event_id, --, 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 = 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:

package event_id name
sqlos 47 process_killed
sqlserver 55 sql_exit_invoked

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):

showplanxml_xsd_-1Diffing 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:

showplanicons-1Sniffing 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;
  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')
  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):

name counter_name instance_name
Columnstore Delta Rowgroups Closed _Total
Columnstore Delta Rowgroups Compressed _Total
Columnstore Delta Rowgroups Created _Total
Columnstore Segment Cache Hit Ratio _Total
Columnstore Segment Cache Hit Ratio Base _Total
Columnstore Segment Reads/Sec _Total
Deprecated Features Usage Database compatibility level 120
Deprecated Features Usage Deprecated hash algorithm
Query Store Query Store CPU usage _Total
Query Store Query Store logical reads _Total
Query Store Query Store logical writes _Total
Query Store Query Store physical reads _Total

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;
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:

message_id text
14804 A database credential with the name '%s' was not found. A database credential must be created before stretching a database with it.
14806 The identity specified for the database credential '%s' is invalid. The identity must be a valid username for the remote stretch server administrator.
14807 The secret specified for the database credential '%s' is invalid. The secret must be a valid password for the remote stretch server administrator.
14811 Remote server '%s' is not an Azure SQL Database V12 (or higher) server. A database can only be stretched to an Azure SQL Database V12 (or higher) server.
14819 Stretch operation failed due to an internal error.
14820 The stretch code generator output is corrupted. Stretch code generation and remote table provisioning will be re-tried.
14836 Cannot reconcile stretched indexes for database '%.*ls' because REMOTE_DATA_ARCHIVE is not enabled on the database.
14837 Cannot reconcile indexes for stretched table '%.*ls' because migration is not outbound on the table.
14853 Function '%.*ls' cannot be used as Stretch filter predicate because it does not meet necessary requirements.
14858 Cannot queue more stretch tasks. Please wait for remaining tasks to be finished and try again later.
14863 Stretch was disabled after migration started. Please enable stretch and retry.
14866 Attempted unlinking of the stretched table failed. If this table isn't dropped, please retry the operation of setting REMOTE_DATA_ARCHIVE to OFF on the table.
14883 Stretch remote table creation failed without specific exception.
14884 Unable to load the stretch filter predicate for table "%.*ls".
14896 Reconciliation proc %.*ls brought down the local batch ID for the stretch table '%.*ls' of database '%.*ls' from %I64d to %I64d.
14897 Cannot run the procedure %.*ls for table '%.*ls' since it is not stretched.
14901 Running an admin %ls operation on stretched table with ID %d using %ls hint.
14904 Reauthorizing stretched database '%.*ls' with the remote database has completed successfully. Now reconciling remote tables and/or remote columns...
14905 Reconciliation of remote tables and/or remote columns for stretched database '%.*ls' has completed successfully.
14914 The edition for database '%ls' is invalid. Only the Azure SQL Stretch edition is supported as a target for Stretch database.

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 (@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, 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.


Fighting Downtime and Slowdowns