January 10, 2014 at 9:16 am
I've been running into a lot of blocking issues lately, to the point where the SPID won't die and I have to restart the server. I'm still digging into what is happening, but in the meantime, I'd really like to eliminate my SSRS subscriptions as a possible culprit. I'm thinking about using Snapshot Isolation level, from the description on MSDN, it sounds too good to be true. Basically it'll not contain dirty reads, won't lock up the table, and won't be blocked by other processes. The only issue I'm seeing is that if another SPID was in the process of updating the data, I won't see that update. I'm fine with this. There is also some issues regarding recovery though that I'm not sure I understand. I've never had to recover this database, but in the event of a disaster I may have to. Can someone tell me what I'm missing?
Thanks!
January 10, 2014 at 9:38 am
You can use snapshot isolation after you enable it for that database, but you will need to set the isolation level to snapshot on each connection or stored procedure that you want to use it. You can enable it while the database is in use:
use master;
alter database [MyDatabase] set allow_snapshot_isolation on;
It is probably simpler to set the database to read committed snapshot, because it will become the default isolation level, and no code changes are required. However you must disconnect all users from the database when you set it.
This code will enable read_committed_snapshot. Be aware that is will disconnect all active users in that database and rollback any open transactions.
use master;
alter database [MyDatabase] set read_committed_snapshot on with rollback immediate;
As always, make sure you understand all implications before you make any change:
Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx
Using Row Versioning-based Isolation Levels
January 10, 2014 at 12:33 pm
Thanks for the heads-up, however what I was considering was leaving the default database isolation level as-is, and instead just declaring the isolation level on all of my SSRS report queries. This would leave all of my ETL processes operating as they are, but not risk them being blocked by a report that is running too long, or that gets executed at the wrong time. I'm just making sure that it's really that simple.
January 10, 2014 at 12:49 pm
You'll see heavier usage on TempDB, but it may not be significant depending on your workload.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2014 at 3:25 pm
Bad_Karma (1/10/2014)
Thanks for the heads-up, however what I was considering was leaving the default database isolation level as-is, and instead just declaring the isolation level on all of my SSRS report queries. This would leave all of my ETL processes operating as they are, but not risk them being blocked by a report that is running too long, or that gets executed at the wrong time. I'm just making sure that it's really that simple.
It's really that simple. As Gail mentioned above, the payment is in TempDB usage. That cost can get EXPENSIVE if you're working with some huge systems.
Most folks wouldn't notice. Just keep a weather eye on it the first few weeks.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply