It’s amazing how quickly some features can become deprecated in the world of software.

This article presents two methods to see whether deprecated features are being used in a SQL Server instance.

Method 1 – sys.dm_os_performance_counters

Perhaps the quickest way to do it is use the sys.dm_os_performance_counters system dynamic management view. Among the many things you can do with this view, is get a list of deprecated features, along with how many times they’ve been used since SQL Server started.

Here’s an example taken from my article Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance:

SELECT
  RTRIM(instance_name) 'Feature', 
  cntr_value 'Usage Count'
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Deprecated Features'
AND cntr_value > 0;

Result:

+-----------------------------------+---------------+
| Feature                           | Usage Count   |
|-----------------------------------+---------------|
| String literals as column aliases | 1             |
| TIMESTAMP                         | 1             |
| numbered_procedures               | 1             |
| sysdatabases                      | 3             |
| sp_db_vardecimal_storage_format   | 1             |
| XP_API                            | 2             |
+-----------------------------------+---------------+

In this example, I return only those rows where the object_name column has a value of SQLServer:Deprecated Features, and the cntr_value column’s value is greater than zero.

You could remove the cntr_value column from this query to return all deprecated features, including those that haven’t been used.

Method 2 – Use Extended Events

Another way to do it is to use extended events. This is a more comprehensive method, and you can use it to create a log file that contains all instances of deprecated feature usage, along with the SQL statement used that contains the deprecated feature, who ran it, the database it was run against, etc. Below is an example.

Create the event:

CREATE EVENT SESSION [Deprecation Events] ON SERVER 
ADD EVENT sqlserver.deprecation_announcement(
    ACTION(
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.username
        )
),
ADD EVENT sqlserver.deprecation_final_support(
    ACTION(
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.username
    )
)
ADD TARGET package0.event_file(
    SET filename=N'/var/opt/mssql/tmp/DeprecationEvents.xel'
    )
WITH (
    TRACK_CAUSALITY = ON
    );

Start the event:

ALTER EVENT SESSION [Deprecation Events] ON SERVER STATE = START;

Run some deprecated code:

SELECT * FROM sys.sql_dependencies;

Read the log:

SELECT
    EventXml.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
    EventXml.value('(action[@name="username"]/value)[1]', 'nvarchar(256)') AS username,
    EventXml.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
    EventXml.value('(action[@name="sql_text"]/value)[1]', 'varchar(4000)') AS sql_text,
    EventXml.value('(@name)[1]', 'varchar(50)') AS event_name,
    EventXml.value('(data[@name="feature"]/value)[1]', 'varchar(255)') AS feature,
    EventXml.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS message
FROM (SELECT CAST(event_data AS XML) AS XmlEventData
    FROM sys.fn_xe_file_target_read_file (
        '/var/opt/mssql/tmp/DeprecationEvents*.xel', 
        null, 
        null, 
        null
    )) AS EventTable
CROSS APPLY EventTable.XmlEventData.nodes('event') AS q(EventXml);

Result (using vertical output):

timestamp     | 2019-10-31 04:03:06.5280000
username      | sa
database_name | Test
sql_text      | SELECT * FROM sys.sql_dependencies;
event_name    | deprecation_announcement
feature       | sql_dependencies
message       | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.

For a more detailed discussion on this method, see Using Extended Events to Log Deprecated Features Being Used in a SQL Server Instance. That article includes the same example, but with a bit more detail.