I think I may be getting somewhere, anyone else have any suggestions on what to look at next? If I execute:

exec sys.sp_MSenumchanges_belongtopartition

38,

100,

'1510638,1510652,1510653,1510654,1510655,1510656',

89259003,

'00000000-0000-0000-0000-000000000000',

'8236EE55-51EF-4E2E-A0A6-B2D26781C214',

1510638,

1510656,

1,

0,

'F6DE0C2C-3305-4F24-B272-7C9B84705A03'

It takes 10 minutes to run.

But, as far as I can tell, that SP pretty much just builds and executes a dynamic SQL statement. I grabbed the SQL statement out of the profiler, and if I execute it manually with the same values, it executes in <1s.

declare @mingen int, @maxgen int;

declare @tablenick int, @publication_number int;

declare @partition_id int, @rowguid uniqueidentifier, @pubid uniqueidentifier;

select @partition_id = 38,

@tablenick = 89259003,

@rowguid = '00000000-0000-0000-0000-000000000000',

@pubid = '8236EE55-51EF-4E2E-A0A6-B2D26781C214',

@mingen = 1510638,

@maxgen = 1510656,

@publication_number = 1;

select rows.tablenick, rows.rowguid, rows.generation, rows.lineage, rows.colv, t.[TanSessionGUID], t.[StudioGUID], t.[ClientGUID], t.[BedGUID], t.[EmployeeGUID], t.[CreatedDate], t.[WaitingDate], t.[WarmupDate], t.[StartedDate], t.[FinishedDate], t.[ProcessedDate], t.[CleanedDate], t.[StartDelay], t.[IsStoppedEarly], t.[IsRestored], t.[IsAutoCompleted], t.[IsVirtual], t.[IsMinutes], t.[IsVisits], t.[PrimaryPackageType], t.[VisitsUsed], t.[Replicate], t.[IsGuardianPresent], t.[IsWarmupCycle], t.[IsCleanCycle], t.[IsCancelled], t.[IsGlobalClient], t.[ParentalAccompanimentName], t.[IsOpportunity], t.[IsNewProspect], t.[IsNewSprayProspect]

from (

select distinct top 100 mc.tablenick as tablenick, mc.rowguid as rowguid, mc.generation as generation, mc2.lineage as lineage, mc2.colv1 as colv

from [FabWareHouse].[dbo].[MSmerge_contents] mc

inner join [FabWareHouse].[dbo].[MSmerge_current_partition_mappings] cpm

on cpm.tablenick = mc.tablenick

and cpm.rowguid = mc.rowguid

and cpm.tablenick = @tablenick

and mc.tablenick = @tablenick

inner join [FabWareHouse].[dbo].[MSmerge_contents] mc2

on mc2.tablenick = mc.tablenick

and mc2.rowguid = mc.rowguid

inner join (

select 1510638 as gen union all

select 1510652 as gen union all

select 1510653 as gen union all

select 1510654 as gen union all

select 1510655 as gen union all

select 1510656 as gen

) as genlist

on mc.generation = genlist.gen

and genlist.gen is not NULL

where mc.generation >= @mingen and mc.generation <= @maxgen and

mc.tablenick = @tablenick

and cpm.tablenick = @tablenick

and

(

(cpm.partition_id = @partition_id and cpm.publication_number = @publication_number) or

(cpm.partition_id = @partition_id and cpm.publication_number = 0) or

(cpm.partition_id = -1 and cpm.publication_number = @publication_number) or

(cpm.partition_id = -1 and cpm.publication_number = 0)

)

order by mc.tablenick, mc.rowguid

) as rows

inner join [FabWareHouse].[dbo].[MSmerge_repl_view_8236EE5551EF4E2EA0A6B2D26781C214_359376F00519403CB8B48E9E47C3EC8D] t on t.rowguidcol = rows.rowguid

order by t.rowguidcol

Any ideas why the SP may take so long to run the same query?