June 21, 2016 at 8:28 am
Hi!
Maybe some of you can help us 🙂
We've a fresh installation of SharePoint 2013 and along with that a new SQL-Server 2014 SP1 (Standard).
Some facts about the SQL server:
- running on ESX 6.0
- 4 vCPUs
- 32 GB RAM
- 5 virtual disks (based on a HP 3PAR SAN [SSD only]) for system C, for data, for temp and for log (every disk NTFS 64k)
We've created 4 temp files each on the temp disk (initial size 8200Mb, growing by 400MB).
Based on the following query:
SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
We have avg_write_stall_ms = 850ms, and avg_read_stall_ms = 1.2ms
It's just a single instance SQL server and SharePoint 2013 is the only system which uses the instance.
Does anybody know reasons why TempDB has that high write latency?
For SCOM we use the same configuration (another SQL Server) . SCOM doesn't have any issues with TempDB.
regards,
Simon
June 21, 2016 at 9:17 am
Kindly check on below points
1. how many reads and writes per second. compared to the response time.
2. did u contacted sharepoint developer for this.
3. what is the disk configuration, is it a SAN disk?.
4. is it during specific duration the issue occurs and any other activity during that time?.
Regards
Durai Nagarajan
June 21, 2016 at 9:34 am
Not unusual for Sharepoint.
https://technet.microsoft.com/en-us/library/ff945791.aspx
There are a lot more, google it!
Did you set maxdop = 1?
Have you turned on trace flag T1117 and T1118?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 21, 2016 at 1:09 pm
How about a poorly configured or overwhelmed IO path between host and SAN? I've seen a silly 1Gbps iSCSI IO path at clients more times than I care to remember.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2016 at 1:34 am
Thanks for your Answers!
The Task to check our TempDB Performance came from an SharePoint Developer (with no SQL Skills).
So we tried to google for solutions we found this forum here 🙂
Let me tell you a little more about what've already tried:
- We removed the virus scanner from the system
- We created more TempDB Files, (deleted them after no success)
- We increased the start size of TempDB Files.
- We already restarted the (production) SharePoint Environment
- We disabled Auto Create Statistics
- We moved the virtual machine to another LUN (etc.)
- ...
Facts about the SAN
It's a RAID 5 build with SSD only (HP 3PAR), connected with multiple 16 Gbit/s HBAs. Every ESX Hosts has 2 16Gbit/s HBAs.
The Issues are permanent.
MaxDop is already set to 1.
TraceFlag T1117 is active.
TraceFlag T1118 is not set. Could that be the Reason?
I executed sp_blitz on the Hosts and under the Performance Group it says that many Plans (759) for One Query are present...
Could that be a reason?
along with that I thought about to executeDBCC FREEPROCCACHE. I'm scared to fire the query because SharePoint is in use.
Some Write/Read Stats are following!
Many Thanks for your answers! You're Great!
(if you like chocolate - drop me a pm to send you some swiss-chocolate :-P)
June 22, 2016 at 5:08 am
SharePoint is not the most efficient application. The results from sp_blitz are normal.
Freeing the cache is not going to do much.
Have you captured the file growths on tempdb? Are you sure that tempdb is actually the issue?
Keep in mind that deadlocks and blocking are normal in SharePoint. It's designed to handle that!
I would likely add more RAM. As an example, I have 384 GB allocated on my SharePoint servers.
I also would look into moving the search databases onto another server, separate from the content databases.
How large are your content databases?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 22, 2016 at 7:36 am
1) There are umpteen things that could be at play here. Search the web for queries that tell you what is consuming tempdb resources.
2) Glenn Berry's SQL Server Diagnostic scripts are a great resource for sure.
3) sp_whoisactive is also awesome. It can tell you tempdb usage, and also has a differential mode where you can let it run for some seconds and it will tell you the usage of various resources during that time.
4) Consider hiring a professional tuner to very quickly find the root cause(s) of the problems and offer corrective actions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 24, 2016 at 2:28 pm
Figure out what exactly issue is and events waiting for
Good article to gather wait stats stats
https://www.brentozar.com/responder/triage-wait-stats-in-sql-server/[/url]
June 24, 2016 at 4:52 pm
sith (6/22/2016)
... SharePoint Developer (with no SQL Skills).
Not quite on-subject but you have no idea how much I enjoyed that little bit of irony. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply