Nolock/isolation level - READONLY database

Posted on 2012-09-15
Last Modified: 2012-11-15

We have a sole readonly database which is updated monthly at night during maintenance window. The db has several stored procedures from simple to with medium complexity.
Certain stored procedures are slow And need advice on what could be done given the database is readonly.

The stored procedures do have Set NoCount On.
I'm looking at possible indexes that cold be added to tables.

Question 1:
But on another note I'm wondering if introducing Nolock in all sqls a good idea?

Question 2:
Also is there an alternative to putting no lock in each and every SQL in the database?

Question 3:
Would READ COMMITTED SNAPSHOT isolation level be an alternative for Nolock given the database is READONLY (database itself is not set to READONLY. The applications accessing the database will only read from it and will not right to it ever).

Question by:sath350163
    LVL 7

    Assisted Solution

    Good thinking however putting the DB in 'read only' mode disables write locks from being used on the DB already, it keeps the locking at the highest compatibility level since it knows it will not be written to.  Kimberly Tripp, one of the programmers of SQL server stated this.

    Read Commited Isolate Level is when writes are blocking readers, a scenario that you do not have.  Read Commited will take a quick copy or snapshot of the affected rows, make them accessible from TempDB so all readers will read the pre-write value as it's locking it for writing.  Once it's finished the engine will then redirect all future reads to the actual table and get rid of that version store in tempdb.

    NOLOCK will not be required in every sproc as it is in a read only db.

    You can verify this by analyzing your sys.dm_os_wait_stats DMV or using extended events if every db on the server isn't in read only mode (the stat is server wide, if you want db wide you have to use extended events)

    Author Comment

    Thanks for your response.

    The database I'm dealing with is not set to readonly. Application just reads from it.

    Instead of making the db as readonly, what will be the performance implications between isolation levels READ UNCOMMITTED and READ COMMITTED SNAPSHOT isolation?
    Which would be better for my case?

    LVL 7

    Assisted Solution

    Read Uncommited would act as if you wrote WITH (NOLOCK) on every table in your statement.  Use NOLOCK when you only want to select specific tables.

    Read Committed Snapshot only has gains when writers are blocking readers.  Readers would be able to get the previous data while it's locked for updating if the queries are written right.
    LVL 68

    Accepted Solution

        Yes; NOLOCK adds efficiency by not taking shared locks when SELECTing data.


        No.  Snapshot isolation would not reduce overhead, and could (in theory) increase it.

    >> I'm looking at possible indexes that could be added to tables. <<

    That is almost certainly the main issue with the slow performance.  In particular, the clustered indexes may need changed.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now