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


Capturing I/O patterns using Process Monitor


Capturing I/O patterns using Process Monitor

Author
Message
Alan. T.
Alan. T.
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 646
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
jenda
jenda
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
edwardelliott
edwardelliott
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
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
Woodc
Woodc
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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



Alan. T.
Alan. T.
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 646
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
edwardelliott
edwardelliott
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
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
MudLuck
MudLuck
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 519
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...!



mail.schaefer
mail.schaefer
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Allen McGuire
Allen McGuire
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 551
I had the BIGINT issue as well - a few years has past ;-)


Kindest Regards,

Allen McGuire
madtownlounge.com
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