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!