Seventh--dmvs and DMFs (4)--monitoring disk IO with DMV and DMF

Original: Seventh--dmvs and DMFs (4)--monitoring disk IO with DMV and DMF

Objective:

This is the last article in this series, as a DBA, you must always focus on disk I/O issues, and when problems arise, analyze what is the problem as quickly as possible. SQL Server also provides some columns of I/O -related DMO to monitor.

This article describes how to use DMO to monitor the performance of the I/O subsystem and find I/O bottlenecks. In this article, I/O usage patterns of different databases can be distinguished. Once the I/O to the database is found to be high, you may want to consider migrating the database to a separate disk, or delving into the problems of I/O .

Preparatory work:

This article demonstrates how to monitor the I/O situation of a database file and will present a demonstration on the AdventureWorks database on SQL Server 2008R2 .

Steps:

1. open SQL Serverand connect to AdventureWorks.

2. Enter the following script to monitor the log files and data files on the SQL Server instance:

SELECT  db_name (vfs.database_id) as DatabaseName,        mf.name as Logicalfilename,        Mf.physical_name as Physicalfilename, Case        Mf.type if          0 Then ' Data file ' when          1 then ' Log file '        END as FileType,        VFS.N Um_of_reads as Totalreadoperations,        Vfs.num_of_bytes_read as Totalbytesread,        vfs.num_of_writes as Totalwriteoperations,        Vfs.num_of_bytes_written as Totalwriteoperations,        Vfs.io_stall_read_ms as Totalwaittimeforread,        Vfs.io_stall_write_ms as Totalwaittimeforwrite,        Vfs.io_stall as Totalwaittimeforio ,        vfs.size_on_disk_bytes as Filesizeinbytesfrom    sys.dm_io_virtual_file_stats (null, NULL) as VFS        INNER JOIN sys.master_files as MF on vfs.database_id = mf.database_id and                                             vfs.file_id = Mf.file_idorder by Vfs.databa se_id DESC

3. in the new window, enter the following script to empty the data cache:

Use ADVENTUREWORKSGODBCC dropcleanbuffersgo SELECT  *from    [sales].[ Salesorderdetail]go

4. now execute the script in the second step again to see the situation.

5. execute the following statement to see if there is an IO suspend operation:

SELECT  db_name (vfs.database_id) as DatabaseName,        mf.name as Logicalfilename,        Mf.physical_name as Physicalfilename, Case Mf.type when the          0 Then ' Data file '          is 1 Then ' Log file '        END as FileType,        Pior. Io_type as Inputoutputoperationtype,        pior.io_pending as is_request_pending,        Pior.io_handle,        Pior.scheduler_addressfrom    sys.dm_io_pending_io_requests as Pior        INNER JOIN sys.dm_io_virtual_file_stats (db_id (' AdventureWorks '), NULL)        As VFS on pior.io_handle = Vfs.file_handle        INNER joins Sys.master_files as MF on vfs.database_id = mf.database_id
   
    and vfs.file_id = Mf.file_idgo
   

Analysis:

The first thing to remember is not to use the DBCC dropcleanbuffers command in a formal environment, which will severely impact performance over time.

In this case, for sys.dm_io_pending_io_requests , there may be no data in this computer, because in the case of a single machine there is basically very little io operation, and the likelihood of suspending is greatly reduced, but in the formal environment, When multiple users connect and operate, there is a good chance that a hang situation will occur.

The DMO mentioned above is very helpful in finding I/O subsystem problems, and based on this information, we can further find the database of high I/O and check the I/O status of the disk.

Seventh--dmvs and DMFs (4)--monitoring disk IO with DMV and DMF