"Hilary Cotter" wrote:

> George - Can you contact me offline. I have a tool I am testing to
> benchmark/improve replication performance.
>
> --
> 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
> "George" <George@discussions.microsoft.com> wrote in message
> news:C8775C85-C5EB-40E2-B3EC-5BBB9022261F@microsoft.com...
> > Yes, it could be. I typically have about 1.500.000 rows in MSrepl_commands
> > table on morning, but about 6.000.000 rows by the end of day. Replications
> > are usually set up by default values. It's stock database with online
> > data,
> > with a lot of writes and reads during day.
> >
> > "Hilary Cotter" wrote:
> >
> >> Because your data load patterns were unanticipated by Microsoft. It could
> >> be
> >> an oversight on Microsoft's part, or your data load patterns could be
> >> unique, or you might be in a very small window where the optimizer can
> >> take
> >> advantage of your index, but as the number of rows increase it will do a
> >> table scan, scan another index, or do index intersection or something
> >> else
> >> like that.
> >>
> >> --
> >> 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
> >> "George" <George@discussions.microsoft.com> wrote in message
> >> news:BDA3E344-2C5A-4B2E-8148-8C7FCFB2D0E0@microsoft.com...
> >> > Yes, may be you are right. But main question is: Why do I need my own
> >> > index
> >> > in distribution database?
> >> >
> >> > "Hilary Cotter" wrote:
> >> >
> >> >> Why do you have to drop it when it hits this number? It almost looks
> >> >> like
> >> >> you need to update statistics perhaps even hourly.
> >> >>
> >> >> --
> >> >> 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
> >> >> "George" <George@discussions.microsoft.com> wrote in message
> >> >> news:E024D61B-0221-4929-9783-779A9D9C2BBE@microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have problem with high CPU utilization using by replication. I
> >> >> > found,
> >> >> > that this command takes a lot of time (for example 21 seconds in my
> >> >> > case)
> >> >> > by
> >> >> > using standard cluster index:
> >> >> > ----------------------------------------------------------
> >> >> > select @max_xact_seqno = max(xact_seqno) from MSrepl_commands with
> >> >> > (READPAST)
> >> >> > where
> >> >> > publisher_database_id = @publisher_database_id and
> >> >> > command_id = 1 and
> >> >> > type <> -2147483611
> >> >> > ----------------------------------------------------------
> >> >> > I solve this by creating my own index
> >> >> > ----------------------------------------------------------
> >> >> > CREATE NONCLUSTERED INDEX [JR_ReplCommandsHelper] ON
> >> >> > [dbo].[MSrepl_commands]
> >> >> > (
> >> >> > [publisher_database_id] ASC,
> >> >> > [command_id] ASC,
> >> >> > [type] ASC,
> >> >> > [xact_seqno] DESC
> >> >> > )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
> >> >> > IGNORE_DUP_KEY
> >> >> > =
> >> >> > OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
> >> >> > ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> >> > GO
> >> >> > ----------------------------------------------------------
> >> >> > but I have strange experience. When number of rows in table
> >> >> > MSrepl_commands
> >> >> > reached above 5.000.000, I have to drop my index. When the number is
> >> >> > lower
> >> >> > (about 2.500.000) I have to create it.
> >> >> >
> >> >> > It is strange behaviour, specially a bug in SQL 2005. Does anybody
> >> >> > have
> >> >> > the
> >> >> > same experience or can help me.
> >> >> > I 'm thinking of creating a job and ceck the rows to create/drop my
> >> >> > index,
> >> >> > but it does not solve the problem.
> >> >> >
> >> >> > Thanks a lot.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>