October 31, 2006 at 2:37 pm
While analyzing a slow query, the Database Tuning Advisor suggested I run CREATE STATISTICS on a multitude of columns. The database is set to automatically update statistics, so I'm wondering about the Pros and Cons of creating these additional statistics for specific columns. Is there much additional overhead as there is with additional indexes ?
Example: CREATE STATISTICS [_dta_stat_263281039_5_3_13_12] ON [dbo].[PROORD_M]([CTM_NBR], [ORD_CTG], [ORD_TYPE], [SHP_DTE])
TIA
November 1, 2006 at 2:13 am
I'm not a great lover of stats vs indexes. I'd look at the query plan myself. You do have the option of profiling your query, add the stats, then profiling it again to see if it made any difference.
Overhead is all about performance, yup every index/stat technically introduces overhead, but, if you didn't have any indexes or stats on your database ( to avoid degredation ) how would it run?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 1, 2006 at 9:10 am
stats are pretty fundamentally vital. They tell the optimiser what the distribution of data for a given column is. This allows it to decide more effectively what indexes and join strategies to employ.
The concept of it keeping stats up to date is great, but it seems to have a pretty alternative concept of uptodate...!
I usually bung in a script to recalculate relevant stats every few days/after big data loads.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply