April 27, 2011 at 11:37 pm
Hi Guys,
My company is having a discussion with a vendor concerning implementation of their product and ways to handle TempDB.
A concept was raised to use RAMDisk to store the TempDB files to improve performance. It was then noted that if there is that much RAM available then just ditch RAMDisk and assign it all to SQL Server, because "SQL Server can run 100% in memory, TempDB is only ever used when memory runs out."
This seems wrong, as I'm sure I've seen references to tasks that bypass memory and operate only in TempDB, but I don't know enough about the internals of SQL Server [2008] to definitively rule this in or out. Does anyone have a handy list of "Things that only live in TempDB and go nowhere near Memory"? Or even items that start in memory and slip to TempDB regardless of free memory available?
Thanks,
Steve.
April 28, 2011 at 5:09 am
Tempdb is used by so much it's hard to list it all. I suppose if you had terrabytes of memory and gigabytes of data you might not see tempdb used as much, but it would still be used.
It's used by, off the top of my head:
Hash tables in exec plans
Cursors
Ordering in exec plans
index creation
index updates
temporary tables
table variables
snapshot data
There's more if I started thinking harder. Tempdb is a major piece of the pie and has to be accounted for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2011 at 7:01 am
Yes, I was somewhat thrown when it was suggested that TempDB would be redundant if there was enough RAM. Problem is that the vendor concerned is, well, prestigious enough that what they say shouldn't need checking. My problem was trying to disprove this with items which live solely in TempDB.
Steve.
April 28, 2011 at 7:49 am
I would still be inclined to assign the RAM to SQL Server before using it for tempdb and let tempdb stay on physical disk, unless SQL Server is generating no read IO at all.
April 28, 2011 at 8:14 am
Being in TempDb doesn't mean not being in memory. TempDB is treated like any other database, in memory where possible, written to disk when not.
That said, stuff certainly gets written down even when there's no memory pressure at all. I've found temp tables in the physical files when the server still had tonnes of memory available.
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 28, 2011 at 10:09 am
tempdb is not like as swap partition in linux or virtual memory concept in windows,The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb
http://msdn.microsoft.com/en-us/library/ms190768.aspx
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 28, 2011 at 10:21 am
GilaMonster, what you say is my assumption and experience also. Looking at sys.dm_io_virtual_file_stats there is definately write and read activity on tempdb data files, even on systems with large amounts of free memory.
I have a new server with 128GB RAM where 100 is allocated to SQL Server. There is currently about 7.000.000 free pages (~53GB free buffer cache). TempDb has 40GB of data files and a 4 GB log file where just a few percent is used.
Even with 53 GB of free memory I still se I/O on TempDb and TempDb log file usage is way below 70%, which causes (most commonly) a checkpoint for databases in SIMPLE recovery.
Based on these experiences, I'm a little puzzled when I read this blog by Paul and Kimberly about checkpoints in tempdb.
Another checkpoint behavior that is different for tempdb is that changed data file pages are not written to disk when an automatic tempdb checkpoint occurs. There is no reason for them to be written to disk as, again, there is no crash recovery for tempdb and no reason to provide on-disk durability of committed operations for tempdb.
What is causing the frequent writes to TempDb if checkpoint isn't?
April 28, 2011 at 10:26 am
I don't think that tempdb is ever redundant. It's used for a variety of things as mentioned above, and it isn't necessarily because of memory.
May 2, 2011 at 6:55 pm
TempDb and Memory are completely 2 different concepts. You can't replace the tempdb functionality with memory. Tempdb will be in use even though you have lots of free memory for a variety of reasons. That is due to the architecture itself .
Thank You,
Best Regards,
SQLBuddy.
May 3, 2011 at 8:33 am
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.
May 3, 2011 at 8:46 am
Steve Jones - SSC Editor (5/3/2011)
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.
MCM Book again? I think I'll go get me one of those :w00t:. Where can I buy the study stuff?
May 3, 2011 at 8:56 am
May 3, 2011 at 8:57 am
Ninja's_RGR'us (5/3/2011)
Steve Jones - SSC Editor (5/3/2011)
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.MCM Book again? I think I'll go get me one of those :w00t:. Where can I buy the study stuff?
You can get far without buying anything (other than a good internet connection)
http://www.sqlskills.com/T_MCMVideos.asp
http://www.sqlskills.com/MCM.asp
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
May 3, 2011 at 8:58 am
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Steve Jones - SSC Editor (5/3/2011)
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.MCM Book again? I think I'll go get me one of those :w00t:. Where can I buy the study stuff?
You can get far without buying anything (other than a good internet connection)
You're right, I only have 20 MBPS here. Do I have to upgrade to T3 speed :w00t:.
Thanks, both of you.
May 3, 2011 at 9:10 am
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
Steve Jones - SSC Editor (5/3/2011)
There are a few things that will automatically go to disk with tempdb. After some checking, you cannot get away from spillage during queries. If a query is estimated to use 10MB of memory, and it uses 15, then that 5MB will spill to disk, no matter what. Doesn't matter if you have 4GB or 4TB of RAM.MCM Book again? I think I'll go get me one of those :w00t:. Where can I buy the study stuff?
You can get far without buying anything (other than a good internet connection)
You're right, I only have 20 MBPS here. Do I have to upgrade to T3 speed :w00t:.
It's only about 12 GB of videos. You should be able to get it down in reasonable time.
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
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply