Does your Application use deprecated features of SQL Server or SQL Azure?

Monitor the use of deprecated features by using the SQL Server Deprecated Features performance counters

In computer science, the term deprecated is used to indicate that a feature available in the current version of the software will be eliminated in the next versions. It is not recommended to use deprecated features in new projects and it is recommended to adapt existing software that uses deprecated features!

Even on SQL Server and Azure SQL Database some features are deprecated, Microsoft therefore does not recommend their use for new developments.

The list of deprecated features in SQL Server 2017 is available at this link (SQL Server 2019 does not deprecate any features).

How is it possible to identify the database deprecated features that are, at the same time, used by an Application?

We can answer this question using the DMV sys.dm_os_performance_counters which returns a row for each performance counter managed by SQL Server instance. By filtering the rows returned by the sys.dm_os_performance_counters on the object_name and asking for the string “:Deprecated Features” we obtain the list of deprecated features in the version of SQL Server or Azure SQL Database you are using.

We know the system view sys.sysdatabases is deprecated, so the following script will produce an increment of the related counter.

SELECT
  [object_name]
  ,[instance_name]
  ,[cntr_value]
FROM
  sys.dm_os_performance_counters
WHERE
  ([object_name] LIKE N'%:Deprecated Features%')
  AND ([instance_name] = 'sysdatabases');
SELECT
  *
FROM
  sys.sysdatabases
OPTION (RECOMPILE);
SELECT
  [object_name]
  ,[instance_name]
  ,[cntr_value]
FROM
  sys.dm_os_performance_counters
WHERE
  ([object_name] LIKE N'%:Deprecated Features%')
  AND ([instance_name] = 'sysdatabases');
GO

The query on sys.dm_os_performance_counters was performed twice, before and after the deprecated call to the sys.sysdatabases. The value of the counter :Deprecated Features “sysdatabases” is incremented by one for each execution.

The following query returns all deprecated counters values from the last restart of SQL Server service.

SELECT
  [object_name]
  ,[instance_name]
  ,[cntr_value]
FROM
  sys.dm_os_performance_counters
WHERE
  ([object_name] LIKE N'%:Deprecated Features%');
GO