I am using SQL Server 2012. We have a table that specifies the incoming requests to be processed. Some of the columns are (total columns are 10),

requestStatus (Enum - Processed, NotProcessed, Failed, etc)

The query to pick the new requests is

select top 10000 
from RequestProcessingTable 
where requestStatus = 'NotProcessed' 
order by requestId

The total number of rows in this table is 300 million.

We have 2 indexes, one clustered (requestId - primary key of this table) and one non-clustered (requestStatus).

Since we want to process the requests based on the requestId we need to do the order by. When we introduce the order by it is taking 3 times more time to select. Is there a best way to create indexes or do it in a different approach so that the performance of the SQL query can be improved.

Thanks a lot for your help in advance.

  • Can you share the execution plan of the query? – ypercubeᵀᴹ Dec 23 '15 at 7:35
  • Also include what you're selecting from the table. Is it just requestId or something else too – James Z Dec 23 '15 at 10:16

Create a filtered index on requestId with the predicate where requestStatus = 'NotProcessed'. The optimiser is likely to favour this as long as the query conditions exactly match the index definition.

It may help to use a small tinyint for the status instead of a longer character string. There will be more rows per data page and less IO.

| improve this answer | |

Share the execution plan, like @YperSillyCubeᵀᴹ said.

I think you can improve the performance of this query by creating a nonclustered index on requestStatus, requestId.

However: the fact that you are not specifying the column list (I'm guessing SELECT *) may change the plan by introducing Key Lookup or Clustered Index Scan (probably CIS on such a big table). If Clustered Index Scan is inserted to compensate for the lookup, the nonclustered index will not be helpful.

I can answer the question more precisely if you update the question with the plan.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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