SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary Tables performance issue


Temporary Tables performance issue

Author
Message
nadersam
nadersam
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 519
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13320 Visits: 4077
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;-)
nadersam
nadersam
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 519
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13320 Visits: 4077
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;-)
nadersam
nadersam
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 519
The error is:
Lock request time out exceeded error:1222

Thanks
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13320 Visits: 4077
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;-)
nadersam
nadersam
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 519
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?
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13320 Visits: 4077
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;-)
nadersam
nadersam
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 519
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13320 Visits: 4077
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search