DATABASE I/O STATISTICS (SQL Server 2000)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mark Jones » DATABASE I/O STATISTICS (SQL Server 2000)

DATABASE I/O STATISTICS (SQL Server 2000)

Rate Topic

Display Mode

Topic Options

Author

Message

Mark.L.Jones

Posted Thursday, March 06, 2008 6:16 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:23 AM
Points: 226, Visits: 155

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

Post #465589

Pieter-423357

Posted Thursday, May 01, 2008 7:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 1:26 PM
Points: 86, Visits: 473

Good article and seems to work for SQL2005.

I would add 1 statement to review available disk space

EXEC master..xp_fixeddrives

Post #493556

Mark.L.Jones

Posted Thursday, May 01, 2008 7:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:23 AM
Points: 226, Visits: 155

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.

Post #493561

ksouak

Posted Friday, May 02, 2008 10:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 3:11 PM
Points: 100, Visits: 37

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

Post #494404

Mark.L.Jones

Posted Tuesday, May 06, 2008 6:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:23 AM
Points: 226, Visits: 155

Yep, good spot, cheers for that ;)

Post #495553

fjmorales

Posted Tuesday, March 02, 2010 2:17 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:38 AM
Points: 73, Visits: 221

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

Post #875531

ksouak

Posted Thursday, March 04, 2010 6:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 3:11 PM
Points: 100, Visits: 37

it meens , the number of milliseconds spent waiting for io

Post #876762

fjmorales

Posted Thursday, March 04, 2010 7:24 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:38 AM
Points: 73, Visits: 221

Thanks dude! Gracias!

Post #876830

« Prev Topic | Next Topic »

Permissions