Product articles SQL Change Automation SQL Code Analysis
Tracking use of Deprecated SQL Server…

Tracking use of Deprecated SQL Server Features

Phil Factor explains how to use Dynamic Management Views and Extended Events to track use of deprecated SQL Server syntax on working SQL Server databases, as well as SQL Prompt and SQL Change Automation to detect its use during database development.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

SQL Server is an evolving product, tugged in many different directions by the demands of its users. Just as the new is introduced, so old features and syntax must go. This is hard for applications that span a range of installed SQL Servers, ranging from 2005 upwards. The advice from Microsoft is to avoid deprecated features, just so they can be removed in a future version of SQL Server, at the point where the effort of maintaining the feature becomes a burden. There are two different severities of deprecation. Firstly, there is deprecation_announcement, where the feature is added to the ‘deprecated’ list, and then the deprecation_final_support, where the deprecation is imminent in the next release.

How seriously you take warnings about deprecation depends on many factors, so there are no hard and fast rules. Deprecated features are “maintenance mode only”; they won’t be changed or improved, so are unlikely to work well with new features. Occasionally, though rarely, a deprecated SQL Server feature may be discontinued, meaning that it is no longer available in that SQL Server version. Of course, if you upgrade to that version, any code that relies on that old feature will break. Redgate sometimes has to continue to use features announced as deprecated, just so a wider range of SQL Server releases can be spanned by a tool. However, there is always a point where the current alternative needs to be used instead. It is good to be reminded of an imminent problem, but the decision depends on your application.

How to find databases that use deprecated SQL

You can easily check if deprecated syntax is being used in a working database using the SQLServer:Deprecated Features object in the sys.dm_os_performance_counters DMV:

Which, in my case produces the rather shaming …

Using the SQLServer:Deprecated Features object

This lists all deprecated features that have been encountered since SQL Server last started, along with the number of times each item has been encountered. There are two hundred and fifty-three of these deprecated features in SQL Server 2016 & 2017, mainly because it lists every deprecated system view, system stored procedure and system function separately, and lists deprecated collations too. A useful summary is provided in the documentation for the SQL Server, Deprecated Features Object which does its best to provide an alternative for each type.

This gives you a good overview, but what if you want to know what database is doing this? What user is executing this antiquated syntax? What is the SQL being executed? It is time to put on the PPE and get stuck into an Extended Events session.

The DeprecatedFeatures Extended Events session

When I created this, I just used the Extended events wizard to generate the create statement. As well as the data about the two relevant events deprecation_announcement (features whose deprecation has been announced) and deprecation_final_support (features whose deprecation is imminent), I want to know about the database name, SQL username, NT username, and the actual SQL Text. These are all classed as actions, in extended events, rather than event data. I’ll just put the data into a ring buffer because I don’t need anything more permanent.

Once we’ve got this working, we can see the events flowing in by choosing ‘Watch Live Data’ from the right-click context menu of the browser pane for the event session you’ve just created.

Using the DeprecatedFeatures Extended Event

You will see something like this. In my case, Redgate’s SQL Monitor was one of the critters unlucky enough to be caught in the headlamps.

Deprecation syntax report for a database

Which users are executing the deprecated SQL?

So now that we have the event session working to our satisfaction, we can devise the SQL Query that will drill into the detail or generate a ‘deprecated syntax’ report for a database.

You can, of course, get a report of how often each event is happening with this

Checking for use of deprecated syntax during development

You should always avoid use of deprecated syntax in new developments, and remove it when appropriate during ongoing development and maintenance of existing applications. SQL Prompt’s code analysis feature is a SQL syntax checker that sniffs out ‘code smells’ in your SQL, as you develop it and has a section of deprecated syntax rules to deal with issues such as using deprecated System tables or views, or specifying column aliases using strings:

DEP001 specifying a Table hint without using the WITH keyword
DEP002 using WRITETEXT, UPDATETEXT and READTEXT statements with TEXT and NTEXT
DEP003 using the ALL keyword in a GROUP BY clause (usually done to return empty groups eliminated ay a where clause as well)
DEP006 the SETUSER statement to allow a member of the sysadmin fixed server role or the owner of a database to impersonate another user
DEP007 specifying TAPE as backup device
DEP009 using DBCC DBREINDEX statement, which rebuilds an index for a table, or all indexes defined for a table.
DEP011 DBCC INDEXDEFRAG, which defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes
DEP012 DBCC SHOWCONTIG, which displays fragmentation information for the data and indexes of the specified table or view.
DEP013 using one of the deprecated SET options such as SET FMTONLY, SET REMOTE_PROC_TRANSACTIONS, SET ANSI_NULLS OFF, SET ANSI_PADDING OFF, SET CONCAT_NULL_YIELDS_NULL OFF, SET OFFSETS
DEP014 using SET ROWCOUNT to limit results rather than TOP xxx
DEP015 specifying the READONLY and READWRITE options
DEP016 using the TORN_PAGE_DETECTION option of ALTER DATABASE
DEP018 using the ALL option in a GRANT/DENY/REVOKE statement
DEP019 using deprecated System tables or views
DEP020 using numbered procedures
DEP021 specifying column aliases using strings
DEP022 using DROP INDEX with two-part name
DEP025 using a deprecated System stored procedure
DEP026 specifying columns with Three-part and four-part references in a SELECT list
DEP027 using a deprecated System function such as fn_servershareddrives

SQL Change Automation incorporate the code analysis rules engine so that you can check the code of a build. It includes a few additional rules…

DEP004 COMPUTE and COMPUTE BY clauses are deprecated
DEP005 FASTFIRSTROW table hint is deprecated
DEP008 PASSWORD/MEDIAPASSWORD options in BACKUP/RESTORE statement are deprecated
DEP010 DBCC CONCURRENCYVIOLATION is deprecated.
DEP017 Non-ANSI join (*= or =*) is used

Conclusion

If you are using deprecated features of SQL Server, that’s fine, as long as you are aware of the issues such as the maintenance repercussions. I like to use old-fashioned T-SQL defaults and rules myself, so I’m no saint. If you do so, it is wise to be able to track them both in development (SQL Prompt), and during the build (SCA). You can then go on to track them in the live database, either from using performance counters or, for the details, extended events. This last method is excellent when preparing a database to be run on a new version of SQL Server, because you get to see the SQL where it is being used.

 

Tools in this post

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more