Finding Deprecated Uses in SQL Server

How well do you know your environment? You probably know all of the jobs that are running, the frequency that indexes need to be rebuilt, and even which users have which level of access to each object in the SQL Server instance. Do you know that your applications are accessing deprecated datatypes over 300 million times a week? What if your TSQL constructs are a bit archaic? Do you know that the code needs to be updated? Do you know how to find deprecated uses in SQL Server?

In this article, I will explore how to use Extended Events to track feature use and abuse. To be more precise, I will share how this tool can help you better understand all of the ways that your applications have been abusing your database by continuing to employ the use of deprecated features, syntax, or constructs in general. In case you are a bit behind in your exploration of XEvents, I have the perfect solution for you – my series on the topic that is continually growing. You can explore the full list of articles in the series by visiting the table of contents – here.

Audit Deprecated Uses

I would dare say that most data professionals think there is some use of deprecated constructs, datatypes or features within their environment. I would double down on that and say that most do not know just how bad it really may be. To find just how bad it really is, we need to audit for the use and abuse of these deprecation events.

Right here would be a good time to point out that your mileage may vary. Some items that are deprecated are more painful than others. Some may be deprecated and may have been on the list for 10+ years at this point. The point is, know your environment and then use good judgement to determine which items in your results need the most attention to fix and update. Why? Well, things really may break especially if you are looking to upgrade to a new version of SQL Server. Just because an item is still available in your current edition, that does not ensure it will still be available in a future release of SQL Server.

Now for the juicy stuff. As I mentioned, finding when and where a deprecated feature or syntax is employed, there are a couple of neat little events within Extended Events that can help to track each time a deprecated feature is accessed or employed. How do we do that? Use the deprecation_announcement and deprecation_final_support events. To help create sessions to track these events, I have the following script to create an “audit” session to do exactly that.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
				FROM sys.server_event_sessions
				WHERE name = 'AuditDeprecated' )
	DROP EVENT SESSION AuditDeprecated
    ON SERVER;
GO

EXECUTE xp_create_subdir 'C:\Database\XE';
GO


CREATE EVENT SESSION [AuditDeprecated] ON SERVER
ADD EVENT sqlserver.deprecation_announcement (
	ACTION ( sqlserver.database_id, sqlserver.database_name,
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,
	sqlserver.context_info, sqlserver.client_connection_id )
	WHERE  [sqlserver].[database_name] = N'AdventureWorks2014' 
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
			AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'
		),
ADD EVENT sqlserver.deprecation_final_support (
	ACTION ( sqlserver.database_id, sqlserver.database_name,
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,
	sqlserver.context_info, sqlserver.client_connection_id )
	WHERE  [sqlserver].[database_name] = N'AdventureWorks2014' 
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
			AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'
		)
ADD TARGET package0.event_file (  SET filename = N'C:\Database\XE\AuditDeprecated.xel'
									, max_rollover_files = ( 25 ) )
WITH (  MAX_MEMORY = 4096 KB
		, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY = 30 SECONDS
		, MAX_EVENT_SIZE = 0 KB
		, MEMORY_PARTITION_MODE = NONE
		, TRACK_CAUSALITY = ON
		, STARTUP_STATE = OFF );
GO

ALTER EVENT SESSION AuditDeprecated ON SERVER
STATE = START;

USE master;

-- Create the Event Session

IF EXISTS ( SELECT *

FROM sys.server_event_sessions

WHERE name = 'AuditDeprecated' )

DROP EVENT SESSION AuditDeprecated

ON SERVER;

EXECUTE xp_create_subdir 'C:\Database\XE';

CREATE EVENT SESSION [AuditDeprecated] ON SERVER

ADD EVENT sqlserver.deprecation_announcement (

ACTION ( sqlserver.database_id, sqlserver.database_name,

sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,

sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,

sqlserver.context_info, sqlserver.client_connection_id )

WHERE [sqlserver].[database_name] = N'AdventureWorks2014'

AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'

AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'

ADD EVENT sqlserver.deprecation_final_support (

ACTION ( sqlserver.database_id, sqlserver.database_name,

sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,

sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,

sqlserver.context_info, sqlserver.client_connection_id )

WHERE [sqlserver].[database_name] = N'AdventureWorks2014'

AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'

AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'

)

ADD TARGET package0.event_file ( SET filename = N'C:\Database\XE\AuditDeprecated.xel'

, max_rollover_files = ( 25 ) )

WITH ( MAX_MEMORY = 4096 KB

, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 30 SECONDS

, MAX_EVENT_SIZE = 0 KB

, MEMORY_PARTITION_MODE = NONE

, TRACK_CAUSALITY = ON

, STARTUP_STATE = OFF );

ALTER EVENT SESSION AuditDeprecated ON SERVER

STATE = START;

And just in case you are using 2008 or 2008R2, use this version instead.

/* 2008r2 and older */
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
				FROM sys.server_event_sessions
				WHERE name = 'AuditDeprecated' )
	DROP EVENT SESSION AuditDeprecated
    ON SERVER;
GO

EXECUTE xp_create_subdir 'C:\Database\XE';
GO


