Of course you're absolutely correct and, considering the general nature of the original post, my unadorned suggestion to use READPAST is actually inappropriate. I should have been much more specific instead of being in such a hurry. :blush: My apologies.

For most types of reporting, updating of other tables, etc, etc, I agree with Gail and 99% of the rest of the world that WITH(NOLOCK), WITH(READPAST), and other such trickery is the wrong thing to do. As stated, the best thing to do is to fix the queries/processes so that when there is necessary blocking, it only lasts for the briefest of moments. Performance is in the code.

There are only two general places where I'll use READPAST. As Gail pointed out, dashboards and dashboard-like reports are one of those places. For example, we have a call-center where I work. If a supervisor wants to see who has handled what types of calls, they can call a report generator. If a call is in the process of being inserted into the table, it shouldn't be included at that particular instant that the report is being generated so READPAST works fine on this particular type of "close enough" report.

The other place I use it is on high volume OLTP "feeder" or "staging" tables. For example, we have several "audit" systems that capture changes made to groups of several related tables. The "final" audit table is used a whole lot for audit reporting so it's important to keep inserts of data not only as short as possible, but as efficient as possible. Instead of 9 (for example) very busy OLTP tables (for a given audit group) constantly inserting a row per column changed and possibly slowing down the audited processes because indexes on the audit table must also be updated during the inserts, we feed the audit data (via audit trigger) to a staging table that only has a clustered index on an IDENTITY column to keep the inserts at the logical "end" of the table. It's very quick.

We then have a scheduled job that will move successfully inserted (fully committed) rows from that staging table to the final table in larger, more efficient groups of rows. We also want this to be quick and not wait on any uncommitted rows being inserted and we don't want to actually use any uncommitted rows because we also have to delete the rows that we moved to the final audit table. NOLOCK would be a problem here because, with NOLOCK, you do stand a chance of reading a row that might be rolled back, which also means that a change to the source table wasn't actually made and shouldn't be audited. READPAST does the job of skipping uncommitted rows perfectly, in this case. Any rows that are not picked up for movement (because they haven't been committed, yet) to the final table and deletion in the staging table are picked up on the next pass of the job that does the movement. No data is lost and no data that has been rolled back is moved nor even considered for movement. Since the rows currently being inserted are all in "one spot" in the table, there's very little locking competition for either the copy or the deletion of the rows being moved to the final table, which also lends itself to performance of the audit system.

This also helps the performance of the reporting systems on the final audit table, as well. Instead of having to wait for sometimes hundreds of transactions to clear for any given minute and with the idea that one insert of many rows is much faster than many inserts of one or two rows, the once per minute insert of many audit rows from the staging table to the final table causes very little competition for locks with the reporting systems.

As cited above, there are places where skipping uncommitted transactions are ok and will cause no loss of data but those are very rare cases. The general recommendation to avoid using NOLOCK or READPAST and the recommendation to make your queries more efficient to cause less blocking to begin with should seriously be taken to heart.