Identify Allocation Contention in TEMPDB

  • Brad McGehee

    SSCertifiable

    Points: 5272

    Comments posted to this topic are about the item Identify Allocation Contention in TEMPDB

    Brad M. McGehee
    DBA

  • feodor.georgiev

    SSC Enthusiast

    Points: 104

    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

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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

  • feodor.georgiev

    SSC Enthusiast

    Points: 104

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply