exchange spill

vlt shootout (part 3): single-partition query performance

In part 1 of this series, I gave a brief overview of different strategies for squeezing performance out of very large tables. To recap, these are the proposals: Partitioned Table Partitioned View Filtered Indexes Clustered Columnstore Indexes In part 2, I checked my stats to ensure my statistics from the various fact table strategies that the optimizer chose to use were comparable. In this post we’ll look at how a basic implementation of the fact strategies responds to different types of queries consisting of varieties of aggregates and non-aggregating queries.

vlt shootout: statistics (part 2)

Cardinality estimation is a large portion of creating a query plan that is good enough to satisfy the query in a reasonable amount of time. Partitioned Views and Filtered Indexes provide an opportunity to increase the resolution of statistics’ histogram windows. A brief note on Filtered Indexes though, is that their statistics will be updated when the threshold is met on the full table’s cardinality and not that of the index.

vlt shootout: theories to test (part 1)

Organizations are collecting more data in order to make decisions that best help their enterprise. Larger data sets and increasing availability requirements makes presents interesting challenges to supporting complex analysis. For the DBA these challenges generally fall into either performance or maintenance strategies. There is no shortage of opinions and ideas as to how to address these challenges. In the environment that we manage, we have a few large tables that have traditionally caused us some performance headaches, so I decided to explore the suggestions that I’ve heard.

Finding Value in Incremental Stats (Pt. 2)

Previously, I did a little investigation into incremental statistics and what benefit they can provide. The goal was to find the intersection of update timings for incremental and non-incremental statistics, with the hope of being able to demonstrate a reclamation of statistics maintenance times - and perhaps higher sample rates. Summary: we can, and auto_stats is a beneficiary. However, is this a solution in search of a problem? If you’ve been bumping into statistics maintenance issues, you’re probably already familiar with more of the hands-on intervention available (my personal favorite is Ola Hallengren’s solution).

Finding Value in Incremental Stats (Pt. 1)

I originally created this post and observed some rather embarrassing sloppiness and miscalculations. I’ve simply decided to pull the post and rework it. This is the result of those labors. Recently, I answered a question on dba.stackexchange related to an interesting occurrence with incremental statistics. This led to an good conversation about the efficacy (and utility) of incremental statistics. The primary focus was on Erin Stellato’s confirmation that partition-level stats are not used by the Query Optimizer, thereby identifying a rather sizable gap in the utility of incremental statistics.


As noted last time, sometimes people catch wind of a resolution to a specific problem (or maybe even something that cured the symptom, but didn’t solve the problem) and they begin to believe that to be the solution for all problems, for all time. Statistics is, unfortunately, one of those resolutions. Stale statistics may be the problem, but it also may not. I love statistics and I love learning about them so whether they’re over or underappreciated, the reality is that once it caught on that updating statistics in our largest environment helped a query once, that’s become the go-to resolution for a query gone sideways.

Duplicate Statistics Finder

I’m often asked about whether or not we have “duplicate” statistics in our environment. The thought is that, perhaps, duplicate statistics would cause a delay in execution plan compilation or even a different plan. I’m not sure that it’d be a big deal to have them (please correct me if I’m wrong), but I still decided to work up a little script that will help identify them, at least.

The SQL Server 2014 DDL Bulldozer Part 1.1: Implicit Insert

