January 11, 2007 at 1:49 pm
I've looked at a few other posts on this site about Stats vs indexes while doign some performance tuning. I'm working with a Query and the Index Tuning Wizard came up with a bunch of statistics to add. My question is this, If the Wizard thinks I should use multi column stats why not just go ahead and use indexes?
I understand there would be some performance issues with indexes during Update, delete, Insert statments, but wouldn't those same statements render the statistics less than accurate until an autoupdate is performed?
I guess the main question is what do stats get me that indexes don't performance wise? And/OR what indexes get me that stats don't(besides an index of the actual column contents).
Thanks in advance.
-Luke.
January 11, 2007 at 2:46 pm
Stats just give the distribution of data in the column, helping the optimiser make decisions on how to run the query.
They do get out of date, but unless you're doing lots and lots of changes, it won't be much and the auto update ususlly fixes those. Autoupdate is generally very quick, as it only samples tables, and it won't run on every change
I don't have much faith in the index tuning wizard myself, so I don't know why it suggests what it does.
Indexes always have stats associated with them, but only on the first column of the index, so it may be that in this case, the stats on the firt column alone aren't enough for the optimiser to make a good decision.
Try running the query with and without the suggested stats, see if you get a different plan, see if you get a change in speed and then decide whether or not to implement the stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2007 at 4:54 am
I'd echo that, the index tuning wiz in sql2k is rubbish. Try using the advisor in 2005. I've seen some really bad decisions made with output from the tuning wizard.
Kalen Delaney and Kimberley Tripp have some good books/blogs/white papers on all this, or if they're running a presentation or course near you get yourself onto it to start to understand this subject area.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 12, 2007 at 5:10 am
Hi,
I have found this article and would like to share with you all.
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
January 14, 2007 at 6:29 am
Kalen Delaney and Kimberley Tripp have some good books/blogs/white papers on all this, or if they're running a presentation or course near you get yourself onto it to start to understand this subject area.
Seconded. They're both excellent speakers/presenters and very knowledgable.
See Kimberly's scripts and blog at http://www.sqlskills.com/
Kalen's blog is at http://sqlblog.com/blogs/kalen_delaney/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply