What's the most effective way to re-index tables without using the Index
Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
which have a large amount of data)whose indices I need to update relatively
quickly, and I do not have a trace file to work off of.
I'm considering removing the PK attributes on these tables, then reassigning
them (which should rebuild them if I am not mistaken).
Any tips would be appreciated. Thanks.THE ITW is not meant for index maintenance. Check out DBCC DBREINDEX and
DBCC INDEXDEFRAG in BooksOnLine.
Andrew J. Kelly SQL MVP
"Elliot M. Rodriguez" <noemail> wrote in message
news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
> What's the most effective way to re-index tables without using the Index
> Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
> which have a large amount of data)whose indices I need to update
relatively
> quickly, and I do not have a trace file to work off of.
> I'm considering removing the PK attributes on these tables, then
reassigning
> them (which should rebuild them if I am not mistaken).
> Any tips would be appreciated. Thanks.
>|||Good source of info here too:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Ray Mond
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OeGSGZhEEHA.2076@.TK2MSFTNGP09.phx.gbl...
> THE ITW is not meant for index maintenance. Check out DBCC DBREINDEX and
> DBCC INDEXDEFRAG in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Elliot M. Rodriguez" <noemail> wrote in message
> news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
of
> relatively
> reassigning
>|||I appreciate your help guys... thank you!
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eBCoWyhEEHA.3064@.tk2msftngp13.phx.gbl...
> Good source of info here too:
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> --
> Regards
> Ray Mond
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OeGSGZhEEHA.2076@.TK2MSFTNGP09.phx.gbl...
and
Index
> of
>|||"Elliot M. Rodriguez" <noemail> wrote in message
news:eoGIxSgEEHA.2988@.TK2MSFTNGP12.phx.gbl...
> What's the most effective way to re-index tables without using the Index
> Tuning Wizard? I have a large collection of tables (200 or so, only 30 of
> which have a large amount of data)whose indices I need to update
relatively
> quickly, and I do not have a trace file to work off of.
> I'm considering removing the PK attributes on these tables, then
reassigning
> them (which should rebuild them if I am not mistaken).
> Any tips would be appreciated. Thanks.
script the CREATE INDEX commands, but add WITH EXISTING to it. This
effectively rebuilds the index without having to drop it first
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Sunday, February 19, 2012
Friday, February 17, 2012
Effective Implementation
Hi,
I am in the process of evaluating the existing Implementation of Fulltext
Index in our product(sql server 2000).We have full text index (text type) in
a table with out timestamp column.The existing settings for creation and
maintenance are ,
sp_fulltext_catalog 'test_catalog', 'create'
sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
sp_fulltext_column 'test', 'value', 'add'
sp_fulltext_table 'test', 'activate'
sp_fulltext_catalog 'test_catalog', 'start_incremental'
I think the above setting should populate the Index.
And then they have a process which is invoked every 2 minutes and does the
following.My contention is this will ne never invoked ..If yes , then how do
i implement incremental indexing ...If no , pls tell me the scenarions under
which this will get invoked...(assume usingChangeTracking=true)
boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
if (usingChangeTracking) {
//check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
turn it on
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextChangeTrackingOn')") == 0) {
KanaPrint.println("Start change tracking for Microsoft FullText
Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_change_tracking'"});
}
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextBackgroundUpdateIndexOn')") == 0) {
KanaPrint.println("Start background updating for Microsoft
FullText Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_background_updateindex'"});
}
return;
}
Rect
Rect,
If I correctly understand that "test_catalog" is the true name of your FT
Catalog for your FT-enabled table "test", then there is a difference between
the code examples you provided. Specifically, your code runs an Incremental
Population against the FT Catalog "Test", while the Change Tracking with
Update Index in Background is operating on the FT-enabled table "Test"
within the FT Catalog "test_catalog":
sp_fulltext_catalog 'test_catalog', 'start_incremental'
-- vs.
sp_fulltext_table 'test', 'Start_change_tracking'
sp_fulltext_table 'test', 'Start_background_updateindex'
Additionally, as your table does not have a timestamp column, when you start
an Incremental Population or enable Change Tracking, what is executed is a
Full Population (see BOL title "sp_fulltext_table" and under
start_change_tracking - "If the table does not have a timestamp, start a
full population of the full-text index". Furthermore, it is not necessary to
invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
is changed.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type)
in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how
do
> i implement incremental indexing ...If no , pls tell me the scenarions
under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
>
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
hangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Thanks John for your reply.
Does that mean the followig scripts run once , would do the job.Pls
confirm... I did some cut & paste job to hide my original table name..I am
attaching here the original scripts..
sp_fulltext_database 'enable'
sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
'kc_rawtext_pk'
sp_fulltext_column 'kc_rawtext', 'value', 'add'
sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
--Not required right ?..
And then one time invokation of the following..
sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
Also tell me if i am wrong , restart of mssearch will cause full population
of the catalogs...?
Rect
sp_fulltext_table 'kc_rawtext', 'activate'
Pls review the same and give your feedback..
"John Kane" wrote:
> Rect,
> If I correctly understand that "test_catalog" is the true name of your FT
> Catalog for your FT-enabled table "test", then there is a difference between
> the code examples you provided. Specifically, your code runs an Incremental
> Population against the FT Catalog "Test", while the Change Tracking with
> Update Index in Background is operating on the FT-enabled table "Test"
> within the FT Catalog "test_catalog":
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
> -- vs.
> sp_fulltext_table 'test', 'Start_change_tracking'
> sp_fulltext_table 'test', 'Start_background_updateindex'
> Additionally, as your table does not have a timestamp column, when you start
> an Incremental Population or enable Change Tracking, what is executed is a
> Full Population (see BOL title "sp_fulltext_table" and under
> start_change_tracking - "If the table does not have a timestamp, start a
> full population of the full-text index". Furthermore, it is not necessary to
> invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
> is changed.
> Regards,
> John
>
> "Rect" <Rect@.discussions.microsoft.com> wrote in message
> news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> in
> do
> under
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
> hangeTracking"));
>
>
|||Awaiting your feedback John.
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Rect,
Yes, that is what I mean. Specifically that running the "Change Tracking"
and "Update Index in Background" should be only run once. However, I'd also
recommend that you alter the table and add a timestamp column and then set
the CT with UIiB first without running a Full Population as setting the CT
with UIiB will run automatically run a Full Population on an un-populated FT
Catalog or if it is populated, it will run an Incremental Population, but in
your case without a timestamp column it will run a Full Population.
There is no need to run both an Incremental Population when you have CT with
UIiB set, unless you have massive (>50%) updates to the table. See BOL title
"Maintaining Full-Text Indexes" for more details on when to use turn off
UIiB and use an Incremental or Full Population with Change Tracking.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:72C752D9-99A7-4B29-B21E-628B31F3E70B@.microsoft.com...
> Thanks John for your reply.
> Does that mean the followig scripts run once , would do the job.Pls
> confirm... I did some cut & paste job to hide my original table name..I am
> attaching here the original scripts..
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
> sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
> 'kc_rawtext_pk'
> sp_fulltext_column 'kc_rawtext', 'value', 'add'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
> --Not required right ?..
> And then one time invokation of the following..
> sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
> sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
> Also tell me if i am wrong , restart of mssearch will cause full
population[vbcol=seagreen]
> of the catalogs...?
> Rect
>
> sp_fulltext_table 'kc_rawtext', 'activate'
>
> Pls review the same and give your feedback..
>
>
>
> "John Kane" wrote:
FT[vbcol=seagreen]
between[vbcol=seagreen]
Incremental[vbcol=seagreen]
start[vbcol=seagreen]
a[vbcol=seagreen]
necessary to[vbcol=seagreen]
it[vbcol=seagreen]
Fulltext[vbcol=seagreen]
type)[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
how[vbcol=seagreen]
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC[vbcol=seagreen]
FullText[vbcol=seagreen]
'test',[vbcol=seagreen]
'test',[vbcol=seagreen]
|||Thanks John.I shall remove the code for start_incermental portion and rest
will be run as one time activity for the fresh set-ups.Altering the table to
have timestamp column will be big exercise as many of the customers are using
this option already , as it will involve code changes also on the application
front.
Regards
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
I am in the process of evaluating the existing Implementation of Fulltext
Index in our product(sql server 2000).We have full text index (text type) in
a table with out timestamp column.The existing settings for creation and
maintenance are ,
sp_fulltext_catalog 'test_catalog', 'create'
sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
sp_fulltext_column 'test', 'value', 'add'
sp_fulltext_table 'test', 'activate'
sp_fulltext_catalog 'test_catalog', 'start_incremental'
I think the above setting should populate the Index.
And then they have a process which is invoked every 2 minutes and does the
following.My contention is this will ne never invoked ..If yes , then how do
i implement incremental indexing ...If no , pls tell me the scenarions under
which this will get invoked...(assume usingChangeTracking=true)
boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
if (usingChangeTracking) {
//check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
turn it on
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextChangeTrackingOn')") == 0) {
KanaPrint.println("Start change tracking for Microsoft FullText
Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_change_tracking'"});
}
if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
'TableFullTextBackgroundUpdateIndexOn')") == 0) {
KanaPrint.println("Start background updating for Microsoft
FullText Index");
DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
'Start_background_updateindex'"});
}
return;
}
Rect
Rect,
If I correctly understand that "test_catalog" is the true name of your FT
Catalog for your FT-enabled table "test", then there is a difference between
the code examples you provided. Specifically, your code runs an Incremental
Population against the FT Catalog "Test", while the Change Tracking with
Update Index in Background is operating on the FT-enabled table "Test"
within the FT Catalog "test_catalog":
sp_fulltext_catalog 'test_catalog', 'start_incremental'
-- vs.
sp_fulltext_table 'test', 'Start_change_tracking'
sp_fulltext_table 'test', 'Start_background_updateindex'
Additionally, as your table does not have a timestamp column, when you start
an Incremental Population or enable Change Tracking, what is executed is a
Full Population (see BOL title "sp_fulltext_table" and under
start_change_tracking - "If the table does not have a timestamp, start a
full population of the full-text index". Furthermore, it is not necessary to
invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
is changed.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type)
in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how
do
> i implement incremental indexing ...If no , pls tell me the scenarions
under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
>
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
hangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Thanks John for your reply.
Does that mean the followig scripts run once , would do the job.Pls
confirm... I did some cut & paste job to hide my original table name..I am
attaching here the original scripts..
sp_fulltext_database 'enable'
sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
'kc_rawtext_pk'
sp_fulltext_column 'kc_rawtext', 'value', 'add'
sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
--Not required right ?..
And then one time invokation of the following..
sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
Also tell me if i am wrong , restart of mssearch will cause full population
of the catalogs...?
Rect
sp_fulltext_table 'kc_rawtext', 'activate'
Pls review the same and give your feedback..
"John Kane" wrote:
> Rect,
> If I correctly understand that "test_catalog" is the true name of your FT
> Catalog for your FT-enabled table "test", then there is a difference between
> the code examples you provided. Specifically, your code runs an Incremental
> Population against the FT Catalog "Test", while the Change Tracking with
> Update Index in Background is operating on the FT-enabled table "Test"
> within the FT Catalog "test_catalog":
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
> -- vs.
> sp_fulltext_table 'test', 'Start_change_tracking'
> sp_fulltext_table 'test', 'Start_background_updateindex'
> Additionally, as your table does not have a timestamp column, when you start
> an Incremental Population or enable Change Tracking, what is executed is a
> Full Population (see BOL title "sp_fulltext_table" and under
> start_change_tracking - "If the table does not have a timestamp, start a
> full population of the full-text index". Furthermore, it is not necessary to
> invoked every 2 minutes, as once CT with UIiB is enabled it is set until it
> is changed.
> Regards,
> John
>
> "Rect" <Rect@.discussions.microsoft.com> wrote in message
> news:2BC0C902-C40E-48FB-A465-2B3AB2E34002@.microsoft.com...
> in
> do
> under
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC
> hangeTracking"));
>
>
|||Awaiting your feedback John.
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
|||Rect,
Yes, that is what I mean. Specifically that running the "Change Tracking"
and "Update Index in Background" should be only run once. However, I'd also
recommend that you alter the table and add a timestamp column and then set
the CT with UIiB first without running a Full Population as setting the CT
with UIiB will run automatically run a Full Population on an un-populated FT
Catalog or if it is populated, it will run an Incremental Population, but in
your case without a timestamp column it will run a Full Population.
There is no need to run both an Incremental Population when you have CT with
UIiB set, unless you have massive (>50%) updates to the table. See BOL title
"Maintaining Full-Text Indexes" for more details on when to use turn off
UIiB and use an Incremental or Full Population with Change Tracking.
Regards,
John
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:72C752D9-99A7-4B29-B21E-628B31F3E70B@.microsoft.com...
> Thanks John for your reply.
> Does that mean the followig scripts run once , would do the job.Pls
> confirm... I did some cut & paste job to hide my original table name..I am
> attaching here the original scripts..
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'create'
> sp_fulltext_table 'kc_rawtext', 'create', 'kc_rawtext_catalog',
> 'kc_rawtext_pk'
> sp_fulltext_column 'kc_rawtext', 'value', 'add'
> sp_fulltext_catalog 'kc_rawtext_catalog', 'start_incremental'
> --Not required right ?..
> And then one time invokation of the following..
> sp_fulltext_table 'kc_rawtext', 'Start_change_tracking'
> sp_fulltext_table 'kc_rawtext', 'Start_background_updateindex'
> Also tell me if i am wrong , restart of mssearch will cause full
population[vbcol=seagreen]
> of the catalogs...?
> Rect
>
> sp_fulltext_table 'kc_rawtext', 'activate'
>
> Pls review the same and give your feedback..
>
>
>
> "John Kane" wrote:
FT[vbcol=seagreen]
between[vbcol=seagreen]
Incremental[vbcol=seagreen]
start[vbcol=seagreen]
a[vbcol=seagreen]
necessary to[vbcol=seagreen]
it[vbcol=seagreen]
Fulltext[vbcol=seagreen]
type)[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
how[vbcol=seagreen]
ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextC[vbcol=seagreen]
FullText[vbcol=seagreen]
'test',[vbcol=seagreen]
'test',[vbcol=seagreen]
|||Thanks John.I shall remove the code for start_incermental portion and rest
will be run as one time activity for the fresh set-ups.Altering the table to
have timestamp column will be big exercise as many of the customers are using
this option already , as it will involve code changes also on the application
front.
Regards
Rect
"Rect" wrote:
> Hi,
> I am in the process of evaluating the existing Implementation of Fulltext
> Index in our product(sql server 2000).We have full text index (text type) in
> a table with out timestamp column.The existing settings for creation and
> maintenance are ,
> sp_fulltext_catalog 'test_catalog', 'create'
> sp_fulltext_table 'test', 'create', 'test_catalog', 'test_pk'
> sp_fulltext_column 'test', 'value', 'add'
> sp_fulltext_table 'test', 'activate'
> sp_fulltext_catalog 'test_catalog', 'start_incremental'
>
> I think the above setting should populate the Index.
> And then they have a process which is invoked every 2 minutes and does the
> following.My contention is this will ne never invoked ..If yes , then how do
> i implement incremental indexing ...If no , pls tell me the scenarions under
> which this will get invoked...(assume usingChangeTracking=true)
> boolean usingChangeTracking = KanaServer.getDBLink().isSqlServer() &&
> ParameterHelper.getParameterValueBoolean(liveNode. getParameter("UseFullTextChangeTracking"));
> if (usingChangeTracking) {
> //check if ChangeTracking and BackgroundUpdatingIndex are on, if not,
> turn it on
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextChangeTrackingOn')") == 0) {
> KanaPrint.println("Start change tracking for Microsoft FullText
> Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_change_tracking'"});
> }
> if (DBAccess.readInteger ("SELECT ObjectProperty (Object_ID('test'),
> 'TableFullTextBackgroundUpdateIndexOn')") == 0) {
> KanaPrint.println("Start background updating for Microsoft
> FullText Index");
> DBAccess.executeNoTransactions(new String[] {"sp_fulltext_table 'test',
> 'Start_background_updateindex'"});
> }
> return;
> }
> Rect
>
Effect on snapshots while reindexing
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
All the pages changed by the reindexing would be pushed to the snapshot so its files would grow. It should not cause any blocking in the snapshot, though there can be a significant increase in the IO load.
Subscribe to:
Posts (Atom)