"This page is messed up, records appear to be missing or out of order."

So today I got some people telling me something appears wrong on page X. Records appear to be missing or out of order.

  1. Did anything change? No.
  2. Are you sure nothing changed? Yes.
  3. OK I'll take a look.
    • Use Profiler to capture sql command that is returning bad results.
    • Review sql code.
    • Assess if there really were any changes made recently. Found a couple variation of same proc.
    • Decipher what has changed. In conclusive.
    • Apply different sql code to rectify results. Success, but why did I have to adjust code, if it was working all this time.
    • Ah ha moment, someone added 2 columns to the table being accessed and added them to the result. So now, how does that affect us? Let's review the query plan before and after the columns were added. BINGO!
  4. The results were returned out of ordered because the original query plan use a covering index specifically for this proc. Because (someone) added 2 new columns to the results, the query plan changed and was no longer using the covering index, instead it used a lesser index which had a SORT ORDER of ASC on an IDENTITY column as oppose to the covering INDEX which had a SORT ORDER of DESC on a DateTime column.

Good day to you sir.

Note to self on Debugging a Service in Development.

Why does something keep happening when I'm debugging my service locally. When I reset an event to be triggered it almost instantly get switched. 100 strands of hair later.... OH, geezzz, the service I'm debugging locally is running on another development server and is polling the same SQL Table that I am, DUH. Ugh that drove me nuts. Man its always the little oversights that seems to bite you in the arse.

Only positive thing I can take away from this is that I won't be making this same mistake in the future.