I'm running into some behaviour that appears strange to me. I'm using SQL Server 2008 Enterprise, 32-bit (quad Core 2), on Windows 7 (for testing).
I have a stored procedure that uses two table variables. One gets about 2 or 3 rows inserted, the other gets 0 to 100 rows. Then I select out maybe 20-60 rows from the second, and that's it.
Performance is pretty fast. I created a simple app to loop doing queries, and I can do 1300/sec with 1 thread, and around 4000 with 4 threads.
Enter tempdb: When I open the resource monitor to see what's going on, I see that there is a lot of writing to tempdb logfiles. (I created 2, 100MB on 2 different physical disks -they don't seem to grow past 100MB.) There is zero read activity — the entire DB fits in RAM.
With a single thread running queries, there is about 3MB/sec write to tempdb logfiles. As I increase that, it goes up to 20MB/sec per logfile.
In the SQL Activity Monitor, "Logging" goes over 300ms/sec for "Wait Time" when I'm using 5 threads. At 3 threads, it's down to 25ms/sec.
Question: What's going on? Why is SQL writing to tempdb logs like crazy, but issuing zero reads (I see no read activity in resource monitor or in activity monitor)? In a non-test environment, it seems to me that having an extra 40MB/sec write might be detrimental to overall performance.
I know table variables (@foo) are not always stored in memory, but I'm confused as to why tempdb has to log all this stuff. How can I troubleshoot what it's doing? Can I put tempdb's log on a ramdisk or something? Any other pointers?
Thanks in advance!
Best Answer
This is typical log write ahead behavior. When a page is updated in a database the update is first written into the log, then applied to the in memory page. The page stays dirty in memory until checkpoint occurs, at which point it written on disk. The log has to be written ahead of the update to support recovery and rollback. Unless one of these two occurs (recovery or rollback), there is no need to ever read again the log. So the behavior you see is typical for a system that modifies pages in tempdb. You would only see log reads if rollback would occur (since recovery cannot occur for tempdb).
A more interesting question is why are so many page updates occuring in tempdb? Typical culprits are either direct updates (eg. Session state in tempdb with ASP) or indirect ones (spools and sorts in querry plans).