Databases Reference
In-Depth Information
“Avg. Disk sec/Read” and “Avg. Disk sec/Write” separate the requests into read and write
measurements, respectively, that can be useful for determining how to coni gure disk controller
cache (see Chapter 4). For example, if you're seeing poor read performance and excellent write
performance, you might want to optimize the cache for reads.
SQL Server DMVs
Monitoring the performance of a disk volume using Performance Monitor is a useful indicator of a
potential storage performance issue, but you can get a further level of granularity from SQL Server
itself. The following script (code i le:
Ch8_5ReadAndWriteLatency.sql
) uses the
sys
.
dm
_
io
_
virtual
_
file
_
stats
DMV to calculate the read and write latency for all database i les that have
been used since the SQL Server service was last started.
SELECT DB_NAME(database_id) AS 'Database Name',
file_id,
io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM sys.dm_io_virtual_file_stats(-1, -1)
WHERE num_of_reads > 0
AND num_of_writes > 0 ;
You can see part of the output from running the script on
a busy production SQL Server in Figure 8-14. Tempdb has
four data i les with
file
_
id
's 1, 3, 4, and 5, and a
transaction log with
file
_
id
2. All the data i les
have the same read and write latency, which is a positive
indicator that the I/O is balanced across all the i les, and all
the results indicate good performance from tempdb.
FIGURE 8-14
Thresholds
Microsoft suggests the following performance thresholds for disk latency on drives containing SQL
Server database i les:
Database data i les:
➤
Ta rget:
<10ms
➤
Acceptable:
10 -20ms
➤
Unacceptable:
>20ms
Database log i les:
➤
Ta rget:
<5ms
➤
Acceptable:
5-15ms
➤
Unacceptable:
>15ms
You should use these thresholds for guidance only because some systems will never be able to
achieve the target latency. If you don't have any performance issues with your application and you're
Search WWH ::
Custom Search