Why so many logical reads? 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.

Thanks

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
Those are page reads, mind you.  That is relevant.

Possibilities:


Improper or insufficient indexing.  Are any of the filtered fields indexed?  How wide are the indexes (see below)?
Poor page density.  What's your fill factor on any indexes you may have?  If it's too low, you are pulling a lot of pages for this.
Very wide indexes.  If you have indexes but they have a lot of fields, or very wide fields, then you get less rows per page.


I'm guessing you have a table or clustered index scan happening for at least one of these criteria, and the table is wide-ish which causes a lot of data to be read, regardless of how much data you actually need.