Sql-server – Options for setting NOLOCK hint in dataset queries

lockingsql serverssrs

Some context:
At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by using the WITH (NOLOCK) hint for tables in the query.

Because (a) it's quite obtrusive, and (b) it's easy to forget the hint for one of the tables, we moved to a second approach setting TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED (which is fine) at the top of each dataset's query.

As you may guess, it's still easy to forget the hint for one of the datasets. So this leads to the question:

Question: What are the options for sending NOLOCK hints along with report queries?

PS. I realize this is to some extent an XY-problem (with a lot of my other options for X, such as optimizing the query, not doing reporting on the operational database, etc), but tried to make this a valid question on itself nonetheless.

Options:
Here are the options mentioned above, with added options about which I'm curious if they would work:

  1. Set WITH (NOLOCK) hint for each table. (obtrusive, very easy to forget)
  2. Set isolation level to READ UNCOMMITTED for the entire query. (still easy to forget)
  3. Is it possible to specify this at the report level? E.g. make sure all dataset queries for one report will be run without locking.
  4. Is it possible to specify this at some other SSRS level? E.g. perhaps set this for a certain Report Folder, or by utilizing an extension?
  5. Is it possible to specify this at the data source / connection string level? E.g. have all relevant reports use a certain "No-lock-data-source"?
  6. Related to the previous option: perhaps it's possible to specify a default locking hint for a specific "no-lock-sql-user" (the one that's used in the connection)?
  7. ???

Which options are viable? Are there options I've missed?

Best Answer

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.
Related Question