Databases Reference
In-Depth Information
Table 12-2: Output from sys.dm_io_virtual_file_stats
database_id
file_id
sample_ms
num_of_reads
num_of_bytes_read
1
1
-1568777343
32041
555245568
1
2
-1568777343
165
1388544
2
1
-1568777343
21265151
9.18027E
+
11
2
2
-1568777343
1164
70148096
2
4
-1568777343
21144937
9.17142E
+
11
io_stall_read_ms
num_of_writes
num_of_bytes_written
io_stall_write_ms
141224
825
6799360
3467
897
4873
3683840
3721
1596900444
23121253
1.32498E
+
12
2342759655
62562
3698286
2.27204E
+
11
39249458
1680433554
23142095
1.32683E
+
12
2351237062
io_stall
size_on_disk_bytes
file_handle
144691
30605312
0x00000688
4618
23592960
0x0000068C
3939660099
31457280000
0x000007D0
39312020
31457280000
0x00000704
4031670616
20971520000
0x000007D4
Table 12-3 shows the results we obtained when we ran this on one of our SQL Servers. Your results will
differ based on how much I/O you are pushing totemdbandhowyourtempdbisconfigured.
Table 12-3 is a very long table that won't fit on the printed page. It has been broken into three segments
for easier viewing.
Another challenge with this DMV is that these are the cumulative numbers since SQL Server was last
started. On a system that's been running for some time, they will be very large and may reflect a workload
that was run once a long time ago.
To get results that reflect recent activity, you will need to write some T-SQL to sample this function
and write the results into a table, and then some additional SQL that queries that table to show you the
changes over a given period.
Search WWH ::
Custom Search