Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1063
  • Last Modified:

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

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
3 Solutions
 
orommCommented:
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
 
Easwaran ParamasivamAuthor Commented:
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
 
David ToddSenior DBACommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Easwaran ParamasivamAuthor Commented:
Thanks. Is there any option to use regex to find the SPs out?
0
 
Anthony PerkinsCommented:
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
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now