Skip to Content

Row count discrepancy for system table sysprocedures

Recently upgraded to ASE 16 SP02PL05

I noticed row counts in sysprocedures system table in a user database.

Numbers are drastically different !

(sp_flushstats sysprocedures did not make any difference)

I used two ways to count the rows

- count (*) and

- row_count function

select @@servername, db_name(), getdate()

select row_count(db_id(), id) from sysobjects where name = 'sysprocedures'

select count(*) from sysprocedures

select @@servername, db_name(), getdate()

go

And results were :

1> select @@servername, db_name(), getdate()

2> select row_count(db_id(), id) from sysobjects where name = 'sysprocedures'

3> select count(*) from sysprocedures

4> select @@servername, db_name(), getdate()

5> go

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

PHI3_PRD PRISMDB1 Jul 11 2017 12:28PM

(1 row affected)

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

176486

(1 row affected)

-----------

1615717

(1 row affected)

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

PHI3_PRD PRISMDB1 Jul 11 2017 12:28PM

(1 row affected)

1>

Comments please.

Avinash

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jul 12, 2017 at 02:27 AM

    Run "update statistics sysprocedures" and the value from row_count should be updated and match count(*).

    -bret

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Mark.

      As always very valuable feedback from you.

      Our database is big, and very active. It is used for custom APP. syscolumns count may be around 100K or so.

      Will follow your advice to run update index statistics on system tables once in a while.

      Specific to our site, we use eRSSD and most of the rs_* system tables are cached.

      SRS performance has been good.

      Your feedback on SRS is very useful for other sites using RSSD on ASE.

      Thanks again.

      Avinash