Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATABASE I/O STATISTICS (SQL Server 2000) Expand / Collapse
Author
Message
Posted Thursday, March 6, 2008 6:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Thursday, May 1, 2008 7:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
Good article and seems to work for SQL2005.

I would add 1 statement to review available disk space

EXEC master..xp_fixeddrives
Post #493556
Posted Thursday, May 1, 2008 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, May 2, 2008 10:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:16 AM
Points: 100, Visits: 43
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
Posted Tuesday, May 6, 2008 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, March 2, 2010 2:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 73, Visits: 243
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
Posted Thursday, March 4, 2010 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:16 AM
Points: 100, Visits: 43
it meens , the number of milliseconds spent waiting for io
Post #876762
Posted Thursday, March 4, 2010 7:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 73, Visits: 243
Thanks dude! Gracias!
Post #876830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse