DATABASE I/O STATISTICS (SQL Server 2000)

  • Comments posted to this topic are about the item DATABASE I/O STATISTICS (SQL Server 2000)

  • Good article and seems to work for SQL2005.

    I would add 1 statement to review available disk space

    EXEC master..xp_fixeddrives

  • Cheers, my first article so great to have some feedback :D. After I wrote this I found it works on 2005, so will likely be 2008 compatible as well. Not Tried 7.0 or earlier versions.

    Adding fixeddrives could possibly give you a good break down by disk, possibly joining sysfiles for each db would have been helpful to provide what DB's are using which disk in the results sets as well... hehe:

    What I think would have really been really nice is a way to reset the counters without a restart of services. Yet to find that though. But certainly this gives you a good insight into which are you busy DB's based on I/O.

  • Hello,

    It seems to me that IostallMs is not related to Number of Bytes Reads/Writes

    but to the number of Read/Writes

    So the last select, should be

    SELECT TOP 5

    DataBaseName,

    TimeStamp,

    SUM(NumberReads) + SUM(NumberWrites) AS NBIORequests,

    SUM(IoStallMS) AS IoStallMS,

    SUM(IoStallMS) / (SUM(NumberReads) + SUM(NumberWrites)) AS IOStallRatio

    FROM

    tempdb.dbo.tbl_DatabaseFileStats

    GROUP BY

    DataBaseName,TimeStamp

    ORDER BY

    IOStallRatio DESC

  • Yep, good spot, cheers for that 😉

  • What does “stall” means in this context?

    I’m from PR and cannot find a translation that makes sense.

    Any help will be appreciated.

    FJM

  • it meens , the number of milliseconds spent waiting for io

  • Thanks dude! Gracias!

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

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