Row count discrepancy for system table sysprocedures

[former_member89972]

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

[former_member89972]

Cool. Thanks Steffi.

Still discovering the nukes and corners of new SCN !!

Avinash

[former_member188958 · Accepted solution]

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

-bret

[former_member89972 · Accepted solution]

Thanks Bret.

Update stats did the trick.

Counts do not match exactly but are close enough.

Any implications on performance for not running update stats on system tables ?

Thanks again

Avinash

[former_member89972 · Accepted solution]

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