StatsGrabber

StatsGrabber.sql

/*

	StatsGrabber.sql

	(2014 only)

	This script will grab the statistics collections used to generate a query plan and their current state 
	from the database. Find this section and place your query between the chunks:
	
					/*
					YOUR QUERY GOES BELOW HERE
					*/
	
					/*
						YOUR QUERY GOES ABOVE HERE
					*/

	
	         DON'T FORGET TO SET YOUR DATABASE CONTEXT BEFORE THE WARNING
					/*
						You need to have set your database context by now, otherwise you won't match anything!!!
					*/
*/

set showplan_xml off
go

if exists (select 1 from sys.server_event_sessions where name = 'xe_stats')
	begin		
		drop event session xe_stats on server;		
	end
go

declare @sql nvarchar(4000);
declare @event_data xml;

set @sql = '
			create event session xe_stats on server
			add event sqlserver.query_optimizer_estimate_cardinality(
				action(sqlserver.database_id,sqlserver.username,sqlserver.session_id)
				where (sqlserver.session_id = ' + cast(@@SPID as nvarchar(5))+ ')),
			add event sqlserver.sql_statement_starting, 
			add event sqlserver.sql_statement_completed
			add target package0.ring_buffer(SET max_events_limit=(0),max_memory=(204800))
			with (track_causality=on);';
exec sp_executesql @sql;

alter event session xe_stats on server
	state = start;
go

set showplan_xml on;
go


/*
	YOUR QUERY GOES BELOW HERE
*/

	select *
	from sys.databases;

/*
	YOUR QUERY GOES ABOVE HERE
*/
go
set showplan_xml off;
go

declare @filename sysname;
declare @event_data xml;

select @event_data = cast(target_data as xml)
from sys.dm_xe_session_targets xst
join sys.dm_xe_sessions xes
	on xst.event_session_address = xes.address
where target_name = 'ring_buffer'
	and xes.name = 'xe_stats';


alter event session xe_stats on server
	state = stop;


drop event session xe_stats on server;

select 
	--@event_data,
	--e.a.query('.'),
	DatabaseID = e.a.value('@DbId','int')
	,ObjectID = e.a.value('@ObjectId','bigint')
	,StatsId = e.a.value('@StatsId','int')
into #ls
from @event_data.nodes('//StatsInfo') e(a)

select 
	distinct 
	schema_name = sh.name, 
	table_name = t.name, 
	stat_name = s.name, 
	stats_column_name = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
	i.name,
	sp.steps,
	sp.rows, 
	sp.rows_sampled, 	
	sp.unfiltered_rows,
	sp.modification_counter,
	sp.last_updated
from #ls 
join sys.tables t 
	on #ls.ObjectID = t.object_id
join sys.schemas sh
	on t.schema_id = sh.schema_id
join sys.stats s 
	on #ls.StatsId = s.stats_id
	and #ls.ObjectID = s.object_id
left join sys.indexes i 
	on s.name = i.name
	and s.object_id = i.object_id
cross apply sys.dm_db_stats_properties(#ls.ObjectID,#ls.StatsId) sp
where sh.name != 'sys';

select @event_data;

drop table #ls;

go