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

Constant Disk Activity

While running MSDE it looks like the service hits the disk every 30
45 seconds. Any hints as to if / how to configure the service to not
hit the disk so often?
Do you have any reason to believe that is kind of activity is unusual? Are
there any clients connected to the MSDE instance that are doing extracting
data or doing updates or deletes? If data pages are being changed the the
Lazy Writer will be flushing them out to disk on a periodic basis.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?
|||Our application only connects with and interacts with the database
infrequently. We are seeing this disk activity constantly, whether
there is any application currently accessing the database or not.
|||You might try using Filemon from www.sysinternals.com. It's a free utility
that can show any file acivity on your system by any process, including SQL
Server. You can use it to see exactly what process is hitting the file
system and what file it is accessing.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405191328.657e38c3@.posting.google.c om...
> Our application only connects with and interacts with the database
> infrequently. We are seeing this disk activity constantly, whether
> there is any application currently accessing the database or not.
|||ryan_columbus@.agilent.com (Ryan Columbus) wrote:
>While running MSDE it looks like the service hits the disk every 30
>45 seconds. Any hints as to if / how to configure the service to not
>hit the disk so often?
Why are you worrying about this? What problem are you trying to solve?
- Tim Roberts, timr@.probo.com
Providenza & Boekelheide, Inc
|||The hard drives that our product will be running on have a finite
lifetime (i.e. only a certain number of continuous hours of
operation). With MSDE constantly accessing the disk, the disk is
never able to spin down. Thus, the life of our product will be
significantly shortened if we cannot stop MSDE from making these
constant disk accesses.
|||We have used a similar tool to determine that MSDE is constantly
hitting the disk. It doesn't really matter which file it is
accessing. What we need is to find a way to stop MSDE from causing
this constant disk activity.
|||I'd check if the autoclose database option is turned on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?