SQL Server Deprecated Features

Overview

SQL Server keeps a running count of every deprecated feature something asks it for. Almost nobody looks at it, and it is the cheapest upgrade risk assessment available: the features on this page are the ones Microsoft has said out loud will stop working, and this instance is still using them.

The Deprecated Features report reads those counters, drops the ones sitting at zero, ranks what is left, and then answers the question the counters themselves cannot: where is it.

The point of the page is what breaks on the next upgrade, and what you have to touch to fix it. A deprecated feature is not an error today. It is an error on some future version, and this is the list of things somebody will have to find and change before then.

---

Where to find it

An instance level report. Right-click the server → Instance Level Reports → Deprecated Features.

The page title reads Deprecated Features for <server name>.

---

Requirements

• No minimum version. The deprecated features counter object has been in sys.dm_os_performance_counters since SQL Server 2008.
• VIEW SERVER STATE on the instance, for the counters themselves.
• ALTER ANY EVENT SESSION as well, but only for the live capture. Without it every other part of the page works and the Watch live button is disabled with the reason on it.

Everything else degrades to a stated “unknown” rather than turning the page away. If the instance start time cannot be read, the Per day column shows dashes and says why.

---

Reading the chart

The chart is a Pareto. Bars are the lit features ranked by how often each has fired, and the line rising across them is the running total.

The line is the reason the chart exists. Usage of these features is brutally skewed, so a small number of them usually account for almost all of the volume, and the vertical marker says exactly how many: five of seventeen features carry 82% of every deprecation event here. Five is a morning’s work. Seventeen is a project nobody starts.

Bar height and bar colour are two different facts.

• Height is how often the feature has fired since the instance started.
• Colour is where to go looking for it, and it is the more useful of the two.

Colour	Band	What it means
Green	Catalog	A catalog query names the exact object. The only band whose answer is a fact rather than a text match.
Amber	Stored code	A scan of module definitions across every database finds the object and the line.
Blue	Plan cache	Not in the catalog and probably not in the database at all. Read what has actually run.
Purple	Live capture	No reliable static pattern finds this. Only the capture will.
Grey	Unknown	This instance publishes a counter this build has no entry for. The capture still finds it.

A hollow bar is drawn at a four pixel minimum, so its height is not its value. That happens whenever the honest height would be under a pixel, which on a real instance is most of the tail. Read the number in the grid rather than the bar. The alternative most Pareto charts take is to collapse the tail into one “everything else” bar, and that would hide the compatibility level rows, which frequently have a count of one and are simultaneously the most actionable finding on the page.

A mark under the axis means Database Health Monitor fires that counter itself. See below.

---

Reading the grid

Column	What it is
Feature	The deprecated feature, named exactly as SQL Server names the counter instance.
Class	Final support means support ends at the next major version. Announced means it is on notice but not slated to stop. A dash means this build has not seen which of the two events the feature raises; run the capture and it fills in.
Announced	The release that put the feature on notice, where that is not in dispute. A dash where it is.
Events	How many times it has been used since the instance last started.
Per day	The same number divided by the uptime. This is the column to compare across instances.
Moving	Events per minute right now, from two samples taken a moment apart while the page loaded. Blank means nothing happened in that window.
Where to look	The band, with the same colour the chart uses.
Found	Blank until you run Find it, then the number of places the locator found.
Replacement	What to write instead.
Ours	Marked where Database Health Monitor’s own SQL uses this feature.

Rows are sorted by Events, highest first.

Per day is the column that makes the page comparable. A count of 3 on an instance up for 400 days and a count of 3 on one up for ten minutes are the same number and completely different findings.

Moving is a needle, not a measurement. It is blank on most rows by design, so the eye goes straight to the features that fired while you were reading the page. Those are the ones that are not archaeology.

---

Finding where a feature is used

This is what the page is for. The counter is instance wide and carries no attribution at all: no database, no object, no statement, no caller. Four probes close that gap.

Select a row and press Find it, or right-click it and choose Find where this is used. The locators run on demand rather than on page load, because a cross database module scan on a large estate runs for minutes.

Catalog. One query names the exact object. This covers text, ntext and image columns, timestamp columns, the compatibility level of every database, page verify settings, mirroring, weak encryption keys, standalone rules and defaults, numbered procedures, tape backup devices, and every sp_configure setting on the list.

Stored code. A pattern scan of sys.sql_modules across every online database, returning the database, the object, the line number and the line itself.

Plan cache. The same pattern against sys.dm_exec_cached_plans and sys.dm_exec_sql_text, plus Query Store in every database that has it on. This is where a feature turns up when the code is not stored in the database at all.

Live capture. An extended events session on the deprecation_announcement and deprecation_final_support events. The only probe that works for every feature, and the only one that names the caller.

A text match is not proof. A pattern finds a word in a comment as happily as in a FROM clause, so the stored code and plan cache locators return the line and leave the judgement to you. Only the catalog band is presented as a certainty.

Modules you cannot read are reported rather than skipped. A module created WITH ENCRYPTION has no definition to search, so the scan says how many it could not read in each database instead of quietly reporting “nothing found” over the top of them.

---

The live capture

Press Watch live. The report tells you exactly what it is about to run and offers you the script instead if you would rather run it yourself.

It creates a server level event session called DatabaseHealthMonitor_Deprecation and starts it. The target is a 4 MB ring buffer in memory, so nothing is written to the instance’s disk. The session does not survive a service restart, and it is dropped when you press Stop watching, close the page, or navigate to another report.

The Captured view then shows, per statement:

Column	What it is
Feature	Matches a row in the counter grid exactly.
Class	Which of the two deprecation events fired. This is a reading rather than a judgement.
Times	Identical statements are collapsed to a count. Without that the pane fills with the same SQL Agent statement in seconds.
Database, Application, User, Host	Who did it.
Statement	The batch text, exactly as SQL Server captured it.
Ours	Marked where the statement is one of Database Health Monitor’s own.

Right-click a row in the counter grid and choose Watch just this feature to predicate the session down to one feature. On a busy instance that is the version to use: String literals as column aliases alone can be tens of thousands of events.

The capture refreshes every six seconds, which is the session’s dispatch latency. Polling faster would show an empty pane while events are still queued.

---

Some of these counts are ours

Database Health Monitor’s own queries use several of these features, and the historic collector runs on a SQL Agent schedule, so on a monitored instance those counters climb whether or not anybody wrote a line of code.

The affected rows are marked in the Ours column and with a mark under the bar on the chart, and the detail window for each one names the file and line inside the product that does it.

We show this rather than filtering it out. The counter is one query away in SSMS, and a page whose numbers disagree with the DMV would be a worse problem than the admission.

Some of it is deliberate and stays: the blocking, sessions and connections pages read master..sysprocesses on purpose, because it answers on SQL Server 2005 and returns one row per worker, which is what those pages need. Some of it is simply old and is being cleaned up.

---

The counters reset on restart

Every raw number on this page is cumulative since the instance last started, and it goes back to zero on a restart.

• An instance restarted an hour ago has an almost empty page. That is not a clean bill of health. Look again after a full business cycle, including the overnight jobs.
• Use Per day rather than Events to compare instances. The raw totals are not comparable unless the uptimes are similar.
• Read the last sentence of a description. Counters do not all increment the same way. Some count once per compilation, some once per query, some once per use, and some once per database start. A large number on a once-per-database-start counter means uptime, not a problem.

---

How to read the report

1. Check whether the page is empty. It often is, and that is the good answer.
2. Look at the cut line first. It says how many features you actually have to deal with.
3. Read the Class column before the counts. A feature at Final support breaks the next upgrade regardless of how rarely it fires.
4. Sort your work by colour, not by height. A green row can be fixed this afternoon. A purple row needs a capture before you know who is even calling it.
5. Ignore the rows marked Ours when you are sizing your own work.
6. Check the uptime before you trust a low count.
7. Take the list into the upgrade plan. This is the concrete half of an upgrade risk assessment, and Migration Planner is where the rest of it lives.

Common patterns

sysprocesses, syslogins, sysdatabases, sysobjects, sysusers and friends. The SQL Server 2005 compatibility views. Usually the top of the chart, usually partly ours, and almost always also in somebody’s monitoring script from 2009. The replacements are one-for-one: sys.dm_exec_sessions, sys.server_principals, sys.databases, sys.objects, sys.database_principals.

Data types: text ntext or image with a large count. The most common real finding on any long lived database. The catalog locator names every column, so this one goes from “somewhere” to a list of tables in one click. Index LOB Columns shows which indexes are carrying them.

Database compatibility level 100 (or 90, 110, 120, 130, 140, 150). A database left on an old compatibility level. One counter per level, and the catalog locator names the databases. It also increments when such a database starts, so a low number usually means the level rather than repeated changes.

String literals as column aliases. The SELECT 'alias' = column form. Frequently enormous, and frequently all of it is SQL Agent’s own procedures in msdb rather than anything you wrote. Purple, because no text pattern finds it honestly, so the capture is the only way to know whose it is.

Database Mirroring. Mirroring is deprecated and Availability Groups replaced it. The catalog locator says whether this instance is actually mirroring or merely referencing the feature in a script. Availability Groups and Log Shipping are the two destinations.

DBCC DBREINDEX, DBCC INDEXDEFRAG or DBCC SHOWCONTIG. An index maintenance script written before SQL Server 2005 and still running nightly. The stored code locator finds the procedure and the line, and the rewrite is one-for-one.

Deprecated encryption algorithm. RC4 or a DES variant on a symmetric key. The catalog locator names the keys.

---

Where the data comes from

• sys.dm_os_performance_counters, filtered to the counter object whose name matches %Deprecated Features% and to counters with a value above zero, read twice a moment apart so the page can say what is still moving.
• sys.dm_os_sys_info.sqlserver_start_time, for the per day rate.
• SERVERPROPERTY for the version and edition.
• The catalog views, sys.sql_modules, the plan cache and Query Store, for the locators.
• The deprecation_announcement and deprecation_final_support extended events, for the capture.

The object name is matched with a wildcard on both sides deliberately. A named instance publishes its counters under MSSQL$INSTANCENAME:Deprecated Features rather than SQLServer:Deprecated Features, so a match on the literal prefix would return nothing on every named instance.

The descriptions and replacements are held in the report rather than read from the instance, because SQL Server publishes the counter names and nothing else. That catalog covers all 255 counters published by SQL Server 2019 and 2022.

Nothing is stored by this page, and nothing is executed against the instance until you ask for it. The one thing that changes the server is the live capture, which is created only on an explicit click and dropped when you leave.

---

Related reports

Report	Why you would go there
Migration Planner	The rest of the upgrade assessment, and what a replacement server needs.
Schema Search	Searching the source yourself. The right-click menu opens it with the term filled in.
Index LOB Columns	Which indexes carry text, ntext and image columns.
Configuration Values	The sp_configure settings, several of which are on this list.
Linked Servers	Which linked servers still use the deprecated OLE DB provider.
Trace Flags	The other place old decisions survive an upgrade unnoticed.
Quick Scan Report	The broader instance review.

---

Frequently asked questions

The report is empty. Is that right? Usually, yes. It means nothing on the instance has used a deprecated feature since it last started. Check the uptime before treating it as a clean result.

Which database is using the feature? That is what Find it and Watch live are for. The counter itself does not say, and no report can get it from the counter alone.

Find it came back with nothing, but the count is thousands. Then the code is almost certainly not stored in the database. Use Watch live and it will name the application, the host and the statement.

Is a deprecated feature broken? Not today. Deprecated means announced for removal. A row marked Final support is the one that stops working at the next major version.

Why is the Class column full of dashes? Because this build only marks that column where the split has actually been observed rather than assumed. Nothing on the instance classifies a feature statically, so run the capture and the column fills in from the event that fires.

Why does the report say some of this is yours? Because it is. Several of Database Health Monitor’s own queries use these features, and it is more honest to say so than to quietly subtract them.

Does the live capture slow the instance down? It is two events with a memory target and it does not touch disk. On a very busy instance, use Watch just this feature from the right-click menu rather than watching everything.

Will the capture be left running if the application crashes? The session does not survive a SQL Server service restart, and the report offers to clear an old one if it finds it. You can also drop it yourself: DROP EVENT SESSION [DatabaseHealthMonitor_Deprecation] ON SERVER;

Why is a feature I know we use missing? Either it has not been used since the last restart, or SQL Server does not publish a counter for it. The counter set is Microsoft’s, not this report’s.

---