[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Nolock/isolation level - READONLY database

Posted on 2012-09-15
Medium Priority
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
  • 2

Assisted Solution

MrAli earned 1332 total points
ID: 38402329
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

ID: 38402378
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?


Assisted Solution

MrAli earned 1332 total points
ID: 38402532
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 70

Accepted Solution

Scott Pletcher earned 668 total points
ID: 38406140
    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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 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