Control "master merge"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tib - 12 Oct 2006 12:45 GMT

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

Reply to this Message

Hilary Cotter - 12 Oct 2006 13:17 GMT

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.

Signature

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

> Hi,
> We have a fulltext catalog, configure with CHANGE_TRACKING OFF and we have
[quoted text clipped - 12 lines]
> Thanks,
> Thibaut

Reply to this Message

tib - 12 Oct 2006 14:37 GMT

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 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.

Reply to this Message

Hilary Cotter - 12 Oct 2006 17:52 GMT

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.

Signature

> Thanks for your reply,
>
[quoted text clipped - 44 lines]
>
> Ps: We are using sql server 2005.

Reply to this Message