Databases Reference
In-Depth Information
database mirroring, replication, or a long-running transaction that cause your transaction log to i ll
up despite frequent transaction log backups.
The next query, shown in Listing 15-35, will enable you to gather some I/O statistics by i le for the
current database.
LISTING 15-35:
I/O statistics by fi le for the current database
-- I/O Statistics by file for the current database
SELECT DB_NAME(DB_ID()) AS [Database Name],[file_id], num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms,
CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms)
AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms)
AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
(num_of_reads + num_of_writes) AS [Writes + Reads], num_of_bytes_read,
num_of_bytes_written,
CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1))
AS [# Reads Pct],
CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1))
AS [# Write Pct],
CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written)
AS DECIMAL(10,1)) AS [Read Bytes Pct],
CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written)
AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);
-- This helps you characterize your workload better from an I/O perspective
This query returns the number of reads and writes for each i le in your database. It also returns
the number of bytes read and written for each i le in the database, and the number of read I/O
and write I/O stalls for each i le in the database. Finally, it breaks down the read/write ratio and
read/write I/O stall ratio into percentage terms. The point of all this information is to help you
better characterize your I/O workload at the database-i le level. For example, you might discover
that you are doing a lot more writes to a particular data i le than you expected, which might be a
good reason to consider using RAID 10 instead of RAID 5 for the logical drive where that data i le
is located. Seeing a lot of I/O stalls for a particular database i le might mean that the logical drive
where that i le is located is not performing very well or simply that the database i le in question is
particularly active. It is dei nitely something to investigate further.
Next, with the query shown in Listing 15-36, you are going to take a look at the transaction log
Virtual Log File (VLF) count.
LISTING 15-36:
Virtual Log File count
-- Get VLF count for transaction log for the current database,
-- number of rows equals the VLF count. Lower is better!
DBCC LOGINFO;
-- High VLF counts can affect write performance
-- and they can make database restore and recovery take much longer
Search WWH ::
Custom Search