9/14/2011 4:38:32 AM
Amit Bansal -
In one of my recent assignment, I was demonstrating Snapshot Isolation and a question came up from the audience “Can you disable Snapshot Isolation while there are records in the version store?” You can but no active transactions must be referring to the version store ! But it was interesting to note the wait type assigned to a session which was trying to disable Snapshot Isolation while another transaction was referring to the version store. Let me quickly demonstrate this.
First check, if there is anything in the row version:
-- Check row versions
SELECT * FROM sys.dm_tran_version_store;
You will observe that there are no records in the version store.
Next, enable snapshot isolation in NORTHWIND database.
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON;
After enabling Snapshot Isolation, update some data in a new query window / new session:
-- session 1 / query window 1
SET NOCOUNT ON;
UPDATE dbo.Employees SET FirstName = 'Amit' WHERE EmployeeID = 1;
SELECT FirstName FROM dbo.Employees WHERE EmployeeID = 1;
You shall observe that for this transaction, the output now shows ‘Amit’. The transaction is still ON.
Check the version store and you shall be able to see the last committed version of the record being store in the data store:
Now, let us try to disable Snapshot Isolation on Northwind database while there are records in the version store and while another transaction is referring to them.
ALTER DATABASE NORTHWIND SET ALLOW_SNAPSHOT_ISOLATION OFF;
When you run the above piece of code, you would observe that the execution keeps waiting. Let us see what wait type is assigned.
While this connection waits, start a new query window and execute the following code:
-- session 2 / query window 2
select * from sys.dm_os_waiting_tasks
While there are not too many sessions on my system right now, I can easily figure out that session id 58 has been assigned a wait type of DISABLE_VERSIONING. Note: Your session ID could be different.
It is interesting; there are more than 400 different wait types :)
Now, the moment you roll back the first transaction which was modifying the data, you will see that the waiting session (58) completes and disables SNAPSHOT ISOLATION (while the records are still there in the version store which will automatically get cleaned up in about a few seconds) – this proves 2 things:
1. You cannot disable SNAPSHOT ISOLATION when there are other active transactions referring the version store.
2. You can disable SNAPSHOT ISOLATION when there is data in the version store; but no active transactions must be referring them.
After SNAPSHOT ISOLATION is disabled, you can check the version store; it should be empty.
That’s it !
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Regards, Amit Bansal
http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net/ Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions.
FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346
LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Hi SIva, please visit http://www.sqlservergeeks.com/default-category/write-for-us to know more.
Leave a comment