Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-18
6
Medium Priority
?
1,041 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 664 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 668 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 668 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

916 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