| Groups | Blog | Home |
sql server replication : Creation of Indexes on the subscriber database
I have to create a number of indexes on the publisher database. These indexes
are then replicated to the subscriber, and the distributor agent is taking many hours to delivered these transactions. Can I prevent these indexes from replicating, and then recreate them on the subscriber database? The tables with these indexes are already replicated, these are new indexes. Thanks, -- George Gopie
Hilary,
The custom script that I ran on the publisher DB has the following commands IF EXISTS (SELECT name FROM sysindexes WHERE NAME = N'x1_SegregationCRRecommendation') DROP INDEX SegregationCRRecommendation.x1_SegregationCRRecommendation CREATE INDEX x1_SegregationCRRecommendation ON SegregationCRRecommendation (Segregation) IF EXISTS (SELECT name FROM sysindexes WHERE NAME = N'x1_SamplingPeriod') DROP INDEX SamplingPeriod.x1_SamplingPeriod CREATE INDEX x1_SamplingPeriod ON SamplingPeriod (PeriodSequence,DiscontinuedFlag,ScheduledFlag) Are u saying that after running this on the publisher DB, I can prevent it from replicating an then run these statements on the subscriber DB? or I have to to run Sp_AddArticle @publication'Geo_Publ' @Ceraetin_script 'CrIdx.sql' George Gopie [quoted text, click to view] "Hilary Cotter" wrote:
> Yes, use a custom creation script for this - use the @creation_script > parameter of sp_addarticle. Then apply the indexes using a post snapshot > command script. > > -- > 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 > > > > "georgeg" <ggg@hotamil.com> wrote in message > news:1D3BBB6C-9D9B-4AB2-83F0-8D1D5C56615A@microsoft.com... > >I have to create a number of indexes on the publisher database. These > >indexes > > are then replicated to the subscriber, and the distributor agent is taking > > many hours to delivered these transactions. Can I prevent these indexes > > from > > replicating, and then recreate them on the subscriber database? The tables > > with these indexes are already replicated, these are new indexes. > > Thanks, > > -- > > George Gopie > > > >
Paul,
The problem is that the distribution agent keeps moving status from Delivering Replicated Transactions to This process is waiting for a response from the backend server. This seems to take many hours, and no other transaction is replicated. -- George Gopie [quoted text, click to view] "Paul Ibison" wrote:
> George, > I'm not too sure why this is seen as an advantage. If the initialization > process creates the indexes or you create them manually it'll take the same > length of time. Perhaps the issue is that you are using a shared > distribution agent? In that case I'd set up an independant one (I usually do > this anyway to avoid errors clogging up unrelated publications). > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
Yes, use a custom creation script for this - use the @creation_script
parameter of sp_addarticle. Then apply the indexes using a post snapshot command script. -- 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 [quoted text, click to view] "georgeg" <ggg@hotamil.com> wrote in message news:1D3BBB6C-9D9B-4AB2-83F0-8D1D5C56615A@microsoft.com... >I have to create a number of indexes on the publisher database. These >indexes > are then replicated to the subscriber, and the distributor agent is taking > many hours to delivered these transactions. Can I prevent these indexes > from > replicating, and then recreate them on the subscriber database? The tables > with these indexes are already replicated, these are new indexes. > Thanks, > -- > George Gopie >
If you bcp into a table with indexes the bcp process takes far longer than
if the table did not have indexes on it. Also the odbcbcp utility does not know about the order hint. It is faster to create them after the process has completed. -- 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 [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:ONFuVJIZGHA.3424@TK2MSFTNGP02.phx.gbl... > George, > I'm not too sure why this is seen as an advantage. If the initialization > process creates the indexes or you create them manually it'll take the > same length of time. Perhaps the issue is that you are using a shared > distribution agent? In that case I'd set up an independant one (I usually > do this anyway to avoid errors clogging up unrelated publications). > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) >
Really? I'll have to confirm this. I was thinking more about the
clustered/unique indexes. -- 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 [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... > AFAIR the non-clustered indexes are created after the data-load naturally? > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > > > >
you are completely correct Paul! Thanks for the correction. All indexes are
created after the bcp operation is complete. -- 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 [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:ObeSz1IZGHA.1200@TK2MSFTNGP03.phx.gbl... > Really? I'll have to confirm this. I was thinking more about the > clustered/unique indexes. > > -- > 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 > > > > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... >> AFAIR the non-clustered indexes are created after the data-load >> naturally? >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> (recommended sql server 2000 replication book: >> http://www.nwsu.com/0974973602p.html) >> >> >> >> > >
Also are you using the concurrent snapshot option? This causes significant
messages like the backend message you are getting. -- 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 [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:esQqRLJZGHA.2376@TK2MSFTNGP03.phx.gbl... > George - I had a publication where this stage would take hours. AFAIK > there's no shortcut. What I do is dbcc inputbuffer to see which index it > is up to which at least gives me confidence that everything is proceeding > normally. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > > > >
George,
I'm not too sure why this is seen as an advantage. If the initialization process creates the indexes or you create them manually it'll take the same length of time. Perhaps the issue is that you are using a shared distribution agent? In that case I'd set up an independant one (I usually do this anyway to avoid errors clogging up unrelated publications). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
AFAIR the non-clustered indexes are created after the data-load naturally?
Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
George - I had a publication where this stage would take hours. AFAIK
there's no shortcut. What I do is dbcc inputbuffer to see which index it is up to which at least gives me confidence that everything is proceeding normally. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Do you know if I increase or decrease the snapshot agent the BCpBatchSize
from 100000, will improve performance. Also, are there any other properties that can increase performance? -- George Gopie [quoted text, click to view] "Hilary Cotter" wrote:
> you are completely correct Paul! Thanks for the correction. All indexes are > created after the bcp operation is complete. > > -- > 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 > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:ObeSz1IZGHA.1200@TK2MSFTNGP03.phx.gbl... > > Really? I'll have to confirm this. I was thinking more about the > > clustered/unique indexes. > > > > -- > > 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 > > > > > > > > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > > news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... > >> AFAIR the non-clustered indexes are created after the data-load > >> naturally? > >> Cheers, > >> Paul Ibison SQL Server MVP, www.replicationanswers.com > >> (recommended sql server 2000 replication book: > >> http://www.nwsu.com/0974973602p.html) > >> > >> > >> > >> > > > > > >
It will slow it down but can prevent the agent being marked suspect. If you
have a multiproc subscriber use inprocloader -- 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 [quoted text, click to view] "georgeg" <ggg@hotamil.com> wrote in message news:A3AA4E6F-60D3-40B1-B8F4-BBD7E0F825FE@microsoft.com... > Do you know if I increase or decrease the snapshot agent the BCpBatchSize > from 100000, will improve performance. Also, are there any other > properties > that can increase performance? > -- > George Gopie > > > > "Hilary Cotter" wrote: > >> you are completely correct Paul! Thanks for the correction. All indexes >> are >> created after the bcp operation is complete. >> >> -- >> 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 >> >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:ObeSz1IZGHA.1200@TK2MSFTNGP03.phx.gbl... >> > Really? I'll have to confirm this. I was thinking more about the >> > clustered/unique indexes. >> > >> > -- >> > 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 >> > >> > >> > >> > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> > news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... >> >> AFAIR the non-clustered indexes are created after the data-load >> >> naturally? >> >> Cheers, >> >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> >> (recommended sql server 2000 replication book: >> >> http://www.nwsu.com/0974973602p.html) >> >> >> >> >> >> >> >> >> > >> > >> >> >>
Hilary,
How and where you set the inprocloader? Is this a parameter on the ditribution agent? -- George Gopie [quoted text, click to view] "Hilary Cotter" wrote:
> It will slow it down but can prevent the agent being marked suspect. If you > have a multiproc subscriber use inprocloader > > -- > 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 > > > > "georgeg" <ggg@hotamil.com> wrote in message > news:A3AA4E6F-60D3-40B1-B8F4-BBD7E0F825FE@microsoft.com... > > Do you know if I increase or decrease the snapshot agent the BCpBatchSize > > from 100000, will improve performance. Also, are there any other > > properties > > that can increase performance? > > -- > > George Gopie > > > > > > > > "Hilary Cotter" wrote: > > > >> you are completely correct Paul! Thanks for the correction. All indexes > >> are > >> created after the bcp operation is complete. > >> > >> -- > >> 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 > >> > >> > >> > >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > >> news:ObeSz1IZGHA.1200@TK2MSFTNGP03.phx.gbl... > >> > Really? I'll have to confirm this. I was thinking more about the > >> > clustered/unique indexes. > >> > > >> > -- > >> > 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 > >> > > >> > > >> > > >> > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > >> > news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... > >> >> AFAIR the non-clustered indexes are created after the data-load > >> >> naturally? > >> >> Cheers, > >> >> Paul Ibison SQL Server MVP, www.replicationanswers.com > >> >> (recommended sql server 2000 replication book: > >> >> http://www.nwsu.com/0974973602p.html) > >> >> > >> >> > >> >> > >> >> > >> > > >> > > >> > >> > >> > >
Yes it is! Right click on your distribution agent, select properties, select
the steps tab, double click on run step, and in the command box type -InProcLoader. -- 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 [quoted text, click to view] "georgeg" <ggg@hotamil.com> wrote in message news:724EF382-C5F2-41AE-AFC1-5036065D4D79@microsoft.com... > Hilary, > How and where you set the inprocloader? Is this a parameter on the > ditribution agent? > -- > George Gopie > > > > "Hilary Cotter" wrote: > >> It will slow it down but can prevent the agent being marked suspect. If >> you >> have a multiproc subscriber use inprocloader >> >> -- >> 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 >> >> >> >> "georgeg" <ggg@hotamil.com> wrote in message >> news:A3AA4E6F-60D3-40B1-B8F4-BBD7E0F825FE@microsoft.com... >> > Do you know if I increase or decrease the snapshot agent the >> > BCpBatchSize >> > from 100000, will improve performance. Also, are there any other >> > properties >> > that can increase performance? >> > -- >> > George Gopie >> > >> > >> > >> > "Hilary Cotter" wrote: >> > >> >> you are completely correct Paul! Thanks for the correction. All >> >> indexes >> >> are >> >> created after the bcp operation is complete. >> >> >> >> -- >> >> 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 >> >> >> >> >> >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> >> news:ObeSz1IZGHA.1200@TK2MSFTNGP03.phx.gbl... >> >> > Really? I'll have to confirm this. I was thinking more about the >> >> > clustered/unique indexes. >> >> > >> >> > -- >> >> > 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 >> >> > >> >> > >> >> > >> >> > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> >> > news:%23KizcfIZGHA.2376@TK2MSFTNGP03.phx.gbl... >> >> >> AFAIR the non-clustered indexes are created after the data-load >> >> >> naturally? >> >> >> Cheers, >> >> >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> >> >> (recommended sql server 2000 replication book: >> >> >> http://www.nwsu.com/0974973602p.html) >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> > >> >> >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |