July 12, 2015 at 8:48 pm
I am running into an intermittent inexplicable issue. This happens on a high end SQL server, like 64 cores with 256GB of RAM. The database is just about 100GB. Needless to say resource isn't an issue here
The condition is that an insert SPID will enter sleeping awaiting command state, escalate quickly to blocking leader and stay put blocking all SPIDs reading this table via a join. The blocking will grow until the insert SPID is killed, which releases all SPIDs instantaneously. Obviously, killing the SPID is undesired and 3rd party vendor doesn't want to move trigger code into the application and disable it
Application is a ASP.NET application. It open a transaction to send in an insert and transaction is open while entering sleeping state. There could be other aspects of the application reading the table but not necessarily the same record. Until the original insert/update transactions is committed or rolled back, rest of the SPIDs will remain stuck. This explains the blocking. I am trying to figure out why it entered the sleep state
The table has an insert/update trigger. This trigger records last activity timestamp. The only problem is it uses the PK and the clustered index for the insert/update command and used by trigger to update. I suspect it is the trigger and I suspect it has to do with SQL internals. I am seeing quite a few tables with the same logic and they all encounter the same state until killed
Any help/guidance is much appreciated
example:
CREATE TABLE [Table1]
(
[Id] [VARCHAR](30) NOT NULL,
[Name] [VARCHAR](100) NULL,
[last_change_dt] [DATETIME] NULL,
CONSTRAINT [pk_Table1_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
);
CREATE TRIGGER [table1_ins_upd]
ON [Table1] FOR INSERT, UPDATE NOT FOR REPLICATION
AS
DECLARE @num_rows INT
DECLARE @system_timestamp DATETIME
SELECT @num_rows=@@ROWCOUNT
IF @num_rows=0
RETURN
SELECT @system_timestamp = GETDATE()
UPDATE Table1
SET last_change_date = @system_timestamp
FROM Table1, inserted
WHERE Table1.Id = inserted.Id
RETURN
July 13, 2015 at 9:12 am
I'm not sure about this, but I'm wondering if this sets up an infinite loop of the trigger firing the trigger. If you changed the trigger to INSTEAD OF instead of AFTER (the default you're getting by specifying FOR), you'd need to change the code slightly, but I think you could be sure that the trigger won't end up calling itself because of the following from Books Online:
If an INSTEAD OF trigger defined on a table executes a statement against the table
that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called
recursively. Instead, the statement is processed as if the table had no INSTEAD OF
trigger and starts the chain of constraint operations and AFTER trigger executions.
For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and
the trigger executes an INSERT statement on the same table, the INSERT statement
executed by the INSTEAD OF trigger does not call the trigger again. The INSERT
executed by the trigger starts the process of performing constraint actions and firing
any AFTER INSERT triggers defined for the table.
See also this part about Recursive Triggers:
SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS
setting is enabled using ALTER DATABASE.
Recursive triggers enable the following types of recursion to occur:
Indirect recursion
With indirect recursion, an application updates table T1. This fires trigger TR1,
updating table T2. In this scenario, trigger T2 then fires and updates table T1.
Direct recursion
With direct recursion, the application updates table T1. This fires trigger TR1,
updating table T1. Because table T1 was updated, trigger TR1 fires again, and
so on.
The following example uses both indirect and direct trigger recursion.
Assume that two update triggers, TR1 and TR2, are defined on table T1.
Trigger TR1 updates table T1 recursively. An UPDATE statement executes
each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the
execution of TR1 (recursively) and TR2. The inserted and deleted tables for a
specific trigger contain rows that correspond only to the UPDATE statement
that invoked the trigger.
Note:
The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is
enabled by using ALTER DATABASE. There is no defined order in which multiple
triggers defined for a specific event are executed. Each trigger should be
self-contained.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 10:21 am
Thanks for your response. We did think about possible recursion but the "Recursive Triggers Enabled" option is set to False. Will give INSTEAD OF a spin
Also, this is intermittent not consistent. Recursion would have kicked in everytime. Anything else that could be looked at
July 13, 2015 at 7:32 pm
An "instead of" trigger seems like the best solution. It is pretty simple for your example here:
create trigger table1_ins_upd
on table1 instead of insert, update
as
begin
declare @num_rows int;
declare @system_timestamp datetime;
set @num_rows = @@ROWCOUNT;
if @num_rows = 0
return;
set @system_timestamp = getdate();
-- insert any records that don't have matching "deleted" rows
insert into table1 (id, name, last_change_dt)
select id, name, @system_timestamp
from inserted ii
where not exists (select 1 from deleted dd where dd.id = ii.id);
-- update any records in both "inserted" and "deleted"
update table1
set name = ii.name, last_change_dt = @system_timestamp
from inserted ii
join deleted dd on dd.id = ii.id
where table1.id = ii.id;
end;
The primary advantage here is that the initial write to the table is delayed until the "last_change_dt" has the desired value, so you are avoiding the initial write followed by a second write when the trigger fires.
Note however, that you need to handle Inserts and "Updates" slightly differently, since an Update to a column other than the PK will cause a duplicate key error if you just try to use the "inserted" table.
On a true Insert, the "inserted" table will hold the new row and there will be no corresponding row in the "deleted" table. On an Update, the "deleted" table holds the old (pre-deletion) row, so you can join on the primary keys between "inserted" and "deleted" to properly handle each type.
Just to make sure you aren't counting on all the "last_change_dt" being the same for all records in a transaction, if you try this with some multi-record transactions, you will find that the timestamp values will vary across the records. On my test server, running batches of 1000 records showed measurable (4-10 millisecond) time differences every 21 records (using Datetime, which is fairly low resolution).
July 14, 2015 at 2:54 pm
Thank you. I will give this a spin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply