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 12»»

Capturing I/O patterns using Process Monitor Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 12:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
Comments posted to this topic are about the item Capturing I/O patterns using Process Monitor
Post #739922
Posted Tuesday, June 23, 2009 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:57 AM
Points: 4, Visits: 79
Very good article indeed. One small issue that is easily fixed is that you need to use BIGINT and not INT as any respectable sized database is going to produce offsets that are too large.

Also our databases (some nearing 1TB) are not working unless the output is first imported into Excel and resaved as XLS as the flat file import driver is cracking up on SQL 2005.
Post #740143
Posted Tuesday, June 23, 2009 7:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
A ha yes of course, i'll submit a change to the article at some point.
Post #740149
Posted Tuesday, June 23, 2009 7:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:57 AM
Points: 4, Visits: 79
Man, that was one fast response!
Post #740150
Posted Tuesday, June 23, 2009 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:57 AM
Points: 4, Visits: 79
One other change that may be needed is that not all the Data presented is an offset, some cleaning may be required with

DELETE FROM Iops WHERE SUBSTRING(Detail, 1, 6) <> 'Offset'

where access appears to be denied otherwise the SUBSTRING section will fail on occasions.

or adding in another filter for Detail beginning with "Offset"

This may have been caused as I added in .ldf files also. Log files shows a very marked contrast to Data mdf files as the block size changes a lot and the tempdb can write a large amount of data out in quite large blocks for database.
Post #740313
Posted Tuesday, June 23, 2009 11:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
How do you get around the following issue on SQL 2000?
Warning: The table 'Iops' has been created but its maximum row size (64050) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.


MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Post #740413
Posted Tuesday, June 23, 2009 12:08 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:17 AM
Points: 282, Visits: 2,208
skyline6969 (6/23/2009)
One other change that may be needed is that not all the Data presented is an offset, some cleaning may be required with

DELETE FROM Iops WHERE SUBSTRING(Detail, 1, 6) <> 'Offset'

where access appears to be denied otherwise the SUBSTRING section will fail on occasions.

or adding in another filter for Detail beginning with "Offset"

This may have been caused as I added in .ldf files also. Log files shows a very marked contrast to Data mdf files as the block size changes a lot and the tempdb can write a large amount of data out in quite large blocks for database.


Ok, got past that part.

The Delete helped.

I also had to change the Update statement from INT to BIGINT.


When I ran the IOCount though I end up with
34,818 that have IOSize of 8.
54,401 that have IOSize of 256. That concerns me since I only grabbed mdf items. Could I have corrupted the data with my changes above?


MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Post #740439
Posted Tuesday, June 23, 2009 12:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:39 PM
Points: 33, Visits: 268
Using procmon for this is certainly a new twist on this--thanks!.

Post #740464
Posted Tuesday, June 23, 2009 1:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
Hi Jason,

Changing to a BIGINT shouldn't cause any corruption - I will make the changes myself to the article.

If you are getting IO sizes of 256 then it probably means sql is doing read ahead which is good for performance, take a look at http://technet.microsoft.com/en-us/library/cc966500.aspx "Read-Ahead" for further details.

It does say that editions under enterprise are limited to 128 pages so you should divide the 256 by 8 (8kb = 1 page) to give you 32 pages.

Ed
Post #740492
Posted Tuesday, June 23, 2009 1:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
Hi Skyline,

It should be fine, but if other files or operations are being included then it is probably best to filter them out at some point.


Ed
Post #740495
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse