Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.

We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of action to TempDB (ok, I think it's "quite a lot of action" - it may not be!). I installed a demo version of RamDisk Plus Server, created a 50GB ramdrive, pointed TempDB at it, and saw no improvement in performance at all.

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache? Is a ramdisk pointless in this scenario? I know SQL Server 6.5 (showing my age here) had support for TempDB-In-Ram, but I see that was discontinued a long time ago!




[Even though this question is marked 'Answered', I'd like to encourage your input since I believe a thorough understanding of how SQL Server manages memory and disk is going to become more and more important as our databases see large growth - I'd like to convert this to a posting on http://dba.blogoverflow.com if there is enough interest.]

share|improve this question
    
When I say "quite a lot of action" I mean we are averaging 50 iops, with 2.6ms average latency, at 3.2MB / second on the primary servers TempDB LUN. –  Max Vernon Sep 4 '12 at 20:39
    
Max, unrelated to this question I wanted to thank you for all your activity with editing and cleaning up other people's posts. It has been noticed and greatly appreciated. –  JNK Sep 5 '12 at 17:25
2  
My pleasure - this site is a great resource for me, and I just wanted to give back a little! –  Max Vernon Sep 5 '12 at 17:26
add comment

1 Answer

up vote 10 down vote accepted

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache?

Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical mechanism. Reference here is What does checkpoint do for tempdb?.

In a "well behaved" system, writes to a user database file occur on checkpoint. In a badly behaved system, writes will also occur when the lazywriter needs to flush pages from the buffer pool to make room for other pages.

A checkpoint is only done for tempdb when the tempdb log file reaches 70% full - this is to prevent the tempdb log from growing if at all possible (note that a long-running transaction can still essentially hold the log hostage and prevent it from clearing, just like in a user database).

But there is no need to flush tempdb to disk, as crash recovery is never run on tempdb, it's always recreated on startup.

Tempdb is not recovered in the event of a crash, and so there is no need to force dirty tempdb pages to disk, except in the case where the lazywriter process (part of the buffer pool) has to make space for pages from other databases.

This remarkably (it was a surprise to me) is the only mechanism by which tempdb pages will be written to disk. If there is buffer pool pressure, tempdb pages may be flushed to disk. If there isn't, it should not occur.

Edit: Debatable whether "badly behaved" is an appropriate description for a user database that is writing pages outside of checkpoint. Unusual, atypical, or just not ideal maybe?

Additional edit (following comments/chat with @SqlKiwi):

The glaring omission above is that temporary tables are not sole source of tempdb traffic. Quoting from Understanding Hash, Sort and Exchange Spill Events:

Certain SQL Server query execution operations are calibrated to perform best by using a (somewhat) large amount of memory as intermediate storage. The Query Optimizer will choose a plan and estimate the cost based on these operators using this memory scratch-pad. But this is, of course, only an estimate. At execution the estimates may prove wrong and the plan must continue despite not having enough memory. In such an event, these operators spill to disk.

I had incorrectly assumed that the mechanism behind a physical write for a spill operation were exactly the same as described earlier i.e. the lazywriter forcing tempdb pages to disk as a result of pressure on the buffer pool (caused by the spill).

@SqlKiwi explained where I was wrong (thanks Paul!):

I think you are asking why physical tempdb activity occurs when a query exceeds its workspace memory grant, rather than just using tempdb-in-memory If it did that, the query would be using more memory than its grant, defeating the point of restricting memory grants in the first place.

Spills are indeed special in writing through to storage. Physical tempdb activity is seen on a spill, even in the face of oodles of free memory and zero pressure on tempdb.

Paul also pointed me to his blog post Advanced TSQL Tuning: Why Internals Knowledge Matters which includes example scripts for demonstrating spills, for those that want to delve deeper.

share|improve this answer
    
Wow, Mark, thanks! That explains why the Tempdb-in-RAM is no longer an option - it's defacto on all the time! –  Max Vernon Sep 4 '12 at 23:20
2  
+1 but adding that other tempdb activity may cause writes. One example is workspace memory for sorts, hashes, and parallelism operators. If the allocation turns out to be too small for the query (often a cardinality estimation problem rather than a server memory issue) the sort/hash/exchange will spill to physical tempdb disk. –  Paul White Sep 5 '12 at 12:34
    
@SQLKiwi Does a sort/hash spill not occur by the same mechanism i.e. ultimately buffer pool pressure as a result of the tempdb allocations, which causes the lazywriter to make room? –  Mark Storey-Smith Sep 5 '12 at 12:54
    
@MarkStorey-Smith No. Workspace memory is requested based on optimizer estimates, which may be incorrect for any of the usual reasons (typically cardinality estimation). A query that requests 50MB for sorts/hashes and ultimately needs 100MB will spill to physical tempdb. See sqlblog.com/blogs/paul_white/archive/2011/02/23/… for a detailed example and discussion. –  Paul White Sep 5 '12 at 13:31
2  
@SQLKiwi I understand the workspace issue, what I'm not clear on is how a spill/hash is bypassing the buffer pool and interacting with physical tempdb. Given the scenario you describe, if there were sufficient (100MB) of free pages in the buffer pool, how/why would there be physical writes to tempdb files? Pop into chat if you get a moment, might be easier/quicker :) –  Mark Storey-Smith Sep 5 '12 at 13:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.