Previously I posted my experimentation with wait_at_low_priority. Specifically, I investigated what would happen when index rebuild was executed while another session was inserting data. Next we want to see what happens when the index rebuild (with wait_at_low_priority abort blockers specified) is running and the insert is subsequently executed. Here’s the setup (note that I’ve pre-populated tempdb.dbo.numbers with 15 million rows): [code lang=sql] use master; go alter event session ddl_bulldozer on server state = start; go use tempdb; go alter index ix_tmp_numbers_num on dbo.numbers rebuild with ( data_compression = page, online = on ( wait_at_low_priority ( max_duration = 0 minutes, abort_after_wait = blockers )) ); go use master; go alter event session ddl_bulldozer on server state = stop; go drop table #ddl_bulldozer go select object_name, cast(event_data as xml) event_data into #ddl_bulldozer from sys.fn_xe_file_target_read_file(‘ddl_bulldozer*‘,null,null,null) xf select x.d.value(‘@timestamp’,‘datetime2(3)’) event_timestamp, db.object_name, x.d.value(‘(action[@name=“session_id”]/value)[1]‘,‘int’) session_id, x.d.value(‘(data[@name=“statement”]/value)[1]‘,‘nvarchar(max)’) sql_statement, x.d.value(‘(data[@name=“state”]/text)[1]‘,‘nvarchar(max)’) sql_state, x.d.value(‘(data[@name=“duration”]/value)[1]‘,‘bigint’) duration, x.d.value(‘(data[@name=“type”]/text)[1]‘,‘sysname’) ddl_type, x.d.value(‘(data[@name=“abort_after_wait”]/text)[1]‘,‘sysname’) abort_after_wait, x.d.value(‘(data[@name=“transaction_state”]/text)[1]‘,‘sysname’) transaction_state, x.d.value(‘(data[@name=“killed_process_id”]/value)[1]‘,‘int’) killed_process_id, cast(substring( x.d.value(‘(action[@name=“attach_activity_id”]/value)[1]‘, ‘varchar(50)’), 1, 36) as uniqueidentifier) as activity_id, cast(substring( x.d.value(‘(action[@name=“attach_activity_id”]/value)[1]‘, ‘varchar(50) ‘), 38, 10) as int) as event_sequence, cast(substring( x.d.value(‘(action[@name=“attach_activity_id_xfer”]/value)[1]‘, ‘varchar(50)’), 1, 36) as uniqueidentifier) as activity_id_xfer –,db.event_data from #ddl_bulldozer db cross apply event_data.nodes(‘//event’) x(d) where db.object_name not like ‘lock%’ order by event_timestamp,event_sequence [/code] On my test instance, the rebuild takes about 28 seconds to complete, so during that time, I switch to another window and run the insert session: [code lang=sql] /* kick off an implicit transaction (with code shamelessly lifted from Kendra Little at: ) */ use tempdb; go ;WITH Pass0 AS ( SELECT 1 AS C UNION ALL SELECT 1), Pass1 AS ( SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B), Pass2 AS ( SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B), Pass3 AS ( SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B), Pass4 AS ( SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B), Pass5 AS ( SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B), tally AS ( SELECT row_number() OVER ( Order BY C ) AS N FROM Pass5 ) insert numbers select * FROM tally WHERE N <= 1000000; GO [/code] Once again, the insert fails (after about 12 seconds) and sends the error message to the client but what happened along the way?

The SQL Server 2014 DDL Bulldozer Part 1: Implicit Insert

Of the fascinating new features in SQL Server 2014, the one that provides me the most catharsis has got to be wait_at_low_priority. If your maintenance window SLA is threatened by “accidental” report cycles spilling into the window, perhaps you’ll want to explore this extension. While there are many blog posts out there to explain how to use it, I wanted to explore it and how it could apply to a variety of situations.

Can you repro this bug? Trace Flag 2363 Causes Fatal Exceptions on Windowed Aggregates

While testing SQL Server 2014 Enterprise edition, I wanted to see if I could use trace flag 2363 to gather meaningful information for statistics analysis. Part of this analysis included getting some information on the statistics in question. With Trace Flag 2363 enabled: DBCC TRACEON(2363,-1); GO select schema_name =, table_name =, stats_name =, leading_column = index_col(quotename('.'+quotename(,s.stats_id,1), modification_percent = (100.*sp.modification_counter)/sp.unfiltered_rows ,sp.modification_counter ,sp.unfiltered_rows ,sp.rows_sampled ,avg_rows_in_db = avg(1.*sp.unfiltered_rows) over () from sys.stats s join sys.tables t on s.object_id = t.object_id join sys.schemas sh on t.schema_id = sh.schema_id cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp where != 'sys'; This results in a severe error to the client Msg 0, Level 11, State 0, Line 77 A severe error occurred on the current command.