We have a database used for reporting, which is a replication of the live database (no OLAP for us, nosiree!). We're potentially going to end up in a bit of a pickle here because many years ago someone recommended that all queries on the reporting db include the NOLOCK hint. There may have been good reasons for this, related to the particular way replication is set up here. I don't know. It was years before my time. Anyway, for years people have been writing SQL all over the shop (RDLs, connections to Excel, sprocs, functions etc etc) along the following lines: SELECT [whatever] FROM my_db.my_schema.my_table tbl (NOLOCK) ...and guess what... this syntax is to be deprecated. Arrgh! It should say: ...WITH (NOLOCK) I saw a suggestion somewhere that you can avoid the nolock hint and its dirty reads problem by using: SET TRANSACTION ISOLATION LEVEL SNAPSHOT I'm not really sure about the precise mechanics of this though. For example if I'm writing a SELECT statement for an RDL and have the [SET TRANSACTION...] statement at the start, do I need to explicitly wrap the query in a transaction, or will it implicitly work on everything in that session? When the SQL in the RDL has run, and the session has closed, does the isolation level revert to the database's default setting? Is there a query/dmv that returns what my current transaction isolation level is (just out of curiosity)? Is there a performance hit, or are there other side-effects of using the [SET TRANSACTION...] statement? Will the [SET TRANSACTION...] statement break/interfere with/slow replication? Kindest regards DataMinor
To answer your questions in the order you asked: 1.Setting the isolation level is connection based. You would set it once, run your queries and close the connection. Any new connections to the database would revert to the default isolation level and would need to be re-set to the isolation level you wanted each time the connection is made.
2.To find out the isolation level of a specific session you can query the DMV `sys.dm_exec_sessions`: ; /* Isolation Level ENUM translation used from books online entry for the DMV sys.dm_exec_sessions */ WITH IsoLevels (transaction_isolation_level, IsolationLevelName)AS ( SELECT 0, 'Unspecified' UNION ALL SELECT 1, 'Read Uncomitted' UNION ALL SELECT 2, 'Read Committed' UNION ALL SELECT 3, 'Repeatable' UNION ALL SELECT 4, 'Serializable' UNION ALL SELECT 5, 'Snapshot') ; SELECT session_id, IsolationLevelName FROM sys.dm_exec_sessions s INNER JOIN IsoLevels i ON s.transaction_isolation_level = i.transaction_isolation_level --WHERE session_id = @yoursession_id The sessions DMV supplies a lot more information about sessions too (DateFirst settings, ANSI settings etc.) 3. The performance hit of SNAPSHOT ISOLATION will be mainly on the tempdb side (that is where the behind-the-scenes rowversioning information is stored). To read more into it, take a look at the MSDN entry "[Choosing Row Versioning-based Isolation Levels]". After reading this article, you may want to consider using the Read Commited isolation level, as it is lighter weight and should still fulfill your needs for the reporting you want to do. If you still have questions, get back in touch here. :-) :