Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
4  
What are the indicies on the tables and what does the profiler show how the tables and indices are being scanned –  Mark Dec 16 '11 at 19:44
    
In which table is the DAYS column? And the RESULT_DATE one? –  ypercube Dec 16 '11 at 19:45
add comment

migrated from stackoverflow.com Dec 16 '11 at 20:12

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
What can you do about wide indexes? Just flat out remove them? I usually don't add any indexes that aren't being asked for by SQL server. –  Omikron Theta Dec 17 '11 at 14:02
    
nevermind I can't format my index query correctly to post here, I don't think I'm missing any indexes. Wouldn't SQL server be suggestion indexes if I were? Also the query itself runs extremely fast, less than 1 second so what am I missing? –  Omikron Theta Dec 17 '11 at 14:14
add comment

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.

share|improve this answer
    
+1 For suggesting indexes. –  JNK Dec 16 '11 at 20:27
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.