Trace recompilations and statistics usage with Extended Events

xe.classify-recompiles.sql

/*
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE [name] = 'classify_recompiles')
	DROP EVENT SESSION [classify_recompiles] ON SERVER;

CREATE EVENT SESSION [classify_recompiles] ON SERVER
    ADD EVENT sqlserver.sql_statement_recompile(
		WHERE ([source_database_id]>(4)) -- skip system databases
		)
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.sql_statement_recompile', source = N'recompile_cause', source_type = (0))
WITH (EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS );
ALTER EVENT SESSION [classify_recompiles] ON SERVER STATE = START;
GO
*/

;WITH cte_histogram AS (
	SELECT
		slot_node.value('(value)[1]', 'int') AS histogram_value
	,	slot_node.value('@count[1]', 'int') AS histogram_count
	FROM
	(
		SELECT CAST(XT.target_data AS xml) AS target_data
         FROM	sys.dm_xe_sessions AS XS
		JOIN	sys.dm_xe_session_targets AS XT
		  ON	XS.[address] = XT.event_session_address
		WHERE	XS.name = N'classify_recompiles'
		  AND	XT.target_name = N'histogram'
     ) AS ED
     CROSS APPLY
		target_data.nodes('HistogramTarget/Slot') as XQ(slot_node)
 )
SELECT	
		XM.map_value
	,	HI.histogram_count
FROM	cte_histogram AS HI
JOIN	sys.dm_xe_map_values AS XM
  ON	HI.histogram_value = XM.map_key
WHERE	XM.[name] = N'statement_recompile_cause';

xe.trace-statistics-usage.sql

/*
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE [name] = 'track_auto_stats')
	DROP EVENT SESSION [track_auto_stats] ON SERVER;
CREATE EVENT SESSION [track_auto_stats]
ON SERVER
    ADD EVENT sqlserver.auto_stats
    (WHERE (
				-- skip system databases (or configure to trace just one)
               ([database_id] > (4))
			   -- for other status to filter see
			   -- SELECT * FROM sys.dm_xe_map_values WHERE [name] = 'statistics_update_status';
               AND ([status] <> ('Loading stats without updating'))
           )
    )
    ADD TARGET package0.ring_buffer (SET max_memory = (20480)) --20MB
WITH (EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS );
ALTER EVENT SESSION [track_auto_stats] ON SERVER STATE = START;
GO
*/

DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp (
	[timestamp]			datetime2		NULL
,	[status]			sysname			NULL
,	[database_id]		int				NULL
,	[database_name]		sysname			NULL
,	[object_id]			int				NULL
,	[object_name]		sysname			NULL
,	[index_id]			int				NULL
,	[stats_name]		sysname			NULL
,	[duration_µs]		bigint			NULL
,	[job_type]			nvarchar(max)	NULL
,	[statistics_list]	nvarchar(max)	NULL
,	[sample_percentage]	bigint			NULL
,	[incremental]		bit				NULL
,	[async]				bit				NULL
,	[max_dop]			int				NULL
,	[count]				int				NULL
,	[success]			bit				NULL
,	[retries]			int				NULL
,	[last_error]		int				NULL
--,	full_event		xml
);

DECLARE	@ring_buffer	xml;

SELECT	@ring_buffer = 	CAST(XT.target_data AS xml)
FROM	sys.dm_xe_sessions AS XS
JOIN	sys.dm_xe_session_targets AS XT
	ON	XS.[address] = XT.event_session_address
WHERE	XS.name = N'track_auto_stats'
	AND	XT.target_name = N'ring_buffer';

WITH cte_auto_stats AS
(
	SELECT
		XQ.event_node.value('(@timestamp)[1]', 'datetime2') AS [timestamp]
	,	XQ.event_node.value('(data[@name="status"]/text)[1]', 'nvarchar(max)') as [status]
	,	XQ.event_node.value('(data[@name="database_id"]/value)[1]', 'int') as [database_id]
	,	XQ.event_node.value('(data[@name="object_id"]/value)[1]', 'int') as [object_id]
	,	XQ.event_node.value('(data[@name="index_id"]/value)[1]', 'int') as [index_id]
	,	XQ.event_node.value('(data[@name="duration"]/value)[1]', 'int') as [duration]
	,	XQ.event_node.value('(data[@name="job_type"]/text)[1]', 'nvarchar(max)') as [job_type]
	,	XQ.event_node.value('(data[@name="statistics_list"]/value)[1]', 'nvarchar(max)') as [statistics_list]
	,	XQ.event_node.value('(data[@name="sample_percentage"]/value)[1]', 'int') as [sample_percentage]
	,	XQ.event_node.value('(data[@name="incremental"]/value)[1]', 'bit') as [incremental]
	,	XQ.event_node.value('(data[@name="async"]/value)[1]', 'bit') as [async]
	,	XQ.event_node.value('(data[@name="max_dop"]/value)[1]', 'int') as [max_dop]
	,	XQ.event_node.value('(data[@name="count"]/value)[1]', 'int') as [count]
	,	XQ.event_node.value('(data[@name="success"]/value)[1]', 'bit') as [success]
	,	XQ.event_node.value('(data[@name="retries"]/value)[1]', 'int') as [retries]
	,	XQ.event_node.value('(data[@name="last_error"]/value)[1]', 'int') as [last_error]
	--,  XQ.event_node.query(''.'') as full_event
	FROM	@ring_buffer.nodes('RingBufferTarget/event[@name=''auto_stats'']') AS XQ(event_node)
)
INSERT	#tmp ([timestamp], [status], [database_id], [database_name], [object_id], [index_id], [duration_µs], [job_type], [statistics_list], [sample_percentage], [incremental], [async], [max_dop], [count], [success], [retries], [last_error])
SELECT	[timestamp], [status], [database_id], DB_NAME([database_id]) AS [database_name], [object_id], [index_id], [duration], [job_type], [statistics_list], [sample_percentage], [incremental], [async], [max_dop], [count], [success], [retries], [last_error]
FROM	cte_auto_stats AS E

DECLARE	@SqlStmtTemplate nvarchar(MAX) =
N'
	USE [%s];
	UPDATE	#tmp
	SET
		[object_name] = OBJECT_NAME(E.object_id)
	,	[stats_name] = I.[name] 
	FROM	#tmp AS E
	LEFT
	JOIN	sys.stats AS I
	  ON	E.[object_id] = I.[object_id]
	 AND	E.[index_id] = I.stats_id
	WHERE	E.database_id = DB_ID()
';
DECLARE	@SqlStmt nvarchar(MAX);
DECLARE	@Databases		table (DatabaseName sysname primary key);
INSERT	@Databases		SELECT DISTINCT DB_NAME(database_id) FROM #tmp;
DECLARE	@DatabaseName	sysname = (SELECT MIN(DatabaseName) FROM @Databases);
WHILE	@DatabaseName IS NOT NULL
BEGIN
	SET @SqlStmt = FORMATMESSAGE(@SqlStmtTemplate, @DatabaseName);
	EXEC (@SqlStmt);
	DELETE @Databases WHERE DatabaseName = @DatabaseName;
	SET	@DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);
END

SELECT	* FROM	#tmp;