Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Identify Deprecated SQL Server Code with Extended Events

MSSQLTips author Sankar Reddy By:   |   Read Comments (5)   |   Related Tips: More > 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.

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



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





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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?


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

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


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

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


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.




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Top Ten

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.