Identify Deprecated SQL Server Code with Extended Events

By:   |   Comments (6)   |   Related: > Deprecated Features


Problem

We are migrating our enterprise from SQL Server 2000/SQL Server 2005 to SQL Server 2008.  I have heard that many features are deprecated in SQL Server 2008.  How do I check our enterprise SQL Server code to find out if it is free from the deprecated features listed in SQL Server 2008?  Do we have to manually check each line of code? Is there an automated way of identifying the deprecated SQL code?

Solution

In SQL Server 2008, deprecated features can be identified using two different methods. First is with SQL Profiler and second is with Extended Events. Extended Events are a new mechanism introduced in SQL Server 2008 that allows more granular monitoring than was impossible in prior SQL Server versions. In this tip, we will use Extended Events because they are simple to setup and provide great deal of insight into the database engine.

First, we need to take a look at the events that will help us identifying the deprecated features. We will also need to look at what columns are available to track for these events. Let's jump into this code:
 
--Find the event name that allows tracking the deprecation features
SELECT xo.name, xo.description, *
FROM sys.dm_xe_objects xo INNER JOIN sys.dm_xe_packages xp
ON xp.[guid] = xo.[package_guid]
WHERE xo.[object_type] = 'event' AND xo.name LIKE '%deprecation%'
ORDER
BY xp.[name];
GO

/*
Name Description
deprecation_announcement Occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release of SQL Server.
deprecation_final_support  Occurs when you use a feature that will be removed from the next major release of SQL Server.
*/

--Find the additional columns that can be tracked
SELECT *
FROM sys.dm_xe_objects xo INNER JOIN sys.dm_xe_packages xp
ON xp.[guid] = xo.[package_guid]
WHERE xo.[object_type] = 'action'
ORDER BY xp.[name];
GO

--Find the columns that are  available to track for the
--deprecation_announcement event
SELECT * FROM sys.dm_xe_object_columns
WHERE [object_name] = 'deprecation_announcement';
GO

--Find the columns that are  available to track for the
--deprecation_final_support event
SELECT * FROM sys.dm_xe_object_columns
WHERE [object_name] = 'deprecation_final_support';
GO


The code snippet below creates the session and uses the the asynchronous filetarget syntax which should be used as a best practice to avoid the overhead of the Ringbuffer syntax. Ringbuffer uses 4 MB of data only and if additional data is captured, then the ringbuffer data is overwritten.
 
--Create the event
CREATE EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER

--We are looking at deprecated features
ADD EVENT sqlserver.deprecation_announcement

(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,
sqlserver
.tsql_stack, sqlserver.username, sqlserver.client_hostname, sqlserver.database_context)
),

--We are looking at discontinued features
ADD EVENT sqlserver.deprecation_final_support
(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack
))

--As a best practice use asynchronous file target, reduces overhead.
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\Monitor_Deprecated_Discontinued_features.etl', metadatafile='c:\Monitor_Deprecated_Discontinued_features.mta')
GO

--Now start the session
ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER
STATE
= START;
GO


Let's run a test script that has some deprecated features in SQL Server 2008.
 
USE AdventureWorks;
GO

--String alias
SELECT 'FullName' = FirstName + ' ' + MiddleName + '. ' + LastName
FROM
Person.Person
GO

--# table name
CREATE TABLE #(c1 INT)
GO

--3 part column name
SELECT TOP 1 AdventureWorks2008.Person.Person.LastName, *
FROM Person.Person
SELECT TOP 1 Person.Person.LastName, *
FROM
Person.Person
GO

--use :: for function calls
SELECT * FROM ::fn_virtualfilestats(2,1)
GO

--using hints without WITH keyword
SELECT TOP 1 Person.LastName, * FROM Person.Person (NOLOCK)
GO

--use fn_get_sql
DECLARE @Handle VARBINARY(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = @@SPID AND request_id = 0;
SELECT * FROM sys.fn_get_sql(@Handle);
GO


Once the script is completed, let's take a look to see if the Extended events captured any of the deprecated features.

SELECT FinalData.R.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.R.value ('@timestamp', 'nvarchar(50)') AS TIMESTAMP,
FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') AS Feature,
FinalData.R.value ('data(data/value)[2]', 'nvarchar(500)') AS MESSAGE,
FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID,
FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText,
FinalData.R.value ('(action/.)[3]', 'nvarchar(50)') AS SessionID
FROM ( SELECT CONVERT(XML, event_data) AS xmldata
FROM sys.fn_xe_file_target_read_file
('c:\Monitor_Deprecated_Discontinued_features*.etl', 'c:\Monitor_Deprecated_Discontinued_features*.mta', NULL, NULL)
)
AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
ORDER BY TIMESTAMP, Feature
ASC
GO

Here is a screen shot from the results.

DeprecatedFeatures xe

Once we are done, let's go ahead and drop the event.
 
--Drop the event if it already exists
DROP EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER;
GO
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, February 12, 2015 - 12:10:34 PM - BateTech Back To Top (36216)

Great article!  Thanks for the info!

@MaryKDBA, I had similar results in regards to events sometimes appearing after I ran a query that I know used deprecated features, but sometimes the new events would not appear.  The reason that you do not always see new events in the event log for every query that uses a deprecated feature is that many of the deprecated feature counters state that they occur "Occurs once per compilation."  https://msdn.microsoft.com/en-us/library/bb510662(v=sql.105).aspx 


Monday, April 9, 2012 - 3:59:26 PM - MaryKDBA Back To Top (16826)

This example is giving me inconsistent results.  I clear all the objects and start a new session.  The example will return me some results and not others, and then the next time I will get different results than I got the previous time.  Any idea why?


Friday, October 16, 2009 - 12:39:03 PM - SankarReddy Back To Top (4211)

I will come up with few beginner tips on XE soon.


Friday, October 16, 2009 - 7:59:19 AM - admin Back To Top (4208)

Sankar,

I concur this is a good tip.

Could you write a tip just on extended events in the future?  Perhaps a beginner's tip on the concepts, syntax and options?  I think community members new to extended events could benefit from a basic explanation.

I think both of your tips (http://www.mssqltips.com/tip.asp?tip=1853 and http://www.mssqltips.com/tip.asp?tip=1857) on extended events are great, but if someone has not used this technique before they may not know how to apply the concepts to other scenarios.

Thank you again for your many contributions to the community!

Thank you,
The MSSQLTips Team


Tuesday, October 13, 2009 - 5:11:45 PM - SankarReddy Back To Top (4189)

Thanks. Extended Events is a new and exciting framework to track and monitor events in SQL Server 2008 without impacting the performance like Profiler does. I am glad this tip is helpful and hope others will pick up using XE. It is easy to setup and trace the events in XE.


Tuesday, October 13, 2009 - 4:48:36 PM - grobido Back To Top (4188)

This is a great tip.  It is nice to know you can get this data from the database. This will be very helpful when upgrading.















get free sql tips
agree to terms