Collection of full table stats is taking longer and consuming lot of CPU resources,and we are not able to complete the stats collection for all tables in the Prod system within the scheduled window.We plan to move some of the full table stats collection into sample stats collection.
Please let me know which type of tables qualify for sample stats collection.Is it okay to collect sample stats on unique indexes?what are the recommendations?
Collection of full table stats can be time-consuming :). If you can, maybe you can think of running them when less load on the system. It is all about planning,synergyzing etc maybe doing part by part if possible. If not then , of course , other alternatives.
I posted to Carrie about my questions too. Please find the link. It will answer most of your doubts.
The "official" recommendations by Carrie Ballinger can be found here:
The recommendations for sample stats are a bit cautious, just to be on the safe side :-)
I usually try do most stats for big tables using sample, in most cases it's ok. There are some specific situations where sample stats are really bad, that's why you should check full and sample stats if they're too different.
You didn't specify your TD release, in TD14 you can do even more sample stats because the sample size can be specified. If the 2% default is not provididing good stats you can easily increase it.