With each new release of SQL Server comes a list of new features, along with a list of features that have been deprecated. These deprecated features are not immediately disabled or removed, but they are considered to be features not supported in a future version of SQL Server, and should no longer be used. Ideally, once something becomes deprecated you should begin removing it from your code so that it doesn’t break one day down the road when you upgrade SQL Server. Ideally. Realistically, though, ain’t nobody got time for that.
Plus, how are you going to find out which, if any, of those deprecated features are actually being used on your SQL Servers?
You just have to ask SQL Server. Yep, as it does for so many other things, SQL Server is keeping track of this behind the scenes. Simply run the following query against sys.dm_os_performance_counters to get a list of deprecated features and how many times each of them has been used.
SELECT object_name, instance_name, cntr_value
WHERE counter_name = 'Usage'
According to the MSDN doc on the Deprecated Features Object, it:
provides a counter to monitor the features designated as deprecated. In each case the counter provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started.
That MSDN doc also has a description of what each of the deprecated features is.
Now, this won’t tell you where the deprecated feature is being called from, but it will tell you which ones are being called. This could be extremely useful when planning a migration to a new SQL Server release. Especially in the case where you are actively using something that has been confirmed to be disabled in the release you are moving to.