SQL Query waiting on Wait Resource

677 Views Asked by At

I have below query which is a fairly simple query and as per the execution plan it is also using appropriate non-clustered index seek for both the tables.

declare @P0 int,@P1 int
SELECT PTD_TEST_RESULT_TYPE, PTD_READING_TEXT  FROM TRN_PT_TESTS_HEAD, 
TRN_PT_TESTS_DET  WHERE PTH_ENC_ID = @P0  AND  PTD_PTH_ID = PTH_ID AND  
PTD_READING_TEXT IS NOT NULL AND  PTD_READING_TEXT <> '' AND  PTD_TEST_ID = @P1  
AND 
PTD_TEST_SET_NO = 0  ORDER BY PTD_ID

However, I can see sometimes the query takes 13sec to execute, while checking into sys.dm_exec_requests table I can see it is waiting on wait_resource '60:1:1971533'

On further debugging I found 60:1:1971533 is actually Database_Id : FileId : PageNumber i.e. database_id=60 data_file_id = 1 page_number = 1971533

Further I found the page_number is actually a PK_PTH_ID for TRN_PT_TESTS_HEAD table

I used below query to find this

SELECT sc.name as schema_name, so.name as object_name, si.name as index_name
FROM sys.objects as so
  JOIN sys.indexes as si on so.object_id = si.object_id
  JOIN sys.schemas AS sc on so.schema_id = sc.schema_id
WHERE so.object_id = 1602104748
  and si.index_id = 1

My question is, If the query is using appropriate non-clustered index seek for TRN_PT_TESTS_HEAD table then why the query is waiting on PK_PTH_ID clustered index of same table.

Do anyone have some idea on this?

0

There are 0 best solutions below