question

dataminor avatar image
dataminor asked

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

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
isolation-levelsnapshotdirty-read
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
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][1]". 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. :-) [1]: http://msdn.microsoft.com/en-us/library/ms188277(v=SQL.90).aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sincere thanks @WilliamD. I had not made the connection between row versioning and isolation level, but it looks like they are different aspects of the same thing. Just to be clear, am I right in thinking that the row versioning costs outlined in that article only exist within the scope of the session to which the [SET TRANSACTION...] statement applies. For example where it says "...Row versioning information requires 14 bytes added to the database row...", does that mean only those row referred to in the session?
0 Likes 0 ·
The 14 bytes are added to the "real" row in the source database. This stores information about the transaction that caused the row to change and a pointer to the row in the version-store. Take a look at this article on [Row Versioning Resource Usage][1] for further details. [1]: http://msdn.microsoft.com/en-us/library/ms175492(v=SQL.90).aspx
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.