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