Answers inline.
Carl B. (4/21/2009)
Hi Matija,I don't suggest to set these database settings at connection time. I wrote : "I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself."
Thanks for clarifying that. 🙂
I was only asking if there is any penalty to set both database properties at database creation time and only using READ_COMMITED snapshot (since we think that the SNAPSHOT isolation level won't be use frequently, only in particular business cases). This is my most important concern.
I can't imagine the setting having any effect on performance, until tempdb is actually used (i.e. after snapshots have been created).
Anyway, your best bet would be to just try it out.
Speaking of tempdb, I suggest you read up on tempdb monitoring.
E.g.: http://milambda.blogspot.com/2006/09/sql-2005-and-tempdb.html
We need to switch to SNAPSHOT isolation level in order to have a consistent read of several tables.
I read something on your blog that seems to be a limitation for me: "Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.".
Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?
It should. Let me get back to you with this one.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com