as earlier in this post we have discussed the issue of latches in this post we will see how to fine tune them and find out why they are happening
I will be querying a table with a count of 172709683 rows and the clustered index for the wild card search string , and I have DOP set to 2 and cost of threshold is set to 5 and expecting a parallel execution plan we also be observing lot of cxpacket wait same time
we will use below query on sys.dm_os_waiting_tasks for any waiting tasks
SELECT W.SESSION_ID,W.WAIT_DURATION_MS,W.WAIT_TYPE,W.RESOURCE_DESCRIPTION,T.TEXT,P.QUERY_PLAN
FROM SYS.DM_OS_WAITING_TASKS W
JOIN SYS.DM_EXEC_REQUESTS R ON W.SESSION_ID = R.SESSION_ID
CROSSAPPLYSYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
CROSSAPPLYSYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) P
WHERE W.WAIT_TYPE LIKE ‘PAGE%’
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE
procedure [dbo].[pagetoobject](@RESOURCE NVARCHAR(1024))
as
begin
declare @dbid int,@fileid int,@pageid int,@objectid int,@indexid int
SELECT @dbid=SUBSTRING(@RESOURCE,0,CHARINDEX(‘:’,@RESOURCE))
,@fileid = substring
(
lTRIM(RTRIM(SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1,LEN(LTRIM(RTRIM(@RESOURCE))))))
,0
,charindex(‘:’,lTRIM(RTRIM(SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1,LEN(LTRIM(RTRIM(@RESOURCE)))))))
)
,@pageid =SUBSTRING(@RESOURCE,CHARINDEX(‘:’,@RESOURCE,CHARINDEX(‘:’,@RESOURCE)+1)+1,LEN(@RESOURCE))
declare @page table (parentobj sysname,[object] sysname,field sysname, value sysname)
declare @params Nvarchar(1000)=
N’@dbid int,@fileid int,@pageid int’
insert into @page(parentobj,[object],field,value)
exec sp_executesql N’dbcc page (@dbid,@fileid,@pageid) with tableresults, no_infomsgs’, @params ,
@dbid= @dbid,@fileid = @fileid,@pageid = @pageid
select @objectid = value from @page
where field = ‘Metadata: ObjectId’
select @indexid = value from @page
where field = ‘Metadata: IndexId’
select t.name as’table name’,i.name as’index name’
from sys.tables t inner join sys.indexes i on t.object_id= i.object_id
where t.object_id= @objectid AND I.index_id = @indexid
end
GO
Thanks for posting it. I was looking for something like this to easily troubleshoot performance issue caused by Pageiolatches. However I ran into problem. When I gave the @resource = ‘234:1:9’
I get error saying conversion failed when nvarchar value of ‘1:9″ to data type int. I am looking into it but thought to let you know.
Ameena
HI, Thank you
I just fixed it , and updated the new code up there, I could have used FOR XML in an easy way…. let me know if it didn’t worked
Thanks