August 25, 2010 at 11:35 am
I have a data warehouse that holds sales data. I found quite some time ago that query performance is much better if the database is read-only. However, there are a number of stored procedures on that database that are maintained on a fairly regular basis. Also, whenever one of the self-serve tools doesn't meet our needs, I write queries (for reports) directly against the data warehouse data. Those queries are saved as stored procedures, and since they run against the data in that database, I save them to that database (under an independant schema).
So the import process sets the database to read-only every day, and almost every day I have to turn that off in order to work in the database. Some days, I work in the database all day, other times I just forget to set it back to read-only. I am almost completely missing out on the benefits of having a read-only database.
Ideally, only the tables would be read-only. Even then, the staging tables don't need to be read-only, and sometimes when testing things I use them. Really, what I want is for objects in two specific schemas (fact and dim) to be read-only, while everything else can be modified. That seperation of production and development objects might also serve as a way to prevent accidently modifying the production tables during development.
It's important to note that what I'm looking for is for SELECT queries to not lock any data. I've found several solutions for making a table read-only, but they all deal with data security, not with lock prevention.
August 25, 2010 at 12:22 pm
Create a second filegroup and make it the default filegroup. Put all the tables into that filegroup. Set the filegroup readonly. Then you can still alter the procs (they're in Primary), but the tables are on a read-only filegroup and hence won't be locked.
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
August 25, 2010 at 12:40 pm
Beautiful. Thanks.
One question. All the examples I'm finding online of "MOVE TO" assume that the PK and clustered index are one in the same. That's fine for all but one table, but my fact table has a PK based on a meaningless identity column, but a clustered index on InvoicedDate. Is there anything special I need to do?
Thanks again,
--J
August 25, 2010 at 12:49 pm
If you want to completely avoid locking, you'll need to move all indexes, clustered and nonclustered to the new filegroup. You can move all with CREATE ... WITH DROP EXISTING, even the ones enforcing primary keys/unique constraints.
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
August 25, 2010 at 12:53 pm
Alright. I use a script to drop/recreate that table every day anyways. I'll just modify it to the new filegroup, and let it do all the leg work tonight.
Thanks.
August 25, 2010 at 3:55 pm
Gail's suggestion works - and might even be the best option. However, there is no reason why you couldn't create your objects in another database to work with the read only databases.
All you need to do is use 3-part naming and specify the database in the queries.
As for the regular maintenance of the procedures - are those done by other people?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply