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

Contracts ?

Hi There

I have noticed that one cannot alter a contract, there is no alter contract syntax.

I understand the reason is because one can break applications by altering messages in contracts used to build apps.

However now it seems that everytime i create a new message type i have to create a new contract which in turn i have turn alter a service to use it. I cannot drop and re-create contracts because they are bound by services.

So basically what i am saying is that it is really a mission to add new messages types in a complex SB application (create new message+create new contract+alter service), is this just the way it is or what am i missing here ?

Thanx

Indeed, changing contracts once an application started using them will break the apps. Similar to how one cannot change a COM interface after a component has shipped.

While in development, is an OK approach to drop the contract (and service) and recreate it again with the new message types. But once you deployed the app, the proper way to add new message types is to create new contracts. Typically this is done by creating a new contract that has all the previous contract message types and in addition, the new message types.

HTH,
~ Remus

|||Thanx Remus , just wanted to be sure.

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>