In SQL Server, the sys.dm_os_performance_counters system dynamic management view returns the performance counters maintained by the server.

One of the many things you can do with sys.dm_os_performance_counters is return a list of deprecated features in the current instance of SQL Server. You can also use this list to see how many times a deprecated feature has been used since SQL Server was started.

This is quite possibly the quickest way to find out if you’re using a deprecated feature in SQL Server.

The view returns a row per performance counter maintained by the server. The SQLServer:Deprecated Features object provides a counter to monitor the features designated as deprecated. It has a cntr_value column, which provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started.

Therefore, by running a query against this view, we can return all deprecated features along with a count of how many times each one was encountered since SQL Server last started.

Example 1 – Return all Deprecated Features Encountered Since SQL Server Startup

We can therefore use the following query to find all deprecated features that have been encountered since SQL Server last started, along with the number of times each item has been encountered:

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             |
+-----------------------------------+---------------+

Here I used the RTRIM() function (you could also use TRIM()) to trim the white space on the right of the feature. I did this because the instance_name column uses a nchar(128) data type, which causes the column to be padded out to 128 characters. By trimming the whitespace, the example can now fit on the webpage without requiring horizontal scrolling.

Example 2 – Return Features by Name

If you have a specific feature that you’re interested in, you can always search for it by name:

SELECT 
  TRIM(instance_name) 'Feature', 
  cntr_value 'Usage Count'
FROM sys.dm_os_performance_counters   
WHERE object_name = 'SQLServer:Deprecated Features'
AND instance_name LIKE '%timestamp%';

Result:

+------------------------------------+---------------+
| Feature                            | Usage Count   |
|------------------------------------+---------------|
| INSERT NULL into TIMESTAMP columns | 0             |
| TIMESTAMP                          | 1             |
+------------------------------------+---------------+

More Detail about Each Deprecated Feature

The above method is a nice quick way to see if deprecated features are still being used in your application. One downside is that it doesn’t provide a great deal of information about what to do about it, or even which SQL statements included the deprecated feature, let alone which user ran it.

You can use extended event sessions to return more information, such as the offending SQL statements, the user who ran it, the time it was run, and more. You can even capture it all in a log file so that you can refer to it later. See Using Extended Events to Log Deprecated Features Being Used in a SQL Server Instance for an example.

Also see Microsoft’s article Deprecated Database Engine Features in SQL Server 2017 for recommendations on how to deal with each deprecated item. That list is exactly the same as the one for SQL Server 2016.