Is your application using deprecated features in SQL Server?

As and when new versions of SQL Server is released, Microsoft releases a list of features from the previous version which will not be supported in the future versions. This information is available on MSDN and Books Online (BOL). Here is the current list of features that will be deprecated in the next version of SQL Server.

Suppose I have a lot of instances running SQL Server 2008 and those databases cater to hundreds of applications. Now that I came know that several features will be deprecated in the next version, I am curious to know if those applications are using any of those features. I just want to be sure that none of the applications break when I upgrade to the next release of SQL Server. With quite a few features listed in the document, it is humanly impossible to search the application/stored procedure source code to verify if any of them are using the deprecated features.

SQL Server 2008 makes your job easy. You can get the list of deprecated features in SQL Server 2008 with a simple query.

SELECT * FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME like '%deprecated%'
order by cntr_value desc

The output lists 228 deprecated features in SQL Server 2008.

In this output the value of cntrl_value indicates the number of times the deprecated feature has been used since the last restart of the SQL Server service. In this case DATABASEPROPERTY is the one which has been used the maximum number of times (this instance has a database which is used for a legacy monitoring system).

Now that we know that there is a DMV which helps us to figure out the usage of deprecated features, it is upto to your imagination to figure out which stored procedure or application is making use of those. The following query will search all the stored procedures in a database for the instance_name values reported in the sys.dm_os_performance_counters DMV.

SELECT DISTINCT name
FROM   sys.all_objects
       INNER Join sys.dm_os_performance_counters opc
         ON Object_Definition (object_id) Like '%' + opc.instance_name + '%'
WHERE  Type = 'P'
       And is_ms_shipped = 0
ORDER BY Name

5 thoughts on “Is your application using deprecated features in SQL Server?

  1. Neil Hambly

    One way we can help find those is by use Profiler — this has an event category “Deprecation” there are 2 events “Deprecation Annoucement” & “Deprecation Final Support”, we will need to add in a splash of other profiler events (RPC:Completed, SP:Completed;Stmt:Completed) to help find the code that contains the deprecated code etc..
    Happy deprecation hunting

    1. PradeepAdiga Post author

      Thanks @Neil_Hambly ! I missed out on mentioning SQL Profiler to hunt the deprecated features.

  2. Tom Groszko

    You can also use Service Broker to identify the use of deprecated features.

    CREATE EVENT NOTIFICATION DeprecationEventNotificationSetProcessing
    ON SERVER FOR TRC_DEPRECATION
    TO SERVICE ‘DeprecationServiceProcessing’, ‘current database’;
    GO

    It is still difficult to get to the code that caused the error. The events provide us with a database a beginning and ending offset of the statement within the stored procedure or batch but no good way to get the text of the stored procedure or batch. It is possible to use sys.dm_exec_sql_text with the provided SqlHandle but there is no certainty that the handle is still valid when the procedure is activated and no way is provided to test the validity of the handle.

Comments are closed.