Post reply

dbcc dbcc show_Statistics question

  • January 8, 2015 at 2:33 pm

    #293623

    dbcc show_Statistics('tb1',pk_tbl1)

    Why is the result empty?

    pk_tb1NULLNULLNULLNULLNULLNULLNULLNULLNULL

  • January 8, 2015 at 3:25 pm

    #1768918

    Run this:

    select * from sys.stats

    where object_id = object_id('tbl1')

    If it is still empty, than you don't have any indexes and never ran any select query that has where or order by clause, or all statistics for a table was dropped by DROP STATISTICS.

  • January 8, 2015 at 4:30 pm

    #1768925

    The index is empty, i.e., there are no rows in that index.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 9, 2015 at 1:31 am

    #1768974

    Table is empty. No rows in the table means that the statistics objects are empty.

    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

  • January 9, 2015 at 7:11 am

    #1769072

    But after I run the following now I get the results.

    UPDATE STATISTICS tbl1 WITH FULLSCAN

  • January 9, 2015 at 7:22 am

    #1769075

    Then statistics had not been updated since data had been added to the table.

    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

  • January 9, 2015 at 8:23 am

    #1769106

    Thank you!!

  • January 9, 2015 at 9:09 am

    #1769121

    GilaMonster (1/9/2015)

    Table is empty. No rows in the table means that the statistics objects are empty.

    Not necessarily true -- a given index can be empty even if the table is not.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 12, 2015 at 9:33 am

    #1769551

    This is what I found out

    If an index is created and rows are inserted after the creation of the index the result of dbcc show_statisitics will be empty.

    dbcc show_Statistics('tbl1_test',pk_num1)

    If the rows are added and then the index is created after the insertion of the rows dbcc show_statisitics returns results.

    dbcc show_Statistics('tbl2_test',pk_num2)

Viewing 9 posts - 1 through 8 (of 8 total)

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