SQL Server TempDB files 1-> n and write stalls growing

Question

I have been kinda accidentally pushed to starting to manage our DB settings, performance analyzing etc. Also as it's commonly said that TempDB file should be spread into n files based on core numbers and performance stuff.

I ran some analyzis on one of our servers and spotted that tempDB Write stalls were averaging around 600ms with single file. With this find and with some more analyzis I decided to split TempDB into 4 same sized files (as recomended). At start it looked really first. For first day new tempDB stalls were around 1.5ms (SSD). After 24h though all the new TempDB files were above original write stall. This same thing is repeating on different enviroments also. Am I looking on wrong numbers in here? Am I missing some setting that I should check? Or is single TempDB file still working better.

Here are some environments that this thing has happened: SERVER1 SQL2008R2, SSD drive for SQL, 2 main databases with around 10gb each.

Before: 1 autogenerated TempDB file with stats:

num_of_Writes: 18909792
Avg_Write_stall_ms: 645.554ms
Avg_Read_stall_ms: 2.207ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 2.15ms
Avg_Read_stall_ms around: 1.3ms

After 24hours:

Avg_Write_stall_ms around: 650-700ms
Avg_Read_stall_ms around: 2.2ms

SERVER2 (virtual):

SQL2012, heavy use and lot of database
Before: 1 autogenerated TempDB file with stats:
Avg_Write_stall_ms: 1576.154ms
Avg_Read_stall_ms: 12.788ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 19-39ms
Avg_Read_stall_ms around: 6.8-7.6ms

After 24hours:

Avg_Write_stall_ms around: 8700-9500ms
Avg_Read_stall_ms around: 18.5 - 19.1 ms

This same situation is on multiple of our servers with different setups (virtuals, newer and older, SSD and disk drives). What are we missing?

Answer 1

The information from sys.dm_io_virtual_file_stats is aggreagated since the database came online.

To get usable IO numbers from sys.dm_io_virtual_file_stats, you really need to capture a baseline, wait for a period of time (say 30 minutes) and take another snapshot of sys.dm_io_virtual_file_stats. The difference between the snapshots is the relative latency during that time.

This script take a baseline snapshot of sys.dm_io_virtual_file_stats, waits 30 minutes (by default) and takes another snapshot of sys.dm_io_virtual_file_stats. The difference between the snapshots is the relative latency during that time.