swasheck

CREATE TABLE #results
(
	DatabaseName SYSNAME ,
	VLFcount INT
);
--	Create the SQL Server 2012-compatible table. We'll drop the column if it's not 2012
CREATE TABLE #stage
(
	RecoveryUnitId INT ,
	FileID INT ,

swasheck

SELECT 			
		s.session_id,
		r.request_id,
		DB_NAME(r.database_id) as request_database_name,
		s.login_name,
		r.command,
		w.exec_context_id,
		w.blocking_session_id,
		w.blocking_exec_context_id,
		s.login_time, 

swasheck

DECLARE @FileName NVARCHAR(4000)	
SELECT @FileName = 

	LEFT(target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)') ,
		CHARINDEX('system_health',target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)') )-1) + 'system_health*.xel'
	
	FROM (
			SELECT 
				CAST(target_data AS XML) target_data

swasheck

DECLARE @sql varchar(max);
IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE NAME='HISTOGRAM')
BEGIN
	DROP TABLE tempdb.dbo.HISTOGRAM;
END
IF (SELECT CURSOR_STATUS('global','statcur')) >= -1
BEGIN
 DEALLOCATE statcur
END
IF (SELECT CURSOR_STATUS('global','histo_cur')) >= -1

swasheck

			SELECT 
				td.r.value('@name','sysname') event_name,
				td.r.value('@timestamp','datetime2(7)') event_timestamp, 
				td.r.value('(data[@name="wait_type"]/text)[1]','sysname') wait_type,
				td.r.value('(data[@name="duration"]/value)[1]','bigint') wait_duration,
				td.r.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
				td.r.query('.') event_data
			into #xe
			FROM (
						SELECT

swasheck

select
	 
	/*
		session values
	*/
	s.session_id,
	s.login_time, 
	s.host_name, 
	s.program_name, 
	s.login_name, 

swasheck

<event name="query_post_execution_showplan" package="sqlserver" timestamp="2014-07-30T16:14:17.237Z">
  <data name="source_database_id">
    <value>2</value>
  </data>
  <data name="object_type">
    <value>20801</value>
    <text>ADHOC</text>
  </data>
  <data name="object_id">
    <value>13245692</value>

swasheck

  <data name="showplan_xml">
    <value>
      <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2000.8">
        <BatchSequence>
          <Batch>
            <Statements>
              <StmtSimple StatementSubTreeCost="0.0783322" StatementEstRows="2.03781" StatementOptmLevel="FULL" QueryHash="0xDFC40696863DF555" QueryPlanHash="0xC4973D76B2B6B7B7" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120">
                <QueryPlan DegreeOfParallelism="0" MemoryGrant="1120" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="424" CompileTime="152" CompileCPU="113" CompileMemory="8112">
                  <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1120" RequiredMemory="1024" DesiredMemory="1120" RequestedMemory="1120" GrantWaitTime="0" GrantedMemory="1120" MaxUsedMemory="16" />
                  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="671078" Estimated

swasheck

/*

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

swasheck

with x as (
select 
    stats_generation_number = row_number() over (partition by base.object_id, base.stat_cols, base.filter_definition order by base.is_index desc),      
    * 
from (
        select      
            o.object_id,
            s.stats_id,
            schema_name = sh.name, 
            object_name = o.name,