Solved

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

Posted on 2014-02-18
6
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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