need opinion on NOLOCK

Question

need opinion on NOLOCK

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

Grant Fritchey (5/3/2010)
GilaMonster (5/3/2010)
Grant Fritchey (5/3/2010)
Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles.
Why? SQL won't recompile when a different branch is taken. It'll still use the cached (and probably bad) plan that it generated on the first execution.
If different objects are referenced, I'm pretty sure it recompiles, but I could be wrong (wouldn't be the first time... today).

Don't think so. AFAIK, the entire thing compiles the first time, regardless of which branch will actually be executed (optimiser can't tell that). will test, worth a blog post I think.

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

GilaMonster (5/3/2010)
Grant Fritchey (5/3/2010)
GilaMonster (5/3/2010)
Grant Fritchey (5/3/2010)
Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles.
Why? SQL won't recompile when a different branch is taken. It'll still use the cached (and probably bad) plan that it generated on the first execution.
If different objects are referenced, I'm pretty sure it recompiles, but I could be wrong (wouldn't be the first time... today).
Don't think so. AFAIK, the entire thing compiles the first time, regardless of which branch will actually be executed (optimiser can't tell that). will test, worth a blog post I think.

Yeah, sounds like I might be laboring under yet another long line of bad assumptions. I'll try to post something on this soon(ish).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Tested and confirmed

CREATE PROCEDURE TestingIfREcompiles (@Option INT)

AS

IF @option = 1

SELECT * FROM dbo.LargeTable

ELSE

SELECT * FROM dbo.LargeTable2

GO

First execution passing 1 (to get first branch) generates a cache miss and a cache insert

Second execution (passing 2 to get 2nd branch) gets a cache hit. No cache remove, no cache insert (which would be there if there was a recompile)

Blog fodder for me this week or next. Good. I was short of ideas.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Grant Fritchey (5/3/2010)
Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles. If you need to set up this kind of IF clause, then you need to create four new seperate stored procedures, each with the appropriate query, and call those procedures from this wrapper proc. You'll get radically less recompiles that way.
You're guaranteed a table scan if you put any function on a column as part of a WHERE clause or JOIN.
Other than, as Gail says, please post the details for more detailed help.

Looking at the WHERE clauses, the functions are all on the right so I think these are still SARGable and will use the appropriate indexes if available.

samsql Hall of Fame Points: 3044 More actions · Answer 5

Hi Gail and other experts

How does "ROWLOCK" work? I have this situation:

User1 - Query Analyzer

begin transaction

update t1 with (rowlock) set c1 = 10 where c1 = 5

User 2 - Query Analyzer

update t1 with (rowlock) set c1 = 20 where c1 = 15

Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.

According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

samsql (5/5/2010)
Hi Gail and other experts
How does "ROWLOCK" work? I have this situation:
User1 - Query Analyzer
begin transaction
update t1 with (rowlock) set c1 = 10 where c1 = 5
User 2 - Query Analyzer
update t1 with (rowlock) set c1 = 20 where c1 = 15
Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.
According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?

You really should start a new thread instead of hijacking this one.

samsql Hall of Fame Points: 3044 More actions · Answer 7

Hi Lynn i have started a new thread for this but there is no reply