SQL Clone
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 (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

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

Group: General Forum Members
Points: 227 Visits: 577
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 (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 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 (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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 (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 155
Yep, good spot, cheers for that Wink
fjmorales
fjmorales
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 283
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 (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 46
it meens , the number of milliseconds spent waiting for io
fjmorales
fjmorales
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 283
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