SQL Server – Why So Many Logical Reads?

sql serversql-server-2008

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

Best Answer

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.

Related Question