SQL Server Performance

DBCC SHOW_STATISTICS

Discussion in 'SQL Server 2005 General Developer Questions' started by ashish.johri2007, Apr 26, 2007.

  1. ashish.johri2007 New Member

    DBCC SHOW_STATISTICS(Table.Index_Name) gives statistics. This is very different in these two cases as below.

    If the index is made and then the records are inserted in the table.
    If records are inserted and then the index is made.
    The statistics are always NULL in case 1 while in case 2 this gives comfortable analysis. Why this happens? Does case 1 lead to the conclusion that index if made before the insertion of records is not worth? I was told that if we insert the records after the creation of index this insertion is quick but why the statistics are null always?


    Ashish Johri
  2. satya Moderator

    Explain the case of 1 and 2 about indexes & queries.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. ashish.johri2007 New Member

    1. Table sales is an empty table and has 100 crore records. Index on column sales_id is made before insertion of records.
    2. Same table same no of records but first the records are inserted and then the index is made on sales_id.

    DBCC SHOW_STATISTICS (Sales.Sales_ID_Index) in both the cases will give different results.

    Ashish Johri
  4. Luis Martin Moderator

    You did that in both tables and after you run DBCC, whitout any user user of these tables?.

    If you have autostatistics on, then the statistics are created when the table is used, not before.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. MohammedU New Member

    If you create the index, statistics is updated with FULL SCAN sample option other wise it will use default value (10% or 20%)...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page