Sql-server – Justify NOT using (nolock) hint in every query

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, but before it attempts to read the data row.

Craig Freedman gives a good write up here

Fortunately, the fix is relatively simple: http://support.microsoft.com/kb/815008

If that doesn't work, you have the slightly more painful option of removing all your WITH (NOLOCK) hints and setting your transaction isolation level to something above READ UNCOMMITTED.

Quick answers:

1. Works, as you noted.
2. Works, as you noted.
3. This does not appear to work. I didn't see an option off-hand and whenever that question gets asked, the answer always comes back to setting the isolation level in the stored procedure.
4. I wouldn't believe so. SSRS is at a higher abstraction layer than the database engine, so in some sense, it doesn't 'care' what the isolation level is--after all, you can use non-RDBMS solutions in your reports.
5. This does not work. You cannot set isolation level in the connection string.
6. This could work. You can create a login trigger.

There are a few options that are viable if the reports are optimized and are still causing problems:

1. Use Always On if you have SQL 2012. You could then have a read-only replica that your SSRS reports could use.
2. Use replication: snapshot if you don't need real-time, and transactional if you need it to be close to real-time.
3. If you don't have the budget for Always On or the patience to deal with replication, do replication on the cheap: create a report-friendly schema (i.e., denormalize the tables and put them in a format which makes it easier to run reports) and use SSIS to feed that Report schema. This works better if your end users can live with "older" data (e.g., updating hourly or every 5 minutes). The downside is that you will be designing the data model twice: once for the OLTP model and once for the pseudo-warehousing model. The upside is that if you ever move in the direction of a centralized data warehouse, this is a very helpful exercise.