Code/Syntax for setting READ | UNCOMMITTED

Question

Code/Syntax for setting READ | UNCOMMITTED

DucatiRider

SSC-Addicted

Points: 486
More actions
March 8, 2011 at 1:33 pm

#234785

Hi, wondering if anyone can help. I'm working on SQL 2008 in SSMS. I have a stored procedure that I had to modify, (my first so I'm new at this). I needed to add a READ | UNCOMMITTED statement, (we don't want to do NOLOCK) to the code because there were performance issues. I added this code to the top of my stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- 030411 added Read UnCommitted statement to stored proc. Users experiencing performance issues.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION;
GO

ALTER PROCEDURE [dbo].[proc_UsageStudy]
SELECT
SELECT
then, at the bottom of the stored proc I added this:
-- 030411 added Read UnCommitted statement to stored proc. Users experiencing performance issues
COMMIT TRANSACTION;
GO
When I run this in a "New Query" panel in SSMS and do an execute I get a Successful Execution message so I assume all went well.
However, when I select "Modify" on the stored proc in SSMS, the code that I added is gone, (including the comments). I can't understand it. I added other coding changes in the middle of the SELECT and they're all in there, how come this isn't? Is it because these are object properties and those don't display? Is it possible our Admin set my rights and permissions in SSMS so that I can't see these types of commands? Or, did this code bomb out and everything else ran, giving me the Successful message?
Sorry if this is elementary, it's my first time doing this.
Any help would be greatly appreciated....
thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Neither the set isolation level nor the begin tran nor the commit is part of the procedure. All they did was set the isolation level in the connection that created the procedure, not set it for when the procedure is run.

Contents of a procedure go between the CREATE PROCEDURE and the following GO.

Before you go and add that to the procedure however, please note that all that is doing is hiding blocking problems. It's hiding symptoms of a performance problem, it's not fixing the problem. It can also result in incorrect results for the queries.

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

p.s. READ UNCOMMITTED is equivalent to NOLOCK on all tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 2

To fix the problem at hand, the Set Transaction... Statements need to be inside the Alter Procedure statement. Only items below the Alter relate to running the stored proc. Statements on top of Alter relate to the statement of actually changing the stored proc code.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

DucatiRider SSC-Addicted Points: 486 More actions · Answer 3

I got it to work! Thanks for the advice, it did the trick.

Here's my code:

ALTER PROCEDURE [dbo].[proc_Usage]

@custNumber CHAR(10),

@polNumber CHAR(7) = NULL,

@fromDate DATETIME = '1900/01/01',

@toDate DATETIME = '2100/01/01'

AS

-- 030411 1513 added Read UnCommitted statement to stored proc. Users experiencing performance issues.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

Then at the bottom I did this:

COMMIT TRANSACTION;

GO

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

DucatiRider (3/8/2011)
I needed to add a READ | UNCOMMITTED statement, (we don't want to do NOLOCK) to the code because there were performance issues.

Just a note of caution... Even though it has side effects (some are quite damaging), the whole reason why most people make the mistake of using WITH(NOLOCK) is to solve performance issues. Setting the isolation level to READ|UNCOMMITTED will NOT solve your performance issues. Further, that isolation level has all the same side effects of using WITH(NOLOCK). I recommend you do what's really necessary to solve your performance issues... evaluate and repair your code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DucatiRider SSC-Addicted Points: 486 More actions · Answer 5

Thanks for the info on performance. I'm inheriting these stored procedures from another team and I'll pass this on to my boss to see if he wants to look at some performance tuning....

thanks again!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

In case you missed the warning I gave earlier...

GilaMonster (3/8/2011)
Before you go and add that to the procedure however, please note that all that is doing is hiding blocking problems. It's hiding symptoms of a performance problem, it's not fixing the problem. It can also result in incorrect results for the queries.
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
p.s. READ UNCOMMITTED is equivalent to NOLOCK on all tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass