marrowyung
asked on
tempdb latch contention
hi,
some tools report to us that we have tempdb latch contention, which from their support point of view, we need to add one more .ndf for the tempdb to make one more file for load balancing purpose.
so we only solve this by latch contention ?
some tools report to us that we have tempdb latch contention, which from their support point of view, we need to add one more .ndf for the tempdb to make one more file for load balancing purpose.
so we only solve this by latch contention ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ste5an,
as I am using Idera's DM and it has a feature to detect it and it send out alert like this when it detected it.
the message when it detected it is :
"Adding one additional tempdb file sounds wrong. H"
actually is Idera said this feature and this alert only means add one more tempdb files.
"How many tempdb files do you have? When contention is a problem or r have a machine above a certain size, then you should use at least 4 files. btw, this is the default number in a SQL Server 2016 installation.
"
we have 16 tempdb .mdf files, and it seems by default SQL 2016 gives 8 tempdb files ? and it said just add one more if we see tempdb contention.
"Run sp_blitz from Brent Ozar."
this is for ?
pawankkmr,
" Add data files in batches of 4 until the contention is reduced to acceptable level
then we are right! but what links tell you this ?
I know that by MS logic on tempdb file has to be design with:
1) same as the # of physical CPU socket.
2) same # as the physical CPU Core.
3) 1/2 of the # as the phyical CPU core.
it seems more and more rules is here.
Daniel Jones,
by this link, it seems Idera DM is based on this meansure.
https://mssqlwiki.com/2013/09/17/tempdb-latch-contention/
I like this one too:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
it seems adam use his whoisactive to demo that, right ?
as I am using Idera's DM and it has a feature to detect it and it send out alert like this when it detected it.
the message when it detected it is :
DATETIME, Tempdb Contention (ms) on <hostname> is Critical.
Tempdb latch contention has been detected on <hostname>. The total wait time detected is 4843 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.
PFS Wait Time: 4843 ms
GAM Wait Time: 0 ms
SGAM Wait Time: 0 ms
Tempdb latch contention has been detected on <hostname>. The total wait time detected is 4843 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.
PFS Wait Time: 4843 ms
GAM Wait Time: 0 ms
SGAM Wait Time: 0 ms
"Adding one additional tempdb file sounds wrong. H"
actually is Idera said this feature and this alert only means add one more tempdb files.
"How many tempdb files do you have? When contention is a problem or r have a machine above a certain size, then you should use at least 4 files. btw, this is the default number in a SQL Server 2016 installation.
"
we have 16 tempdb .mdf files, and it seems by default SQL 2016 gives 8 tempdb files ? and it said just add one more if we see tempdb contention.
"Run sp_blitz from Brent Ozar."
this is for ?
pawankkmr,
" Add data files in batches of 4 until the contention is reduced to acceptable level
then we are right! but what links tell you this ?
I know that by MS logic on tempdb file has to be design with:
1) same as the # of physical CPU socket.
2) same # as the physical CPU Core.
3) 1/2 of the # as the phyical CPU core.
it seems more and more rules is here.
Daniel Jones,
by this link, it seems Idera DM is based on this meansure.
https://mssqlwiki.com/2013/09/17/tempdb-latch-contention/
I like this one too:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
it seems adam use his whoisactive to demo that, right ?
ASKER
hi,
it seems that by adam's page:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
he also suggest just add one more file !! hahhaha
it seems that by adam's page:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
he also suggest just add one more file !! hahhaha
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think in this case it is easier to handle AT THAT TIME in case too much tempdb request at the same time, but I'd like to say NEARLY the same time.
ASKER
tks all, I will turn on the detection of this.
ASKER
hi,
I read this:
http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
this say tempdb contention is referring to db contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O. from my point of view, it should be above accessing the tempdb file so it is about contention on both logical and physical IO, right?
I read this:
http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
this say tempdb contention is referring to db contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O. from my point of view, it should be above accessing the tempdb file so it is about contention on both logical and physical IO, right?
ASKER
I am sorry, one more question.
I read this:
http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
The best guidance I’ve seen is from a great friend of mine, Bob Ward, who’s the top Escalation Engineer in Microsoft SQL Product Support. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too. (This advice is now official Microsoft guidance in KB article 2154845.)
" add more in chunks of 4"
this means if we have 12 cores and we already have 16 tempdb. mdf files, the next time we see tempdb latch contention, we still add 4 more tempdb .mdf file of the same sizes as the existing one ?
I read this:
http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
The best guidance I’ve seen is from a great friend of mine, Bob Ward, who’s the top Escalation Engineer in Microsoft SQL Product Support. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too. (This advice is now official Microsoft guidance in KB article 2154845.)
" add more in chunks of 4"
this means if we have 12 cores and we already have 16 tempdb. mdf files, the next time we see tempdb latch contention, we still add 4 more tempdb .mdf file of the same sizes as the existing one ?
ASKER
one thing, any thing like tempdb log contention ? from whoisactive, I can see the wait type from time to time is tempdb log or reportserver log, is that mean I have add one more tempdb log file ?
and under what situation should I add one more tempdb log file ? SQL server access tempdb log file in round robin manner also ?
and under what situation should I add one more tempdb log file ? SQL server access tempdb log file in round robin manner also ?
ASKER
hi, any update?
Adding one additional tempdb file sounds wrong. How many tempdb files do you have? When contention is a problem or r have a machine above a certain size, then you should use at least 4 files. btw, this is the default number in a SQL Server 2016 installation.
And for such poblems: Run sp_blitz from Brent Ozar.