Can I/How do I apply Database level settings?

gregfig

Hi folks,

I can't seem to find any information about this, so it might not be possible.  I have a situation where I need to deploy an update to several existing databases, but the change involves making sure the  ALLOW_SNAPSHOT_ISOLATION setting is on.

Since this a database alteration, I can't do it in a migration script.  Is there a way to do it?  Pre-Deployment script, maybe?  I'm trying to avoid having to go to each database (on different servers) an issue an ALTER DATABASE command on each one.

Thanks, Greg

Kendra_Little

Hi Greg,

Apologies in advance, you've asked a question about one of my favorite topics, so I'll probably write way more info than you actually want   

I would do this in a pre-deployment script in a rerunnable format, something like what is in the gist here: 

https://gist.github.com/LitKnd/ae231bcd669a9a6c2cc23aacc0429974

Note: i believe the CURRENT syntax for alter database is 2012+ only, so you may need a variation if you're on a lower version.

As soon as you allow snapshot isolation, SQL Server will begin creating versions in tempdb for modified rows, and an extra 14 bytes becomes needed on the rows themselves in the database. For existing production databases where performance matters, I recommend releasing the ability to allow snapshot isolation in its own change window, before you actually begin using it. This allows you some time to observe whether or not the row versioning itself has any negative effect before you add in any changes to actually use the snapshot isolation.

Part of the reason for this is that if you have connections running SET TRANSACTION ISOLATION LEVEL SNAPSHOT and then you change the database setting so that snapshot isn't allowed, those connections are going to start getting an error when they try to run queries.

So it's worth first enabling the setting and letting that burn in for a bit before you start using it -- just simplifies troubleshooting if anything looks weird.

I'm a huge fan of snapshot isolation, please do discuss and follow up if you'd like more info!

Kendra

gregfig

Kendra,

Thank you for the answer... and for putting he answer at the beginning of your response! :upside down:  

I use snapshot isolation for queries that populate a ui grid from table(s) that get records added/edited at an extremely high rate.  Without snapshot isolation, deadlocks are a frequent companion.  I don't need to wait for that uncommitted data.  

I realize the impact to TempDb, and in fact, TempDb has it's own drive array, partitioned on SSD's.  (However, I haven't quite figured out the best approach for an Azure database, in terms of configuration and cost).

Anyway, it has saved my bacon before, and probably will again.  I am a fan too.

Thanks, Greg

Kendra_Little

Nice! Yeah, that sounds like a scenario where it will really shine. Glad to hear you love the feature, it's a favorite of mine and can be so powerful!

For an Azure VM that didn't have great disk under tempdb, I might still test out Snapshot Iso, TBH, would just use a little caution. The versioning overhead can vary quite a bit, and might be quite small if there aren't a ton of updates and deletes, or if updates and deletes happen against relatively narrow rows. For inserts, it generally doesn't need tempdb (no previous version of the row to capture), and it's also generally smart enough to not version off-row LOB data if you have updated other columns on the row. So the tempdb impact is quite small for many applications depending on the write pattern. I just always figure the time I don't mention it is the time it's likely to be a big deal, hahaha.

Anyhoo, cheers! Have a great weekend.