Control "master merge"

Hi,
We have a fulltext catalog, configure with CHANGE_TRACKING OFF and we have a
Timestamp column. After adding some new data, we execute an “ALTER FULLTEXT
INDEX ON [MyCatalog] START INCREMENTAL POPULATION”.
It’s work well but each START INCREMENTAL POPULATION, fire a master merge;
as we can see in the event viewer:
Component: MicrosoftIndexer
Catalog: SQLFT0000600005. A master merge was started due to an external
request.
A master merge have a very bad performance’s impact (and it take 4min to
complete!). How can we control it?
Thanks,
Thibaut
You can set sp_fulltext_service 'resource_usage' to a lower value. Master
merges occur (IIRC) after every 500,000 rows are processed as described in
http://msdn2.microsoft.com/en-us/library/ms143272.aspx
In SQL Server 2000, a master merge would start at midnight, or when 500,000
documents were full-text indexed.
In SQL Server 2005, a master merge starts at the end of full population and
also when an internal threshold on the number of full-text index files has
been reached.
A master merge also occurs when 500,000 documents are full-text indexed,
which is the same as in SQL Server 2000.
SQL Server 2005 also allows users to start a master merge using data
definition language.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tib" <tib@.discussions.microsoft.com> wrote in message
news:9C2B85EA-3900-478C-B7B6-FD9F2239FF7F@.microsoft.com...
> Hi,
> We have a fulltext catalog, configure with CHANGE_TRACKING OFF and we have
> a
> Timestamp column. After adding some new data, we execute an "ALTER
> FULLTEXT
> INDEX ON [MyCatalog] START INCREMENTAL POPULATION".
> It's work well but each START INCREMENTAL POPULATION, fire a master merge;
> as we can see in the event viewer:
> Component: MicrosoftIndexer
> Catalog: SQLFT0000600005. A master merge was started due to an external
> request.
>
> A master merge have a very bad performance's impact (and it take 4min to
> complete!). How can we control it?
> Thanks,
> Thibaut
>
|||Thanks for your reply,
If the master merge occurred after 500 000 new rows it will be ok for us.
But in our case, it will start after each “START INCREMENTAL POPULATION”
(sometime we just add 10 rows).

> In SQL Server 2005, a master merge starts at the end of full population
> and also when an internal threshold on the number of full-text index
> files has been reached.
We are not in this case. So why a master merge occurs?
Before the execution of an “ALTER FULLTEXT INDEX ON [MyTable] START
INCREMENTAL POPULATION”, we have:
SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') as
PopulateStatus,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'IndexSize') as IndexSize,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'ItemCount') as ItemCount,
FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'MergeStatus') as MergeStatus,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
TableFulltextPopulateStatus,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextFailCount') as
TableFulltextFailCount,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextDocsProcessed') as
TableFulltextDocsProcessed,
OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
TableFulltextPopulateStatus
Go
IndexSize ItemCount MergeStatus
0 1455 5493526 0 0000
Just after [ALTER FULLTEXT INDEX ON [MyTable] START INCREMENTAL POPULATION]:
0 0 5493604 11 0000
And in the event viewer:
Component: MicrosoftIndexer
Catalog: SQLFT0000600005. A master merge was started due to an external
request.
“START INCREMENTAL POPULATION” is an external request that’s force a master
merge?
Thanks for help,
Thibaut
Ps: We are using sql server 2005.
|||Why can't you use Change Tracking?
It does sound like a master merge is done when a full or incremental
population is completed.
From http://msdn2.microsoft.com/en-us/library/ms143272.aspx
In SQL Server 2005, a master merge starts at the end of full population and
also when an internal threshold on the number of full-text index files has
been reached.
And in my test I have verified that it also occurs upon completion of an
incremental population.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tib" <tib@.discussions.microsoft.com> wrote in message
news:A195BAAF-2D46-478A-9578-FA3694C2BD1E@.microsoft.com...
> Thanks for your reply,
>
> If the master merge occurred after 500 000 new rows it will be ok for us.
> But in our case, it will start after each "START INCREMENTAL POPULATION"
> (sometime we just add 10 rows).
> We are not in this case. So why a master merge occurs?
> Before the execution of an "ALTER FULLTEXT INDEX ON [MyTable] START
> INCREMENTAL POPULATION", we have:
> SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') as
> PopulateStatus,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'IndexSize') as IndexSize,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'ItemCount') as ItemCount,
> FULLTEXTCATALOGPROPERTY(' MyCatalog ', 'MergeStatus') as MergeStatus,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
> TableFulltextPopulateStatus,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextFailCount') as
> TableFulltextFailCount,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextDocsProcessed') as
> TableFulltextDocsProcessed,
> OBJECTPROPERTYEX(OBJECT_ID('MyTable'), 'TableFulltextPopulateStatus') as
> TableFulltextPopulateStatus
> Go
> IndexSize ItemCount MergeStatus
> 0 1455 5493526 0 0 0 0 0
> Just after [ALTER FULLTEXT INDEX ON [MyTable] START INCREMENTAL
> POPULATION]:
> 0 0 5493604 11 0 0 0 0
> And in the event viewer:
> Component: MicrosoftIndexer
> Catalog: SQLFT0000600005. A master merge was started due to an external
> request.
> "START INCREMENTAL POPULATION" is an external request that's force a
> master
> merge?
> Thanks for help,
> Thibaut
> Ps: We are using sql server 2005.
>