Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
The issue with going to nolocks everywhere, or changing the isolation to read uncommitted across the board is a data quality issue. You don't just get dirty reads, but you can potentially return the same data twice or miss data altogether. It may be better to look at your design and see if it is time for a separate reporting database. See this question –  Mike Walsh Nov 26 '12 at 14:40
    
@MikeWalsh Agreed. That's what I also tried to relate to in the bit about the XY-problem. Still, knowing where and when it is an option to use locking hints may be beneficial, if used with care. –  Jeroen Nov 26 '12 at 20:44

2 Answers 2

up vote 6 down vote accepted

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.
share|improve this answer

Have you considered READ_COMMITTED_SNAPSHOT row versioning for the database?

Kim Tripp has a good article about it at http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

READ_COMMITTED_SNAPSHOT allows better functionality than WITH (NOLOCK) in that it provides absolute point-in-time consistency for long-running aggregations or queries with increased throughput because of reduced lock contention.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.