Why so many logical reads?

Question

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

Answer 1

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.

Answer 2

Because you don't have appropriate indexes. Most importantly you would need an index on TABLEX (ORDER_ID) and an index on TABLEX_RESULTS (ID). Preferably the index on ORDER_ID should include (DAYS, ID, RESULT_DATE).

You have an index on ORDER_ID, but is not covering. For the tables_results, either you don't have any index, or your comment does not include them.

Answer 3

There are a large number of reads because SQL Server is scanning the table to execute your query, which requires reading every row.

You can reduce the number of reads by creating narrow indexes that contain only the columns needed by the query.

You might also consider caching the results of the query, to possibly avoid having to run it more times that you really need. You could cache in memory, or perhaps in a small table.

Another option is to periodically move your data into an Analysis Services cube, and use ADOMD.NET and MDX to query it -- should be much faster than running large aggregation type queries in the relational engine.