Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

Identify Deprecated SQL Server Code with Extended Events

MSSQLTips author Sankar Reddy By:   |   Read Comments (5)   |   Related Tips: More > Deprecated Features

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?

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%'
BY xp.[name];

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
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];

--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';

--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';

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,
.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')

--Now start the session
ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER

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

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

--# table name

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

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

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

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

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)
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)

Here is a screen shot from the results.

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;

Next Steps

Last Update: 10/13/2009

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.

View all my tips
Related Resources

print tip Print  
Become a paid author

Recommended For You

Learn more about SQL Server tools
Comments and Feedback:
Tuesday, October 13, 2009 - 4:48:36 PM - grobido Read The Tip

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.

Tuesday, October 13, 2009 - 5:11:45 PM - SankarReddy Read The Tip

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.

Friday, October 16, 2009 - 7:59:19 AM - admin Read The Tip


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

Friday, October 16, 2009 - 12:39:03 PM - SankarReddy Read The Tip

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

Monday, April 09, 2012 - 3:59:26 PM - MaryKDBA Read The Tip

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?

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter

*Enter Code refresh code

Sponsor Information

Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.