I was geeking last weekend, no wait, actually three weeks ago, and then last night and tonight…it doesn’t really matter, with CASCADE DELETES in SQL Server 2008 R2, and was curious about whether or not I could trace Cascade Referential Integrity in SQL Server Profiler. Do you use Cascade Deletes in your foreign keys? Me neither that much, but I find them sort of fascinating. I should run a test comparing them to coded deletes – I bet they would leave delete statements in the dust. Anytime that SQL Server can do something natively, I’m putting my money on the engine that it can process faster whatever you or I might be trying to do in code.
Create the tables to set up our little experimentation:
/****** Object: Table [dbo].[tbl_1] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_1](
[Id1] [int] NOT NULL,
[Value1] [varchar](20) NULL,
CONSTRAINT [PK_tbl_1] PRIMARY KEY CLUSTERED
(
[Id1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_2] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_2](
[Id2] [int] NOT NULL,
[Id1] [int] NULL,
[Value2] [varchar](20) NULL,
CONSTRAINT [PK_tbl_2] PRIMARY KEY CLUSTERED
(
[Id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_3] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_3](
[Id3] [int] NOT NULL,
[Id2] [int] NULL,
[Value3] [varchar](20) NULL,
CONSTRAINT [PK_tbl_3] PRIMARY KEY CLUSTERED
(
[Id3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_4] Script Date: 11/15/2011 20:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_4](
[Id4] [int] NOT NULL,
[Id3] [int] NULL,
[Id2] [int] NULL,
[Value4] [varchar](20) NULL,
CONSTRAINT [PK_tbl_4] PRIMARY KEY CLUSTERED
(
[Id4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_tbl_2_tbl_1] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_2] WITH CHECK ADD CONSTRAINT [FK_tbl_2_tbl_1] FOREIGN KEY([Id1])
REFERENCES [dbo].[tbl_1] ([Id1])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_2] CHECK CONSTRAINT [FK_tbl_2_tbl_1]
GO
/****** Object: ForeignKey [FK_tbl_3_tbl_2] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_3] WITH CHECK ADD CONSTRAINT [FK_tbl_3_tbl_2] FOREIGN KEY([Id2])
REFERENCES [dbo].[tbl_2] ([Id2])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_3] CHECK CONSTRAINT [FK_tbl_3_tbl_2]
GO
/****** Object: ForeignKey [FK_tbl_4_tbl_3] Script Date: 11/15/2011 20:57:06 ******/
ALTER TABLE [dbo].[tbl_4] WITH CHECK ADD CONSTRAINT [FK_tbl_4_tbl_3] FOREIGN KEY([Id3])
REFERENCES [dbo].[tbl_3] ([Id3])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_4] CHECK CONSTRAINT [FK_tbl_4_tbl_3]
GO
I’ve created four tables, all with a PK and an FK that include DELETE CASCADE; so what’s happening here is that when I delete a record in tbl_1, the foreign keys through to tbl_4 will delete the associated foreign key records. Rather than write some trigger or delete the associated rows in some other fashion, I’m letting SQL Server do it.
Compile this procedure (it’s complicated I know right?) and then take a look at the execution plan. If you forgot, just highlight the stored procedure name in a management studio query window and type control + L:
DROP PROCEDURE proc_cascadetest
GO
CREATE PROCEDURE proc_cascadetest
AS
DELETE tbl_1 WHERE Id1=1
GO
Notice that the execution plan shows the CASCADE DELETE action for the stored procedure; each step for the foreign key tables is clearly found in the plan. This sort of coincides with what Microsoft said awhile back in the following post on Microsoft Support.
You cannot trace cascading deletes and updates in the context of regular events such asTSQL:StmtCompleted and TSQL:BatchCompleted. However, when you execute a statement that performs cascading updates or deletes in SQL Server Profiler, you can trace these cascade actions in the execution plan.
So, these in fact cannot be traced in SQL Profiler. Then, I remember the hundreds or maybe thousands of blogs by Jonathan Keyhaias on the internet about Extended Events in SQL Server 2008 R2. I think this dude maybe invented them and put them into the SQL Engine or something, I’m not quite sure. Since these are supposed to be the all-powerful, all-knowing for internals or so I’m told or led to believe, I thought I’d give them a shot and see if they could serve up the SQL text since profiler can’t. Let’s give it a go. Run the following:
First get the data into our tables…all that I’m doing here is creating the FK equal to the PK + 1, with the FK inserted alongside. Run the script and then go look at the data:
DECLARE @t1_insert int
,@t2_insert int
,@t3_insert int
,@t4_insert int
SET @t1_insert=1
SET @t2_insert = @t1_insert+1
SET @t3_insert = @t2_insert+1
SET @t4_insert = @t3_insert+1
WHILE (@t1_insert < 5)
BEGIN
INSERT tbl_1 VALUES (@t1_insert, 'tbl1 val')
INSERT tbl_2 VALUES (@t2_insert, @t1_insert, 'tbl2 val')
INSERT tbl_3 VALUES (@t3_insert, @t2_insert, 'tbl3 val')
INSERT tbl_4 VALUES (@t4_insert,@t3_insert, @t2_insert, 'tbl4 val')
SET @t1_insert+=1
SET @t2_insert+=1
SET @t3_insert+=1
SET @t4_insert+=1
ENDGO
Run this code now to start up extended events session. Make sure and change the sqlserver.database_id for your particular database ID. And, don’t run the script below all at once – maybe just the first part to create the session, then run the stored procedure, and then the part to kill the session:
-- Create the event session and add some events
DROP EVENT SESSION track_sprocs ON SERVER
GO
CREATE EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.module_end (ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_starting (ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
WHERE sqlserver.database_id=6),
ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
WHERE sqlserver.database_id=6)
ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
GO
-- Start the session
ALTER EVENT SESSION track_sprocs ON SERVER
STATE = START
GO
-- Add another event
ALTER EVENT SESSION track_sprocs ON SERVER
ADD EVENT sqlserver.lock_released
-- Run the test procedure
EXEC proc_cascadetest
GO
-- Drop the events now
ALTER EVENT SESSION track_sprocs ON SERVER
DROP EVENT sqlserver.module_end,
DROP EVENT sqlserver.sql_statement_completed,
DROP EVENT sqlserver.lock_released,
DROP EVENT sqlserver.sql_statement_starting
GO
DROP EVENT SESSION track_sprocs ON SERVER GO
Now run either or both of the following: I don’t see the code that shows me that the delete has happened in the CASCADE action that I coded. WTF?
SELECT top 1000
event_xml.value('(./@name)', 'varchar(100)') as [event_name],
event_xml.value('(./data[@name="object_name"]/value)[1]', 'varchar(255)') as [object_name],
CAST(event_xml.value('(./action[@name="tsql_stack"]/value)[1]','varchar(MAX)') as XML) as
[stack_xml],
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text]
FROM #xml_event_data
CROSS APPLY xml_data.nodes('//event') n (event_xml)
SELECT TOP 20 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY Creation_Time DESC
Both images above clearly show that the code that does the RI isn’t in the session that I ran.
My conclusion – none. I tried adding several events in my Extended Event session (is this the same as what people are calling “xevents”?) and couldn’t get anything to pop out. Anyone have the phone number to SQLSkills.com?
Thanks for reading,
Lee Everest
-------------------------
I laid an egg on this one – you try it and give me a shout if you figure it out. I’m gonna go drink a beer. Bye.
