October 17, 2014 at 5:15 am
Hello
TempDB has it separate SSD disks array (RAID10).
Situation is that TempDB write latency sometimes get to the 6000-8000 ms. Which leads to average write wait for the TempDB file be about 800 ms.
High latency seems to happen only 2-3 times every night for all the TempDB data files.
The system is quite busy all the time and running extended logging with a trace generate quite big amount of data in them.
What would you recommend for monitoring and finding the cause of the long write latency?
Thanks.
Olegas
October 17, 2014 at 6:06 am
I'd suggest a combination of capturing query performance metrics using extended events, and monitoring what is using tempdb by looking at sys.dm_db_session_space_usage dynamic management view. You need to know what is causing the problem in order to understand what to do about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 17, 2014 at 9:31 am
Something is COMPLETELY screwed up if you have SSDs for tempdb and it is seeing 6-8 SECONDS of write stalls. Possibilities:
1) left 1MB growth factor and it has a bajillion file fragments on the disk
2) whatever is between CPUs and the physical SSD media is bottlenecked. common in SAN or iSCSI configs, where there could be many items in play
3) VM and it is messed up/misconfigured
4) shared stuff on the SSDs is crushing them
Check for batch processes that kick off around the time of the bad write perf. EVERY client I go to has ALL their SQL Agent jobs start on the hour, usually at the default of 00:00. Spread those out around the clock. Check for external-to-SQL Server processes too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply