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 ««12345»»»

How to preserve global temporary table data Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 3:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 20,677, Visits: 32,271
rajarshi_ghosh_05 (3/29/2013)
Lynn Pettis (3/29/2013)
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.


That's true but there are several process that needs to be followed prior to add anything in db design... so as long as the table created by sp and destroyed by DB itself... i guess we are ok...


Unless losing the table in tempdb due to an unexpected server crash causes you a problem. If it is in the current database, you can still recover the data if needed. If it is in tempdb, say bye bye data.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437112
Posted Friday, March 29, 2013 4:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 2,101, Visits: 3,158
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.

SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1437127
Posted Friday, March 29, 2013 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 20,677, Visits: 32,271
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.


Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437129
Posted Friday, March 29, 2013 4:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 17,617, Visits: 15,472
Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.


Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.


I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1437135
Posted Friday, March 29, 2013 4:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 20,677, Visits: 32,271
SQLRNNR (3/29/2013)
Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.


Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.


I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.


You can't backup tempdb, you get the following error:

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I just have a real problem with using tempdb in the manner suggested. And I have a problem with SQL Server "knowing" that since it doesn't have to recover data that there are less resources used when writing data to tables in tempdb. I mean, really?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437137
Posted Friday, March 29, 2013 4:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 20,677, Visits: 32,271
The only overhead not involved is the recovery process when SQL Server starts up. It doesn't have to run the redo/undo process on tempdb since it is recreated on startup. But then it probably still runs just doesn't have a lot to do since the t-log is empty.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437139
Posted Friday, March 29, 2013 4:57 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 17,617, Visits: 15,472
Lynn Pettis (3/29/2013)
SQLRNNR (3/29/2013)
Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.


Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.


I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.


You can't backup tempdb, you get the following error:

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I just have a real problem with using tempdb in the manner suggested. And I have a problem with SQL Server "knowing" that since it doesn't have to recover data that there are less resources used when writing data to tables in tempdb. I mean, really?


A bit pedantic but what you wrote and what Scott wrote are different. He said less overhead writing data there vs. what you said "when writing data to tables in tempdb."

Different meanings. When writing data (at that moment the write occurs) there is no difference in resources. But the way Scott wrote implies an overall perspecitve - imho. This means not just at the time of write but the whole kit and kaboodle. And since you can't backup tempdb - it is less resources.

Creating a permanent table in tempdb for these things is little different than using a temporary table. It is understood that it can be thrown away. The problem comes from not disposing of the table in tempdb when the process is done. And if the server restarts, then you have to create additional steps to ensure the recreation of the table the next time the process is to be run.

Why put it in tempdb - one good reason is you don't want those tables involved in backup or recovery processes. They are throw-away tables. On the other hand - why not create a database that holds these tables and not worry about backing that up, leave it in simple and build the table recreate processes as appropriate.





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1437140
Posted Friday, March 29, 2013 5:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 20,677, Visits: 32,271
SQLRNNR (3/29/2013)
Lynn Pettis (3/29/2013)
SQLRNNR (3/29/2013)
Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.


Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.


I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.



You can't backup tempdb, you get the following error:

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I just have a real problem with using tempdb in the manner suggested. And I have a problem with SQL Server "knowing" that since it doesn't have to recover data that there are less resources used when writing data to tables in tempdb. I mean, really?


A bit pedantic but what you wrote and what Scott wrote are different. He said less overhead writing data there vs. what you said "when writing data to tables in tempdb."

Different meanings. When writing data (at that moment the write occurs) there is no difference in resources. But the way Scott wrote implies an overall perspecitve - imho. This means not just at the time of write but the whole kit and kaboodle. And since you can't backup tempdb - it is less resources.

Creating a permanent table in tempdb for these things is little different than using a temporary table. It is understood that it can be thrown away. The problem comes from not disposing of the table in tempdb when the process is done. And if the server restarts, then you have to create additional steps to ensure the recreation of the table the next time the process is to be run.

Why put it in tempdb - one good reason is you don't want those tables involved in backup or recovery processes. They are throw-away tables. On the other hand - why not create a database that holds these tables and not worry about backing that up, leave it in simple and build the table recreate processes as appropriate.



Which goes to my concern in this case. If the data needs to be around for a period of time, 120 minutes apparently, then keeping this data in a permanent table in tempdb isn't necessarily a good idea. If there is a server restart for any reason, you lose that data.

I like the idea of another database if you can't use the main database the process is using to store a permanent temporary table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437153
Posted Friday, March 29, 2013 6:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 1,778, Visits: 5,730
I quite like the idea of having a permanent tables (not in tempdb) with a process to remove them, but not automatically...

I would have a system whereby you write an entry to another permanent table that records the "temporary" table name and it's expiration date/time, then have a process that cleans up expired tables. (You could even included a trigger on your "temporary" tables to perform a sliding expiration if you wanted)

You wold have to put some thought into making this process secure enough that the cleanup process couldn't be abused and tricked into removing anything except these "temporary" tables though!


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1437162
    Posted Saturday, March 30, 2013 12:17 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 8:09 AM
    Points: 35,216, Visits: 31,670
    Another problem with doing it using a permanent table, whether in TempDB or some other database, is the chance of a naming conflict. You could go through some gyrations to resolve such conflicts by including a DATETIME stamp in the name of the table, but I think it might be easier just to populate a single permanent table and delete only those rows that are no longer necessary using a "BatchID" or other form of row identification.

    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1437218
    « Prev Topic | Next Topic »

    Add to briefcase ««12345»»»

    Permissions Expand / Collapse