Create Statistics. Why do we need it?

Question

Create Statistics. Why do we need it?

Paresh Randeria

Ten Centuries

Points: 1150
More actions
July 21, 2008 at 5:01 am

#381804

When we create index, SQL server create statistics on first column and this statistics is used to optimize query by selecting proper index.
I surely see advantage of creating statitics for columns other than first column in composite indexes because SQL server keeps statistics for the first column only.
I have read many articles but I still don't understand why do we need to create statistics other than automatically statistics created due to Index Creation and on Composite indexes.
Please let me know the advantage of creating statistics on Non Indexed columns?
Any other advantage of Statistics?

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

There usually isn't an advantage. SQL will automatically create single-column statistics on columns it thinks that is useful.

While there can be a use in creating multi-column statistics so that the optimiser knows the distribution of data, I would rather, in most cases, create an index.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 2

Create Statistics. Why do we need it?

to update database object...in system tables....in simple..

Cheers!

Sandy.

--

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

Sandy (7/21/2008)
to update database object...in system tables....in simple..

???

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Gail,

Can you tell me, what you wants to say in above post??

Cheers!

Sandy.

--

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

I'm trying to figure out what you were trying to say there.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

gail,

I was trying to say

update statistics provide query optimizer with the best possible information for generating query plans. statistics are updated when their are created or modified. and also be can done by admin time intervals basis

I have a small doubt here gail, let say I have a table which have 3 cols like id, fname and lname...and I have Primary key on my Id column and no more indexes on fname and lname. Some times I am using fname and lname for searching in my query.

if I will create one statistics on fname and lname column without index, how does it effect on my query plan??

Cheers!

Sandy.

--

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

Probably not much. You'll still get a clustered index scan (cause there's no other way to get those rows), the optimiser will just know more accuratly how many rows will be returned from the table scan. It may help it pick a more optimal join later (if necessary)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

gail,

just few days back, I come across one query where i noticed the query is going for index scan when there is more result and same query also performing a index seek when its returning no more result..

if you believe it or not, I just changed the * to 'x' in select statements, its changed from index scan to index seek, i got surprised, why query plan is behaving like this..

can you give me some idea how we will get the best optimizer or query plan when we are writing the query? I mean to say what are the thing we need to consider when we are going for a query?

although I know the concept and internal behavior of index seek and index scan but really I wants to know how Index scan and Index seek behaving inside query plan, are they change if my output changes??

Cheers!

Sandy.

--

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

Sandy (7/22/2008)
if you believe it or not, I just changed the * to 'x' in select statements, its changed from index scan to index seek, i got surprised, why query plan is behaving like this..

By removing columns the index it used was covering. It probably scanned cause the bookmark lookups were too expensive. Removing extra columns removed the need to do the lookup, so you get a seek.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 10

gail,

thats what i am trying to ask you, how to find out all these..

Cheers!

Sandy.

--