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?