How to identify deprecated features in use on a SQL Server instance?

Category: Reliability

Item:  Deprecated features in use

What is the deprecated feature in the 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 theses 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 ( system dynamic management view) to retrieve the data.

  1. SELECT OBJECT_NAME,
  2. instance_name AS 'Deprecated Feature',
  3. cntr_value AS 'Number of Times Used'
  4. FROM sys.dm_os_performance_counters
  5. WHERE OBJECT_NAME LIKE '%:Deprecated%'
  6. AND cntr_value > 0
  7. 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).

Show the result of the query, some rows and collumns with data.
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 into new ones.
  • Do not allow code to be deployed with deprecated features.

More information

Microsoft – Deprecated Database Engine Features in SQL Server 2017
Microsoft – Documentation for the sys.dm_os_performance_counters

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.