locked
SQL Server always-on maintenance plans RRS feed

  • Question

  • Hello, I have question about Statistic Updates and Index Rebuild and Reorganization.
    We have deployment of 4 SQL Servers with AG, and if on one of replicas we run:
    Index defragmentation
    Update statatistic
    All changes will replicate on all SQL Server in AG or we should run this on each of servers.
    Friday, September 9, 2016 1:49 PM

Answers

  • Friday, September 9, 2016 2:06 PM

  • Index defragmentation
    Update statatistic
    All changes will replicate on all SQL Server in AG or we should run this on each of servers.

    The changes done by index defragmentation and reorganize would easily be sent over to secondary replica and necessary changes would be done but for statistics it is bit different because the secondary replicas are basically read only. I would suggest you to read  This and This  blogs and I would quote from the blog

    The challenge for readable secondary is that that statistical information cannot be created or updated as this will amount to modifying the data on the secondary replica in the context of a read-only access. One common question that comes up in the context of readable secondary is ‘if the statistics created on the primary replica are automatically available on the secondary replica, why do I care about creating/updating statistics on the secondary?’  While it is true that any statistics that is created on the primary replica is automatically available on the secondary replica however the queries that you will run on the secondary replica are, in all likelihood, very different than the ones you run on the primary replica. For this reason, the statistics may either be missing or possibly stale when a query is run on the secondary replica. Interestingly, this issue is not limited to readable secondary.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, September 9, 2016 3:20 PM

All replies

  • Friday, September 9, 2016 2:06 PM
  • Replicas in AG topology are read only. AG is master , secondary concept and the primary server ships the t-log and it will apply in the secondaries. Since the secondaries are read only, I do not believe you can do stats updates in any secondaries. However whatever the changes you do in the primary will replicate to all secondaries including index and stats changes. 
    Friday, September 9, 2016 3:11 PM

  • Index defragmentation
    Update statatistic
    All changes will replicate on all SQL Server in AG or we should run this on each of servers.

    The changes done by index defragmentation and reorganize would easily be sent over to secondary replica and necessary changes would be done but for statistics it is bit different because the secondary replicas are basically read only. I would suggest you to read  This and This  blogs and I would quote from the blog

    The challenge for readable secondary is that that statistical information cannot be created or updated as this will amount to modifying the data on the secondary replica in the context of a read-only access. One common question that comes up in the context of readable secondary is ‘if the statistics created on the primary replica are automatically available on the secondary replica, why do I care about creating/updating statistics on the secondary?’  While it is true that any statistics that is created on the primary replica is automatically available on the secondary replica however the queries that you will run on the secondary replica are, in all likelihood, very different than the ones you run on the primary replica. For this reason, the statistics may either be missing or possibly stale when a query is run on the secondary replica. Interestingly, this issue is not limited to readable secondary.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, September 9, 2016 3:20 PM