Product articles SQL Prompt SQL Code Analysis
Tracking use of Deprecated SQL Server…

10 July 2020

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.

10 July 2020

Tracking use of Deprecated SQL Server Features

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

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. 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.

Checking for use of deprecated syntax during development

SQL Prompt’s code analysis feature allows you to check code as you develop it, and SQL Change Automation allows you to check the code of a build. SQL Prompt can alert you to deprecated syntax in code and has a section of deprecated syntax rules to deal with it.

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 also includes …

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

Checking for use of deprecated syntax on a working database

In addition to this, you can easily check if deprecated syntax is being used in a working database with:

Which, in my case produces the rather shaming …

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.

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.

Who and what is using the deprecated syntax?

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 reports.

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

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.

 

You may also like