Identify Deprecated SQL Server Code with Extended Events

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 = STARTGO

Let’s run a test script that has some deprecated features in SQL Server 2008.

USE AdventureWorksGO

–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(64SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = @@SPID AND request_id = 0SELECT * FROM sys.fn_get_sql(@HandleGO

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 SERVERGO

Next Steps

Before you migrate to SQL Server 2008 consider taking a look at the following tips:
Catch up on Extended Events article by Paul Randal and whitepaper from Jonathan Kehayias.

Sankar Reddy

Sankar is a Database Engineer/DBA currently working for FunMobility, a mobile media entertainment company. He has been working with SQL Server from 2003 in a variety of roles including development DBA, Database administrator, Report writer and datawarehouse developer. Currently he works with high traffic OLTP and Tera byte datawarehouse systems. Sankar spends most of his free time at MSDN social forums answering questions.

Leave a ReplyCancel Reply