Query plan metamorphosis
-
Today a statement inside a system stored procedure (sys.sp_mergemetadataretentioncleanup - yes, this is merge replication...) started to behave quite oddly.
This is the statement:
delete top (@delbatchsize) cpm
from dbo.MSmerge_current_partition_mappings as cpm with (rowlock)
where cpm.tablenick = @artnick
and exists ( select mc.tablenick,
mc.rowguid
from dbo.MSmerge_contents as mc
inner join #oldgens as og
on (mc.generation = og.gen)
where mc.tablenick = @artnick
and mc.tablenick = cpm.tablenick
and mc.rowguid = cpm.rowguid )
The temporary table #oldgens is defined this way:
create table #oldgens
(
artnick int,
gen bigint unique clustered,
genstatus tinyint not null,
coldate datetime not null
)
When I checked the execution plan I got this one:
The optimizer estimates 1 row from the temporary table #oldgens and the plan reflect this estimation.
I tried rebuilding the statistics for MSmerge_contents and MSmerge_current_partition_mapping, hoping for a better plan.
Even worse: this is what I got:
Running the statement from SSMS with 5000 rows into #oldgens generates this (fast) plan:
The query plan appears to be determined by the number of rows inside the temporary table, which is determined by the number of rows in MSmerge_genhistory for the article in the argument @artnick. So, long story short, if the first time the procedure gets invoked the number of rows in #oldgens is low, the procedure will be compiled with an inefficient plan.
IMHO, this statement should have been coded with OPTION RECOMPILE to work around this issue.
Any ideas?
I cannot modify the query or alter the procedure (MS stuff).
I cannot create a plan guide to add RECOMPILE option, because SQL Server claims that the object name "sys.sp_mergemetadataretentioncleanup" is non-existent.
This thing is driving me crazy!! :crazy:
-- Gianluca Sartori
-
Can't you just add OPTION(RECOMPILE) to the end of the query?
--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) -
I wish I could, Jeff!
This statement is inside a system stored procedure and I can't modify it. I must admit I tried (when everything else failed) and my ALTER PROCEDURE failed for the same reason as the plan guide: the object does not exists!
The funny thing is that I can see the procedure in object explorer and I can also script it out, but I can make no changes.
:angry:
-- Gianluca Sartori
-
OK. What about setting up a Plan Guide?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
... and once you get this resolved, please post the bug on connect so that ms can fix this isssue for the rest of us.
TIA.
-
Really useful info, thanks for sharing!!..
-
Grant Fritchey (3/2/2011)
OK. What about setting up a Plan Guide?I tried, but the problem is always the same: I can't reference the object named "sys.sp_mergemetadataretentioncleanup" in the plan guide, because SQL Server refuses to find it.
I remember reading somewhere that temporary tables cause recompilations (or avoid plan caching).
For instance: http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx
Why doesn't this happen in this procedure?
If I query sys.dm_cached_plans I find the plan in the cache, with a high hit count:
select *
from sys.dm_exec_cached_plans as a
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS c
where text like '%#oldgens%'
Another interesting thing: the objectid column from sys.dm_exec_query_plan contains the id 677964155, that cannot be found in any database!
sp_msforeachdb 'USE ?;
select db_name(), name
from sysobjects
where id = 677964155'
Ghost object??? :crazy:
EDIT: fixed url
-- Gianluca Sartori
-
Ninja's_RGR'us (3/2/2011)
... and once you get this resolved, please post the bug on connect so that ms can fix this isssue for the rest of us.TIA.
I don't want to be pessimist, but I think I'm far from that point. :crying:
I'll post on connect for sure.
-- Gianluca Sartori
-
Gianluca Sartori (3/2/2011)
I wish I could, Jeff!This statement is inside a system stored procedure and I can't modify it. I must admit I tried (when everything else failed) and my ALTER PROCEDURE failed for the same reason as the plan guide: the object does not exists!
The funny thing is that I can see the procedure in object explorer and I can also script it out, but I can make no changes.
:angry:
Try with DAC, not saying it will work. But *sometimes* you get better access.
-
Dave Ballantyne (3/3/2011)
Gianluca Sartori (3/2/2011)
I wish I could, Jeff!This statement is inside a system stored procedure and I can't modify it. I must admit I tried (when everything else failed) and my ALTER PROCEDURE failed for the same reason as the plan guide: the object does not exists!
The funny thing is that I can see the procedure in object explorer and I can also script it out, but I can make no changes.
:angry:
Try with DAC, not saying it will work. But *sometimes* you get better access.
Ah! Nice tip, Dave.
I'll try and keep you posted.
-- Gianluca Sartori
-
Dave Ballantyne (3/3/2011)
Try with DAC, not saying it will work. But *sometimes* you get better access.
WTF! No, way, even from DAC.
EDIT: Interesting side-effect of my original issue: I found out that DAC didn't work on that server and it started responding only after enabling remote connections. Local DAC access still impossible. :crazy:
-- Gianluca Sartori
-
Time to call ms product support.
Hopefully they'll see it as a bug and not charge the call!
-
You're probably right.
Let's see what happens, I'll keep you posted.
Thank you, guys.
-- Gianluca Sartori
-
Gianluca Sartori (3/3/2011)
You're probably right.Let's see what happens, I'll keep you posted.
Thank you, guys.
I don't see what else to try. You found the issue. You know what fixes to apply to solve the problem. You can't and won't get access.
Nothing left to suggest in my mind besides call PSS!
-
I'm still at the MVP summit so I have no time to play. I'll see if I can experiment a little when I get back next week. I agree. Call support.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply