Checking issues in merge replication filters

2007-03-07

Merge replication filters may affect replication performance significantly, if the filters are not designed properly. I have a script that will check the following issues in merge filters:

Checking existence of subset filter and join filters on the same article.
Checking existence of multiple join filters on the same article
Checking existence of circular join filters in articles
Finding deepest join filter chains of articles
Checking reference to un-published tables in article filters

set nocount on

if DATABASEPROPERTYEX(DB_NAME(), 'IsMergePublished') = 0
begin
print 'Current database is not published for merge replication.'
goto done
end

print ' '
print '*****************************************************************************************************'
print '* Checking existence of subset filter and join filters on the same article ...'
print '*****************************************************************************************************'
print ' '

select 'pubname' = p.name, 'artname' = a.name
into #multiplefilters1
from sysmergearticles a, sysmergesubsetfilters f, sysmergepublications p
where a.artid = f.artid
and a.pubid = f.pubid
and a.subset_filterclause is not null
and a.subset_filterclause <> ''
and a.pubid = p.pubid

if exists (select * from #multiplefilters1)
begin
    print 'The following articles have both subset filter and join filters. The logical relationship of these '
    print 'filters is OR and will be implemented as UNION in the article view. This may have performance impact.'
    print 'You may wish to consider re-implementing them.'
    print ' '
    select 'Publication Name' = pubname, 'Article Name' = artname
      from #multiplefilters1
    print ' '
end

drop table #multiplefilters1

print '*****************************************************************************************************'
print '* Checking existence of multiple join filters on the same article ...'
print '*****************************************************************************************************'
print ' '

select f.pubid, f.artid
into #multiplefilters2
from sysmergesubsetfilters f
group by f.pubid, f.artid
having count(*) > 1

if exists (select * from #multiplefilters2)
begin
    print 'The following articles have multiple join filters. The logical relationship of these filters is OR '
    print 'and will be implemented as UNION in the article view. This may have performance impact. '
    print ' '
    select 'Publication Name' = p.name, 'Article Name' = a.name, 'Join Article Name' = f.join_articlename, 'Filter Name' = f.filtername
      from #multiplefilters2 m, sysmergearticles a, sysmergesubsetfilters f, sysmergepublications p
     where m.artid = a.artid
       and m.pubid = a.pubid
       and m.artid = f.artid
       and m.pubid = f.pubid
       and p.pubid = m.pubid
     order by 1, 2
    print ' '
end

drop table #multiplefilters2

print '*****************************************************************************************************'
print '* Checking existence of circular join filters in articles ...'
print '*****************************************************************************************************'
print ' '

select distinct 'pubname' = p.name, 'artname' = a.name, f.join_articlename, 'level' = NULL
into #multiplefilters3
from sysmergesubsetfilters f, sysmergepublications p, sysmergearticles a
where f.pubid = a.pubid
and f.artid = a.artid
and f.pubid = p.pubid

declare @level int
select @level = 0
while @level = 0 OR @@rowcount > 0
begin
    select @level = @level + 1
    update m3a
       set m3a.level = @level
      from #multiplefilters3 m3a
     where m3a.level is NULL
       and m3a.join_articlename not in (select artname from #multiplefilters3 m3b
     where m3b.level is NULL and m3a.pubname = m3b.pubname)
end

select @level = 0
while @level = 0 OR @@rowcount > 0
begin
    select @level = @level - 1
    update m3a
       set m3a.level = @level
      from #multiplefilters3 m3a
     where m3a.level is NULL
       and m3a.artname not in (select join_articlename from #multiplefilters3 m3b
     where m3b.level is NULL and m3a.pubname = m3b.pubname)
end

if exists (select * from #multiplefilters3 where level is NULL)
begin
    print 'The following articles have circular join filters with other articles. This may have performance impact. '
    print ' '
    select 'Publication Name' = pubname, 'Article Name' = artname, 'Join Article Name' = join_articlename
      from #multiplefilters3
     where level is NULL
     order by 1, 2, 3
    print ' '
end

print '*****************************************************************************************************'
print '* Finding deepest join filter chains of articles ...'
print '*****************************************************************************************************'
print ' '

declare @max int, @min int, @start int, @end int, @pubname sysname, @artname sysname, @sequence int
select @max = max(level), @min = min(level) from #multiplefilters3
where level is not null
if @max >= 0 - @min
select @start = @max, @end = 1
else
select @start = -1, @end = @min

declare #cursor_deepest_articles cursor for
select pubname, artname from #multiplefilters3 where level = @start

print 'The following artile sets have the deepest level join filters in the database. '
print ' '

select @sequence = 1
open #cursor_deepest_articles
fetch #cursor_deepest_articles into @pubname, @artname
while @@fetch_status <> -1
begin
    create table #articlechain (id int identity primary key, pubname sysname, artname sysname)
    insert #articlechain (pubname, artname) values (@pubname, @artname)
    if @max >= 0 - @min
      select @start = @max, @end = 1
    else
      select @start = -1, @end = @min

    while @start >= @end
      begin
        select @artname = join_articlename
          from #multiplefilters3
         where level = @start
           and artname = @artname
           and pubname = @pubname
        insert #articlechain (pubname, artname) values (@pubname, @artname)
        select @start = @start - 1
      end
    print 'Article Set #' + convert(varchar(32), @sequence)
    print ' '
    select 'Publication Name' = pubname, 'Article Name ' = artname from #articlechain order by id desc
    print ' '
    drop table #articlechain
    fetch #cursor_deepest_articles into @pubname, @artname
    select @sequence = @sequence + 1
end
deallocate #cursor_deepest_articles

drop table #multiplefilters3

print '*****************************************************************************************************'
print '* Checking reference to un-published tables in article filters ...'
print '*****************************************************************************************************'
print ' '

declare #csr_filtered_articles cursor for
select a.pubid, a.artid, a.sync_objid
from sysmergearticles a
where a.subset_filterclause <> ''
and a.subset_filterclause is not null
or exists
(select * from sysmergesubsetfilters f
where a.artid = f.artid and a.pubid = f.pubid)

create table #filter_article_dependencies (objid int primary key, expanded bit null)

declare @pubid uniqueidentifier, @artid uniqueidentifier, @sync_objid int

open #csr_filtered_articles
fetch #csr_filtered_articles into @pubid, @artid, @sync_objid
while @@fetch_status <> -1
begin
    truncate table #filter_article_dependencies
    insert #filter_article_dependencies (objid, expanded) values (@sync_objid, NULL)
    while exists (select * from #filter_article_dependencies where expanded IS NULL)
      begin
        insert #filter_article_dependencies (objid, expanded)
        select distinct d.depid, 0
          from sysdepends d, #filter_article_dependencies f
         where d.id = f.objid
           and f.expanded IS NULL
           and not exists (select * from #filter_article_dependencies f2
                            where f2.objid = d.depid)
        update #filter_article_dependencies set expanded = 1 where expanded IS NULL
        update #filter_article_dependencies set expanded = NULL where expanded = 0
      end
    select f.objid
      into #nonpublishedtables
      from #filter_article_dependencies f, sysobjects o
     where f.objid = o.id
       and o.xtype = 'U'
       and f.objid not in (select objid from sysmergearticles where pubid = @pubid)

    if exists (select * from #nonpublishedtables)
      begin
        select @pubname = name from sysmergepublications where pubid = @pubid
        select @artname = name from sysmergearticles where pubid = @pubid and artid = @artid
        print ' '
        raiserror('Publication [%s] article [%s] references in its subset or join filter(s) ', 0, -1, @pubname, @artname)
        raiserror('the following tables that are not published. This may cause unexpected results at the subscriber.', 0, -1)
        select 'Table name' = object_name(objid) from #nonpublishedtables
      end
    drop table #nonpublishedtables
    fetch #csr_filtered_articles into @pubid, @artid, @sync_objid
end
deallocate #csr_filtered_articles
drop table #filter_article_dependencies

done:

Share via

Checking issues in merge replication filters

Additional resources