swasheck

            select  getdate() ,
                    schema_name = sh.name ,
                    table_name = t.name ,
                    stat_name = s.name ,
                    index_name = i.name ,
                    stat_leading_column = 
							index_col(quotename(sh.name) + '.' + quotename(t.name),
                              s.stats_id, 1) ,
                    s.stats_id ,
                    s.has_filter ,

swasheck

dbcc traceon(2363,3604)
go

set nocount on;

create table #test (
	id int
)

declare @id int = 1;

swasheck

/****** Object:  Table [dbo].[Element]    Script Date: 1/5/2016 1:06:05 PM ******/
/****** Object:  Table [dbo].[Element]    Script Date: 1/5/2016 1:47:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Element](
	[ElementCode] [char](4) NOT NULL,

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, 

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

  <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

<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

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

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