TSQL Scripts to Analyze Merge Replication Filter Logic

Provided by: Suresh B. Kandoth, SR Escalation Engineer, Microsoft SQL Escalation Services

These 2 scripts are used by the SQL Server Replication Support team to help analyze the Filter Logic used in Merge Replication.  The first lists all defined Subset and Join filters.  The first listing shows published table with indexes, the second listing shows published tables without any indexes.  If you’re seeing performance problems with Merge Replication, I begin investigating why the second list of tables has no indexes.

This example is based on the Northwind samples database provided with SQL Server 2000.  The published tables are Customers –> Orders –> OrderDetails.  The Subset filter is based on Country = Host_Name() with Join filters to pickup the matching Orders and OrderDetails rows.

Script 1: TSQL Scripts to Analyze Merge Replication Filter Logic – Part 1

Published Tables WITH Indexes  (paste in Notepad w/o wordwrapping)

>>Query to return articles (for each publication) for which there is a filter defined
Publication Name                    join_filterid filtername                          Article Name                        Object Id   Object Name                         nickname    Join Article Name                   Join Object Id Join Object Name                    join_nickname join_filterclause                   filter_type join_unique_key
———————————– ————- ———————————– ———————————– ———– ———————————– ———– ———————————– ————– ———————————– ————- ———————————– ———– —————
NWINDMERGE                          1             Orders_Customers                    Orders                              181575685   Orders                              727001      Customers                           69575286       Customers                           727000        [Customers].[CustomerID] = [Orders  1           1
NWINDMERGE                          2             Order Details_Orders                Order Details                       117575457   Order Details                       10727000    Orders                              181575685      Orders                              727001        [Orders].[OrderID] = [Order Detail  1           0

(2 row(s) affected)

Published Tables WITHOUT Indexes  (paste in Notepad w/o wordwrapping)

>>Query to return filter definitions on tables which have no index defined
Publication Name                    join_filterid filtername                          Article Name                        Object Id   Object Name                         nickname    Join Article Name                   Join Object Id Join Object Name                    join_nickname join_filterclause                   filter_type join_unique_key
———————————– ————- ———————————– ———————————– ———– ———————————– ———– ———————————– ————– ———————————– ————- ———————————– ———– —————

Script 2:TSQL Scripts to Analyze Merge Replication Filter Logic – Part 2

The second script analyzes the filters to ensure appropriate indexes are created to support these Filters.  In the output below we can see there is a missing index in the join relationship between Customer and Orders.

Warning!!! Carefully review the indexes of the table [Customers] and filters specified for the article [Orders]

Published Tables Index Analysis  (paste in Notepad w/o wordwrapping)

>>Script to explictly check for columns used in the article filter and check existence of indexes on those columns
Filter : Orders_Customers    Join clause specified: [Customers].[CustomerID] = [Orders].[CustomerID]
Indexes for Object : Orders
<idx>
<IndexName>PK_Orders</IndexName><index_id>1</index_id><type_desc>CLUSTERED</type_desc>
<col><ColumnName>OrderID</ColumnName><column_id>1</column_id><is_merge_published>1</is_merge_published>
<indcol><key_ordinal>1</key_ordinal><is_included_column>0</is_included_column><is_descending_key>0</is_descending_key>
</indcol>
</col>
</idx>
The following index recommendations were provided by the Missing Index Information DMVs:
CREATE INDEX IDX_Missing_Index_Orders_hdl_4_1 ON [Northwind2000].[dbo].[Orders] ([CustomerID] , [rowguid]) INCLUDE ([OrderID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])
Indexes for Object : Customers
Msg 50000, Level 11, State 2, Line 102
Warning!!! Carefully review the indexes of the table [Customers] and filters specified for the article [Orders]
Filter : Order Details_Orders    Join clause specified: [Orders].[OrderID] = [Order Details].[OrderID]
Indexes for Object : Order Details
<idx>
<IndexName>PK_Order Details</IndexName><index_id>1</index_id><type_desc>CLUSTERED</type_desc>
<col><ColumnName>OrderID</ColumnName><column_id>1</column_id><is_merge_published>1</is_merge_published>
<indcol><key_ordinal>1</key_ordinal><is_included_column>0</is_included_column><is_descending_key>0</is_descending_key>
</indcol>
</col>
<col><ColumnName>ProductID</ColumnName><column_id>2</column_id><is_merge_published>1</is_merge_published>
<indcol><key_ordinal>2</key_ordinal><is_included_column>0</is_included_column><is_descending_key>0</is_descending_key>
</indcol>
</col>
</idx>
Indexes for Object : Orders
<idx>
<IndexName>PK_Orders</IndexName><index_id>1</index_id><type_desc>CLUSTERED</type_desc>
<col><ColumnName>OrderID</ColumnName><column_id>1</column_id><is_merge_published>1</is_merge_published>
<indcol><key_ordinal>1</key_ordinal><is_included_column>0</is_included_column><is_descending_key>0</is_descending_key>
</indcol>
</col>
</idx>
The following index recommendations were provided by the Missing Index Information DMVs:
CREATE INDEX IDX_Missing_Index_Order Details_hdl_4_2 ON [Northwind2000].[dbo].[Orders] ([CustomerID] , [rowguid]) INCLUDE ([OrderID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])