|
|
You're in the
sql server replication
group:Snapshot Agent Updates statistics on all PK indexes even when there is NOTHING to do!!
sql server replication:
aware of why you do need the update statistics but what I can't tell is WHY you run the update statistics if there is NOTHING to do? Thanks M$. Does any one knows how or if it is possible, to turn that "feature" OFF? -Noel Sr. DBA
Noel, the snapshot agent will only attempt to update the statistics on the
clustered index (primary key) if the statistics on it has not been populated (as indicated by a null date returned from DBCC SHOW_STATISTICS). This can happen if 1) the table is empty or 2) statistics has never been populated since the creation of the table. For case 1), updating statistics will be a trivial operation, and for case 2), updating statistics generally allows the query optimizer to choose better query plans as well as enables the snapshot agent to partition large tables more evenly for parallel processing. You can disable this behavior by specifying the unofficial "-EnableArticleBcpPartitioning 0" option on the snapshot agent command line. Hope that helps, -Raymond [quoted text, click to view] <isyourfriend@gmail.com> wrote in message news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... > On SQL Server 2005 SP2 we got this "new feature" which I am probaly > aware of why you do need the update statistics but what I can't tell > is WHY you run the update statistics if there is NOTHING to do? Thanks > M$. > > Does any one knows how or if it is possible, to turn that "feature" > OFF? > > -Noel > Sr. DBA >
On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com>
[quoted text, click to view] wrote: > Noel, the snapshot agent will only attempt to update the statistics on the > clustered index (primary key) if the statistics on it has not been populated > (as indicated by a null date returned from DBCC SHOW_STATISTICS). This can > happen if 1) the table is empty or 2) statistics has never been populated > since the creation of the table. For case 1), updating statistics will be a > trivial operation, and for case 2), updating statistics generally allows the > query optimizer to choose better query plans as well as enables the snapshot > agent to partition large tables more evenly for parallel processing. You can > disable this behavior by specifying the unofficial > "-EnableArticleBcpPartitioning 0" option on the snapshot agent command line. > > Hope that helps, > > -Raymond<isyourfri...@gmail.com> wrote in message > > news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... > > > On SQL Server 2005 SP2 we got this "new feature" which I am probaly > > aware of why you do need the update statistics but what I can't tell > > is WHY you run the update statistics if there is NOTHING to do? Thanks > > M$. > > > Does any one knows how or if it is possible, to turn that "feature" > > OFF? > > > -Noel > > Sr. DBA Raymond, Thanks you very much for your prompt reply. The reason that I am complaining is that it can potentially invalidate my queries plan cache if it is run on a schedule, causing unnecessary recompilations. I do understand that it helps with the parallel processing; I went through that (with you) before, thanks again. It is my opinion though that the check to determine that there is nothing to do should be done before updating statistics no matter how trivial it may be. --- And yes it *does* helps :-) Regards, -Noel Sr. DBA
Just out of curiousity, do you routinely have empty tables in your publisher
database that are heavily queried by your application? That is the case where I can see how the snapshot agent update stats behavior will affect you (of course I may be wrong). -Raymond [quoted text, click to view] <isyourfriend@gmail.com> wrote in message news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > wrote: >> Noel, the snapshot agent will only attempt to update the statistics on >> the >> clustered index (primary key) if the statistics on it has not been >> populated >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). This >> can >> happen if 1) the table is empty or 2) statistics has never been populated >> since the creation of the table. For case 1), updating statistics will be >> a >> trivial operation, and for case 2), updating statistics generally allows >> the >> query optimizer to choose better query plans as well as enables the >> snapshot >> agent to partition large tables more evenly for parallel processing. You >> can >> disable this behavior by specifying the unofficial >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent command >> line. >> >> Hope that helps, >> >> -Raymond<isyourfri...@gmail.com> wrote in message >> >> news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... >> >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly >> > aware of why you do need the update statistics but what I can't tell >> > is WHY you run the update statistics if there is NOTHING to do? Thanks >> > M$. >> >> > Does any one knows how or if it is possible, to turn that "feature" >> > OFF? >> >> > -Noel >> > Sr. DBA > > Raymond, Thanks you very much for your prompt reply. > > The reason that I am complaining is that it can potentially invalidate > my queries plan cache if it is run on a schedule, causing unnecessary > recompilations. I do understand that it helps with the parallel > processing; I went through that (with you) before, thanks again. It is > my opinion though that the check to determine that there is nothing to > do should be done before updating statistics no matter how trivial it > may be. --- And yes it *does* helps :-) > > Regards, > > -Noel > Sr. DBA >
On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com>
[quoted text, click to view] wrote: > Just out of curiousity, do you routinely have empty tables in your publisher > database that are heavily queried by your application? That is the case > where I can see how the snapshot agent update stats behavior will affect you > (of course I may be wrong). > > -Raymond > > <isyourfri...@gmail.com> wrote in message > > news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... > > > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > > wrote: > >> Noel, the snapshot agent will only attempt to update the statistics on > >> the > >> clustered index (primary key) if the statistics on it has not been > >> populated > >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). This > >> can > >> happen if 1) the table is empty or 2) statistics has never been populated > >> since the creation of the table. For case 1), updating statistics will be > >> a > >> trivial operation, and for case 2), updating statistics generally allows > >> the > >> query optimizer to choose better query plans as well as enables the > >> snapshot > >> agent to partition large tables more evenly for parallel processing. You > >> can > >> disable this behavior by specifying the unofficial > >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent command > >> line. > > >> Hope that helps, > > >> -Raymond<isyourfri...@gmail.com> wrote in message > > >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... > > >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly > >> > aware of why you do need the update statistics but what I can't tell > >> > is WHY you run the update statistics if there is NOTHING to do? Thanks > >> > M$. > > >> > Does any one knows how or if it is possible, to turn that "feature" > >> > OFF? > > >> > -Noel > >> > Sr. DBA > > > Raymond, Thanks you very much for your prompt reply. > > > The reason that I am complaining is that it can potentially invalidate > > my queries plan cache if it is run on a schedule, causing unnecessary > > recompilations. I do understand that it helps with the parallel > > processing; I went through that (with you) before, thanks again. It is > > my opinion though that the check to determine that there is nothing to > > do should be done before updating statistics no matter how trivial it > > may be. --- And yes it *does* helps :-) > > > Regards, > > > -Noel > > Sr. DBA Raymond, There are tables that get "cleared" (moved to history) at the end of a daily session (for performance and other reasons we don't want to accumulate history there) and there are others configuration tables that are populated or cleared by agents adaptively. This process repeats at different timezones and 24x7 with very narrow windows in between *used* by archivers, reports,etc. I am worried that my cache gets trashed in the middle of a heavy activity if I leave the agent running. Are you saying that the minute those tables get populated I won't get the "update statistics" behaviour ? -Noel
On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com>
[quoted text, click to view] wrote: > Just out of curiousity, do you routinely have empty tables in your publisher > database that are heavily queried by your application? That is the case > where I can see how the snapshot agent update stats behavior will affect you > (of course I may be wrong). > > -Raymond > > <isyourfri...@gmail.com> wrote in message > > news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... > > > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > > wrote: > >> Noel, the snapshot agent will only attempt to update the statistics on > >> the > >> clustered index (primary key) if the statistics on it has not been > >> populated > >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). This > >> can > >> happen if 1) the table is empty or 2) statistics has never been populated > >> since the creation of the table. For case 1), updating statistics will be > >> a > >> trivial operation, and for case 2), updating statistics generally allows > >> the > >> query optimizer to choose better query plans as well as enables the > >> snapshot > >> agent to partition large tables more evenly for parallel processing. You > >> can > >> disable this behavior by specifying the unofficial > >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent command > >> line. > > >> Hope that helps, > > >> -Raymond<isyourfri...@gmail.com> wrote in message > > >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... > > >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly > >> > aware of why you do need the update statistics but what I can't tell > >> > is WHY you run the update statistics if there is NOTHING to do? Thanks > >> > M$. > > >> > Does any one knows how or if it is possible, to turn that "feature" > >> > OFF? > > >> > -Noel > >> > Sr. DBA > > > Raymond, Thanks you very much for your prompt reply. > > > The reason that I am complaining is that it can potentially invalidate > > my queries plan cache if it is run on a schedule, causing unnecessary > > recompilations. I do understand that it helps with the parallel > > processing; I went through that (with you) before, thanks again. It is > > my opinion though that the check to determine that there is nothing to > > do should be done before updating statistics no matter how trivial it > > may be. --- And yes it *does* helps :-) > > > Regards, > > > -Noel > > Sr. DBA Raymond, There are tables that get "cleared" (moved to history) at the end of a daily session (for performance and other reasons we don't want to accumulate history there) and there are others configuration tables that are populated or cleared by agents adaptively. This process repeats at different timezones and 24x7 with very narrow windows in between *used* by archivers, reports,etc. I am worried that my cache gets trashed in the middle of a heavy activity if I leave the agent running. Are you saying that the minute those tables get populated I won't get the "update statistics" behaviour ? -Noel
Like I mentioned before, the snapshot agent will only run update stats if it
couldn't find any *statistics* on the leading clustered index column but I don't really know if the storage engine will actively *remove* statistics that already exists if you delete all rows in a table (I will try that). You can definitely try to minimize the impact that update statistics from the snapshot agent may have by scheduling it to run no more often then necessary. That said, I can't help but notice that with the way tables are emptied and repopulated everyday, the statistics will basically get out-dated on a daily basis which will in turn mislead the query optimizer. Wouldn't plan guides be a better solution to ensure more stable\robust (hence no-recompilation) plans for your critical queries? -Raymond [quoted text, click to view] <isyourfriend@gmail.com> wrote in message news:1178046998.278261.206030@n59g2000hsh.googlegroups.com... > On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > wrote: >> Just out of curiousity, do you routinely have empty tables in your >> publisher >> database that are heavily queried by your application? That is the case >> where I can see how the snapshot agent update stats behavior will affect >> you >> (of course I may be wrong). >> >> -Raymond >> >> <isyourfri...@gmail.com> wrote in message >> >> news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... >> >> > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> >> > wrote: >> >> Noel, the snapshot agent will only attempt to update the statistics on >> >> the >> >> clustered index (primary key) if the statistics on it has not been >> >> populated >> >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). This >> >> can >> >> happen if 1) the table is empty or 2) statistics has never been >> >> populated >> >> since the creation of the table. For case 1), updating statistics will >> >> be >> >> a >> >> trivial operation, and for case 2), updating statistics generally >> >> allows >> >> the >> >> query optimizer to choose better query plans as well as enables the >> >> snapshot >> >> agent to partition large tables more evenly for parallel processing. >> >> You >> >> can >> >> disable this behavior by specifying the unofficial >> >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent command >> >> line. >> >> >> Hope that helps, >> >> >> -Raymond<isyourfri...@gmail.com> wrote in message >> >> >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... >> >> >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly >> >> > aware of why you do need the update statistics but what I can't tell >> >> > is WHY you run the update statistics if there is NOTHING to do? >> >> > Thanks >> >> > M$. >> >> >> > Does any one knows how or if it is possible, to turn that "feature" >> >> > OFF? >> >> >> > -Noel >> >> > Sr. DBA >> >> > Raymond, Thanks you very much for your prompt reply. >> >> > The reason that I am complaining is that it can potentially invalidate >> > my queries plan cache if it is run on a schedule, causing unnecessary >> > recompilations. I do understand that it helps with the parallel >> > processing; I went through that (with you) before, thanks again. It is >> > my opinion though that the check to determine that there is nothing to >> > do should be done before updating statistics no matter how trivial it >> > may be. --- And yes it *does* helps :-) >> >> > Regards, >> >> > -Noel >> > Sr. DBA > > > Raymond, > > There are tables that get "cleared" (moved to history) at the end of a > daily session (for performance and other reasons we don't want to > accumulate history there) and there are others configuration tables > that are populated or cleared by agents adaptively. This process > repeats at different timezones and 24x7 with very narrow windows in > between *used* by archivers, reports,etc. > > I am worried that my cache gets trashed in the middle of a heavy > activity if I leave the agent running. Are you saying that the minute > those tables get populated I won't get the "update statistics" > behaviour ? > > -Noel > > >
Just a brief update: It doesn't look like delete all rows or truncate table
will "empty" populated statistics. -Raymond [quoted text, click to view] "Raymond Mak [MSFT]" <rmak@online.microsoft.com> wrote in message news:O2bguiCjHHA.2552@TK2MSFTNGP06.phx.gbl... > Like I mentioned before, the snapshot agent will only run update stats if > it couldn't find any *statistics* on the leading clustered index column > but I don't really know if the storage engine will actively *remove* > statistics that already exists if you delete all rows in a table (I will > try that). You can definitely try to minimize the impact that update > statistics from the snapshot agent may have by scheduling it to run no > more often then necessary. That said, I can't help but notice that with > the way tables are emptied and repopulated everyday, the statistics will > basically get out-dated on a daily basis which will in turn mislead the > query optimizer. Wouldn't plan guides be a better solution to ensure more > stable\robust (hence no-recompilation) plans for your critical queries? > > -Raymond > > <isyourfriend@gmail.com> wrote in message > news:1178046998.278261.206030@n59g2000hsh.googlegroups.com... >> On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> >> wrote: >>> Just out of curiousity, do you routinely have empty tables in your >>> publisher >>> database that are heavily queried by your application? That is the case >>> where I can see how the snapshot agent update stats behavior will affect >>> you >>> (of course I may be wrong). >>> >>> -Raymond >>> >>> <isyourfri...@gmail.com> wrote in message >>> >>> news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... >>> >>> > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> >>> > wrote: >>> >> Noel, the snapshot agent will only attempt to update the statistics >>> >> on >>> >> the >>> >> clustered index (primary key) if the statistics on it has not been >>> >> populated >>> >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). >>> >> This >>> >> can >>> >> happen if 1) the table is empty or 2) statistics has never been >>> >> populated >>> >> since the creation of the table. For case 1), updating statistics >>> >> will be >>> >> a >>> >> trivial operation, and for case 2), updating statistics generally >>> >> allows >>> >> the >>> >> query optimizer to choose better query plans as well as enables the >>> >> snapshot >>> >> agent to partition large tables more evenly for parallel processing. >>> >> You >>> >> can >>> >> disable this behavior by specifying the unofficial >>> >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent >>> >> command >>> >> line. >>> >>> >> Hope that helps, >>> >>> >> -Raymond<isyourfri...@gmail.com> wrote in message >>> >>> >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... >>> >>> >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly >>> >> > aware of why you do need the update statistics but what I can't >>> >> > tell >>> >> > is WHY you run the update statistics if there is NOTHING to do? >>> >> > Thanks >>> >> > M$. >>> >>> >> > Does any one knows how or if it is possible, to turn that "feature" >>> >> > OFF? >>> >>> >> > -Noel >>> >> > Sr. DBA >>> >>> > Raymond, Thanks you very much for your prompt reply. >>> >>> > The reason that I am complaining is that it can potentially invalidate >>> > my queries plan cache if it is run on a schedule, causing unnecessary >>> > recompilations. I do understand that it helps with the parallel >>> > processing; I went through that (with you) before, thanks again. It is >>> > my opinion though that the check to determine that there is nothing to >>> > do should be done before updating statistics no matter how trivial it >>> > may be. --- And yes it *does* helps :-) >>> >>> > Regards, >>> >>> > -Noel >>> > Sr. DBA >> >> >> Raymond, >> >> There are tables that get "cleared" (moved to history) at the end of a >> daily session (for performance and other reasons we don't want to >> accumulate history there) and there are others configuration tables >> that are populated or cleared by agents adaptively. This process >> repeats at different timezones and 24x7 with very narrow windows in >> between *used* by archivers, reports,etc. >> >> I am worried that my cache gets trashed in the middle of a heavy >> activity if I leave the agent running. Are you saying that the minute >> those tables get populated I won't get the "update statistics" >> behaviour ? >> >> -Noel >> >> >> > >
On May 1, 3:41 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com>
[quoted text, click to view] wrote: > Like I mentioned before, the snapshot agent will only run update stats if it > couldn't find any *statistics* on the leading clustered index column but I > don't really know if the storage engine will actively *remove* statistics > that already exists if you delete all rows in a table (I will try that). You > can definitely try to minimize the impact that update statistics from the > snapshot agent may have by scheduling it to run no more often then > necessary. That said, I can't help but notice that with the way tables are > emptied and repopulated everyday, the statistics will basically get > out-dated on a daily basis which will in turn mislead the query optimizer. > Wouldn't plan guides be a better solution to ensure more stable\robust > (hence no-recompilation) plans for your critical queries? > > -Raymond > > <isyourfri...@gmail.com> wrote in message > > news:1178046998.278261.206030@n59g2000hsh.googlegroups.com... > > > On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > > wrote: > >> Just out of curiousity, do you routinely have empty tables in your > >> publisher > >> database that are heavily queried by your application? That is the case > >> where I can see how the snapshot agent update stats behavior will affect > >> you > >> (of course I may be wrong). > > >> -Raymond > > >> <isyourfri...@gmail.com> wrote in message > > >>news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... > > >> > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > >> > wrote: > >> >> Noel, the snapshot agent will only attempt to update the statistics on > >> >> the > >> >> clustered index (primary key) if the statistics on it has not been > >> >> populated > >> >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). This > >> >> can > >> >> happen if 1) the table is empty or 2) statistics has never been > >> >> populated > >> >> since the creation of the table. For case 1), updating statistics will > >> >> be > >> >> a > >> >> trivial operation, and for case 2), updating statistics generally > >> >> allows > >> >> the > >> >> query optimizer to choose better query plans as well as enables the > >> >> snapshot > >> >> agent to partition large tables more evenly for parallel processing. > >> >> You > >> >> can > >> >> disable this behavior by specifying the unofficial > >> >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent command > >> >> line. > > >> >> Hope that helps, > > >> >> -Raymond<isyourfri...@gmail.com> wrote in message > > >> >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... > > >> >> > On SQL Server 2005 SP2 we got this "new feature" which I am probaly > >> >> > aware of why you do need the update statistics but what I can't tell > >> >> > is WHY you run the update statistics if there is NOTHING to do? > >> >> > Thanks > >> >> > M$. > > >> >> > Does any one knows how or if it is possible, to turn that "feature" > >> >> > OFF? > > >> >> > -Noel > >> >> > Sr. DBA > > >> > Raymond, Thanks you very much for your prompt reply. > > >> > The reason that I am complaining is that it can potentially invalidate > >> > my queries plan cache if it is run on a schedule, causing unnecessary > >> > recompilations. I do understand that it helps with the parallel > >> > processing; I went through that (with you) before, thanks again. It is > >> > my opinion though that the check to determine that there is nothing to > >> > do should be done before updating statistics no matter how trivial it > >> > may be. --- And yes it *does* helps :-) > > >> > Regards, > > >> > -Noel > >> > Sr. DBA > > > Raymond, > > > There are tables that get "cleared" (moved to history) at the end of a > > daily session (for performance and other reasons we don't want to > > accumulate history there) and there are others configuration tables > > that are populated or cleared by agents adaptively. This process > > repeats at different timezones and 24x7 with very narrow windows in > > between *used* by archivers, reports,etc. > > > I am worried that my cache gets trashed in the middle of a heavy > > activity if I leave the agent running. Are you saying that the minute > > those tables get populated I won't get the "update statistics" > > behaviour ? > > > -Noel Thanks, Raymond. I believe that plan guides may help us in the long run. We are migrating still many servers and I was doing my due dilligence and testing to minimize the number of "surprises"; that's why I asked about this. Now that we are at it how does the "database snapshot" for initialization affects (in terms of performace ) the "main" database ?
A database snapshot (not to be confused with replication snapshot) generally
increases the number of I/Os per update of the originating database as well as the storage requirement for the duration of its existence up to what is required for an extra copy of the originating database when the database snapshot is created. It is basically a copy-on-write mechanism where the "original" data page is saved off "to the snapshot" when it is first modified after the snapshot is created. As such, you would want to make sure that: 1) database snapshots do not hang around for longer than necessary 2) database snapshots be ideally created at times when updates to the originating database are limited to a relatively small subset of data pages: frequent updates to the same subset of rows are fine, as are inserts that cluster around the same range of data pages (new pages are not "saved off" to the database snapshot) 3) the number of outstanding database snapshots be kept at a minimum as the processing\resource overhead for maintaining database snapshots scales roughly linearly with their number The same considerations apply if you are using sync_method = 'database snapshot' for generating replication snapshots as the replication snapshot agent will create a database snapshot under the cover for generating a consistent point-in-time set of replication snapshot data (or files). Notice that the throughput for reading from a database snapshot (think the snapshot agent bulk-copy out data) may be lower compared with reading from the originating database because of the extra processing required for traversing between saved data pages in the database snapshot and unmodified pages in the originating database . However, reading from a database snapshot imposes almost no locking contention (except perhaps short-duration internal latches) on the originating database. -Raymond [quoted text, click to view] <isyourfriend@gmail.com> wrote in message
news:1178049804.883848.318060@n59g2000hsh.googlegroups.com... > On May 1, 3:41 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> > wrote: >> Like I mentioned before, the snapshot agent will only run update stats if >> it >> couldn't find any *statistics* on the leading clustered index column but >> I >> don't really know if the storage engine will actively *remove* statistics >> that already exists if you delete all rows in a table (I will try that). >> You >> can definitely try to minimize the impact that update statistics from the >> snapshot agent may have by scheduling it to run no more often then >> necessary. That said, I can't help but notice that with the way tables >> are >> emptied and repopulated everyday, the statistics will basically get >> out-dated on a daily basis which will in turn mislead the query >> optimizer. >> Wouldn't plan guides be a better solution to ensure more stable\robust >> (hence no-recompilation) plans for your critical queries? >> >> -Raymond >> >> <isyourfri...@gmail.com> wrote in message >> >> news:1178046998.278261.206030@n59g2000hsh.googlegroups.com... >> >> > On May 1, 2:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> >> > wrote: >> >> Just out of curiousity, do you routinely have empty tables in your >> >> publisher >> >> database that are heavily queried by your application? That is the >> >> case >> >> where I can see how the snapshot agent update stats behavior will >> >> affect >> >> you >> >> (of course I may be wrong). >> >> >> -Raymond >> >> >> <isyourfri...@gmail.com> wrote in message >> >> >>news:1178042869.133617.293300@u30g2000hsc.googlegroups.com... >> >> >> > On May 1, 1:31 pm, "Raymond Mak [MSFT]" <r...@online.microsoft.com> >> >> > wrote: >> >> >> Noel, the snapshot agent will only attempt to update the statistics >> >> >> on >> >> >> the >> >> >> clustered index (primary key) if the statistics on it has not been >> >> >> populated >> >> >> (as indicated by a null date returned from DBCC SHOW_STATISTICS). >> >> >> This >> >> >> can >> >> >> happen if 1) the table is empty or 2) statistics has never been >> >> >> populated >> >> >> since the creation of the table. For case 1), updating statistics >> >> >> will >> >> >> be >> >> >> a >> >> >> trivial operation, and for case 2), updating statistics generally >> >> >> allows >> >> >> the >> >> >> query optimizer to choose better query plans as well as enables the >> >> >> snapshot >> >> >> agent to partition large tables more evenly for parallel >> >> >> processing. >> >> >> You >> >> >> can >> >> >> disable this behavior by specifying the unofficial >> >> >> "-EnableArticleBcpPartitioning 0" option on the snapshot agent >> >> >> command >> >> >> line. >> >> >> >> Hope that helps, >> >> >> >> -Raymond<isyourfri...@gmail.com> wrote in message >> >> >> >>news:1178036990.733708.277790@p77g2000hsh.googlegroups.com... >> >> >> >> > On SQL Server 2005 SP2 we got this "new feature" which I am >> >> >> > probaly >> >> >> > aware of why you do need the update statistics but what I can't >> >> >> > tell >> >> >> > is WHY you run the update statistics if there is NOTHING to do? >> >> >> > Thanks >> >> >> > M$. >> >> >> >> > Does any one knows how or if it is possible, to turn that >> >> >> > "feature" >> >> >> > OFF? >> >> >> >> > -Noel >> >> >> > Sr. DBA >> >> >> > Raymond, Thanks you very much for your prompt reply. >> >> >> > The reason that I am complaining is that it can potentially >> >> > invalidate >> >> > my queries plan cache if it is run on a schedule, causing >> >> > unnecessary >> >> > recompilations. I do understand that it helps with the parallel >> >> > processing; I went through that (with you) before, thanks again. It >> >> > is >> >> > my opinion though that the check to determine that there is nothing >> >> > to >> >> > do should be done before updating statistics no matter how trivial >> >> > it >> >> > may be. --- And yes it *does* helps :-) >> >> >> > Regards, >> >> >> > -Noel >> >> > Sr. DBA >> >> > Raymond, >> >> > There are tables that get "cleared" (moved to history) at the end of a >> > daily session (for performance and other reasons we don't want to >> > accumulate history there) and there are others configuration tables >> > that are populated or cleared by agents adaptively. This process >> > repeats at different timezones and 24x7 with very narrow windows in >> > between *used* by archivers, reports,etc. >> >> > I am worried that my cache gets trashed in the middle of a heavy >> > activity if I leave the agent running. Are you saying that the minute >> > those tables get populated I won't get the "update statistics" >> > behaviour ? >> >> > -Noel > > Thanks, Raymond. > > I believe that plan guides may help us in the long run. We are > migrating still many servers and I was doing my due dilligence and
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
| home · blog · groups | Questions? Comments? Contact the dn |