Switching a database to Read Only causing performance problems

Question

Switching a database to Read Only causing performance problems

Tim Walker.

SSCertifiable

Points: 5173
More actions
March 21, 2011 at 6:40 am

#237129

I've started having an intermittent problem recently when switching databases into read only mode (using SSMS). Usually it's fine. However sometimes it can take up to 5 minutes plus and during this time users report performance problems. Other users can't expand the databases tree in SSMS whilst this happens which implies some sort of blocking or locking issue is occurring.
I have this issue on two separate servers, both SQL Server 2005 9.00.4035.00. One machine is virtual, one is physical. One has a SAN and one doesn't. They are in different physical locations. Hardware-wise they have little in common.
I am using Microsoft SQL Server Management Studio 10.0.4000.0. Could it be some sort of client side problem?
I'm at a loss really. Has anybody experienced anything like this?
Thanks
.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Changing to Read Only creates a database level lock, and that can cause lock resolution issues till it finishes completing any existing transactions. I suspect that the change is probably due to a size/complexity threshold that you've passed, if changing it used to work fast and now doesn't any more.

Take a look at your locks and blocks data while you're doing the switch. See if that gives you any insight.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

Thanks for the feedback GSquared. One thing I should have added was that the performance issues reported to me are on the same server but different databases. So I'm wondering if something is going on in the master database perhaps?

.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Under SQL Server 2005, the query cache is flushed when a database is change to read only, causing a performance problem on the entire instance. You can determine if the cache is being flushed by searching the "errorlog" for a message like

SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

For the action that will flush the query cache, please read "You may experience a decrease in query performance after you perform certain database maintenance operations or regular transaction operations in SQL Server 2005"

http://support.microsoft.com/kb/917828

SQL = Scarcely Qualifies as a Language

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 4

Thanks Carl!

I have reviewed the article, and this appears to be exactly what has happened. I can track through the messages in the log as described over a nine minute period. I've been puzzled why if I do this twice it is quick the second time, this explains it's because the cache has then already been flushed.

I also presume this is flushing the cache a statement at a time, which is why it takes a while. Next time I'll try a DBCC FREEPROCCACHE first and see if it makes it quicker. (But not in a production system !!)

I think it's overkill that a cache flush happens when I do this and presumably Microsoft agree, hence the less agressive behaviour in SQL Server 2008.

Thanks again

Tim

.