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, and shows what is left sized by how often it happened.
The point of the page is what breaks on the next upgrade. 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 fix 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_counterssince SQL Server 2008. VIEW SERVER STATEon the instance.
Reading the chart
The chart is a circular treemap. One circle per deprecated feature, and circle area is the use count.
That makes the page read at a glance in a way the grid alone does not. A feature used four times since the instance started is a corner of the code somebody touches rarely. A feature used four million times is in the hot path of the application, and it is the one that will take real work to remove.
Do not read circle area as severity. It is frequency. A single use of a feature that is gone in the next major version matters more than a million uses of one that has been deprecated since 2008 and is still there. Severity is a judgement you make from the Description column; the chart just tells you how much of it there is.
Reading the grid
| Column | What it is |
|---|---|
| Item | The deprecated feature, named exactly as SQL Server names the counter instance. |
| Usage | How many times it has been used since the instance last started. |
| Description | What was used, what to use instead, and how often the counter increments. |
Rows are sorted by Usage, highest first.
The Description column is the part worth reading. It carries Microsoft’s own replacement advice for around two hundred features: use varchar(max) rather than text, ALTER INDEX REBUILD rather than DBCC DBREINDEX, sys.dm_db_index_physical_stats rather than DBCC SHOWCONTIG, roles rather than sys.database_principal_aliases, and so on. A handful of counters have no useful description and show an empty cell.
Read the last sentence of the description carefully. Every counter says how often it increments, and they are not the same. Some count once per compilation, some once per query, some once per use, and some once per database start. A count of 500 for a once-per-database-start counter means the instance has been up a while, not that anything is wrong.
The counters reset on restart
Every number on this page is cumulative since the instance last started, and it goes back to zero on a restart.
That has two consequences worth knowing.
- 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.
- The counts are not comparable between two instances unless they have been up for similar lengths of time. Use them to rank features within one instance, not to rank instances against each other.
What the page cannot tell you
It does not say where. The counters are instance wide. There is no database, no object and no statement behind any of these numbers, which is the honest limitation of the source rather than something the report is holding back.
Finding the code is a separate job, and the usual routes are a server side trace or an extended events session on the deprecation_announcement and deprecation_final_support events, or a search of the source with Schema Search for the syntax the description names.
How to read the report
- Check whether the page is empty. It often is, and that is the good answer.
- Read the descriptions before the numbers. A feature removed in the next version is the priority regardless of its count.
- Use the counts to size the work. Big circle means the feature is everywhere in the code.
- Check the uptime before you trust a low count. A recent restart makes the whole page look better than it is.
- 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
Data types: text ntext or image with a large count. The most common finding on any long lived database. The types still work and have been deprecated for well over a decade, but every one of them blocks online index rebuilds and forces the LOB code paths. Index LOB Columns shows which indexes are carrying them.
DATABASE_MIRRORING. Mirroring is deprecated and Availability Groups replaced it. If this instance is mirroring for real rather than referencing the feature in a script, that is a migration project rather than a code change. 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 replacements are one-for-one and the rewrite is small.
Database compatibility level 80, 90, 100, 110, 120, 130, 140. A database left on an old compatibility level. It also increments when such a database starts, so a low number here usually means the level rather than repeated changes.
'@' and names that start with '@@' as Transact-SQL identifiers or '::' function calling syntax. Syntax rather than features. Cheap to fix and easy to find.
Using OLEDB for linked servers. The SQLOLEDB provider on a linked server. Linked Servers shows which ones, and MSOLEDBSQL is the replacement.
Where the data comes from
sys.dm_os_performance_counters, filtered to the counter object whose name matches%SQL%Deprecated Features%and to counters with a value above zero.
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 are held in the report rather than read from the instance. SQL Server publishes the counter names and nothing else, so the replacement advice comes from a table of around two hundred entries maintained with the report.
Nothing is stored by this page.
Related reports
| Report | Why you would go there |
|---|---|
| Migration Planner | The rest of the upgrade assessment, and what a replacement server needs. |
| Schema Search | Finding the code that uses the syntax a description names. |
| Index LOB Columns | Which indexes carry text, ntext and image columns. |
| Linked Servers | Which linked servers still use the deprecated OLE DB provider. |
| Quick Scan Report | The broader instance review. |
| Trace Flags | The other place old decisions survive an upgrade unnoticed. |
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.
Why do the numbers change so much between two visits? Because they are cumulative counters on a live instance. They only ever go up, until a restart puts them back to zero.
Which database is using the feature? The counters do not say. They are instance wide, and there is no per-database breakdown available in the source.
Is a deprecated feature broken? Not today. Deprecated means announced for removal. The point of the page is knowing what will break before the upgrade rather than after it.
Why does one row have no description? Because SQL Server publishes that counter without useful replacement guidance. The name is still the answer to what was used.
Why is a feature I know we use missing? Either it has not been used since the last restart, or it is not one of the features SQL Server publishes a counter for. The counter set is Microsoft’s, not this report’s.