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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy