Temporary Tables performance issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Temporary Tables performance issue

33 posts, Page 1 of 4

1 2 3 4 »»»

Temporary Tables performance issue

Rate Topic

Display Mode

Topic Options

Author

Message

nadersam

Posted Sunday, October 03, 2010 3:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:55 AM
Points: 81, Visits: 189

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 Smile

.
Nader

Post #997278

Bhuvnesh

Posted Monday, October 04, 2010 2:13 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #997393

nadersam

Posted Monday, October 04, 2010 2:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:55 AM
Points: 81, Visits: 189

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

Post #997396

Bhuvnesh

Posted Monday, October 04, 2010 4:02 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #997429

nadersam

Posted Monday, October 04, 2010 4:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:55 AM
Points: 81, Visits: 189

The error is:
Lock request time out exceeded error:1222

Thanks

Post #997448

Bhuvnesh

Posted Monday, October 04, 2010 5:25 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #997458

nadersam

Posted Monday, October 04, 2010 5:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:55 AM
Points: 81, Visits: 189

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?

Post #997466

Bhuvnesh

Posted Monday, October 04, 2010 5:42 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

I would like to see the trigger definition now

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #997468

nadersam

Posted Monday, October 04, 2010 5:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:55 AM
Points: 81, Visits: 189

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

Post #997475

Bhuvnesh

Posted Monday, October 04, 2010 6:03 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #997482

« Prev Topic | Next Topic »

33 posts, Page 1 of 4

1 2 3 4 »»»

Permissions