Hi Everyone,

I have been monitoring recompiles, and am seeing different EventSubClasses
for these, but have been unable to find any further details about the
various EventSubClasses in BOL. Can anyone direct me to more detailed
descriptions/definitions about these?

For example, I am seeing "1 - schema changed" but cannot understand why this
has occurred. Similarly for other EvenSubClasses.

TIA

Michael MacGregor
Database Architect

Hi Michael

Have you read these KB articles?

How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737

Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586

Show quoteHide quote

Hi Kalen,

Thank you for responding to my question.

Yes I have read both of those, and, I have to admit that some of the issues
that can cause recompiles are ones from which our code suffers. However,
there are certain EventSubClass recompiles that don't seem to make sense to
me. For instance, I am seeing some recompiles due to schema changes, but the
schema hasn't changed since November so I don't understand why I'm seeing
these, and the same SP and same EventSubClass will occur again within an
hour, and the schema definitely hasn't changed since the last time the SP
ran. Or does this mean that the schema has changed since the SP was created
or last modified, and it will continue to cause recompiles until the SP is
explicitly recompiled or recreated?

Of the causes I know about, objects referenced are not properly qualified
with the owner name, temp tables are being used when table variables would
be better, and table statistics have gone out of date.  The latter I have
already fixed by updating the statistics on all the tables, I am also
reviewing the whole statistics issue anyway as there are many that probably
should simply be dropped.

I am currently profiling the StmtRecompile event and will be analyzing this
in the near future to determine which out of our 300 SPs are culprits. Those
that have temp tables will be updated to use table variables.

Updating all SPs to qualify reference objects with the owner name will take
a lot longer.

However, I find it a little surprising, and disappointing, that there is
very little further explanation about what each EventSubClass means, how to
investigate further and how to resolve them in BOL itself. Is there perhaps
a good book that covers this topic in detail?

Thanks again.

Michael MacGregor
Database Architect

Michael MacGregor (nospam@nospam.com) writes:
Let me guess: there is a temp table involved? There you have your
schema change. Say that you have:

   CREATE PROCEDURE outer_sp AS
   ....
      CREATE TABLE #tmp (...)
      EXEC inner_sp
      ...
   go
   CREATE PRORECURE inner_sp
   ...
     -- Do something with #tmp

Here there will be a recompile of inner_sp each time, or the statements
that refer to #tmp in SQL 2005. This is because you have a new #tmp
each time.

I'm not 100% sure, but I think a reindexing can cause a recompile
because of schema change (else it will be from statistics change). Adding
or dropping an index is definitely a schema change.

Be selective when you do this. Sometimes those recompiles are good
for you, since the optimizer may find a better plan. Particularly
if the temp tables can grow large, you are better of with temp tables
than table variables.

Actually I find most of them clearcut:

2 - Statistics changed. If you want to know when and why statistics
changes, this white-paper is for you:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

3 - Recompile DNR. DNR = Deferred name resolution. The table did not
    exist when the plan was created, so we to recompile. Typical with
    temp tables.

4 - Set option changed. See
    http://www.karaszi.com/SQLServer/info_sp_recompile_set.asp.

5 - Temp table changed. Don't really know if this is different from 1,
    or if it appears.

6 -  Remote rowset changed. OPENQUERY and the like.

Thanks Erland.

When temp tables are used, do they only cause a schema change recompile when
involved in nested SP calls, or can the schema change occur even if a temp
table is used in a non-nested SP? What causes the Temp Table recompile then?

The design of the schema is not the greatest I'm afraid. GUIDs are used as
clustered PKs - yuk! Due to this, the indexes get badly fragmented and have
to be rebuilt on a regular basis, so this could also be the source of the
schema change based on what you have said.

These recompiles are causing timeouts to occur on the front end, which are
resulting in incomplete transactions. If using table variables can prevent
that, then that is what I shall do. I had to do the same thing at my last
job, we were having a lot of problems with both statistics going out of data
and temp tables. I replaced all of the temp tables with table variables, and
updated and reviewed statistics on a regular basis.

Some of these recompiles though, such as the schema change ones, were
baffling me, and that's why I was looking for more information as to what
actually constitutes a schema change recompile, which I don't think BOL
explains.

Thanks.

MTM

Michael MacGregor (nospam@nospam.com) writes:
The first time an SP is called, or more exactly when there is no plan
for the SP in the cache, the statements that refer to the temp table
will be deferred, because the temp table is missing. Once the tables
have been created, there will be a recompilation.

The most common reason for recompiles due to temp table is statistics.

Adding index on a temp table can be problematic too.

The default timeout of 30 seconds should be destroyed in my opinion.

But it's imperative that when you get a command timeout that you
issue a IF @@rollback > 0 ROLLBACK TRANSACTION, since there is no
automatic rollback in this case.

It isn't clear to me whether you are using SQL 2000 or SQL 2005. I don't
have that much production experience of SQL 2005 yet, but since
recompiles are on statement level in SQl 2005, I expect them to be
less problematic than in SQL 2000.

SQL Server Management Studio - long names in tabs
Help with SQL 2005 and Sourcesafe 6
SSIS and polling
xml viewer
Diagram changes