I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server.
SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN TABLEX_RESULTS mr WITH (NOLOCK) on mr.ID = mm.ID WHERE DAYS IS NOT NULL AND mm.ORDER_ID = 12345
TABLEX has about 28 million rows
TABLEX_RESULTS has about 6 million rows
What can I do here to reduce the number of logical reads on this query? I'm kind of confused on how such a simplistic query can have such a massive number of logical reads.
Index definition (from comment)
tableX IndexName PK Type Key1 Key2 IDX_MP_MEDS 0 B AA ORDER_ID MEDPASS_DATE IDX_MP_MEDS_ID_AND_ORDER_ID 0 B A MEDPASS_DATE IX_MP_MEDS_ROOT_ORDER_ID 0 B DA ROOT_ORDER_ID MEDPASS_DATE PK_MP_MED PK 1 C A MEDPASS_MEDS_ID tablex_resulsts