CREATE EVENT SESSION [AuditDeprecated] ON SERVER
ADD EVENT sqlserver.deprecation_announcement (
	ACTION ( sqlserver.database_id, 
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
	WHERE  [sqlserver].[database_id] > 4 --exclude system databases
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
		),
ADD EVENT sqlserver.deprecation_final_support (
	ACTION ( sqlserver.database_id, 
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
	WHERE  [sqlserver].[database_id] > 4
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
		)
ADD TARGET package0.asynchronous_file_target (  SET filename = N'C:\Database\XE\AuditDeprecated.xel'
									, max_rollover_files = ( 25 ) )
WITH (  MAX_MEMORY = 4096 KB
		, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY = 30 SECONDS
		, MAX_EVENT_SIZE = 0 KB
		, MEMORY_PARTITION_MODE = NONE
		, TRACK_CAUSALITY = ON
		, STARTUP_STATE = OFF );
GO

ALTER EVENT SESSION AuditDeprecated ON SERVER
STATE = START;

/* 2008r2 and older */

USE master;

-- Create the Event Session

IF EXISTS ( SELECT *

FROM sys.server_event_sessions

WHERE name = 'AuditDeprecated' )

DROP EVENT SESSION AuditDeprecated

ON SERVER;

EXECUTE xp_create_subdir 'C:\Database\XE';

CREATE EVENT SESSION [AuditDeprecated] ON SERVER

ADD EVENT sqlserver.deprecation_announcement (

ACTION ( sqlserver.database_id,

sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,

sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)

WHERE [sqlserver].[database_id] > 4 --exclude system databases

AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'

ADD EVENT sqlserver.deprecation_final_support (

ACTION ( sqlserver.database_id,

sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,

sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)

WHERE [sqlserver].[database_id] > 4

AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'

)

ADD TARGET package0.asynchronous_file_target ( SET filename = N'C:\Database\XE\AuditDeprecated.xel'

, max_rollover_files = ( 25 ) )

WITH ( MAX_MEMORY = 4096 KB

, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 30 SECONDS

, MAX_EVENT_SIZE = 0 KB

, MEMORY_PARTITION_MODE = NONE

, TRACK_CAUSALITY = ON

, STARTUP_STATE = OFF );

ALTER EVENT SESSION AuditDeprecated ON SERVER

STATE = START;

Slight differences between these two sessions. First, in the 2008 version of the script, I have to provide database ids instead of names. That is a shortcoming of 2008 and 2008R2 implementations of Extended Events. Additionally, the file target is different between the two (recall that they renamed the file target). And lastly, there are a few actions that I included in the 2012 version of the script that are not available in 2008 and R2.

With the session in place, I am now going to run through some sample scripts that will generate deprecation events to occur. I am sticking with my 2014 instance for this segment. That is important to note because different events may occur for different versions of SQL Server. Additionally, the parse script I will share will require a slight change for 2008 and r2 (again related to the file target name).

USE AdventureWorks2014;
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 AdventureWorks2014.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 (TABLOCK)
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

--remote servers
SELECT @@REMSERVER

--vardecimal
exec sp_db_vardecimal_storage_format 'Adventureworks2014', 'ON'
sp_tableoption 'Person.Person', 'vardecimal storage format', 1

USE AdventureWorks2014;

--String alias

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

FROM Person.Person

--# table name

CREATE TABLE # ( c1 INT )

--3 part column name

SELECT TOP 1 AdventureWorks2014.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 (TABLOCK)

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

--remote servers

SELECT @@REMSERVER

--vardecimal

exec sp_db_vardecimal_storage_format 'Adventureworks2014', 'ON'

sp_tableoption 'Person.Person', 'vardecimal storage format', 1

Now to take a peek at the data with this next script.

IF EXISTS (SELECT OBJECT_ID('tempdb.dbo.#xmlprocess'))
BEGIN
DROP TABLE #xmlprocess
END

SELECT CAST ([t2].[event_data] AS XML) AS event_data, t2.file_offset,t2.file_name, cte1.event_session_id--, ' AS event_predicate
	INTO #xmlprocess
	FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue, ses.event_session_id
				FROM sys.server_event_sessions ses
					INNER JOIN sys.server_event_session_fields sesf
						ON ses.event_session_id = sesf.event_session_id
					--INNER JOIN sys.server_event_session_events sese
					--	ON ses.event_session_id = sese.event_session_id
				WHERE sesf.name = 'filename'
					AND ses.name = 'AuditDeprecated'
					) cte1
				OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,	NULL, NULL, NULL) t2
		;


SELECT x.event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
		, x.event_data.value('(event/@package)[1]', 'varchar(50)') AS package_name
		, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
					x.event_data.value('(event/@timestamp)[1]',
										'datetime2')) AS [timestamp]
		,event_data.value('(event/data[@name="feature_id"]/value)[1]','bigint') AS feature_id
		,event_data.value('(event/data[@name="feature"]/value)[1]','varchar(max)') AS feature
		,event_data.value('(event/data[@name="message"]/value)[1]','varchar(max)') AS message
		,event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS client_app_name
		,event_data.value('(event/action[@name="client_connection_id"]/value)[1]', 'uniqueidentifier') AS client_connection_id
		,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS client_hostname
		,event_data.value('(event/action[@name="context_info"]/value)[1]', 'varbinary(max)') AS context_info
		,event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') AS database_id
		,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS database_name
		,event_data.value('(event/action[@name="nt_username"]/value)[1]', 'varchar(max)') AS nt_username
		,event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id
		,event_data.value('(event/action[@name="session_nt_username"]/value)[1]', 'varchar(max)') AS session_nt_username
		,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
		,event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(max)') AS username
	FROM #xmlprocess x
		LEFT OUTER JOIN sys.server_event_session_events sese
			ON x.event_data.value('(event/@name)[1]', 'varchar(50)') = sese.name
				AND x.event_session_id = sese.event_session_id
	ORDER BY timestamp
		, event_data.value('(event/action[@name="event_sequence"]/value)[1]',
							'varchar(max)');