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!!!
					*/
*/

use weather;
go

set transaction isolation level read uncommitted;
go
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
*/


declare @ElementDescription varchar(255) = 'Snow Depth';
declare @StartYear int = 2000
declare @EndYear int = 2010


dbcc dropcleanbuffers;
dbcc freeproccache;

select 
	-- Filtered Index (SPA)
	base.StateName, 
	Month, 
	base.AvgValue
from (
select 
	StateName, 
	od.Month,
	AvgValue = avg(o.ObservationValue), 
	rn = dense_rank() over (partition by s.StateName order by avg(o.ObservationValue) desc)
from dbo.Observation o
join dbo.ObservationDates od
	on o.ObservationDateKey = od.DateKey
join dbo.Element e
	on o.ObservationElementID = e.ElementID
join dbo.Station st 
	on o.ObservationStateCOOPID = st.StationCOOPId
join dbo.State s 
	on st.StationStateCode = s.StateCode
where e.ElementDescription = @ElementDescription
	and od.year >= @StartYear and od.Year < @EndYear	
group by s.StateName, od.Month
) base
where rn = 1
order by base.StateName;
go


/*
	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 
	e.a.query('.').value('(/event/@name)[1]','varchar(50)')as event_name, 
	e.a.query('.') as event_data, 
	e.a.query('.').value('(/event/@timestamp)[1]','datetime2(3)') as event_timestamp,
	cast(substring(e.a.query('.').value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
	cast(substring(e.a.query('.').value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50) '), 38, 10) AS int) as event_sequence, 
	cast(substring(e.a.query('.').value('(event/action[@name="attach_activity_id_xfer"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id_xfer 
into #event_data
from @event_data.nodes('//event') e(a)


select	
	grp.query_number,
	 s.c.value('@Name','varchar(50)') Op,
	 s.c.value('@Id','int') CollectionId,
	 s.c.value('@Card','real') EstimatedCardinality
	 ,DatabaseID = e.a.value('@DbId','int')
	,ObjectID = e.a.value('@ObjectId','bigint')
	,StatsId = e.a.value('@StatsId','int')
	,base.event_timestamp
into #ls
from #event_data base
cross apply base.event_data.nodes('//data[@name="stats_collection"]/value/StatsCollection')  s(c)
cross apply s.c.nodes('//StatsInfo') e(a)
cross apply (
				select 
					event_name,
					activity_id,
					activity_id_xfer,
					query_number = row_number() over (order by event_timestamp)
				from #event_data
				where event_name = 'query_optimizer_estimate_cardinality'
					and activity_id_xfer is not null
			) grp
where base.activity_id = grp.activity_id
and base.event_name = 'query_optimizer_estimate_cardinality'
order by grp.query_number, base.event_timestamp


select 
	--distinct 
	query_number,
	CollectionId,
	--EstimatedCardinality,
	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, 	
	'update statistics ' + quotename(t.name) + '(' + quotename(s.name) + ') with resample'
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'
order by query_number,event_timestamp,CollectionId
--select @event_data;

drop table #ls;
drop table #event_data
go