Skip to Content
0

Row count discrepancy for system table sysprocedures

Jul 11, 2017 at 05:40 PM

69

avatar image

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

10 |10000 characters needed characters left characters exceeded

Cool. Thanks Steffi.

Still discovering the nukes and corners of new SCN !!

Avinash

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Bret Halford
Jul 12, 2017 at 02:27 AM
0

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

-bret

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

I recommend running update index statistics on all (physical) system tables in all databases.

For most databases we're talking about relatively small tables so update index statistics takes very little time/resources to complete; any changes in performance of associated queries will likely be small/insignificant/unnoticeable.

For larger databases (eg, SAP applications with 60K+ tables, 10K+ views (70K+ entries in sysobjects/syspartitions/sysindexes) => upwards of 1+ million syscolumns entries), having updated stats on the system tables can mean the difference of some system procs running in realistic times vs not returning for quite awhile.

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

On a somewhat related note ...

I also highly recommend running update index statistics on a regular basis against Repserver (SRS) RSSD tables; not only can this improve/ensure the performance of the various rs_* procs, but it can also improve/ensure overall SRS performance (due to SRS's almost-constant querying of RSSD tables).

0

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

0