Extended events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.
This article demonstrates how extended events can be used to create a log file that contains all deprecated features that are still being used in an instance of SQL Server. The log records all occurrences since the event session was started.
If you just want a quick count of how many times a deprecated feature has been used since SQL Server was started up, see Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance.
But if you need a more detailed log that includes stuff like; the SQL statement used that contains the deprecated feature, the database it was run against, the user that ran it, the time it was run, etc, read on.
Create the Extended Event Session
First step is to create the extended event session. Here, we specify the source of the events, the event session target, and the event session options.
CREATE EVENT SESSION [Deprecation Events] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(
sqlserver.database_name,
sqlserver.sql_text,
sqlserver.username
)
),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(
sqlserver.database_name,
sqlserver.sql_text,
sqlserver.username
)
)
ADD TARGET package0.event_file(
SET filename=N'/var/opt/mssql/tmp/DeprecationEvents.xel'
)
WITH (
TRACK_CAUSALITY = ON
);
In this case I specify a target of /var/opt/mssql/tmp/DeprecationEvents.xel. This means that the event data will be stored in that file. You can specify any file name and path.
This example uses a Linux file path, which uses forward slashes. If you’re on Windows, you’d need to use backslashes. For example: C:\Temp\DeprecationEvents.xel.
Start the Extended Event Session
Creating the event session doesn’t start it. Use ALTER EVENT SESSION to stop and start it. In this case we want to start it:
ALTER EVENT SESSION [Deprecation Events] ON SERVER STATE = START;
Do Something Deprecated
Now that we’ve started the extended event session, let’s run some deprecated code:
SELECT * FROM sys.sql_dependencies;
Because sys.sql_dependencies is deprecated, that code will add data to the XEL file that we specified previously.
View the XEL File
Now that we’ve (presumably) added data to our XEL file, let’s take a look at it:
SELECT event_data
FROM sys.fn_xe_file_target_read_file (
'/var/opt/mssql/tmp/DeprecationEvents*.xel',
null,
null,
null
);
Result:
<event name="deprecation_announcement" package="sqlserver" timestamp="2019-10-31T04:03:06.528Z"><data name="feature_id"><value>198</value></data><data name="feature"><value><![CDATA[sql_dependencies]]></value></data><data name="message"><value><![CDATA[sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.]]></value></data><action name="username" package="sqlserver"><value><![CDATA[sa]]></value></action><action name="sql_text" package="sqlserver"><value><![CDATA[SELECT * FROM sys.sql_dependencies;]]></value></action><action name="database_name" package="sqlserver"><value><![CDATA[Test]]></value></action><action name="attach_activity_id_xfer" package="package0"><value>5566866F-8266-467A-9950-895310CF21E3-0</value></action><action name="attach_activity_id" package="package0"><value>07971CB0-F9CC-46C6-B885-5BA8A904B880-1</value></action>
In this case, I only returned event_data, because that’s where all the event data resides.
Unfortunately, it’s not the easiest for us humans to read.
What if I format it?
<event name="deprecation_announcement" package="sqlserver" timestamp="2019-10-31T04:03:06.528Z">
<data name="feature_id">
<value>198</value>
</data>
<data name="feature">
<value><![CDATA[sql_dependencies]]></value>
</data>
<data name="message">
<value><![CDATA[sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.]]></value>
</data>
<action name="username" package="sqlserver">
<value><![CDATA[sa]]></value>
</action>
<action name="sql_text" package="sqlserver">
<value><![CDATA[SELECT * FROM sys.sql_dependencies;]]></value>
</action>
<action name="database_name" package="sqlserver">
<value><![CDATA[Test]]></value>
</action>
<action name="attach_activity_id_xfer" package="package0">
<value>5566866F-8266-467A-9950-895310CF21E3-0</value>
</action>
<action name="attach_activity_id" package="package0">
<value>07971CB0-F9CC-46C6-B885-5BA8A904B880-1</value>
</action>
</event>
It’s slightly easier to read when it’s formatted, but we can do better than that.
Parse the XEL File
In this example, I parse the XEL file so that I can see the data in a grid, just like any other database query.
SELECT
EventXml.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
EventXml.value('(action[@name="username"]/value)[1]', 'nvarchar(256)') AS username,
EventXml.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
EventXml.value('(action[@name="sql_text"]/value)[1]', 'varchar(4000)') AS sql_text,
EventXml.value('(@name)[1]', 'varchar(50)') AS event_name,
EventXml.value('(data[@name="feature"]/value)[1]', 'varchar(255)') AS feature,
EventXml.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS message
FROM (SELECT CAST(event_data AS XML) AS XmlEventData
FROM sys.fn_xe_file_target_read_file (
'/var/opt/mssql/tmp/DeprecationEvents*.xel',
null,
null,
null
)) AS EventTable
CROSS APPLY EventTable.XmlEventData.nodes('event') AS q(EventXml);
Result (using vertical output):
timestamp | 2019-10-31 04:03:06.5280000 username | sa database_name | Test sql_text | SELECT * FROM sys.sql_dependencies; event_name | deprecation_announcement feature | sql_dependencies message | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.
I’m using vertical output here in order to make it easier to read without having to scroll horizontally. This means that the column headings are on the left, and the data is on the right. If you run this using a GUI such as SSMS or Azure Data Studio, you’ll probably see it in the usual table grid format (unless you’ve specified otherwise).
Multiple Rows for a Single Deprecated Feature?
Your XEL file might sometimes get multiple entries for a single event. For example, you execute a single deprecated stored procedure once, only to find that 10 or 11 rows are returned from your XEL file for that single statement.
Here’s an example:
USE Music; EXEC sp_depends @objname = 'Artists';
The sp_depends system stored procedure is deprecated, so I would definitely expect to see a row for that. If I execute that right now, I might expect to end up with 2 rows in total: 1 for the previous example, and 1 for this example.
But as it turns out, 11 more rows are added to my XEL file:
SELECT
EventXml.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
EventXml.value('(action[@name="username"]/value)[1]', 'nvarchar(256)') AS username,
EventXml.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
EventXml.value('(action[@name="sql_text"]/value)[1]', 'varchar(4000)') AS sql_text,
EventXml.value('(@name)[1]', 'varchar(50)') AS event_name,
EventXml.value('(data[@name="feature"]/value)[1]', 'varchar(255)') AS feature,
EventXml.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS message
FROM (SELECT CAST(event_data AS XML) AS XmlEventData
FROM sys.fn_xe_file_target_read_file (
'/var/opt/mssql/tmp/DeprecationEvents*.xel',
null,
null,
null
)) AS EventTable
CROSS APPLY EventTable.XmlEventData.nodes('event') AS q(EventXml)
ORDER BY [Timestamp] ASC;
Result (using vertical output):
-[ RECORD 1 ]------------------------- timestamp | 2019-10-31 04:03:06.5280000 username | sa database_name | Test sql_text | SELECT * FROM sys.sql_dependencies; event_name | deprecation_announcement feature | sql_dependencies message | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 2 ]------------------------- timestamp | 2019-10-31 04:15:13.9920000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_announcement feature | sp_depends message | sp_depends will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 3 ]------------------------- timestamp | 2019-10-31 04:15:13.9940000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | String literals as column aliases message | The ability to use string literals as column aliases will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that -[ RECORD 4 ]------------------------- timestamp | 2019-10-31 04:15:13.9950000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | String literals as column aliases message | The ability to use string literals as column aliases will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that -[ RECORD 5 ]------------------------- timestamp | 2019-10-31 04:15:13.9950000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | String literals as column aliases message | The ability to use string literals as column aliases will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that -[ RECORD 6 ]------------------------- timestamp | 2019-10-31 04:15:14.0020000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_announcement feature | sql_dependencies message | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 7 ]------------------------- timestamp | 2019-10-31 04:15:14.0100000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_announcement feature | sql_dependencies message | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 8 ]------------------------- timestamp | 2019-10-31 04:15:14.0100000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_announcement feature | sql_dependencies message | sql_dependencies will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 9 ]------------------------- timestamp | 2019-10-31 04:15:14.0120000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | sysdepends message | sysdepends will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 10 ]------------------------- timestamp | 2019-10-31 04:15:14.0260000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | sysdepends message | sysdepends will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 11 ]------------------------- timestamp | 2019-10-31 04:15:14.0760000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | sysdepends message | sysdepends will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. -[ RECORD 12 ]------------------------- timestamp | 2019-10-31 04:15:14.0800000 username | sa database_name | Music sql_text | USE Music; EXEC sp_depends @objname = 'Artists'; event_name | deprecation_final_support feature | sysdepends message | sysdepends will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. (12 rows affected)
What’s going on here?
This is happening because the sp_depends system stored procedure itself uses deprecated features.
Not only do I get 1 row for executing sp_depends. I also get 1 row for every deprecated feature that’s used by that stored procedure (whether it’s in the stored procedure, or in another object that it references). In this case I get 10 extra rows.
I had a quick look at sp_depends‘s definition, and I saw that it references (the deprecated) sysdepends in several places, and that view references (the deprecated) sql_dependencies. I also saw that it uses string literals as column aliases, a practice which is also marked for deprecation. All of that supports what I’m seeing in the XEL file.
More Detail about Each Deprecated Feature
See Microsoft’s article Deprecated Database Engine Features in SQL Server 2017 for recommendations on how to deal with each deprecated item. That list is exactly the same as the one for SQL Server 2016.