Post reply

Create Statistics. Why do we need it?

  • 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?

  • July 21, 2008 at 5:13 am

    #845674

    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

  • July 21, 2008 at 5:44 am

    #845692

    Create Statistics. Why do we need it?

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

    Cheers!

    Sandy.

    --

  • July 22, 2008 at 12:04 am

    #846158

    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

  • July 22, 2008 at 12:14 am

    #846166

    Gail,

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

    Cheers!

    Sandy.

    --

  • July 22, 2008 at 12:16 am

    #846169

    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

  • July 22, 2008 at 12:30 am

    #846174

    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.

    --

  • July 22, 2008 at 12:35 am

    #846175

    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

  • July 22, 2008 at 12:44 am

    #846180

    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.

    --

  • July 22, 2008 at 12:47 am

    #846182

    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

  • July 22, 2008 at 12:55 am

    #846186

    gail,

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

    Cheers!

    Sandy.

    --

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

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