update statistics and sp_recompile

[ashish]

Hi,

ASE 12.5.1 -> Reference Manual -> update statistics :

"Adaptive Server automatically recompiles stored procedures
after executing update statistics statements."

Is this true for older versions (upto 11.0) also?

We have an old script which runs on innumerable servers. for
all tables, it runs an update statistics followed by an
sp_recompile.

Can we safely remove the sp_recompile?

Thanks,
Ashish

[Mark A. Parsons]

The documentation could benefit from a re-write:

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

Adaptive Server automatically recompiles stored procedures after

executing update statistics statements. Although adhoc queries that you
start before executing update statistics still continue to work, they do
not take advantage of the new statistics.

In Adaptive Server versions 12.5 and earlier, update statistics was
ignored by cached stored procedures.
-----------------------------------

With 12.5+, whenever you create an index, or issue 'update statistics',
*within* a stored procedure, the stored procedure will be recompiled so
as to take into consideration the new index statistics. The stored proc
is recompiled as the first step *after* the 'create index'/'update
statistics' command.

This comes in handy for jobs that work with sets of indexed #temp tables
which in turn require the optimizer to recognize the new stats on said
#temp table indexes. (Can also be of use with batch jobs that
rebuild/repopulate permanent tables and need to have new index
statistics made a part of follow-on queries.)

You can see this by enabling 'set showplan on' followed by running a
stored proc. Every time the stored proc creates an index, or updates
statistics, you'll find that you get a new set of showplan output (this
is where the optimizer is being called to re-compile the stored proc).

Also notice that the last sentence answers your question about this
'feature' with older versions of ASE, ie, it is not supported in older
versions of ASE.

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

There are 2 different scenario's here:

1 - A stored proc that affects a large part of a table which then needs
to update said table's statistics (or create a new index) ... key here
is that the 'create index', or 'update statistics', has to be *inside*
the stored proc for the stored proc to be automagically recompiled.

2 - When you're updating statistics outside of a stored proc (eg, a
weekly, stand-alone 'update stats' job) you should still issue the
sp_recompile command to make sure stored procs (and triggers) are
recompiled the next time they are run.

[ashish]

Thanks a ton for your detailed reply.

One more question, please.

Is there any good reason to club together update statistics
and sp_recompile in the same script ?

I would think that these two could be in 2 different
scripts.

For databases with many large tables, we could stagger out
the update statistics so that we do an update stats for some
tables every night and all tables have their stats updated
once per week.

sp_recompile seems to update some value in a column in
sysobjects (?) and so the execution time will be independent
of the size of the object and so we could run this every
night for all the objects.

Thanks and regards,
Ashish

[Sherlock, Kevin]

The question as I understand it, is by issuing an "update statistics" command in
12.5.x, is the table automatically "marked for recompile"? And the answer is
yes, since 12.5.0.3 (somewhere near this release?) This was a change of
behaviour from earlier releases.

Previous to this, you normally would execute "sp_recompile" on the table.

So, in more current versions of ASE, yes, you can skip the "sp_recompile"
command. Technically, both "update statistics" and "sp_recompile" (as well as
"create index") increment the "indexdel" counter in sysobjects (and in the
table's "des" structure, it's the dobjstat.objschema2 counter). The "drop
index" command increments the "schemacnt" counter in sysobjects (which is the
dobjstat.objschema counter in the des for the table).

As the docs point out, those stats will only be available upon the next
compilation of the procedure/trigger, and wouldn't affect currently executing
plans.

"Mark A. Parsons" <iron_horse@no_spamola_compuserve.com> wrote in message
news:432663f7$1@forums-1-dub...

[Eric Miner]

Just as a sidebar - The creation of an object, in most cases an index,
within a proc will bump the schema count and cause a reompilation of the
proc in the same run. That is, if you create an index in a proc and then
issue a query in the proc that can use the index compilation will go
back and be done again before executing the proc.......this has been the
case ever since 11.5

As for recompile after an update stats....yeah, it can be handy
sometimes, but I've also seen it cause problems.

Later,

Eric Miner
Client Servers. LLC