April 24, 2012 at 7:44 am
Hi
If I declare a table variable and puts lots of data in it, then as per my understanding this data won't stay in memory and will move to temp table on disk.This will be done to avoid pressure on memory.
Question is : what is a broad criteria used by optimizer to make this shift?
The question might sound theoritical, but if we have a guideline for this shift, then it can help in deciding whether we should create a table variable or a temp table.
I understand there are situations where we have to use temp table only. But if my requirement is just to store some data temporarily and apply some business logic to it, then I wonder if we ever need to create a temp table, because if data is less then it may happily stay in memory and if it is more, it will go to temp table anyway. Then for such requirement , why would I ever bother to create a temp table?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 7:51 am
I was just going thru some posts and I believe the answer is largely going to be "sql server decides it dynamically looking at the memory pressure" 🙂
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 8:24 am
You're right, SQL Server decides on its own how to handle the "shift".
However keep in mind that ALL tables are populated like that. I don't know where the "table variables are memory only" myth came from, but I see it come up quite frequently on the forums.
The lazy writer process handles memory pressure flushing clean and dirty pages to be written to disk. The checkpoint process does a similar job, but the purposes are different.
That said, the good old thumb rule stands: if it's more than a few hundred records, use a temporary table.
I could even push it further: if you don't necessarily need a table variable, use a temp table. Table variables can't have stats and can trick the optimizer into producing horrible query plans.
Hope this helps
Gianluca
-- Gianluca Sartori
April 24, 2012 at 9:11 am
S_Kumar_S (4/24/2012)
The question might sound theoritical, but if we have a guideline for this shift, then it can help in deciding whether we should create a table variable or a temp table.
No, it won't help, because temp tables and table variables are treated exactly the same when it comes to spilling them to disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 9:12 am
Gianluca Sartori (4/24/2012)
The lazy writer process handles memory pressure flushing clean and dirty pages to be written to disk. The checkpoint process does a similar job, but the purposes are different.
In TempDB it's purely the lazy writer. Checkpoint doesn't flush dirty pages to disk in tempDB because the database never needs to be recovered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 9:16 am
Hi Gail
Then it leads me to the question "why on earth we need table variable then?"
Temp table also stays in memory if data is less. Temp table even has stats for fast data retrieval.
So why will ever I would want to use table variable?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 9:21 am
Table variables have their uses.
They can be the target of an OUTPUT clause of an INSERT, UPDATE, or DELETE.
They can be used to capture audit data that needs to be persisted from within a transaction that has to rollback due to errors.
They are effecient for small amounts of data.
I am suer we could come up with more detailed uses if needed.
April 24, 2012 at 9:27 am
Lynn Pettis (4/24/2012)
Table variables have their uses.They can be the target of an OUTPUT clause of an INSERT, UPDATE, or DELETE.
Can't we use temp table there?
They can be used to capture audit data that needs to be persisted from within a transaction that has to rollback due to errors.
Can you pls elaborate this with an example?
They are effecient for small amounts of data.
IS it because no stats are maintained fo rthem?
I am suer we could come up with more detailed uses if needed.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 9:28 am
Sorry my questions went in quote. I thought they will be printed separately.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 9:28 am
S_Kumar_S (4/24/2012)
Hi GailThen it leads me to the question "why on earth we need table variable then?"
Temp table also stays in memory if data is less. Temp table even has stats for fast data retrieval.
So why will ever I would want to use table variable?
Table variables are useful when you need some data to be unaffected by transaction rollbacks.
Example:
DECLARE @Log TABLE (
eventdate datetime DEFAULT GETDATE(),
description nvarchar(4000) NOT NULL
)
BEGIN TRY
BEGIN TRAN
INSERT INTO @Log (description) VALUES('Inserting some rows...')
INSERT INTO SomeTable VALUES('This value exceeds column size')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
INSERT INTO MyLogTable
SELECT * FROM @Log
Edit: been bitten by the quote bug...
-- Gianluca Sartori
April 24, 2012 at 9:30 am
Here are the questions:
They can be the target of an OUTPUT clause of an INSERT, UPDATE, or DELETE.
Can't we use temp table there?
They can be used to capture audit data that needs to be persisted from within a transaction that has to rollback due to errors.
Can you pls elaborate this with an example?
They are effecient for small amounts of data.
IS it because no stats are maintained for them?
Lynn Pettis (4/24/2012)
Table variables have their uses.They can be the target of an OUTPUT clause of an INSERT, UPDATE, or DELETE.
They can be used to capture audit data that needs to be persisted from within a transaction that has to rollback due to errors.
They are effecient for small amounts of data.
I am suer we could come up with more detailed uses if needed.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 24, 2012 at 9:34 am
S_Kumar_S (4/24/2012)
Here are the questions:They can be the target of an OUTPUT clause of an INSERT, UPDATE, or DELETE.
Can't we use temp table there?
Yes.
They can be used to capture audit data that needs to be persisted from within a transaction that has to rollback due to errors.
Can you pls elaborate this with an example?
See my previous example.
They are effecient for small amounts of data.
IS it because no stats are maintained for them?
It's because (almost) no log is maintained for them.
-- Gianluca Sartori
April 24, 2012 at 9:34 am
They log less than temp tables (though not a huge amount less)
They aren't affected by an explicit rollback
The don't cause recompiles when data changes (may be a good thing, maybe a bad)
They are scoped the same as variables, whereas temp table scope is a fair bit more complex and can (and does) confuse people
They can be used inside user-defined functions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 9:47 am
Gilamonster has a good summary. There are differences where one works and the other doesn't, and places where it doesn't matter too much which one you use.
It pays to learn about the differences and use the one that suits your situation better. If it doesn't matter, use the one that you are more comfortable with.
April 24, 2012 at 9:52 am
Really nicely summarized points. Thanks Gail. There were new things here for me.
GilaMonster (4/24/2012)
They log less than temp tables (though not a huge amount less)They aren't affected by an explicit rollback
The don't cause recompiles when data changes (may be a good thing, maybe a bad)
They are scoped the same as variables, whereas temp table scope is a fair bit more complex and can (and does) confuse people
They can be used inside user-defined functions.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply