In every SQL Server version, Microsoft marks some features as “deprecated”. They still work, but they’ll be removed in a future version and shouldn’t be used in new development. The problem: during an upgrade, the fact that a stored procedure nobody has touched for years uses a removed syntax only shows up on migration day.
SQL Server counts on its own which deprecated features are in use. This article shows how to read those counters, how to find the source of the usage with Extended Events, and the most common examples.
Don't let old code derail your upgrade
Aryasoft finds deprecated features and incompatible code before the upgrade and prepares the remediation plan.
Deprecated vs Discontinued
- Deprecated: the feature still works but will be removed in the future. It shouldn’t be used in new code, and existing uses should be replaced in a planned way.
- Discontinued: the feature has been removed in that version; code that uses it fails. This is what stops an upgrade outright.
Each version’s deprecated and discontinued lists are published in Microsoft’s documentation; when planning an upgrade, always check the target version’s discontinued list. Details are in our upgrade guide.
Seeing Usage with Counters
SQL Server keeps a performance counter with how many times each deprecated feature has been used since the service started:
SELECT instance_name AS deprecated_feature,
cntr_value AS usage_count
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%Deprecated Features%'
AND cntr_value > 0
ORDER BY cntr_value DESC;The counters reset when the service restarts. For a meaningful picture, run the query after the server has been through at least one business cycle (for example a month-end close).
Note
The counters tell you which feature was used, not who used it. Seeing that a feature was used millions of times doesn’t help until you find the code that calls it. Use the Extended Events session below to find the source.
Finding the Source: Extended Events
The deprecation_announcement event captures features that will be removed in the future, and deprecation_final_support captures features that will be removed in the next version. Record the events together with the application, user and query text:
CREATE EVENT SESSION DeprecatedUsage ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.username, sqlserver.sql_text)),
ADD EVENT sqlserver.deprecation_final_support
(ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
sqlserver.database_name, sqlserver.username, sqlserver.sql_text))
ADD TARGET package0.event_file (SET filename = N'DeprecatedUsage', max_file_size = 50, max_rollover_files = 4)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = OFF);
ALTER EVENT SESSION DeprecatedUsage ON SERVER STATE = START;A frequently used feature can produce lots of events; on a busy system, run the session for a limited time and watch file sizes. For Extended Events session details, see our Extended Events guide.
Common Examples
| Deprecated | Replacement |
|---|---|
| text, ntext, image data types | varchar(max), nvarchar(max), varbinary(max) |
| SET ROWCOUNT with INSERT, UPDATE, DELETE | TOP (n) |
| ‘alias’ = expression column alias syntax | expression AS alias |
| The timestamp data type name | rowversion |
| DBCC DBREINDEX, DBCC INDEXDEFRAG | ALTER INDEX REBUILD / REORGANIZE |
| DBCC SHOWCONTIG | sys.dm_db_index_physical_stats |
| sp_depends | sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities |
| Compatibility views such as sys.sysprocesses, sys.sysobjects | Catalog views such as sys.dm_exec_sessions, sys.objects |
| Database Mirroring | Always On Availability Groups |
Some of these don’t live inside applications but in DBA scripts and maintenance jobs. An old maintenance plan, or a monitoring script written years ago, can be the source of the uses that show up most in the counters.
Warning
Old encryption algorithms (for example RC4 and DES) are also on the deprecated list, and some don’t work at newer compatibility levels. If keys or certificates are encrypted with these algorithms, plan to re-encrypt with AES before the upgrade; otherwise access to encrypted data can break.
Recommended Process
- Read the counters at the end of a business cycle and list the features in use.
- Use Extended Events to find the application, database and query behind each feature.
- Fix items on the target version’s discontinued list first.
- Fix your own code; for third-party applications, ask the vendor about support.
- Check the counters again after the upgrade.
Related Reading
SQL Server Upgrade Support
No surprises on upgrade day
Aryasoft’s senior DBA team finds deprecated features and incompatible code before the upgrade, identifies their sources and builds the fixes into the migration plan.
