May 15, 2009 at 6:41 am
Hello,
we are using filemon to detect high datafile io usage and its ok but, anybody knows if is there a way to link filemon offset to an specif tabla(or index)?
I spool a sample of filemon info:
14:33:26sqlservr.exe:968READ T:\Data\datos1.mdfOffset: 4344578048 Length: 65536
14:33:26sqlservr.exe:968READ T:\Data\datos1.mdfOffset: 4345692160 Length: 65536
14:33:26sqlservr.exe:968READ T:\Data\datos1.mdfOffset: 4352114688 Length: 65536
14:33:26sqlservr.exe:968READ T:\Data\datos1.mdfOffset: 4352835584 Length: 65536
Thanks in advance.
May 15, 2009 at 6:53 am
I've tested this way (please confirm this could be ok):
1º get pageid from filemon offset
divide filemon offset by 8192 (page size)=739648 (in ex.)
2º Get object id from pageid
dbcc page (12, 3,739648) with tableresults
get m_objId =415496709(in example)
3º get objectname from objectid
select object_name(415496709)
Mytable
May be correct?
May 15, 2009 at 10:08 am
I had to try this, looked very interesting.
It worked for me. The only thing that was a bit different that what you mentioned was the output of DBCC PAGE. I saw [Metadata: ObjectId], which was correct object Id that was modified during the test.
m_objId (AllocUnitId.idObj) = 486
Metadata: ObjectId = 260195977
May 18, 2009 at 1:42 am
Hello,
thanx for your response. I cant see "metadata objectid" value on my dbcc page execution, maybe for the version (I'm testing on sql 2000).
Thanks in advance
May 19, 2009 at 4:46 am
Hi - I had already started an article on how to use process monitor to gather data then run a script to get the name of the table / index that is being hit. If anyone wants a copy of the scripts then let me know, but I will hurry up and finish the article!
In sql 2000 it will be just ObjectID (or objid)
Ed
May 19, 2009 at 7:53 am
yes i am interested.
May 19, 2009 at 8:43 am
please add me to the 'interested luist' as well 😀
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 19, 2009 at 8:49 am
ok well it is fairly easy, create a new db with this table:
CREATE TABLE [dbo].[Iops](
[Sequence] [nvarchar](4000) NULL,
[Time of Day] [nvarchar](4000) NULL,
[Process Name] [nvarchar](4000) NULL,
[PID] [nvarchar](4000) NULL,
[Operation] [nvarchar](4000) NULL,
[Path] [nvarchar](4000) NULL,
[Result] [nvarchar](4000) NULL,
[Detail] [nvarchar](4000) NULL,
[Offset] [int] NULL,
[Length] [int] NULL,
[name] [varchar](max) NULL --If you are not going to run the second script you don't need this.
) ON [PRIMARY]
import the data from procmon into the table, then do:
UPDATE iops
SET Offset = CAST( REPLACE(SUBSTRING(Detail, CHARINDEX(':', Detail) +1, CHARINDEX('Length', Detail) - CHARINDEX(':', Detail)-1), ',', '') AS INT),
Length = CAST( REPLACE(SUBSTRING(Detail, CHARINDEX('Length:', Detail) +8, CHARINDEX('I/O', Detail) - CHARINDEX('Length:', Detail) -8), ',', '') AS INT)
and then finally (this will only work on the same instance as the data was captured from)
DECLARE @sqlcode TABLE( sequence int, sql VARCHAR(MAX), dbname VARCHAR(MAX))
INSERT INTO @sqlcode
SELECT sequence, 'DBCC PAGE(' + CAST(dbid AS VARCHAR(MAX)) + ',' + CAST(fileid AS VARCHAR(MAX)) + ',' + CAST((offset/8192) AS VARCHAR(MAX)) + ', 0) WITH TABLERESULTS', DB_NAME(dbid) FROM iops iop JOIN sys.sysaltfiles saf ON iop.Path = saf.filename
DECLARE @iCur INT, @iMax INT
SELECT @iCur = MIN(sequence), @iMax = MAX(sequence) FROM @sqlcode
CREATE TABLE #pageOutput( ParentBuffer VARCHAR(MAX), OBJECT VARCHAR(MAX), FileId VARCHAR(MAX), Value VARCHAR(MAX))
WHILE @iCur <= @iMax
BEGIN
TRUNCATE TABLE #pageOutput
DECLARE @sql NVARCHAR(MAX), @sequence INT, @dbname VARCHAR(MAX), @ObjectId INT, @IndId INT
SELECT @sql = sql, @sequence = sequence, @dbname = dbname FROM @sqlcode WHERE sequence = @iCur
INSERT INTO #pageOutput
EXEC sp_executesql @sql
SELECT @ObjectId = Value FROM #pageOutput WHERE FileId = 'Metadata: ObjectId'
SELECT @IndId = Value FROM #pageOutput WHERE fileId = 'Metadata: IndexId'
DECLARE @sqlupdate nVARCHAR(MAX)
select @sqlupdate = 'UPDATE iops SET NAME = ''' + @dbname + '.'' + ' + '(SELECT so.NAME + ''.'' + COALESCE(si.NAME, ''' + CAST(@IndId AS VARCHAR(MAX)) + ' (id)'') FROM ' + @dbname + '.sys.sysindexes si JOIN ' + @dbname + '.sys.sysobjects so ON si.id = so.id WHERE so.id = ' + CAST(@ObjectId AS varchar(MAX)) + ' AND si.indid = ' + cast(@IndId as varchar(max)) + ') where sequence =' + CAST(@sequence AS VARCHAR(MAX))
EXEC sp_executesql @sqlupdate
SET @iCur = @iCur + 1
END
SELECT * FROM iops
DROP TABLE #pageOutput
Note this is meant to work with process monitor output, filemon output is slightly different so you would need to modify the update to get offset and length.
May 21, 2009 at 3:59 am
thanks a lot!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply