December 24, 2010 at 9:46 am
Comments posted to this topic are about the item Identify Allocation Contention in TEMPDB
Brad M. McGehee
DBA
June 17, 2011 at 3:34 am
Brad, I would like to suggest a small correction to your script. Since the GAM and SGAM pages are repeated every 64000 extents (~4Gb), this means that you will have to introduce some logic in your script to check for this, especially in a high volume production systems, where the tempdb files are way beyond 4Gb.
For example, you can use a formula similar to:
for GAM pages: (the page ID is 2) % 511232
for SGAM pages: (the page ID is 3) % 511232
Otherwise your script will be inaccurate.
Regards,
Feodor Georgiev
Make everything as simple as possible, but not simpler. –Albert Einstein
June 17, 2011 at 10:01 am
Feodor, I am aware of this issue of the script, but it not really that important for two reasons. First, most tempdb databases are smaller than 4 GB. And two, even if they are larger than 4 GB, realizing that contention is happening in the initial allocation pages is enough information to know that you have a problem. It is kind of like taking the pulse of a person. While knowing the pulse doesn't give you the entire picture of somebody's health, it still is enough information to know if there is a potential problem or not.
Brad M. McGehee
DBA
June 17, 2011 at 1:34 pm
Point taken, Brad. You are right that it is not needed to eat the entire egg in order to know it's spoiled. :hehe:
On the other hand, in my organization today we barely have any tempdb less than 4Gb, and also, I really like to see my data through.
Make everything as simple as possible, but not simpler. –Albert Einstein
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply