October 3, 2010 at 3:40 am
Hi All,
One of my colleagues is having the following situation and i think it's a design problem but need you guys to confirm and tell me what are possible problems that could occur.
To save some audit trail for system events they have triggers on all tables on (INSERT/UPDATE/DELETE) operations.
Those triggers insert the new added or updated record in a temporary table then call a stored procedure that then reads reads from this temporary table , do some parsing on record then insert the result in an audit trail table.
I need to know please the effect of that on :
1. Locking tempdb (is whole db locked) meaning no one can access table indexes.
2. With multi user enviroment,many users inserting and updating records in different table , will they wait for each other to finish although different tables are being updated.
3. Will that consume much memory?.
4. Should the temporary table be dropped in the Stored procedure that parses on records or no need for that?.
Those are the concerns i have so far about this design but if you have more please let me know.
Thanks in advance waiting eagerly for you replies :-).
Nader
October 4, 2010 at 2:13 am
nadersam (10/3/2010)
1. Locking tempdb (is whole db locked) meaning no one can access table indexes.
NO.Locking mechanism work on row level --> page level ---> then table level.no on database level
nadersam (10/3/2010)
2. With multi user enviroment,many users inserting and updating records in different table , will they wait for each other to finish although different tables are being updated.
True thats the drawback of trigger from performance point of view .So better/intelligently design logic of triggers
nadersam (10/3/2010)
3. Will that consume much memory?.
i dont think..much but the magic tables surely take tempdb space or memory.
nadersam (10/3/2010)
4. Should the temporary table be dropped in the Stored procedure that parses on records or no need for that?.
Why ? and then how you will handle the intermediate data ?
i still didnt get your exact problem ? can you be more specific ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 4, 2010 at 2:23 am
Thanks for your reply.
1. Locking tempdb (is whole db locked) meaning no one can access table indexes.
NO.Locking mechanism work on row level --> page level ---> then table level.no on database level
Not sure i understand that but do you mean the lock is only on affected table?
Also i tried doing the following run an insert statement and put wait for delay '00:01' in it when temporary table is still in scope, then from management studio just select properties of tempdb, i got error time out, once the insert statement finished , i could access tempdb again.
2. My question is more about temporary table created inside trigger, how will that affect in mutli user environment.
4.I am asking should i drop the temporary table in the stored procedure after it finishes using it or just leave SQL handle that?.
Thanks again waiting for you feedback.
Nader
October 4, 2010 at 4:02 am
nadersam (10/4/2010)
NO.Locking mechanism work on row level --> page level ---> then table level.no on database level
this is lock escalation see these links
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx
http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx
nadersam (10/4/2010)
Also i tried doing the following run an insert statement and put wait for delay '00:01' in it when temporary table is still in scope, then from management studio just select properties of tempdb, i got error time out, once the insert statement finished , i could access tempdb again.
Post the error you got.
nadersam (10/4/2010)
2. My question is more about temporary table created inside trigger, how will that affect in mutli user environment.
I really didnt get this , if you talk about blocking because of one and another then dont worry . these temp tables work/created mutually exclusive to each other.and if you are talking about usage of temp table as performance point of view then it will make impact but that depends on the trigger design and amount of DML operation happening.
nadersam (10/4/2010)
4.I am asking should i drop the temporary table in the stored procedure after it finishes using it or just leave SQL handle that?.
Sql server it self handles it. but you can also drop them explicitly.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 4, 2010 at 4:57 am
The error is:
Lock request time out exceeded error:1222
Thanks
October 4, 2010 at 5:25 am
nadersam (10/4/2010)
The error is:Lock request time out exceeded error:1222
Thanks
I think this article can help you.
http://support.microsoft.com/kb/308518/en-us
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 4, 2010 at 5:39 am
Thanks for the link.
But does that mean that SQL server will not be able to access indexes and temporary tables , till the lock caused by the creation of the temporary table is freed?
October 4, 2010 at 5:42 am
I would like to see the trigger definition now
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 4, 2010 at 5:51 am
Here is the trigger code
Create TRIGGER [TR_Audit_Trail_Insert_TableName] ON TableName FOR INSERT AS
SET NOCOUNT ON;
SELECT * INTO #ins FROM INSERTED;
EXEC SP_Audit_Trail_Transaction_Insert 'TableName';
and this is the SP called from within the trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SP_Audit_Trail_Transaction_Insert] (@Table_Name VARCHAR(128)) AS
DECLARE @Field_Position AS INT;
DECLARE @Field_Name AS VARCHAR(128);
DECLARE @Field_Type AS VARCHAR(128);
DECLARE @Conversion AS VARCHAR(128);
SELECT @Field_Position = 0;
WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name) )
BEGIN
SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;
SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position;
IF ( @Field_Name NOT IN (N'ID', N'Performed_By', N'Performed_In', N'Performed_At') )
BEGIN
--waitfor delay '00:01'
IF ( @Field_Type IN (N'DATETIME') )
SET @Conversion = ', 121';
ELSE
SET @Conversion = '';
EXEC (N'INSERT INTO [Audit_Trail_Transaction] ([Table_Name], [Record_ID], [Field_Name], [Operation], [Performed_By], [Performed_In], [Performed_At], [Old_Value], [New_Value])
SELECT ''' + @Table_Name + ''', i.[ID], ''' + @Field_Name + ''',
''I'',i.[Performed_By],i.[Performed_In],i.[Performed_At],
NULL,CONVERT(VARCHAR(5000), i.[' + @Field_Name + ']' + @Conversion + ')
FROM
#ins i;');
--WAITFOR DELAY '00:01'
END
END
Thanks
Nader
October 4, 2010 at 6:03 am
Two things you can try here
1) Don't create # table on fly use create approach.
2) SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;
SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position; use WITH (NOLOCK) option.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 4, 2010 at 6:33 am
thanks for ur help
thats what i did pls tell me if am wrong
for trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [TR_Audit_Trail_Insert_Accounting_Period] ON [Accounting_Period] FOR INSERT AS
SET NOCOUNT ON;
CREATE TABLE #ins([ID] [int] ,
[Name] [varchar](100) NOT NULL,[Date_From] [datetime] NOT NULL,[Date_To] [datetime] NOT NULL,
[Performed_By] [varchar](400) NOT NULL,[Performed_In] [varchar](200) NOT NULL,[Performed_At] [datetime] NOT NULL)
insert INTO #ins select * FROM INSERTED with (nolock);
EXEC SP_Audit_Trail_Transaction_Insert 'TableName;
for SP
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SP_Audit_Trail_Transaction_Insert] (@Table_Name VARCHAR(128)) AS
DECLARE @Field_Position AS INT;
DECLARE @Field_Name AS VARCHAR(128);
DECLARE @Field_Type AS VARCHAR(128);
DECLARE @Conversion AS VARCHAR(128);
SELECT @Field_Position = 0;
WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )
BEGIN
SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;
SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position;
IF ( @Field_Name NOT IN (N'ID', N'Performed_By', N'Performed_In', N'Performed_At') )
BEGIN
waitfor delay '00:01'
IF ( @Field_Type IN (N'DATETIME') )
SET @Conversion = ', 121';
ELSE
SET @Conversion = '';
EXEC (N'INSERT INTO [Audit_Trail_Transaction] ([Table_Name], [Record_ID], [Field_Name], [Operation], [Performed_By], [Performed_In], [Performed_At], [Old_Value], [New_Value])
SELECT
''' + @Table_Name + ''',
i.[ID],
''' + @Field_Name + ''',
''I'',
i.[Performed_By],
i.[Performed_In],
i.[Performed_At],
NULL,
CONVERT(VARCHAR(5000), i.[' + @Field_Name + ']' + @Conversion + ')
FROM
#ins with (nolock) i;');
--WAITFOR DELAY '00:01'
END
END
i applied those changes but still tempdb is locked till i finish insertion.
Thanks
October 4, 2010 at 3:18 pm
Although your code is nice and generic and can be used by multiple tables with different columns, I think this is also the cause of your issues.
If I'm reading it right then
you insert a row into a table which has say 10 columns
the trigger fires, and then copies this row into a temp table
you then call your generic stored procedure
this stored procedure then loops around the each of the columns in the table.
for each column it then inserts an entry into your audit table.
So for a single insert statement you have
1. the insert into the real table
2. the insert into the temp table
3. 10 reads from the system tables to get the column names
4. 10 inserts into the audit table.
this seems quite an overhead.
October 4, 2010 at 3:21 pm
Just spotted one possible improvement
this sql
SELECT @Field_Position = 0;
WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )
could be changed to
SELECT @Field_Position = 0;
SELECT @Total_Number_Of_Fields = SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name)
WHILE ( @Field_Position < @Total_Number_Of_Fields )
regards
David
October 5, 2010 at 1:07 am
Man this is amuch better solution , thank u very much.
Nader
October 5, 2010 at 1:52 am
nadersam (10/5/2010)
Man this is amuch better solution , thank u very much.Nader
Did you see some improvement with it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply