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.
Continue reading “Duplicate Statistics Finder”

Duplicate Statistics Finder

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):

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

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:

/*
kick off an implicit transaction
(with code shamelessly lifted from Kendra Little at:
http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/
)
*/
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

Once again, the insert fails (after about 12 seconds) and sends the error message to the client
2.result
but what happened along the way?
xe1
I’ve added a new event (progress_report_online_index_operation) for a bit more visibility into what’s occurring. What we see is the index rebuild starting (line 2, in green) and the ddl_with_wait_at_low_priority event firing because we told the ddl operation to wait at low priority. About four seconds later was when I started the insert (in red). Along the way, we see that the index rebuild is making progress under different activity_id‘s. Because this is an online index rebuild so the page organization is occurring in tempdb and the second (insert) session is allowed to continue its activity, for now. Also note the quick termination of the statement for the sake of a recompile on the insert (recompile cause: “schema changed”).
xe2
At the end of it all, the insert transaction is killed and rolled back, just as in the last test. However, because the index rebuiled was online, the metadata operation to switch the rebuild index structure would be the first block the rebuild encountered. At this point, the insert still has its X lock (as before) on the object. This lock is terminated and the transaction is rolled back, paving the path for the metadata index structure switch.

The SQL Server 2014 DDL Bulldozer Part 1.1: Implicit Insert

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.
Continue reading “The SQL Server 2014 DDL Bulldozer Part 1: Implicit Insert”

The SQL Server 2014 DDL Bulldozer Part 1: Implicit Insert

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 = sh.name,
table_name = t.name,
stats_name = s.name,
leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),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 sh.name != '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. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 77
A severe error occurred on the current command. The results, if any, should be discarded.

Parsing the minidump with WinDbg, I arrived at this as the faulting module

sqllang!DRgCId::Print+0x68

I’ve attempted repro with user tables and different aggregates and still get the same issues. If you can reproduce, feel free to vote at the Connect item below.

https://connect.microsoft.com/SQLServer/feedback/details/1327021/trace-flag-2363-causes-fatal-exceptions-on-windowed-aggregates

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

SQL Saturday 358: ABQ – The Deuce

This Saturday I’ll be in Albuquerque, NM at SQL Saturday 358. Last year was my first time attending and speaking at a SQL Saturday event, so I’m excited to return to the place that gave me that first chance.

I’ll be talking about managing statistics (again), with an eye toward proactive management. This is just like the presentation I gave at SQL Saturday 331 (Denver) and to the Performance Virtual Chapter, except it’s been completely re-written to enjoy some of the undocumented features in SQL Server 2014 and to focus more on the proactive management. I hope to see you there!

SQL Saturday 358: ABQ – The Deuce

SQL Server and ReFS: Part 2 – FIO Benchmarking NTFS vs. ReFS

As I was sitting in Bob Ward’s Inside SQL Server I/O presentation, something interesting caught my eye on a slide that noted that ReFS is now supported for SQL Server 2014. I’d run into problems with 2012 so I’d just given up but this looks promising. I am neither a filesystem aficionado, nor a dilettante but I know that there are some interesting features of ReFS at which Windows server admins are looking to see if it’s viable.

Previously, I examined a couple of features that were potential blockers for 2014/ReFS adoption: DBCC CHECKDB and In-Memory OLTP support.

So with it established that these issues have been addressed and that SQL Server 2014 does support ReFS, should we consider it as a viable option? Let’s see from a FIO benchmark perspective.
Continue reading “SQL Server and ReFS: Part 2 – FIO Benchmarking NTFS vs. ReFS”

SQL Server and ReFS: Part 2 – FIO Benchmarking NTFS vs. ReFS