sequence for DB maintenance

• sqlguy80

  SSCrazy

  Points: 2765

  More actions

  November 15, 2023 at 2:54 pm

  #4328052

  We have a large Production DB , 24 x 7 , few large tables of  ~ 60 M records, called Transactions, transaction detail etc,

  Every now and then I see errors on dynatrace saying , slow inserts, failed inserts etc, and it points to the DB so I'm responsible.

  I inherited this and see Ola Hallegren scripts for IX rebuilds, followed by update stats, This runs ok , but the sampling is not enough , so I tried update stats tablename with Fullscan on live prod and it completed ok within 30 min for large tables . We have powerful  Azure Business critical Managed Instances, with 40 core CPU's, ssd's etc, so its ok ,

  Question- What is the sequence of events

  1. update stats with Full Scan
  2. Rebuild IX with online=on ? or  Rebuild IX's and run exec sp_updatestats ?

  When IX's are rebuilt, are Stats updated with small sampling???

  Thanks for responding!

• Johan Bijnens

  SSC Guru

  Points: 135377

  More actions

  November 15, 2023 at 3:04 pm

  #4328059

  Have a look at Erins blog: Category: Statistics  

  and Kimberlys blog about statistics

   

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• November 16, 2023 at 11:36 am

  This was removed by the editor as SPAM