0

I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s

On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the sizes on avg 2-3 TB's

Now performing index maintenance is causing a lot of problems during the weekend window.

  1. We are not doing ant index rebuilds/re-organize as of now

  2. Update stats is done for those DB's every weekend once

For tables with over 10 billions of rows, we had update stats on indexes with sample of 20% and 30%. As per my knowledge, stats would not be going parallel operation unless its full scan or specified MAXDOP option which is available on higher versions of what i am currently on.

So the problem is update stats with that sample rate runs and almost increasing its run times on avg. 9-10 hours and that causes issues with other processes running due to window exceeding as assigned to DBA work for maintenance .

However if we just run update stats on index without any sample they will just complete in 10-20 mins. I see the rows sampled would be less here and can have some impact on performance.

So what are my best options as of now if i run without sample? Would that worse the performance of servers? Is there a better way of achieving this?

All databases have auto update stats ON [SYNC]

Thanks

  • SQL Server does a pretty good job at estimating sampling rates for any given data set - have you SEEN any performance issues or are you just worried about them? – George.Palacios Nov 21 '18 at 16:35
  • @George.Palacios: I calculated what optimizer used when i ran update stats without sample using rows sampled/no of rows*1oo which came to aprox 0.0087 and that no was way less than 20 or 30. So wandering letting optimizer decide this would impact for such large tables? – BeginnerDBA Nov 21 '18 at 16:42
  • Unless you have any queries running slowly due to bad estimates coming from statistics, there's no issue. Work on the evidence you have - are there currently any queries performing badly? – George.Palacios Nov 22 '18 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.