Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify Allocation Contention in TEMPDB Expand / Collapse
Author
Message
Posted Friday, December 24, 2010 9:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Comments posted to this topic are about the item Identify Allocation Contention in TEMPDB

Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1039137
Posted Friday, June 17, 2011 3:34 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 5:23 AM
Points: 2, 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
Post #1127166
Posted Friday, June 17, 2011 10:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1127483
Posted Friday, June 17, 2011 1:34 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 5:23 AM
Points: 2, 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.
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
Post #1127647
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse