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:
You can run it in a database enabled for merge replication. All rights reserved.
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:
