Haripriya Naidu digs into tempdb:
This article discusses when version store starts generating rows, when cleanup takes place, and how TempDB space gets occupied due to this activity.
I’ve hardly used PerfMon much in the past, nor was I a fan of it, just like many of you. But, when I used it a few times to determine how many data files needed to be added to TempDB while resolving contention, it helped me make a clear decision on the number of files needed. Since then, I’ve become a fan of PerfMon.
Although I don’t use it daily, I don’t hesitate to turn to it when I think it might help me understand things better.Coming back to our topic of version store in this article, I resorted to PerfMon counters because visually watching the lines go up and down when an update is performed on a table helped me better understand how the version generation and cleanup works.
Click through for a demonstration of the process, including relevant PerfMon counters and the type of information you can get directly from SQL Server.