> As I'm running my operational procedure, it might flow something like this:
>
> 1. Find first Ticket in 'Pending' Status.
> 2. Work with Ticket.
> 3. Set Ticket status to 'Complete'.
> 4. Repeat.

Depending on what you mean by "work with ticket", it would seem that you need to use procedure logic with a FOR SELECT ... DO loop. Where you get the list of 'Pending' items and then walk over that list.

In that way the number of reads to build the list of 'Pending' items would be minimized.

If you are re-selecting the 'Pending' items within a loop, it would be expected for the number of indexed read to grow with each iteration.

You might also want to consider a compound index on weight+status (with the field with the lowest number of possible value as the first field). The index would be ascending or descending on how you are managing weight (i.e is weigth = 1 the highest or lowest priority?)


Sean