locked
Update Stats on Principal server will use resources on Mirror too RRS feed

  • Question

  • Here i have 1 query Please reply sir

    If I execute Update statistics with 100 percent on principal server which consumes 100 GB tempdb, will it use 100 GB of tempdb on Mirror too
    Or

    it will ONLY update the endresult of update statistics means it will not use tempdb on mirror as histogram already updated.

    Manish

    Friday, January 1, 2016 6:00 PM

Answers

  • As per my understanding, Update stats operation will generate the transaction log records and these log records will be placed to the send queue. after that these log records will be hardening to the Mirror database log file in Redo queue. where redo phase will take place and same operation will be performed on mirror database. So,

    If I execute Update statistics with 100 percent on principal server which consumes 100 GB tempdb, will it use 100 GB of tempdb on Mirror too

    Not 100% sure about the same tempdb size (100 GB) but yes, redo operation will use Tempdb.

    it will ONLY update the endresult of update statistics means it will not use tempdb on mirror as histogram already updated.

    it should use tempdb on mirror.

    https://technet.microsoft.com/library/Cc917681

    Monday, January 4, 2016 4:36 AM

All replies

  • tempdb is not mirrored.

    The statistics update on the Primary ultimately creates SQL transaction statements to update statistics tables.

    These SQL transactions are written to the log, like all transactions, and are subsequently applied to the Mirror. So, the use of tempdb at the Mirror will not be as it was at the Primary.

    Your second statement is correct: "the endresult of update statistics means it will not use tempdb on mirror as histogram already updated."


    • Proposed as answer by PrinceLucifer Saturday, January 2, 2016 4:23 PM
    Saturday, January 2, 2016 11:16 AM
  • Thanks for the reply.

    Yes I know tempdb is not mirrored.

    Here I hope my question is easy to grasp.

    Update stats with 100% sample size is not sent to mirror as update stats with 100% sample size.

    But sent as Histogram entry to system table as it is send command.

    because Update stats is actually select statman ....orderby and then insert into system table.

    select command is not sent only insert into system table.

    Thanks for anyone to reply if wrong


    Manish

    Saturday, January 2, 2016 4:29 PM
  • As per my understanding, Update stats operation will generate the transaction log records and these log records will be placed to the send queue. after that these log records will be hardening to the Mirror database log file in Redo queue. where redo phase will take place and same operation will be performed on mirror database. So,

    If I execute Update statistics with 100 percent on principal server which consumes 100 GB tempdb, will it use 100 GB of tempdb on Mirror too

    Not 100% sure about the same tempdb size (100 GB) but yes, redo operation will use Tempdb.

    it will ONLY update the endresult of update statistics means it will not use tempdb on mirror as histogram already updated.

    it should use tempdb on mirror.

    https://technet.microsoft.com/library/Cc917681

    Monday, January 4, 2016 4:36 AM