Table Scan with Statistics !!!!!

Question

Table Scan with Statistics !!!!!

Bhuvnesh

SSC Guru

Points: 59351
More actions
March 11, 2010 at 8:06 am

#216479

Hi ,
Let say we have a heap table contains 10k records.
as we know that according to sql server,
Statistics are automatically generated by sql server whenever queries executed on any table first time.
1) So during a table scan, Does sql server use those statistics ?
2) Do sql server creates any relationship between table scan and statistics ?
3) During a query execution ,sql server finds data in (starting) initials data pages.Will it still traverse remaining data pages?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Bhuvnesh (3/11/2010)
Statistics are automatically generated by sql server whenever queries executed on any table first time.

Statistics are generated when the query optimiser needs to know cardinality of a column in a table.

1) So during a table scan, Does sql server use those statistics ?

Running a SELECT * FROM <table> does not generate any statistics (on a heap table, no indexes, no statistics), so apparently not.

2) Do sql server creates any relationship between table scan and statistics ?

Don't understand the question.

3) During a query execution ,sql server finds data in (starting) initials data pages.Will it still traverse remaining data pages?

Depends. If it thinks that needs the data on those remaining data pages, yes.

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

Paul White SSC Guru Points: 150468 More actions · Answer 2

Bhuvnesh,

This is my favourite official paper on statistics:

Statistics Used By the Query Optimizer

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

thanks i will read it this weekend

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150468 More actions · Answer 4

Good man. It's pretty much the definitive work. Don't worry if you don't get all the detail first time around, it's as much a reference as it is a volume to be committed to memory.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi