Search code examples
sqlsybasesap-aserowcount

Configuring the time lag between inserting/deleting a row in a table and updating the systabstats.rowcnt value in Sybase

I found that there is a time lag of close to a minute between inserting/deleting a row in a table in sybase and updating the systabstats.rowcnt value for that table. I need to get the current row count at a certain moment and I can't fire "update statistics" or anything else before fetching the row count. Is there any way of reducing/configuring the time lag and not use count(*) The query I am using is

SELECT sysobjects.name, systabstats.rowcnt, sysusers.name as username, systabstats.statmoddate FROM sysobjects JOIN systabstats ON (sysobjects.id = systabstats.id) join sysusers on (sysobjects.uid = sysusers.uid)

Solution

  • The Housekeeper (or HK WASH) process is what writes the dirty pages and updates systabstats. You can play around with the server settings for those processes to get the updates to run more frequently.

    Alternatively you can force the system to flush the statistics from cache to systabstats using sp_flushstats

    sp_flushstats [TABLENAME]

    If you do not specify a table, it will flush statistics for all tables in the current database.