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.

The most pressing concern, for us, is how DML transactions are handled. The initial concern was “how are INSERTs handled?” After a few initial tests, it looked like INSERT transactions (implicit, or otherwise) are rolled back. However, “looks like” doesn’t fly when trying to pitch the concept to upper management. So, to prove it, we set up a situation that simulates this possibility and track it with an Extended Event. Let’s start with the setup (I get by with a little code help from my friends):

use tempdb;
go

/*
clean up objects
*/
if exists (select 1 from sys.server_event_sessions where name = 'ddl_bulldozer')
begin
drop event session ddl_bulldozer on server;
end

go

/*
set up event session
*/
CREATE EVENT SESSION ddl_bulldozer ON SERVER
ADD EVENT sqlserver.ddl_with_wait_at_low_priority(
ACTION(sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.lock_acquired(
WHERE ([database_id]=(2) AND [object_id]<>(0))),
ADD EVENT sqlserver.lock_cancel(
WHERE ([database_id]=(2) AND [object_id]<>(0))),
ADD EVENT sqlserver.lock_released(
WHERE ([database_id]=(2) AND [object_id]<>(0))),
ADD EVENT sqlserver.process_killed_by_abort_blockers(
ACTION(sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(1)
ACTION(sqlserver.session_id)
WHERE ([sqlserver].[database_id]=(2))),
ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.session_id)
WHERE ([sqlserver].[database_id]=(2))),
ADD EVENT sqlserver.sql_transaction(
ACTION(sqlserver.session_id,sqlserver.sql_text)
WHERE ([sqlserver].[database_id]=(2) AND [transaction_state]=(2)))
ADD TARGET package0.event_file(SET filename=N'ddl_bulldozer')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
/*
create objects
*/

if not exists (select 1 from sys.tables where name = 'numbers')
begin
create table tempdb.dbo.numbers (
num bigint
);
end;

go

if not exists (select 1 from sys.indexes where name = 'ix_tmp_numbers_num')
begin
create index ix_tmp_numbers_num on dbo.numbers(num) with (data_compression = page)
end

go

/*
start event session
*/

alter event session ddl_bulldozer on server
state = start;

go

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

;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 <= 100000000;
GO

Then we kick off a dirty read (just to prove that data is in the table), and a low-priority wait DDL (INDEX REBUILD) which escalates after 0 minutes (immediately) and kills blockers. Once complete, we parse the Event Session details to demonstrate what has happened:

use tempdb;
go

select *
from dbo.numbers (nolock);
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

select *
from dbo.numbers (nolock);
go

use master
go

waitfor delay '00:00:05';

alter event session ddl_bulldozer on server
state = stop;

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,
x.d.value('(data[@name="object_id"]/value)[1]','bigint') locked_object_id,
x.d.value('(data[@name="transaction_id"]/value)[1]','bigint') locked_transaction_id,
x.d.value('(data[@name="mode"]/text)[1]','sysname') lock_mode,
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%'
union
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,
x.d.value('(data[@name="object_id"]/value)[1]','bigint') locked_object_id,
x.d.value('(data[@name="transaction_id"]/value)[1]','bigint') locked_transaction_id,
x.d.value('(data[@name="mode"]/text)[1]','sysname') lock_mode,
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)
join tempdb.sys.objects o
on o.object_id = x.d.value('(data[@name="object_id"]/value)[1]','bigint')
where o.name = 'numbers'
order by event_timestamp, event_sequence

/*
or you can just get the non lock-related events
*/
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,
--x.d.value('(data[@name="object_id"]/value)[1]','bigint') locked_object_id,
--x.d.value('(data[@name="transaction_id"]/value)[1]','bigint') locked_transaction_id,
--x.d.value('(data[@name="mode"]/text)[1]','sysname') lock_mode,
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

At some point along the way, the original session gets killed and the session receives the following error message:
result
The parsed results of our Event Session provides some valuable information as well.
xe_result

Session 53 (whose relevant information is boxed in red) begins by taking an IX lock and then takes an X-lock on the object in question (tempdb.dbo.numbers) so that it can insert data. So far, so good. Session 52 then takes an Sch-S lock to do a dirty (yes,(NOLOCK)) read on the object. This is granted and takes 4660721 microseconds to complete and that session moves on to the next statement, which is the ALTER INDEX DDL (highlighted in green). The ddl_with_wait_at_low_priority event is fired with all of the pertinent details and we see via the process_killed_by_abort_blockers event that a process has been killed. It tells us that process id is 53 (session 53). Finally, we see that a sql_transaction event has fired for session 53 with a transaction state of Rollback.

So, that rebuild will operate such that it will terminate the INSERT and rollback the transaction. Obviously more research will need to be done in order to cover a variety of cases, and I plan to do that … after vacation.

Advertisements

One thought on “The SQL Server 2014 DDL Bulldozer Part 1: Implicit Insert

  1. Pingback: The SQL Server 2014 DDL Bulldozer Part 1.1: Implicit Insert | Exchange Spill

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s