Solved

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

Posted on 2014-02-18
6
938 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

623 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