SanDroid (12/14/2010)
Nils Gustav Stråbø (12/14/2010)
vk-kirov (12/14/2010)
The bad news is that, according to BOL, "this view is applicable only to the tempdb database".I would not expect much change in SGAM pages in a normal database. Table creation rates should be low compared to tempdb, so frequent changes in SGAM page allocation is unusual.
I believe this is more important to monitor when you are using Mirroing or Publishing a VLDB.
Also it may be usefull you execute large querries (5K+ rows) that use TEMP tables or Table Parameters that have to create objects to store data in TEMPdB.
No, Nils is quite correct.
I have no idea why you think mirroring, or publishing a large database would be a prime factor in tempdb SGAM contention.
Where does your magic figure of 5K+ rows come from, or are you just guessing?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi