10 July 2020

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Phil Factor

10 July 2020

Tracking use of Deprecated SQL Server Features

Phil Factor explains how to use Dynamic Management Views and Extended Events to track use of deprecated SQL Server syntax on working SQL Server databases, as well as SQL Prompt and SQL Change Automation to detect its use during database development.

Guest post

SQL Server is an evolving product, tugged in many different directions by the demands of its users. Just as the new is introduced, so old features and syntax must go. This is hard for applications that span a range of installed SQL Servers, ranging from 2005 upwards. The advice from Microsoft is to avoid deprecated features, just so they can be removed in a future version of SQL Server, at the point where the effort of maintaining the feature becomes a burden. There are two different severities of deprecation. Firstly, there is deprecation_announcement, where the feature is added to the ‘deprecated’ list, and then the deprecation_final_support, where the deprecation is imminent in the next release.

How seriously you take warnings about deprecation depends on many factors, so there are no hard and fast rules. Deprecated features are “maintenance mode only”; they won’t be changed or improved, so are unlikely to work well with new features. Occasionally, though rarely, a deprecated SQL Server feature may be discontinued, meaning that it is no longer available in that SQL Server version. Of course, if you upgrade to that version, any code that relies on that old feature will break. Redgate sometimes has to continue to use features announced as deprecated, just so a wider range of SQL Server releases can be spanned by a tool. However, there is always a point where the current alternative needs to be used instead. It is good to be reminded of an imminent problem, but the decision depends on your application.

How to find databases that use deprecated SQL

You can easily check if deprecated syntax is being used in a working database using the SQLServer:Deprecated Features object in the sys.dm_os_performance_counters DMV:

SELECT instance_name AS Deprecated_feature, cntr_value AS Usage_Count

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Deprecated Features' AND cntr_value > 0

ORDER BY cntr_value DESC;

Which, in my case produces the rather shaming …

This lists all deprecated features that have been encountered since SQL Server last started, along with the number of times each item has been encountered. There are two hundred and fifty-three of these deprecated features in SQL Server 2016 & 2017, mainly because it lists every deprecated system view, system stored procedure and system function separately, and lists deprecated collations too. A useful summary is provided in the documentation for the SQL Server, Deprecated Features Object which does its best to provide an alternative for each type.

This gives you a good overview, but what if you want to know what database is doing this? What user is executing this antiquated syntax? What is the SQL being executed? It is time to put on the PPE and get stuck into an Extended Events session.

The DeprecatedFeatures Extended Events session

When I created this, I just used the Extended events wizard to generate the create statement. As well as the data about the two relevant events deprecation_announcement (features whose deprecation has been announced) and deprecation_final_support (features whose deprecation is imminent), I want to know about the database name, SQL username, NT username, and the actual SQL Text. These are all classed as actions, in extended events, rather than event data. I’ll just put the data into a ring buffer because I don’t need anything more permanent.

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something

(

SELECT * FROM sys.server_event_sessions

WHERE server_event_sessions.name = 'DeprecatedFeatures'

)

DROP EVENT SESSION DeprecatedFeatures ON SERVER;

Create EVENT SESSION DeprecatedFeatures ON SERVER

ADD EVENT sqlserver.deprecation_announcement(

ACTION(sqlserver.database_name,sqlserver.username,sqlserver.session_nt_username,sqlserver.sql_text)),

ADD EVENT sqlserver.deprecation_final_support(

ACTION(sqlserver.database_name,sqlserver.username,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.ring_buffer

--we don't need a more permanent record or a bucket count

WITH (STARTUP_STATE = OFF);

ALTER EVENT SESSION DeprecatedFeatures ON SERVER STATE = START;

Once we’ve got this working, we can see the events flowing in by choosing ‘Watch Live Data’ from the right-click context menu of the browser pane for the event session you’ve just created.

You will see something like this. In my case, Redgate’s SQL Monitor was one of the critters unlucky enough to be caught in the headlamps.

Which users are executing the deprecated SQL?

So now that we have the event session working to our satisfaction, we can devise the SQL Query that will drill into the detail or generate a ‘deprecated syntax’ report for a database.

DECLARE @Target_Data XML =

(

SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata

FROM sys.dm_xe_session_targets AS xet

INNER JOIN sys.dm_xe_sessions AS xes

ON xes.address = xet.event_session_address

WHERE xes.name = 'DeprecatedFeatures'

AND xet.target_name = 'ring_buffer'

);

SELECT

CONVERT(datetime2,

SwitchOffset(CONVERT(datetimeoffset,the.event_data.value('(@timestamp)[1]', 'datetime2')),

DateName(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,

the.event_data.value('(@name)[1]', 'nvarchar(40)') AS [Deprecation type],

the.event_data.value('(data[@name="feature"]/value)[1]', 'nvarchar(100)') AS [Notice],

--the.event_data.value('(data[@name="feature"]/value)[1]', 'nvarchar(40)') AS [Feature],

the.event_data.value('(data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],

the.event_data.value('(action[@name="database_name"]/value)[1]', 'sysname') AS [Database],

the.event_data.value('(action[@name="username"]/value)[1]', 'sysname') AS Username,

the.event_data.value('(action[@name="session_nt_username"]/value)[1]', 'sysname') AS [Session NT Username],

the.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [SQL Context]

FROM @Target_Data.nodes('//RingBufferTarget/event') AS the (event_data)

You can, of course, get a report of how often each event is happening with this