Negative numbers found within sys.dm_io_virtual_file_stats


virtual file stats - idea why particualr server "bouncing" statistics?

*** not appear related sample_ms column issue ***

  1. process collects sys.dm_io_virtual_file_stats , writes custom table, on 15 min interval.  has been running since last tuesday, using sql server agent job.
  2. this occurs multiple databases , multiple files, reads only.  writes appear increment. 
  3. the same type of data present in custom table, or if query data directly out of sys.dm_io_virtual_file_stats.
  4. the server "rolled over" sample_ms column, occuring previously.
  5. i validated elapsed time based on delta of insertdate in custom table , delta of sample_ms.
  6. configuration:  10.50.2500.0 / 128gb / 24 lproc / 64-bit
  7. server new our group.  san attached (no details yet).

sample data.

the query joins recorded copy of vfs determine previous record same db/fileid.  notice how read values (cnt, bytes, stall) decrement between subsequent recordings, return back

insertdatetime   rid   sample_ms   ---------read------------ --------write------------                                     cnt   bytes      stall    cnt   bytes      stall  ---------------- ----- ----------- ----- ---------- -------- ----- ---------- --------  2013-08-20 15:30 221   1851413611  62    3948544    785      0     0          0         2013-08-20 15:45 369   1852313363  62    3948544    785      0     0          0         2013-08-20 16:00 517   1853213161  57    3620864    827      0     0          0         2013-08-20 16:15 663   1854113349  61    3883008    882      0     0          0         2013-08-20 16:30 811   1855013428  61    3883008    882      0     0          0         2013-08-20 16:45 959   1855913086  61    3883008    882      0     0          0         2013-08-20 17:00 1107  1856812884  57    3620864    1212     0     0          0         2013-08-20 17:15 1255  1857712978  61    3883008    1258     0     0          0         2013-08-20 17:30 1403  1858613073  61    3883008    1258     0     0          0         2013-08-20 17:45 1551  1859513167  61    3883008    1258     0     0          0         2013-08-20 18:00 1699  1860412435  57    3620864    975      0     0          0         2013-08-20 18:15 1847  1861312655  61    3883008    1010     0     0          0         2013-08-20 18:30 1995  1862212749  61    3883008    1010     0     0          0         2013-08-20 18:45 2143  1863112953  61    3883008    1010     0     0          0         2013-08-20 19:00 2291  1864013079  54    3424256    955      0     0          0         2013-08-20 19:15 2437  1864912237  61    3883008    1033     0     0          0   

thanks; appreciate checking in.  have same challenge of time at moment; hope weekend presents opportunity. 

on surface consdiering original premise, appears statistics transfer on different instance in standby.  io performed either rolled or replaced when subsequent log applied.

i hope confirm through restore of database onto same instance , different instance.  here's questions pursuing.  please let me know if want add list.

  1. do vfs retain value when restored (with recovery) same instance? 
  2. do vfs retain value when restored (with recovery) different instance?
  3. do vfs retain value when restored (standby) same instance? 
  4. do vfs retain value when restored (standyby) different instance?
  5. what effect restore operation have on vfs, compared user activity?

this still not explain how/where statistics managed, little "black box" testing reveal little more. 



SQL Server  >  SQL Server Database Engine



Comments

Popular posts from this blog

Motherboard replacement

Cannot create Full Text Search catalog after upgrading to V12 - Database is not fully started up or it is not in an ONLINE state

Remote Desktop App - Error 0x207 or 0x607