May 6, 2007 at 8:31 am
I used the Database Tuning Advisor on a certain query and it recommended the creation of two indexes and about 30 Statistics on a certain table.
Need 101 help. I thought that "statistics" applied only to indexes. I thought that "statistics" were actually a statistical distribution of the values in the first field of each index in a table. In short, I can't create a statistic iwithout an index on which to base it on.
Please clarify this for me.
TIA,
Barkingdog
May 6, 2007 at 11:05 pm
I think you are right statistics is created on index...
Take a look the following document which explains a lot about statistics and optimizer...
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
MohammedU
Microsoft SQL Server MVP
May 7, 2007 at 12:33 am
Thanks for the URl.
I found this presentation: http://support.microsoft.com/kb/325024
and it answered many of my questions.
It's about sql 2000 but it's a great presentasiton. For example, the presenter makes it clear that if a column is indexed it will automatically have statistics generated . (But stats are a point-in-time phenomena. They need to be updated after they are created.) He also discusses how statistics can be created on non-indexed fields (I didn't know that) and the Index tuning Wizrad.
Barkingdog
May 7, 2007 at 2:15 am
If you have an index on a column, you will always have statistics on that column as well. They will be updated automatically from time to time.
You can create statistics on a column that does not have an index. It means that SQL will know the aprox distribution of data in that column for the purposes of query optimisation. These stats will also be updated as necessary. (see CREATE STATISTICS on BoL)
SQL will automatically create statistics it needs if they don't already exist. You can see these entries in sysindexes, their names always start with _WA_Sys
The presence of automatically created stats can indicate that an index would be useful on that column, but not always.
One other thing. Take the index tuning advisor's suggestions with a pinch of salt. It often suggests far more than is actually necessary. I'd suggest you implement its suggestions one by one and watch for improvements in performance. I've seen several cases where it suggeests 5 or 6 new indexes or statistics. Implementing 1 or 2 gets a massive improvement in performance and the rest give only minor improvements.
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
May 8, 2007 at 6:26 am
I'll echo Gail's comments. You need to be aware of the _WA stats which can in extreme circumstances stop you building an index, in a tuning scenario I usually delete all the _WA stats as it can be useful to see which have been created during a query run. The other issue is if there are stats and you create an index the stats remain, so its something esle to update and maintain.
Agree that you need to be careful with DTA, it's not the "golden solution"
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 8, 2007 at 6:35 am
The other issue is if there are stats and you create an index the stats remain, so its something esle to update and maintain.
I didn't realise that, though it's obvious in retrospect.
Thanks Colin.
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
May 8, 2007 at 12:10 pm
awesome article, thanks
we have had a few problems with bad execution plans for no reason we could guess
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply