April 15, 2014 at 11:55 pm
Hi all,
I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.
Please note that I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.
FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
I am going to look at doing some form of an audit of the write traffic in tempdb to try and find any clues but I would greatly appreciate any advice anyone has with similar issues.
Kind regards,
matt
April 16, 2014 at 12:28 am
how about network?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 16, 2014 at 8:14 am
mattmc (4/15/2014)
Hi all,I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.
Please note that I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.
FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
I am going to look at doing some form of an audit of the write traffic in tempdb to try and find any clues but I would greatly appreciate any advice anyone has with similar issues.
Kind regards,
matt
1) How many cores do you have (physical, not hyperthreaded)? The advice for 1 tempdb file per core is almost always outdated advice on modern hardware. You can actually make IO slower by having too many files on 1 or a small number of rotating spindles.
2) I have heard that SP can need some additional indexing to make it perform acceptably. But if you do that you will need to drop them if you need support or when you patch because doing that is not supported.
3) You can examine the execution metrics using some DMVs. Tim Ford has a free eBook about that (and lots of other DMV help).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 16, 2014 at 6:45 pm
1. I have X4 Temdb Data files which matches to the actual cores (Not Hyper threaded).
2. Yes I have read that somewhere also yesterday during my hunt for some answers. I
did some trawling for suggested missing indexes on the sharepoint databases and there
were not many standout suggestions returned but regardless i will go through and create
some anyways and see if this gives an improvement. I think it makes sense in that if for
large search operations indexes are not present then I think sharepoint is forced to use
tempdb to help it to do a search. This is my best guess at the moement.
3. Ok thanks for the heads up, I will look at doing that to try and track down some of the
operations and see if any clues present themselves.
I appreciate your advice and feedback-ta
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply