May 13, 2009 at 9:47 am
I'm researching a problem and realized that I still don't fully understand statistics. I have several basic questions:
1. How can I determine if I am missing statistics (working with the assumption that auto_create_stats on and and auto_update_stats on)
2. Does Sql Server create multi-column statistics?
Also, I'm working with MSSQL 2005.
Some background, I was working on a perf problem, so I started a trace on my long running queries. I then ran it against the Database Engine Tuning Advisor. It came back with recomendations to build a bunch of multi-column stats. This confused me as I thought that SQL Server would have created them automatically. A co-worker mentioned that SQL Server only creates single column Stats automatically, but I can't find any documentation to either deny or support the claim.
May 13, 2009 at 11:59 am
The way I usually tell if I'm missing statistics is if I see that in the execution plan. There are probably better ways to do that, but that's what I usually use.
I just did some tests with a fresh table and multi-column Where clauses on selects without indexes. In each case, SQL Server created a statistic for each column in the clause, but did not create any multi-column stats.
When I created a multi-column index on the table, it created a multi-column stat on it too.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2009 at 12:46 pm
excuse the links but I wrote a serie sof posts which includes lots of info about stats
http://sqlblogcasts.com/blogs/grumpyolddba/archive/tags/Indexes/default.aspx
you need the analysing indexes posts.
Auto stats get created all the time - in fact they can be a real pain at time - auto update of stats is another matter, as tables get larger, let's say millions of rows, the chances of an auto update happening reduces. You can check the proc cache for auto updates - the query plan conatains the statement statman if I remmber correctly.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2009 at 10:37 am
Yes Thanks for all the help and responses. I guess my next question would be why did the DTA recommend all of these multi-column stats, and what all implications do I have to consider before I implement them? I understand there will be an added cost of maintentance (auto_update_stats, along with any manual updates), anything else?
Again thanks,
Sean
PS Grumpy, your BLOG is like DBA Crack, thanks for making me an addict 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply