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.
We are not doing ant index rebuilds/re-organize as of now
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]