How to Identify Deprecated Features in a SQL Server Instance
Category: Reliability
What are Deprecated Features in Sql server?
Each version of SQL Server adds new features.
However, functions are also marked as deprecated by Microsoft.
This means that Microsoft will remove the theses in a future version of SQL Server (discontinued).
Starting with SQL Server 2008, Microsoft allows you to track the usage of deprecated functions.
How to identify Deprecated Features?
You can use the following query to return to Deprecated Features.
It uses the sys.dm_os_performance_counters DMV (system dynamic management view) to retrieve the data.
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 has been used (since the last restart of SQL Server).
Note
VIEW SERVER STATE permission is required to run this query in SQL Server.
How to fix
- 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.
