Solved

Identify list of SPs which dont have tables with out NOLOCK statement

Posted on 2014-02-18
6
816 Views
Last Modified: 2014-02-18
HI Experts,

  My customer asks to put NOLOCK statement in ALL SPs which has SELECT satement.

  Ex.,

  FROM  [dbo].[table]
  INNER JOIN [test]

I need to identify the such SPs and replace with nolock

 FROM  [dbo].[table] (nolock)
  INNER JOIN [test]   (nolock)


I've 500 SPs in my application. How to achive it easily.. I hope using regex we could do that. Please guide me how to achieve it?
0
Comment
Question by:Easwaran Paramasivam
6 Comments
 
LVL 3

Assisted Solution

by:oromm
oromm earned 166 total points
ID: 39867277
Try this:

select xo.type_desc [type], xs.name [schema], xo.name [object name], xm.definition
from sys.sql_modules xm
 inner join sys.objects xo
  on xo.object_id = xm.object_id
 inner join sys.schemas xs
  on xs.schema_id = xo.schema_id
where xm.definition NOT like '%NOLOCK%'
order by xo.type_desc, xm.definition
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39867295
But it will fail in below case.

Consider the SP has 4 tables. Out of which NOLOCK presents for 2 tables but missing for 2 tables. How to identify them as well? Please do suggest.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 167 total points
ID: 39868856
Hi,

Yeah, I know, the Customer is always right.

But sometimes they are not when it is in technical matters.

Sometimes, User Support involves fixing the User.

So, all this to ask: Why add nolock to all tables? It may avoid locks and blocking, but I suggest that the data so returned may be "dirty".

So, applying this to _all_ procedures is a sledge-hammer tactic. What is the problem the customer is trying to solve?

HTH
  David

PS The above fail - If you are really going down that road, then the only answer is to check all procedures by hand.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39869153
Thanks. Is there any option to use regex to find the SPs out?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 167 total points
ID: 39869314
Since obviously David's suggestion has fallen on deaf ears and your client does not know what he is talking about have you considered a better alternative such as add the following to all your Stored Procedures:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This has exactly the same effect as adding WITH (NOLOCK) to all your tables.

If you do not understand this or your client needs "proof", consider reading Aaron's blog http://sqlblog.com/blogs/aaron_bertrand/archive/2011/07/12/t-sql-tuesday-20-t-sql-best-practices.aspx and specifically the section: "Always set isolation level at query level, rather than as a table hint"

Now of course if you are trying to bill by hour setting WITH (NOLOCK) sounds a lot more profitable.  :)
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39869371
Thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

11 Experts available now in Live!

Get 1:1 Help Now