How to Find SQL Server Deprecated Features Used by the Application? – Interview Question of the Week #165

Question: How can I find deprecated SQL Server features that my application actually uses?

Answer: An application can keep working for years while quietly depending on syntax that is scheduled for removal. Reading a deprecation list is useful, but it doesn’t tell you which old path your application still executes. Capture real workload and inspect the events before an upgrade.

The events are sqlserver.deprecation_announcement and sqlserver.deprecation_final_support. Start a small server-level Extended Events session with an event-file target, then exercise a representative application workload. In the example below, replace the file path with a directory that already exists and to which the SQL Server service account can write. Creating an event session requires the applicable server permission.

-- Create C:\SqlXe and grant the SQL Server service account write access.
-- Change the path if your instance uses a different audit directory.
CREATE EVENT SESSION [DeprecationNotice] ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (sqlserver.database_name, sqlserver.sql_text,
            sqlserver.client_app_name)
),
ADD EVENT sqlserver.deprecation_final_support
(
    ACTION (sqlserver.database_name, sqlserver.sql_text,
            sqlserver.client_app_name)
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\SqlXe\DeprecationNotice.xel',
        max_file_size = (20),
        max_rollover_files = (4)
);

ALTER EVENT SESSION [DeprecationNotice] ON SERVER STATE = START;

After the workload runs, open the .xel target in SSMS, or read it with sys.fn_xe_file_target_read_file. The function returns XML event data, including the feature and message. Review the application name, database, SQL text and event frequency together. A single test run only proves which paths that test exercised.

SELECT object_name AS EventName,
       CAST(event_data AS xml) AS EventDetails
FROM sys.fn_xe_file_target_read_file
     (N'C:\SqlXe\DeprecationNotice*.xel', NULL, NULL, NULL);

Typical finds are old system procedures such as sp_renamedb and sp_dbcmptlevel, and compatibility views such as sysdatabases. Each one has a modern replacement, so the event tells you exactly which line of code to change. SQL Server Profiler offers the same two event classes, but Profiler itself is deprecated, so use Extended Events for new work.

Stop and drop the session when you’re done, so it doesn’t keep writing files. Which deprecated call did you discover only after you captured a real application workload?

A deprecation list is not a to-do list, it is a menu, and only a captured workload tells you what your application ordered.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.