Databases Reference
In-Depth Information
not being able to apply the log activity fast enough on the mirror), you will see a log reuse wait
description of
DATABASE
MIRRORING
. Other common log reuse wait descriptions that bear further
investigation include
REPLICATION
and
ACTIVE
TRANSACTION
.
This query also tells you how large your transaction log i le is for each database, and how full it is,
which is good information to know! I don't like to see a transaction log become more than 50%
full. If that is happening, you can either make the transaction log i le bigger or take more frequent
transaction log backups.
Finally, you are retrieving a number of other important database-level properties for each database
on the instance, including the compatibility level, the page verify option, auto create statistics, auto
update statistics, auto update statistics asynchronously (See Chapter 5), forced parameterization,
and the snapshot isolation level.
Next, the results of the query shown in Listing 15-17 indicate which database i les are seeing the
most I/O stalls.
LISTING 15-17:
I/O stall information by database fi le
-- Calculates average stalls per read, per write,
-- and per total input/output for each database file.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name,
io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS
[avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS
[avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes
AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS
NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
-- Helps determine which database files on
-- the entire instance have the most I/O bottlenecks
This query lists each database i le (data and log) on the instance, ordered by the average I/O stall
time in milliseconds. This is one way of determining which database i les are spending the most time
waiting on I/O. It also gives you a better idea of the read/write activity for each database i le, which
helps you characterize your workload by database i le. If you see a lot of database i les on the same
drive that are at the top of the list for this query, that could be an indication that you are seeing
disk I/O bottlenecks on that drive. You would want to investigate this issue further, using Windows
Performance Monitor metrics such as Avg Disk Sec/Write and Avg Disk Sec/Read for that logical disk.
After you have gathered more metrics and evidence, talk to your system administrator or storage
administrator about this issue. Depending on what type of storage you are using (See Chapter 4), it
Search WWH ::
Custom Search