Post reply

Data File I/O from the SQL 2008 Activity Monitor

  • June 14, 2011 at 6:05 pm

    #239250

    Hello

    I'm just wondering if there's a direct relationship between the Data File I/O from the SQL 2008 Activity Monitor and the performance of the disk subsystem?

    Below is a sample of the stats that I gathered:

    dbread (mb/sec)write (mb/sec)response (ms)

    temp00429

    temp00.1190

    temp0.10.1108

    temp0.60.7310

    temp1.12.2577

    temp00710

    temp00448

    temp00285

    temp0.10.1300

    proddb04.369

    proddb04.988

    Our production server is performing very slow and I would like to point out that one cause could be the disk is performing poorly based on these data.

    Am I correct to assume this?

    Thanks.

  • June 15, 2011 at 3:07 am

    #1338824

    What kind of wait type you are looking at?

    You have keep the SQL Server instance that tries to reach a balance between two goals: Keep the buffer pool from becoming so big that the entire system is low on memory & Minimize physical I/O to the database files by maximizing the size of the buffer pool. This can be achieved with Database Optimization methods.

    In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem add physical memory or tune the queries to use a different and faster query plan, that is different topic.

    Run this statement to get pending I/O information:

    [font="Courier New"]SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os] FROM sys.dm_os_schedulers

    SELECT DB_NAME(database_id) AS [Database],[file_id], io_stall_read_ms],[io_stall_write_ms],[io_stall] FROM sys.dm_io_virtual_file_stats(NULL,NULL) [/font]

    Activity Monitor is a handy tool, however its a resource intensive utility too and for information you may use it for short while and do not keep it open constantly and with regard to I/O all the details are summed up in this http://www.mssqltips.com/tip.asp?tip=1416 TIPS article.

    -Satya SK Jayanty
    SQL Server MVP (Follow me @sqlmaster)
    Author of SQL Server 2008 R2 Administration CookBook
    SQL Server Knowledge Sharing network

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply