execution plans on a read-only subscriber

I'm looking for information about how execution plans are created and revised
for a read-only subscriber. If I have multiple read-only subscribers of the
same publication, do they create their own execution plans or do they get
them from the publisher?
Can we issue a dbcc free procedure cache command on one subscriber, and if
so, how does it get its new execution plans?
Are statistics updated only at the publisher, or can they be updated at the
subscriber?
We have a high volume (read) database with moderate insert activity that we
are looking to convert to a single publisher, multiple read only
subscriber(s) configuration.
We have had problems with random sproc recompiles giving less that optimal
execution plans. I want to undestand specificaly how this works in a
replication configuration. I'm unable to find relivent publications that
detail this aspect of replication. Any help would be greatly appreciated.
query master.dbo.syscacheobjects
then issue a dbcc freeproccache
then issue another
master.dbo.syscacheobjects
then issue a sp_help sp_help. Notice the new execution plan there. Every
time a proc or select statement is issued its execution plan is cached here.
So to answer your question, yes the publisher does "create" the execution
plans on the subscriber for the queries it executes there and the proc it
fires there. When you issue a proc on the subcriber, its execution plan is
cached there.
HTH
Hilary Cotter
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
"Jim Wilson" <Jim Wilson@.discussions.microsoft.com> wrote in message
news:7AB13B23-9FFD-49AF-83FD-2C0F4C35B248@.microsoft.com...
> I'm looking for information about how execution plans are created and
> revised
> for a read-only subscriber. If I have multiple read-only subscribers of
> the
> same publication, do they create their own execution plans or do they get
> them from the publisher?
> Can we issue a dbcc free procedure cache command on one subscriber, and if
> so, how does it get its new execution plans?
> Are statistics updated only at the publisher, or can they be updated at
> the
> subscriber?
> We have a high volume (read) database with moderate insert activity that
> we
> are looking to convert to a single publisher, multiple read only
> subscriber(s) configuration.
> We have had problems with random sproc recompiles giving less that optimal
> execution plans. I want to undestand specificaly how this works in a
> replication configuration. I'm unable to find relivent publications that
> detail this aspect of replication. Any help would be greatly appreciated.