Finding Deprecated Command Usage via Traces and Extended Events

Below is my posting on dealing with upgrading your SQL Server to either 2012 or 2014 and having to deal with databases that are still running under a compatibility mode of 2005. The links below show the deprecated commands with each new version:

Deprecated Database Engine Features in SQL Server 2012
Deprecated Database Engine Features in SQL Server 2014

Here is a link to set up a trace:
Identify deprecated SQL Server code with a server side trace

Compatibility Level

The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server. In a nutshell, this offers up partial “backward compatibility” to an earlier version. This functionality isn’t all encompassing as only certain aspects (i.e. certain syntax) of the database would pertain to this setting. For more details as to what is or isn’t affected, check out Books Online.

We know from various online sources that these are the following compatibility levels:

SQL Server 6.0 – 60
SQL Server 6.5 – 65
SQL Server 7.0 – 70
SQL Server 2000 – 80
SQL Server 2005 – 90
SQL Server 2008 – 100
SQL Server 2012 – 110
SQL Server 2014 – 120
SQL Server 2016 – 130

It’s also worth noting that the compatibility level is only available for 3 previous (including the one that you are on) versions from the current version of SQL Server that you are working with. Here is a listing of the available compatibility levels:

Compatibility Levels
Version, Current Compatibility Level, Oldest Available Level
SQL 2016, 130 110
SQL 2014, 120, 100
SQL 2012, 110, 90
SQL 2008, 100, 80
SQL 2005, 90,

You can find out the compatibility level of a specific database bu running the following query:

SELECT name, compatibility_level from sys.databases WHERE name = 'MyDatabase';

1	SELECT name, compatibility_level from sys.databases WHERE name = 'MyDatabase';

Deprecated Commands Query

Here is a short query that will provide all counters of deprecated commands for your specific version fo SQL Server. For example if you are running SQL Server 2012 the query provided will show all deprecated commands for the version lower than the one that the query ran against.

Here is the query:

SELECT *
FROM sys.dm_os_performance_counters
WHERE [object_name] = 'MSSQL$INSTANCE:Deprecated Features'
AND cntr_value &gt; 0

SELECT *

FROM sys.dm_os_performance_counters

WHERE [object_name] = 'MSSQL$INSTANCE:Deprecated Features'

AND cntr_value > 0

Here are some typical results:

Below is the script found on this site on how to identify deprecated commands via Extended Events:
Identify Deprecated SQL Server Code with Extended Events

Here is the Extended Events script:

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

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

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

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

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

)

--Now start the session

ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER STATE = START;

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

USE AdventureWorks;

--String alias

SELECT 'FullName' = FirstName + ' ' + MiddleName + '. ' + LastName

FROM Person.Person

--# table name

CREATE TABLE #(c1 INT)

--3 part column name

SELECT TOP 1 AdventureWorks2008.Person.Person.LastName, *

FROM Person.Person

SELECT TOP 1 Person.Person.LastName, *

FROM Person.Person

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

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

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

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

Views – 2464

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, Linux and PostgreSQL etc…

Finding Deprecated Command Usage via Traces and Extended Events

Compatibility Level

Deprecated Commands Query