May 3, 2010 at 8:44 am
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
May 3, 2010 at 8:50 am
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
May 3, 2010 at 8:52 am
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
May 3, 2010 at 8:57 am
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.
May 5, 2010 at 12:55 am
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?
May 5, 2010 at 8:24 pm
samsql (5/5/2010)
Hi Gail and other expertsHow 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.
May 14, 2010 at 4:34 am
Hi Lynn i have started a new thread for this but there is no reply
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply