Options for setting NOLOCK hint in dataset queries

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:

• Set WITH (NOLOCK) hint for each table. (obtrusive, very easy to forget)

• Set isolation level to READ UNCOMMITTED for the entire query. (still easy to forget)

• 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.

• 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?

• 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"?

• 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)?

• ???

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

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.

StackExchange Database Administrators Q#29289, answer score: 6