Wait stats : Page and pageio latch waits

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

 

Advertisements

2 thoughts on “Wait stats : Page and pageio latch waits

  1. Ameena says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s