Statistics
-
I'm just trying to gause what others are doing with regards to their data warehouse when it comes to statistics.
Do you use automatically created statistics or do you administrate them manually?
How often do you run a job to update the statistics?
Do you run index defrag jobs and if so how often?
Our Datawarehouse (SQL 2005) is built everynight which leaves pretty much no room for a maintenance window.
Hope this helps,
Rich[p]
[/p] -
RichardDouglas (9/23/2010)
I'm just trying to gause what others are doing with regards to their data warehouse when it comes to statistics.Do you use automatically created statistics or do you administrate them manually?
It depends on object size and performance concerns. Having said that if we are not talking about a high-end system I'll let SS maintain stats automatically.
RichardDouglas (9/23/2010)
How often do you run a job to update the statistics?If doing manually it does not makes sense to gather fresh stats if either data volume or data distribution has not changed in at least 20% - rule of thumbs.
For a particular database that was setup this way we do have a job that runs weekly looking for objects that meet the above defined criteria, when such an object is identified the job dynamically creates a job that gather stats as needed.
RichardDouglas (9/23/2010)
Do you run index defrag jobs and if so how often?Most indexes have a comfort size meaning that it doesn't matter how often you reorganize them they come back to the comfort size pretty quickly. In general reorganazing those indexes is a waste of cpu cycles and i/o.
There are exceptions but that depends of each particular system.
RichardDouglas (9/23/2010)
Our Datawarehouse (SQL 2005) is built everynight which leaves pretty much no room for a maintenance window.Shall we understand the whole data warehouse is rebuilt in a daily basis or shall we understand there is a daily ETL process adding data to the existing data warehouse?
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Hi sorry for the delay and thank you for your reply.
I'm quite removed from the day to day running of the DWH as things stand but I want to bring more of it into my remit.
I believe that the majority of the data is appended with a miority of the data being reloaded each day.
Hope this helps,
Rich[p]
[/p]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply