We have an active OLTP 40GB database on SQL Server 2014 SP1. Queries are found to be slow with IO_Completion waits, Disk Queue Length rising to 900, and SQL Server stops responding. What we tried:
Restart instance and with in a minute it start behaving same way.
After second restart, we changed initial size of each tempdb datafile (there are 16 data files created) and it start working correctly.
Note: We are using table variables for intermediate result sets. These result sets are very small.
It happened twice in a month. Every time I add little bit of space manually to the data files, then it starts working normally. The more interesting thing is that the same setup (same hardware, same folder and files setup, same workload) we have on SQL Server 2008 R2 and and SQL Server 2012 is working fine.
Kindly help us to find a permanent solution.
Initial size of all data files is same 1000MB, Current is 1500MB each. All are identical. Autogrowth is 100MB for each. Before this we were facing PFS and GAM pages contention and we increased to 16 and problem resolved. Both trace flags 1117 & 1118 are enabled. 24 cores on 2 NUMA nodes. All datafiles are on the same volume. Simple disk, no SAN.
Instance is on a physical machine. Queries with Table Variables and queries with Hash Joins are most commonly generating IO_Completion waits.
The detailed answer by wBob pushed us to search more in detail. How did we missed it before:
Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 7704 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
This we found in log when ever this type of problem is occurring. We are moving TempDB to separate fast drive.