May 24, 2011 at 6:43 am
My site running SQL Server 2005 SP3 had a problem with deadlocks until I began using "WITH(NOLOCK)" on all SELECT statements a few years ago. Because nolock has been deprecated, though, my bosses want me to use snapshot isolation instead.
ALTER DATABASE TLCWebLSN
SET ALLOW_SNAPSHOT_ISOLATION ON
This works successfully. But not this:
ALTER DATABASE TLCWebLSN
SET READ_COMMITTED_SNAPSHOT ON
Management Studio just says, "Executing query..." and the query never completes. I let it run all night.
On a possibly related note, I've found that I cannot import to a table that has fulltext indexing on, so I thought that could be the problem. I have 2 tables with fulltext indexing on. But turning indexing off first didn't make any difference.
I'm connecting to the database with Management Studio from a remote client. Could that be a problem? Do I need to run the commands directly on the box itself?
I've googled as best I can and don't see anybody else reporting problems turning on snapshot isolation. Anybody have a clue?
May 24, 2011 at 6:54 am
Alter database needs exclusive database access, so it will wait until all other connections to the DB are closed.
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
May 24, 2011 at 7:21 am
That makes sense. Thanks a lot. Will give it a shot.
May 24, 2011 at 7:38 am
Because nolock has been deprecated, though, my bosses want me to use snapshot isolation instead.
The NOLOCK hint has not been deprecated on SELECT statements, only with the use of UPDATE and DELETE.
> http://msdn.microsoft.com/en-us/library/ms143729.aspx
I am not saying to continue the use of NOLOCK or to stop looking at SNAPSHOT ISOLATION because I think it's a good switch to make for lots of reasons, not the least of which is data integrity, but if management is basing the decision solely on the idea that the feature is deprecated then they have it wrong and you can bring that up if you think its worthwhile for them to revisit the decision (your call).
Just some friendly words of caution:
> Unlike adding NOLOCK everywhere SNAPSHOT is not something to flick on without testing for performance. Without proper hardware support I have seen significant drops in performance after turning it on.
> I'll assume you have lots on concurrent transactions since you decided to start using NOLOCK on all SELECT statements several years ago. With lots of concurrent transactions if tempdb is not configured to handle the additional load SNAPSHOT ISOLATION will bring about then you could be in for trouble.
> SNAPSHOT ISO is no silver bullet...in write-intensive systems it does not resolve as many concurrency issues (writers still block writers) as in read-intensive systems. What kind of system is yours?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 8:08 am
Thanks for the response. I shared your comments with management. Well see where this goes.
We're a company intranet with a customer help-desk interface. I have around 200 staff users and up to 30 customers connected at once. Honestly, I've always considered us very small potatoes; yesterday, for example, we logged fewer than 20K hits total. We got by without nolock for several years, but apparently usage eventually reached some plateau, and I had to start using it to avoid deadlocks. I think contention comes from the nature of our site, where several people typically are working on a problem ticket at the same time, reading and writing. The last straw was a stats app another group wrote that pounds the database with SELECT statements. Users were regularly seeing 20-30 second saves that ought to have completed in about a second. Rather than simply use nolock, they're insisting I use snapshot isolation because it's "best practice." Sigh.
May 24, 2011 at 9:04 am
Not sure about "best practice", sounds like someone tossed in a buzz phrase to justify some action. Management is far enough away from the details to not know the particulars of the NOLOCK deprecation changes but close enough to push a technical direction...that situation always raises a red flag.
I still think turning on SNAPSHOT ISO is a good thing to explore but I would recommend covering up and building a blocking report[/url] for your database before implementing so you have something to compare after implementing. It might not be a bad idea to eval your tempdb config too to see if there is any tightening up that can be done beforehand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 11:42 am
opc.three: I appreciate the cautions.
I'm not a SQL Server power user by any means. I generally take defaults, and I'm hesitant to mess with the settings of tempdb. It looks to have an initial size of 8MB and will grow by 10% unrestricted. The server has around 40GB free, and the database is around 6GB. If I turn snapshow isolation on and find performance is adversely affected, do you think I can simply turn it back off to undo the damage?
May 24, 2011 at 12:25 pm
If I turn snapshot isolation on and find performance is adversely affected, do you think I can simply turn it back off to undo the damage?
You can turn it on and "see how it goes". How critical your managers say this app is to the success of the business should be proportional to the amount of time they provide for you to to spend planning, testing, implementing and supporting this change. If they are not doing that then it's not your fault...but it's a bad situation so all you can do is try to educate the,. As for backing out the changes and surviving any "damage"...it's impossible for me to say. If turning it on causes tempdb to grow to the point where it fills up the disk its on then your instance will have problems. If it causes so much disk activity that it slows the app even further then you'll have performance issues...it depends on a lot of variables including how the app is written.
When SQL Server starts it creates a new tempdb using the init size settings so any growth operations done in tempdb caused by having SNAPSHOT ISO turned will be reverted by turning it off and restarting the SQL Server service...in case that helps you feel like you have "an out".
.
.
.
This is running a bit deep for a forum setting and I am starting to have to speculate more and more because I am not there to eval the whole environment. I would suggest you read as much as possible online about SNAPSHOT ISO, maybe get a good book on the topic, test in a non-prod environment and inform/educate management as much as possible before making any changes to the prod environment.
For future tempdb vision:
SELECT ((size * 8.0) / 1024.0) AS size_mb,
type_desc,
state_desc
FROM tempdb.sys.database_files ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply