$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content

What is the meaning of Reuse_cnt in sp_monitorconfig

Dec 27, 2016 at 05:27 AM


avatar image

Hi friends,

Could some explain what is "Reuse_cnt" and how this is helpful in configuring the parameters.?

Adaptive Server Enterprise/16.0 SP02 PL01/EBF 25184 SMP/P/x86_64/Enterprise Linux/ase160sp02pl00/2424/64-bit/FBO/Thu Sep 17 22:46:42 2015

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Avinash Kothare Dec 28, 2016 at 06:51 PM

Hi Anudeep

Above Mark has already provided some excellent tips.

First and foremost put a batch job to record the results of "sp_monitorconfig 'all'" "sp_countmetadata" periodically (every hour may be !).

Ideally if you can recycle the server to reset the re-use and max-used counters that will be great, so that you start with a clean slate.

If not, use the figures last recorded to see the upward trend if any.

So increase the number of open objects by 100K when you first see the (climb) of reuse count.

And then with that base line keep increasing in steps until reuse count steadies.

Investigate the processes during the particular window when you notice climbing used count. As Mark pointed out if it is due to temporary objects then in your configuration cater to that or accept a small performance hit ( until user(s) start shouting !!)

Also watch errorlog for any messages asking for reconfiguration. E.g. if number of locks run out, it is recorded in the ASE errorlog.



10 |10000 characters needed characters left characters exceeded
Mark A Parsons Dec 28, 2016 at 06:06 PM

Some background ...

* sp_monitorconfig's max_used and reuse_cnt numbers represent high water marks since the dataserver was last booted; these numbers will reset to 0 the next time the dataserver is (re)started

* the discrepancy between what sp_countmetadata is reporting (83325) and what sp_montiorconfig is reporting (max_used = 300000) is likely due to a surge in the number of temporary objects (eg, temp tables, prepared statements) during some period in the recent past (eg, overnight batch processing)

* the dataserver will occasionally dump messages to the errorlog when metadata counters (eg, number of open objects) is exceeded; a review of your dataserver's errorlog (looking for messages about object descriptor reuse) may help in pinpointing a time period when number of open objects was exceeded, which in turn may give you some idea of what process was running at the time that could explain the (temporary) bump up in the number of object descriptors


Going forward ...

* with a process and/or time period in hand, you could then consider running sp_countmetadata while said process is running in order to get a better estimate on the max number of object descriptors in use

* alternatively, since the amount of memory for an object descriptor is (relatively) small, and you're not likely to run sp_countmetadata at the exact moment when the *max* number of (temporary) object descriptors is in use, you could take a trial-and-error approach to addressing the reuse issue; for example, increase number of open objects to 400000, and if reuse_cnt continues to climb (above the current value of 95686), or max_used approaches 400000, then increase number of open objects to 500000 and continue to monitor

10 |10000 characters needed characters left characters exceeded
Avinash Kothare Dec 27, 2016 at 03:41 PM

Check the if a particular configuration value is marked as reused indicated by "yes" in the report.

Then look at the max used value.

Add your buffer / fudge factor and then use this new value as value to be configured.

E.g. "number of open" indexes, objects may be under configured. If number is under configured, descriptors may be written to disk and the reused for next session(s). This can impact performance of one or more queries.

DBAs typically run sp_monitorconfig "all" and watch for under / over configured values.

This needs to be run especially during peak hours to note the configuration tweaks needed.



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

Hi Avinash,

Thank you for your response.

Below is the situation for number of open objects -

Configured value - 300000

Max_Used - 300000

Reuse_cnt - 95686

Could you please help me how much should i configure?


1> sp_countmetadata 'number of open objects'
2> go
There are 83325 user objects in all database(s), requiring 1101462 Kbytes of memory. The 'open objects' configuration parameter is currently set to 300000.

Anudeep S P Jan 05, 2017 at 05:07 AM

Thank Avinash and Mark for your valuable suggestion. It helped a lot.



10 |10000 characters needed characters left characters exceeded