Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATABASE I/O STATISTICS (SQL Server 2000)


DATABASE I/O STATISTICS (SQL Server 2000)

Author
Message
Mark.L.Jones
Mark.L.Jones
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 155
Comments posted to this topic are about the item DATABASE I/O STATISTICS (SQL Server 2000)
Pieter-423357
Pieter-423357
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 564
Good article and seems to work for SQL2005.

I would add 1 statement to review available disk space

EXEC master..xp_fixeddrives
Mark.L.Jones
Mark.L.Jones
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 155
Cheers, my first article so great to have some feedback BigGrin. 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.
ksouak
ksouak
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 46
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
Mark.L.Jones
Mark.L.Jones
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 155
Yep, good spot, cheers for that Wink
fjmorales
fjmorales
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 281
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
ksouak
ksouak
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 46
it meens , the number of milliseconds spent waiting for io
fjmorales
fjmorales
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 281
Thanks dude! Gracias!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search