SQL Server Performance

Rowcount is not correct

Discussion in 'General DBA Questions' started by ramkumar.mu, Sep 12, 2006.

Page 1 of 2

  1. ramkumar.mu New Member

    I Have a table SalesHistory in one of my development server which shows 96 rows when viewed via EM and sysindexes. but actually there are no rows in that.
    There is no open transactions, I ran DBCC UPDATEUSAGE(0), UPDATE STATISTICS SalesHistory, restarted the server only to see the same result.

    what could be the problem???

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 12, 2006
    #2

  2. satya Moderator

    What is the service pack on SQL server?
    Run select count(*) from QA to see the results.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Sep 12, 2006
    #2

  3. ramkumar.mu New Member

    Select count(*) shows me 0 rows.

    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 12, 2006
    #2

  4. ramkumar.mu New Member

    Additional information...

    two of my other development servers are of same configuration and service level. but i didnt face problems with those...

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 12, 2006
    #2

  5. Roji. P. Thomas New Member

    Have you tried prefixing the table with owner name? It could be just that there are two tables with the same name, but different owners.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  6. ramkumar.mu New Member

    I checked. There is no other owner other than dbo on that machine. that was a nice try. i didnt think that way initially...

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  7. dineshasanka Moderator

    did you try with select(filedName)

    ----------------------------------------

    dineshasanka, Sep 13, 2006
    #2

  8. Roji. P. Thomas New Member

    quote:Originally posted by ramkumar.mu

    I checked. There is no other owner other than dbo on that machine. that was a nice try. i didnt think that way initially...
    What you see when you open the table in EM?

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  9. ramkumar.mu New Member

    When i right click on table and if i see the properties, there it shows "owner dbo Rows 96" . but when i went through the option "open Table->Return all rows", i dont see any value.


    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by ramkumar.mu

    I checked. There is no other owner other than dbo on that machine. that was a nice try. i didnt think that way initially...
    What you see when you open the table in EM?

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  10. ramkumar.mu New Member

    I cant understand that. can you please post the full query?



    quote:Originally posted by dineshasanka

    did you try with select(filedName)

    ----------------------------------------



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  11. satya Moderator

    As this is a development server, do you think really it is a problem?
    Check whether similar behaviour occurring on Production servers.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Sep 13, 2006
    #2

  12. Roji. P. Thomas New Member

    quote:Originally posted by ramkumar.mu

    When i right click on table and if i see the properties, there it shows "owner dbo Rows 96" . but when i went through the option "open Table->Return all rows", i dont see any value.

    The Table properties window in EM calls the procedure sp_MStablespace, which in turn refer the rowcnt column in sysindexes to get the row count. So the original problem here is that the sysindexes is not getting properly updated.

    Try updating statistics again with

    UPDATE STATISTICS [TableName] WITH FULLSCAN


    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  13. satya Moderator

    Or even run DBCC UPDATEUSAGE(usertable)
    Oops, a typo [:I]

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Sep 13, 2006
    #2

  14. Roji. P. Thomas New Member

    Roji. P. Thomas, Sep 13, 2006
    #2

  15. ramkumar.mu New Member

    I tried your query. it didnt work... [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ramkumar.mu</i><br /><br />When i right click on table and if i see the properties, there it shows "owner dbo Rows 96" . but when i went through the option "open Table-&gt;Return all rows", i dont see any value.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />The Table properties window in EM calls the procedure sp_MStablespace, which in turn refer the rowcnt column in sysindexes to get the row count. So the original problem here is that the sysindexes is not getting properly updated.<br /><br />Try updating statistics again with <br /><br />UPDATE STATISTICS [TableName] WITH FULLSCAN<br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  16. ramkumar.mu New Member

    Just in case if i get the same problem in any other server...


    quote:Originally posted by satya

    As this is a development server, do you think really it is a problem?
    Check whether similar behaviour occurring on Production servers.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  17. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ramkumar.mu</i><br /><br />I tried your query. it didnt work... [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />OK. Try<br /><br />EXEC sp_statistics [@table_name =] 'SalesHistory', @accuracy = 'E'<br /><br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
    Roji. P. Thomas, Sep 13, 2006
    #2

  18. ramkumar.mu New Member

    I tried DBCC UPDATEUSAGE(0) and UPDATEUSAGE(TableName). it didnt work...

    quote:Originally posted by satya

    Or even run DBCC UPDATEUSAGE(usertable)
    Oops, a typo [:I]

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  19. Roji. P. Thomas New Member

    If the last method I mentioned also failed to correct the issue, try the following.

    DBCC UPDATEUSAGE (0, 'SalesHistory') WITH COUNT_ROWS



    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  20. ramkumar.mu New Member

    DDS_Staging_1dboSalesHistoryNULLNULLNULL0NULLNULLNULL9693NULL<br />96 is the cardinality and 93 is pages<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ramkumar.mu</i><br /><br />I tried your query. it didnt work... [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />OK. Try<br /><br />EXEC sp_statistics [@table_name =] 'SalesHistory', @accuracy = 'E'<br /><br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  21. ramkumar.mu New Member

    Wow!!! that worked.

    It showed a message like "rowcount changed from (96) to (0). "
    can you please explain what we did?



    quote:Originally posted by Roji. P. Thomas

    If the last method I mentioned also failed to correct the issue, try the following.

    DBCC UPDATEUSAGE (0, 'SalesHistory') WITH COUNT_ROWS



    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  22. Roji. P. Thomas New Member

    Roji. P. Thomas, Sep 13, 2006
    #2

  23. ramkumar.mu New Member

    No! i dont have any index on that table.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  24. Roji. P. Thomas New Member

    quote:Originally posted by ramkumar.mu

    Wow!!! that worked.

    It showed a message like "rowcount changed from (96) to (0). "
    can you please explain what we did?

    Well, we just asked SQL server to go count each rows and update the entry in sysindexes.




    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  25. ramkumar.mu New Member

    OK.. then why didnt it work with

    DBCC UPDATEUSAGE(0)
    DBCC UPDATEUSAGE('SalesHistory')


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2

  26. Roji. P. Thomas New Member

    quote:Originally posted by ramkumar.mu

    OK.. then why didnt it work with

    DBCC UPDATEUSAGE(0)
    DBCC UPDATEUSAGE('SalesHistory')

    I dont know the reason. UPDATEUSAGE itself is supposed to correct the inaccuracies in sysindexes. But, with the option WITH COUNT_ROWS , we are explicitly asking for updating the rowcnt (and rows) column with teh current count from teh table.

    But I can tell you that, your chances of facing this problem is far less with a clustered index instead of a heap.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 13, 2006
    #2

  27. ramkumar.mu New Member

    Thanks a lot Roji for all your help.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 13, 2006
    #2
Page 1 of 2

Share This Page