April 18, 2008 at 9:07 am
I'm going to throw this one out as it's beyond my understanding but looks and feels like extremely bad practice to me. Not to mention potentially dangerous.
In a nutshell, a 3rd party application that uses extremely sensitive data is creating "SCRATCHPAD" tables in the tempdb, along with roles and users in tempdb to support access to them. These are obviously created each time the SQL service is restarted.
When I questioned the need to do this, I was told by their DBA that this is for performance reasons as it avoids unnecessary IO on checkpoints as tempdb isn't checkpointed and doesn't flush data pages to disk. He also cited the following document for reference:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Is there any potential benefit from doing this? How busy would a table need to be before a tangible performance benefit would be seen?
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 18, 2008 at 5:32 pm
Rob Goddard (4/18/2008)
I'm going to throw this one out as it's beyond my understanding but looks and feels like extremely bad practice to me. Not to mention potentially dangerous.In a nutshell, a 3rd party application that uses extremely sensitive data is creating "SCRATCHPAD" tables in the tempdb, along with roles and users in tempdb to support access to them. These are obviously created each time the SQL service is restarted.
When I questioned the need to do this, I was told by their DBA that this is for performance reasons as it avoids unnecessary IO on checkpoints as tempdb isn't checkpointed and doesn't flush data pages to disk. He also cited the following document for reference:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
Is there any potential benefit from doing this? How busy would a table need to be before a tangible performance benefit would be seen?
tempdb is called that because its a database for storing temporary objects, so it seems odd to use it to store objects that are permanent (at least between SQL server bounces). I presume the data held in these tables is unimportant because it is obviously at risk of being lost. There would have to be a lot of inserts and updates at a high frequency to affect io performance, is your server io bound? If there are lots of inserts that means more memory used up by these objects, so perhaps increasing physical io elsewhere in the system. Must be better ways to control io (good code, good database design, good indexing strategy). They must be really worried about performance to go to these lengths.
Ask their DBA if he has figures on the performance improvement expected.
Would be interesting to know what is held in these tables that the data needs to be held but is not required between server bounces.
I would be more worried if there were other apps sharing this SQL instance (and thus tempdb) then if only this one app was running on it. If all is running ok then you have to decide if the risk to this data is acceptable and an app redesign (which will have to come from the vendors ) is achievable and worth going after.
HAving said all the above I have to admit I did not realize tempdb was not flushed to disk by checkpointing so maybe this is a real whizzo idea, but as I said, they must be desperate to wring every last bit of performance out of the app, having tuned everything else (you hope)
---------------------------------------------------------------------
April 21, 2008 at 5:25 am
The tables definitely contain data that doesn't need to be persistent, but the server is shared by quite a few different systems...
What's the difference, security wise, between physically creating tables, users and roles in the tempdb, or just letting SQL handle it with global ## tables? Is there a security hole in storing temporary but sensitive data in global ## temp tables?
I have asked for performance figures on this, so I'll wait and see what is said. I have found documentation on 2005 (not 2000) that seems to insinuate that the handling of data pages in the tempdb is no different to that of a user database, and all the changes in I/O are in the handling of the log pages:
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 22, 2008 at 2:08 pm
I would suggest adding a new database for this tables using the simple recovery model to hold the tables. That way the tables will survive a reboot which can help in trouble shooting if needed.
April 24, 2008 at 7:41 am
Jim Wall (4/22/2008)
I would suggest adding a new database for this tables using the simple recovery model to hold the tables. That way the tables will survive a reboot which can help in trouble shooting if needed.
That's a great idea. The actual main database is currently using the simple recovery method, but as there are plans to move to full, it may be worth putting this idea across.
Is there reference material anywhere that states the flushing of dirty data pages to disk is handled in the same way in the tempdb as in a user database set to the simple recovery model?
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply