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 2:48 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:49 AM
Points: 684, Visits: 615
Nice article.

The DELETE was the trick for me as well as the BIG INT. This will be useful to our virtualization managers as the block sizes seem to have a large impact on performance.

Alan
Post #740562
Posted Tuesday, June 23, 2009 6:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:04 PM
Points: 19, Visits: 64
Jason Crider (6/23/2009)
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.


Remove some columns first in Excel (or whatever you use instead). You most probably do not need the Process Name nor the PID, the Result, the paths may most probably be trimmed down to just filenames, etc. If that's not enough you could also extract the Offset and Length from the Detail column in Excel and then import just the two numbers. In either case it's just a warning, you only run into problems if some actual rows cross the limit.


There's a different problem. The DBCC PAGE() can only tell you what table does the offset belong to at the moment you run this query. Which may not be the same one that it belonged to when the disk access was made. The row may have been deleted and the block reassigned to a different table. I believe. How likely that is depends on your database, but you may easily miss all access to tables that are periodically emptied (queue for a batch process or something similar). Or am I wrong?

Jenda
Post #740666
Posted Wednesday, June 24, 2009 2:02 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
Hi Jenda,

Yes it is possible, I think in addition trying to monitor tempdb where tables are created and dropped on the fly it is going to happen consistently.

I think we need to take a prgamatic approach to using procmon, it is a free tool and not specifically designed for this purpose. Bearing that in mind there are some edge cases where we would have to do some extra work if getting the name of the table was critical, as opposed to getting the size and type of I/O's.

If you had a table that was being created and dropped or filled and emptied then it is still possible, how you do this depends on the type of index (heap, clustered index or non-clustered index) and whether partitioning is used. As an example to get a list of pages in a clustered index you:

1 - Use sysindexes to get the "root" page
2 - Use DBCC PAGE(database, fileId, pageId, 3) -- Remember to convert the page id from a hex string to file and page id.
3 - Use the ChildPageId column to get a list of pages which belong to the index and compare these against your offsets

This gives you a list of the FileId and ChildPageId's that belong to the index which you would need to save and then match up with your offsets.

If the table or index is large then you would need to also use dbcc page with option 0 (DBCC PAGE(database, fileId, pageId, 0)) and check m_nextPage if more than one IAM page is used.

If you need to know more about getting the details for different types of indexes I would start by getting the Sql internals Viewer (http://internalsviewer.codeplex.com/).

Thanks,


Ed
Post #740826
Posted Wednesday, June 24, 2009 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:01 PM
Points: 7, Visits: 63
This article has come at a very good time for me as we are having I/O problems on Virtual SQL servers. When I captured the procmon output as a CSV file I don't see column 0 as Sequence and I only get 7 columns in all with last one being the detail.

What am I doing wrong?

Thanks

Chris



Post #741246
Posted Wednesday, June 24, 2009 12:04 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:49 AM
Points: 684, Visits: 615
I couldn't get anything but an "n/a" on the sequence number export so I simply made the sequnce column of the table auto increment and eveything worked fine.

Alan
Post #741252
Posted Wednesday, June 24, 2009 2:09 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
I am not sure why the sequence column would say n/a.

If you go to the "Options" menu the "Select Columns" it might be setup differently, just add the columns you need and untick the ones you don't.

Also there is an additional column "Duration" which I didn't add because I think that using procmon will add some overhead so the results are not 100% accurate, but it is potentially a useful metric to compare servers / response times.


Ed
Post #741357
Posted Tuesday, June 30, 2009 3:16 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:15 PM
Points: 126, Visits: 490
Just added her to the brief case. Will have to get back to this when we leave logical and I have some damn transactional data to play with...!


Post #744915
Posted Monday, September 06, 2010 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 6:11 AM
Points: 3, Visits: 74
A very helpful article but with (at least for me) a number of issues

a) Collecting data with process monitor
- Another filter should be set for the Detail column with the condition "begins with" and the value "offset". Else filling the Offset and Length fields might fail
- Sequence column seems not to be switched on in current Process monitor versions -> it should be switched on
b) Importing data
- Like other people in this forum mentioned the CSV output of Process monitor seems to not work for the Sequence number (it shows always "n/a") therefor CSV should first be processed with a CSV compliant tool (e.G. use CSVed to remove the first column and to add a first column with a row number)
- Importing with leaving the general options in SQL 2008 on the default values failed for me, The Path and Detail column should be set to a higher value (e.G. 4000 in the output column width)
- The detail column should after the import once be re-filled with removing the double quotes
e.g.
Update Iops
SET DETAIL = REPLACE(DETAIL,'"','')
GO

Thanks for the article
Best regards
Thorsten
Post #981213
Posted Monday, January 13, 2014 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:14 PM
Points: 23, Visits: 456
I had the BIGINT issue as well - a few years has past


Kindest Regards,

Allen McGuire
madtownlounge.com
Post #1530364
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse