Devising a comprehensive process for "Collect Stats" on the entire EDW
I am struck in a wonderful situation.
Suppose that I have my TD environment consisting of 720 tables. Of these tables, I have 420 tables that are populated on a periodic basis. I want to determine on the tables that I should collect statistics.
I am aware of the rules: 10% change, last stats collected date methods. This is some thing that is run on each table manually. I don't want to do manually as it is highly time-consuming.
What I am exactly looking is...some thing like a process that will run and give me a list of tables upon which I should collect stats.
Friends, I know I am asking for some thing that might not be a one-shot method. Please, suggest me the Key points to remember while doing for a big EDW. I will figure out.
Re: Devising a comprehensive process for "Collect Stats" on the entire EDW
There is one suggestion, I hope it helps u out..... It goes like this...
1. Create a Table which captures daily the count, size for all the necessary tables 2. Then u can have the difference in percentage when u compare the difference of 2 days data. 3. you can create a collect stats script with the help of dbc involving this table 4. Schedule ur script for daily run