SQL Server Health Check

How to Identify Deprecated Features in Use on a SQL Server Instance?

Updated July 23, 20262 min read

Find deprecated features before you upgrade. An open box of obsolete gadgets carries a red tag with a bold X.

What are the deprecated features in SQL Server?

Every release of SQL Server adds new features.

However, functions are also marked as deprecated by Microsoft.

It means that Microsoft will remove these functions in a future version of SQL Server (discontinued).

Starting with SQL Server 2008, Microsoft allows you to monitor the usage of deprecated functionality.

How to identify the deprecated features?

To return the deprecated features, you can use the query below.

It uses the sys.dm_os_performance_counters DMV (Dynamic Management View) to retrieve the data.

T-SQL
SELECT OBJECT_NAME
	,instance_name AS 'Deprecated Feature'
	,cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%:Deprecated%'
	AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC

The result includes a counter that lists the number of times each deprecated feature was used (since the last SQL Server restart).

Figure 1 - SQL Server deprecated features query output
Figure 1 - SQL Server deprecated features query output

Note: To run this query on SQL Server requires View Server State permission.

How to fix it?

  • Set a policy where developers are no longer allowed to use deprecated features.
  • Ask development to start replacing deprecated features with new ones.
  • Do not allow code to be deployed with deprecated features.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