Reason for Column statistics

Question

Reason for Column statistics

Yuri55

SSCrazy Eights

Points: 8449
More actions
October 12, 2013 at 10:21 am

#278850

Just curious- how column statistics that is not part of any index is used?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Yuri55 (10/12/2013)
Just curious- how column statistics that is not part of any index is used?
Thanks

It can help the optimizer figure out whether or not to actually use an index, for one. It can also help determine what the join type and other things should be in the execution plan. Think of statistics as an adivsor to the optimizer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Same way stats that are part of an index are used - to give the optimiser an idea of the distribution of the data uniqueness and an estimate of how many rows are likely to be affected by query operators.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 3

Sorry, but still not sure- what difference does it (statistics on column not in any index) make? With index stats make sense- optimizer decides on behavior relying on stats. But with columns not in index optimizer has only one way to go- table scan- does it (stats) matter in that case? I mean not sure what am I missing there? Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Yes, stats absolutely matter, and a table scan is not the only option SQL has. It might be able to scan another nonclustered index that has all or some of the columns in the where clause and do key lookups and filters for the others. To decide that, it needs to know approximately how many rows will be returned to see if a nonclustered index scan with key lookups is better than a full table scan. It needs to figure out what the best join type is and how much memory to request to perform that join, plus a whole lot more.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 5

I hope I got you, Gail- optimizer will use this info (stats) to find probably better way to go rather that simple table scan (as last resort).

Thanks Gail/Jeff for your responses

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

And to determine the best join types and order, aggregation, sorts (if necessary), whether or not to use work tables, how much memory to request, etc, etc

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass