SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identify Allocation Contention in TEMPDB


Identify Allocation Contention in TEMPDB

Author
Message
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 730
Comments posted to this topic are about the item Identify Allocation Contention in TEMPDB

Brad M. McGehee
DBA
feodor.georgiev
feodor.georgiev
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 16
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
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 730
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
feodor.georgiev
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 16
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search