Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Temporary Tables performance issue Expand / Collapse
Author
Message
Posted Sunday, October 3, 2010 3:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
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
Post #997278
Posted Monday, October 4, 2010 2:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #997393
Posted Monday, October 4, 2010 2:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
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
Posted Monday, October 4, 2010 4:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #997429
Posted Monday, October 4, 2010 4:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
The error is:
Lock request time out exceeded error:1222

Thanks
Post #997448
Posted Monday, October 4, 2010 5:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #997458
Posted Monday, October 4, 2010 5:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
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
Posted Monday, October 4, 2010 5:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #997468
Posted Monday, October 4, 2010 5:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
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
Posted Monday, October 4, 2010 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #997482
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse