May 18, 2006 at 3:10 pm
Does anyone have some guidelines for creating and maintaining statistics in a data warehouse environment? We have auto create statistics set to on. The result is statistics on nearly every column in the database. Is it worth it to maintain these statistics on every column? The tables generally have very few indexes and a substantial number of queries use full table scans. What would be the downside in turning off auto create statistics and dropping statistics on rarely used columns or columns that have no indexes?
May 19, 2006 at 9:34 am
In general terms the optimiser creates stats on columns that it feels are useful - I often view this as showing poor indexing. You could try dropping the system stats and see which ones the optimiser puts back.
Generally in a DW stats should be updated ( using sp_updatestats or update statistics ) after the data load to enable reports to run best. You might also need to update stats selectively during data loads especially if part of your load process includes deletes.
It sounds to me that your indexing may need sorting out. You might want to disable autostats during load as it can cause blocking and slow down - however if your indexing is bad then this might be a very bad idea!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply