2

What could be causing (and how to fix) my tempdb files (8 equal size) to have [avg_write_stall_ms] of 7300ms and the next highest data file is 47ms.

System is Hyper-V fail-over cluster, SQL 2012 Standard, 60GB RAM, 20 vCPU, EqualLogic PS6100 SAN

Here's my results from the Glen Berry tools

IO Stalls by File

I've requested that the tempdb data files be moved to their own drive letter, but am not sure that will help because it would be the same SAN.

Ulfius
  • 133
  • 2
  • is TF 1118 and 1117 enabled ? Also, is tempdb sharing the same LUN as data / log files ? – Kin Shah Feb 10 '16 at 18:02
  • Looking at your screenshot, you have tempdb data files 2GB each. I would suggest you to properly presize them since 2GB is very less. This will prevent expensive autogrowth events which might be contributing to higher stalls. Use this script to know how many times autogrowth event fired. A seperate LUN for tempdb will definitely help. see this as well http://dba.stackexchange.com/a/53917/8783 – Kin Shah Feb 10 '16 at 18:10
  • @Kin, only Trace Flag 1204, 1222, and 3605 are set. Same LUN as the data files currently. I also ran that script and there were no autogrowths for tempdb. – Ulfius Feb 10 '16 at 18:26
  • 1
    You will need TF 1118 and 1117 enabled. I am away from my computer, but read up on those trace flags. Why have you set TF 3605 - it writes all dbcc output to error log ? – Kin Shah Feb 10 '16 at 18:28
  • @Kin Thanks. I've set TF1118 and 1117, but since I haven't had the files grow and they're still all the same size, 1117 won't change anything. – Ulfius Feb 10 '16 at 18:38
  • If it's not due to growth events, then I would say your R:\ and/or the I/O path to it are completely saturated and insufficient for your workload. – Aaron Bertrand Feb 10 '16 at 18:45

0 Answers0