June 25, 2009 at 10:51 am
Is this how i enable snapshot isolation on a SQL 2008 Database?
ALTER DATABASE Prod SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE Prod SET ALLOW_SNAPSHOT_ISOLATION ON
Once enabled, what are the implications off this? I want to enable this to eliminate blocking. once enabled, are there any side implications of this process?
Appreciated any input.
June 25, 2009 at 11:02 am
SQL Server Row Versioning technology is quite a big subject area. May I suggest that you review the Microsoft White paper on the subject in the first instance. It will not only cover off the basics as to how to implement the technology but also provide example of scenarios when it is appropriate to use.
http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx
One key point to keep in mind here though is that the row versioning technology will primarily only assist with addressing blocking that is as the result of read activity.
If you could provide some further information as to the problem which you are trying to address by implementing this technology then perhaps we can provide you with more specific assistance.
June 25, 2009 at 11:28 am
John - thanks a million for providing you the info. The reason we are implementing the snapshot feature is to avoid blocking. the current application user base is like over 11,000 users. the users will be performing read and write activity and this is causing lot of blocking and un-necessary waits on the system.
the solution is to avoid blocking and thats why we choose to go with snapshot. is that something recommended for our environment?
June 25, 2009 at 9:50 pm
1) If only the readonly SPIDs are getting blocked then it will do wonders for you .
2) if you have a bit of write as well , still it will prove good as readers will not block the writers.
3) but if you find the the blocking/deadlocking is due to Write blocking write or update then its not going to help you .
Implications :
--------------
1) you need to have more space for tempdb
2) you need to make sure tempdb is on faster (more rpm) disks and SAN (in case you use it).
3) you reads will be affected due to many versions available in the tempdb and the optimizer will have to scan through them .
4) for each row version there is some information attached to the versioned row (i think its 10 or 14 bits/characters).So thats the overhead.
All in all you will avoid blocking but at some cost .
Better would be to find out the reason behind blocking .
Generally its solved by :
1) creating statistice (creating index will only create stats on the columns included in the index)
2) rebuilding indexex and updating the statistics.
3) creating more indexes based on the query plans .
A DBA job is not so easy my friend ....its pains sometimes 🙂
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 26, 2009 at 2:00 am
As Abhay explains, the key driver to ensuring successful resolution to your issues is to target the source of the problem by identifying the specific T-SQL code / Stored Procedures etc. that are behind the blocking activity you are experiencing.
Doing so will allow you to evaluate the type of activity causing the blocking i.e. Read or Write, or perhaps a combination of both.
More importantly however you should review the Execution Plans for the responsible T-SQL code. This may provide further performance tuning insight such as the need to consider an alternative indexing strategy for your database.
More often than not, you can reduce blocking behaviour by troubleshooting the underlying T-SQL code through developing an understanding of how your application accesses the database.
Having exhausted these precursors and possibilities, only then would I suggest considering the use of SQL Server Row Versioning Technology. It is intended to alleviate blocking that has arisen as a result of high volume read activity and so should be used when this has been proven to be the case.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply