| |
 |
|
SQL Server Tips by Burleson |
Getting Database I/O Details
If I/O activity appears to be high, more information regarding I/O
specifics for SQL Server 2000 and higher can be obtained. For SQL
Server 2000 and 2005, I/O details for databases and files can be
obtained, and for SQL Server 2005, the DBA can drill down a little
further and get I/O at the object level.
To get I/O statistics for files and databases in SQL Server 2000,
the following two procedures, which presents I/O metrics for all
files and databases on a SQL Server can be used:
* up_wl_file_io
This script is combined with the following script
-- Script is available in the Online Code Depot
* up_wl_file_stats
-- Script is available in the Online Code Depot
Executing the up_wl_file_io procedure will provide statistics.
From an activity interest standpoint, the DBA should certainly want
to pay attention to the reads and writes columns as those columns
will reveal which database is under the most I/O duress. From a
throughput viewpoint, the iostall column should be zeroed in on as
it will reveal which files are experiencing the most delays in terms
of accomplishing I/O goals. The higher the iostall, the worse the
files are doing. These files are likely candidates for physical disk
relocation or other such actions.
With SQL Server 2005, more detail on the iostall issue can be
obtained. The iostall column returned from the fn_virtualfilestats
function is broken out into two columns: IoStallReadMS and
IoStallWriteMS. These columns give details, in milliseconds,
regarding read and write throughput problems for each file. With
this new granular information, it can be determined if read or write
problems are causing the issues instead of just I/O in general.
This SQL Server 2005 enhancement means the procedures above need to
be altered to account for the iostall column being broken up into
two columns. The temporary table holding the I/O statistical data
must be changed, as does the INSERT statement that inserts file
metric info into it and the final SELECT, which presents the
information.
SQL Server 2005 presents an easy way to drill deeper in order to
retrieve actual object I/O statistical data. The
sys.dm_db_index_operational_stats function provides good data that
helps pinpoint hot objects in various databases. This function can
be used to get data back on: an entire SQL Server but is not
recommended if there are many of databases and objects; a particular
database; or a specific object. There are many columns returned by
the function, but the object_io.sql query below will give some of
the most interesting statistics that will help reveal the objects
under heavy I/O pressure.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
