SQL Server Performance

Read-only DB's

Discussion in 'Performance Tuning for DBAs' started by Chappy, Aug 31, 2003.

  1. Chappy New Member

    I have a 1.5 gig database, which will be deployed as a single user system, running with MSDE.

    The bulk of the data is used for heavy statistical analysis with no requirements for modifications, with a new batch of data being added to the database once a day via a file import.

    I am wondering if anyone has any experience of how the read-only database property affects performance on a single user system. Since its single user, all locks can be escalated with no blocking, where required.

    Would it be worth setting this database to read only, and then toggling it to writeable durinbg the daily import, then back again?

    Or perhaps on a single user system, with such a small database, the performance gains are negliglble and I should concentrate elsewhere. I am trying to squeeze every lat ounce of performance out of this system though, so I am thinking if I can persuade sql server to forget about locking altogether, maybe this will boost performance a little.

    Thanks for any experience you can share
  2. Argyle New Member

    If you put the database in read only no locking will take place at all, not even shared locks that you have when doing selects in a read/write database. If that makes a big difference in performance on a single user system I can't say, think you need to test it. On a multi user system (read only) we had a database that could handle 20-30% more queries per second when we put it in read only. Of course before setting the database in read only mode optimized indexes should be in place as well as updated statistics and tuned queries [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />If you are optimizing in all areas I would probably go for read only as well without having done any major testing.<br /><br />/Argyle
  3. satya Moderator

  4. gaurav_bindlish New Member

    I agree with Argyle. You'll have to look at the database design condiderations for this. Still keeping the database in single user mode / read only mode does help increase performance by 10-20%. In one case, I have seen an improvement of 50% reduction in batch processing time when read only mode was used. I think its worth a try...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. Chappy New Member

    Thanks for the advice guys. I think I will put in the effort and split it out into read only mode

Share This Page