This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 , |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
dbcc traceon(2363,3604) | |
go | |
set nocount on; | |
create table #test ( | |
id int | |
) | |
declare @id int = 1; |
This file has been truncated, but you can view the full file.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/****** 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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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: | |
/* |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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: | |
/* |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
/* | |
session values | |
*/ | |
s.session_id, | |
s.login_time, | |
s.host_name, | |
s.program_name, | |
s.login_name, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
NewerOlder