0

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?

  • My assumption is that you are getting those number from sys.dm_io_virtual_file_stats. Just checking to make sure you're aware that those numbers are aggregate since the database came online. If you're already aware of that, you can disregard my comment. I usually use this script to track IO latency. sqlskills.com/blogs/paul/capturing-io-latencies-period-time Also, this post has some good information about IO latency as well sqlskills.com/blogs/paul/… – Scott Hodgin Aug 31 '16 at 11:04
  • Thanks for a tip. I'll check your script also. Numbers are indeed bases on sys.dm_io_virtual_file_stats but they are based on new tempDB file stats (not the original ones). I assume that those should be from livespan of the new files and should be correct ones. My assumption was incorrect one? – Jaakoppi Aug 31 '16 at 11:11
  • 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. The first link in my original comment does exactly this. – Scott Hodgin Aug 31 '16 at 11:39
  • Yes thanks. Looks that at the moment thing are stable and looks quite a lot better. I'll keep following status for temp-files. There has been some performance issues after tempDB changes, but let's see. – Jaakoppi Aug 31 '16 at 11:48
  • Since you indicated that my comments were useful, I went ahead and added it as an answer for others. – Scott Hodgin Aug 31 '16 at 12:26
0

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.

  • Hey, Actually numbers now show that the issue is kinda real. For example from this morning with your script write latencies are around 577-578ms in SSD enviroment. – Jaakoppi Sep 1 '16 at 5:56
  • 1
    Well - you might want to run this latency script hourly to possibly identify a time-frame pattern. Is there a particularly heavy time during the day? What processes are running during that time? You might want to search internet posts for 'tempdb write latency' - they might have information that you'd find useful. The 'script' was written by Paul Randal - sqlskills.com/blogs/paul/… – Scott Hodgin Sep 1 '16 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.