2

I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing it the inserted and deleted tables as XML parameters which will send the message (containing the 2 XMLs) to the queue.

The activation procedure then processes the queue, and inserts the changes done to the initial table into an audit table... Works Great.

The Problem occurs when the update on the Price table is a massive one. In my case, I tested a Price change update on 105 000 Items. The XML Inserted and Deleted Variables, contain 1.3 million lines each... Which causes major issues: TempDB grows infinitely and the processor goes up to 95% which is understandable... but never seems to complete the operation.

The code for the Trigger is Price_TAB_Audit_trig. The trigger calls the SendMsgServiceBroker procedure to send the message to the Queue. See comments below for activation procedure.

I'm trying to figure out how I can break down the inserted and deleted variables (messages) into smaller XMLs. My questions/Thoughts are: Can I create table variables and pass that in the message instead of XML? If so, how to create variable tables in a trigger? Should I create global unique temp tables and reference them in the message?

Any other suggestion, is welcome. Thank you in advance JG

  • What problem are you trying to solve? Auditing? Perhaps look at CDC (Change data capture) – Max Vernon Nov 4 '15 at 15:30
  • I want to do some auditing... I looked and studied the CDC option, the problem is that we are working with SQL-Express. – JohnG Nov 4 '15 at 15:34
  • Edit your question with the code in the trigger so we can more easily help you. – Max Vernon Nov 4 '15 at 15:36
  • @JohnGhali you can just paste the code in pastebin.com then just add the link to your question. – Shawn Melton Nov 4 '15 at 15:44
  • the code to the trigger is Price_TAB_Audit_trig – JohnG Nov 4 '15 at 15:47
1

You could send the xml data in predefined chunks by using something like the trigger in the following setup.

Create the testbed in tempdb:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest_Chunked')
DROP TRIGGER dbo.TriggerTest_Chunked;


IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , DEETS XML NOT NULL
);

The trigger:

CREATE TRIGGER dbo.TriggerTest_Chunked
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @RowStart INT;
    DECLARE @RowEnd INT;
    DECLARE @RowCount INT;
    DECLARE @BatchSize INT;
    SET @BatchSize = 1000;

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM inserted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 1, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM inserted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM deleted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 2, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM deleted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END
END
GO

Insert some test data to see what the trigger does:

INSERT INTO dbo.TriggerTest (ID)
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) 
FROM sys.objects o1
    , sys.objects o2;

Show results:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;
  • +1 That is quite a solution... I like the Idea, though I'm not sure that it will be efficient. I started another solution where the trigger will create unique inserted and Deleted tables which will be referenced in the message... because when I ran an update of 1000 items, it took 40 minutes to run which is too much... I have a feeling that breaking down the XML of 1000 rows of 120 fields per row to do the audit is way too long... If my tests are not satisfactory with the solution I'm building, I will definitely try this one. I thank you for your input it was very helpful. – JohnG Nov 4 '15 at 18:19
  • I picked 1,000 rows out of thin-air. You can try adjusting that down to say 50 rows, or 100, etc. Fine tune it to work with your requirements. – Max Vernon Nov 4 '15 at 18:34
  • It was pure coincidence that I actually tested 1000 rows and you also mentioned 1000 rows. What I was trying to say is that I wasn't satisfied with the results of 1000 rows using XML and if I have a batch of 105 000 updates and i decide to process chunks of 50 or 100 or 500, it would be very time consuming. So I'm trying a solution that doesn't use XML, I hope it works... If it works I will post it... If not I will try with your solution. – JohnG Nov 4 '15 at 18:45
  • ahhhh. I understand now. Best of luck with your solution. – Max Vernon Nov 4 '15 at 18:51
1

By way of an example for using a table-valued-parameter to pass the details into your stored proc, take a look at this:

Cleanup the test-bed objects, if they already exist:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest1')
DROP TRIGGER dbo.TriggerTest1;

IF EXISTS (SELECT 1 FROM sys.sql_modules sm 
    INNER JOIN sys.objects o ON sm.object_id = o.object_id 
    WHERE o.name = 'InsertFromTrigger')
DROP PROCEDURE dbo.InsertFromTrigger;

IF EXISTS (SELECT 1
    FROM sys.types t
    WHERE t.name = 'TriggerData'
)
DROP TYPE TriggerData;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

Create test-bed objects:

/*
    Creates a custom table-type definition
*/
CREATE TYPE TriggerData AS TABLE
(
    OperationType INT NOT NULL
    , ID INT NOT NULL
);

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , ID INT NOT NULL
);
GO

/*
    create a procedure to accept data from the trigger
*/
CREATE PROCEDURE dbo.InsertFromTrigger
(
    @td AS TriggerData READONLY
)
AS
BEGIN
    INSERT INTO dbo.TriggerTestTarget
    SELECT *
    FROM @td;
END
GO


/*
    create a trigger to send data to the stored proc
*/
CREATE TRIGGER dbo.TriggerTest1
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @t TriggerData;
    INSERT INTO @t (OperationType, ID)
    SELECT 1, *
    FROM inserted
    UNION ALL 
    SELECT 2, *
    FROM deleted;
    EXEC dbo.InsertFromTrigger @t;
END
GO

Test the trigger/stored proc:

INSERT INTO dbo.TriggerTest
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id)
FROM sys.objects o1
    , sys.objects o2;

DELETE TOP(50) 
FROM dbo.TriggerTest;

See what is in the target tables:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

This will still probably be fairly problematic if space is a premium and you decide to update a lot of rows, since SQL Server will need to allocate space in the table variable for all the data present in inserted and deleted.

This is however an elegant way to approach the problem, at least from a code-readability perspective.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.