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 Sunday, March 31, 2013 10:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 66, Visits: 437
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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...
Post #1437303
Posted Monday, April 1, 2013 9:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
sharky (3/31/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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...



Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1437484
Posted Tuesday, April 2, 2013 1:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.
Post #1438071
Posted Tuesday, April 2, 2013 1:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.


Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.

Dont make this harder than it is.
Post #1438073
Posted Tuesday, April 2, 2013 3:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
DiverKas (4/2/2013)
rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.


Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.

Dont make this harder than it is.


Actually all the people who are architect of this database are from Oracle background (me as well). Without knowing the limitation of sql server temp table the database design was made and approved by client. Now if we want to create any new table that needs to be passed through lot of process which we want to prevent and that is the reason for so much complexity.
Post #1438107
Posted Tuesday, April 2, 2013 3:16 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 23,299, Visits: 32,048
rajarshi_ghosh_05 (4/2/2013)
DiverKas (4/2/2013)
rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.


Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.

Dont make this harder than it is.


Actually all the people who are architect of this database are from Oracle background (me as well). Without knowing the limitation of sql server temp table the database design was made and approved by client. Now if we want to create any new table that needs to be passed through lot of process which we want to prevent and that is the reason for so much complexity.


If it is to support a process inherent in the design of the database, then it should be included in the database.



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 #1438110
Posted Thursday, April 4, 2013 5:20 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 23,299, Visits: 32,048
ScottPletcher (4/1/2013)
sharky (3/31/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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...



Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.


First of all, I am still going to express my concern of SQL Server "Knowing" that certain things can or can't be done. This implies intelligence in the code itself, not conscious decisions made by developers when designing and implementing the code. Implementing enhancements and optimizations is not the same thing as a piece of software "knowing" something.

On to some interesting things. I have already been able to do a little playing with permanent tables in both a user database and in tempdb. I have found the that there are minor differences in transaction logging between the two. With the simplistic testing I have been able to do so far, I really don't think it is enough to justify using tempdb in the manor suggested by the OP or Scott.

I do firmly believe that if the process being developed is meant to support a business process inherent in the application that the database tables needed to support that process belong in the database itself. It is a waste of time and effort to attempt to bypass change control just because it is a difficult process. If the customer/client wants a specific behavior in the application and it requires changes to the database (the addition of tables in this case), then show them this and explain it. If they want that behavior they should then approve the changes necessary to support it. The other choice is to go without the behavior.



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 #1438722
Posted Thursday, April 4, 2013 8:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
Lynn Pettis (4/4/2013)
ScottPletcher (4/1/2013)
sharky (3/31/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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...



Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.


First of all, I am still going to express my concern of SQL Server "Knowing" that certain things can or can't be done. This implies intelligence in the code itself, not conscious decisions made by developers when designing and implementing the code. Implementing enhancements and optimizations is not the same thing as a piece of software "knowing" something.

On to some interesting things. I have already been able to do a little playing with permanent tables in both a user database and in tempdb. I have found the that there are minor differences in transaction logging between the two. With the simplistic testing I have been able to do so far, I really don't think it is enough to justify using tempdb in the manor suggested by the OP or Scott.

I do firmly believe that if the process being developed is meant to support a business process inherent in the application that the database tables needed to support that process belong in the database itself. It is a waste of time and effort to attempt to bypass change control just because it is a difficult process. If the customer/client wants a specific behavior in the application and it requires changes to the database (the addition of tables in this case), then show them this and explain it. If they want that behavior they should then approve the changes necessary to support it. The other choice is to go without the behavior.



C'mon, that's why I put "knowing" in quotes: to clearly indicate that it's not intended to be taken literally ( even by the perpetually pedantic ).

Hmm, by that latter reasoning, temp tables themselves should not be used then? And all work space used in db processing should be in that db?!


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1438848
Posted Thursday, April 4, 2013 9:14 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 23,299, Visits: 32,048
ScottPletcher (4/4/2013)
Lynn Pettis (4/4/2013)
ScottPletcher (4/1/2013)
sharky (3/31/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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...



Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.


First of all, I am still going to express my concern of SQL Server "Knowing" that certain things can or can't be done. This implies intelligence in the code itself, not conscious decisions made by developers when designing and implementing the code. Implementing enhancements and optimizations is not the same thing as a piece of software "knowing" something.

On to some interesting things. I have already been able to do a little playing with permanent tables in both a user database and in tempdb. I have found the that there are minor differences in transaction logging between the two. With the simplistic testing I have been able to do so far, I really don't think it is enough to justify using tempdb in the manor suggested by the OP or Scott.

I do firmly believe that if the process being developed is meant to support a business process inherent in the application that the database tables needed to support that process belong in the database itself. It is a waste of time and effort to attempt to bypass change control just because it is a difficult process. If the customer/client wants a specific behavior in the application and it requires changes to the database (the addition of tables in this case), then show them this and explain it. If they want that behavior they should then approve the changes necessary to support it. The other choice is to go without the behavior.



C'mon, that's why I put "knowing" in quotes: to clearly indicate that it's not intended to be taken literally ( even by the perpetually pedantic ).

Hmm, by that latter reasoning, temp tables themselves should not be used then? And all work space used in db processing should be in that db?!


Really, you are going there? And you think I am being perpetually pedantic?

No, I am not advocating NOT using temp tables. I am advocating that temp tables have no business being used to hold temporal data over a period of time to support a business process, especially when that business process hasn't been fully explained. No one has answered the question I put forth earlier. Does the loss of data in that temporary table matter if there is restart fo SQL Server during the time frame in which the data is to be persisted (in this case 120 minutes). If data is written at 9:00 AM and the server restarts at 9:10 AM is there a problem with the loss of data written 10 minutes earlier, or 100 minutes earlier?


Temp tables, table variables, all have a purpose and a time to use them. Use them correctly.



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 #1438859
Posted Thursday, April 4, 2013 11:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Lynn Pettis (4/4/2013)
ScottPletcher (4/4/2013)
Lynn Pettis (4/4/2013)
ScottPletcher (4/1/2013)
sharky (3/31/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?



Although data gets logged in tempdb as well, there is a difference in the way it is logged and the amount of overhead required.

1)Because no recovery is required, only the original data is logged, and not the new values(as in another database). This requires less logging
2) Tempdb does not have to be recovered, and therefore the log records does not have to be flushed synchronously in Tempdb

This is what is meant by "knowing"...



Yes, really. SQL can take certain shortcuts when logging data for tempdb, "knowing" that a forward recovery will never be required.

Of course there are at least a dozen ways to use a non-tempdb table to do it, but all of them will intrinsically have at least slightly more overhead, and more if the non-tempdb db is in full recovery vs simple, as user dbs tend to be.


First of all, I am still going to express my concern of SQL Server "Knowing" that certain things can or can't be done. This implies intelligence in the code itself, not conscious decisions made by developers when designing and implementing the code. Implementing enhancements and optimizations is not the same thing as a piece of software "knowing" something.

On to some interesting things. I have already been able to do a little playing with permanent tables in both a user database and in tempdb. I have found the that there are minor differences in transaction logging between the two. With the simplistic testing I have been able to do so far, I really don't think it is enough to justify using tempdb in the manor suggested by the OP or Scott.

I do firmly believe that if the process being developed is meant to support a business process inherent in the application that the database tables needed to support that process belong in the database itself. It is a waste of time and effort to attempt to bypass change control just because it is a difficult process. If the customer/client wants a specific behavior in the application and it requires changes to the database (the addition of tables in this case), then show them this and explain it. If they want that behavior they should then approve the changes necessary to support it. The other choice is to go without the behavior.



C'mon, that's why I put "knowing" in quotes: to clearly indicate that it's not intended to be taken literally ( even by the perpetually pedantic ).

Hmm, by that latter reasoning, temp tables themselves should not be used then? And all work space used in db processing should be in that db?!


Really, you are going there? And you think I am being perpetually pedantic?

No, I am not advocating NOT using temp tables. I am advocating that temp tables have no business being used to hold temporal data over a period of time to support a business process, especially when that business process hasn't been fully explained. No one has answered the question I put forth earlier. Does the loss of data in that temporary table matter if there is restart fo SQL Server during the time frame in which the data is to be persisted (in this case 120 minutes). If data is written at 9:00 AM and the server restarts at 9:10 AM is there a problem with the loss of data written 10 minutes earlier, or 100 minutes earlier?

Temp tables, table variables, all have a purpose and a time to use them. Use them correctly.


No. We dont bother about the temp table data not if anything happens like network failure or server restart or user system restart! We dont have to preserve that data. Only thing if nothing happens then we need to keep the data till 120 min; because user can download the data in excel at anytime between this.
Post #1438940
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse