Kalen Delaney, author of Inside SQL Server 2000, wrote a series of articles (Dec. 2002, Feb. 2001, & Jan. 2000) in SQL Server Magazine regarding recompilations. You may find useful information in these.

The gist of these articles is that certain options are stored as server, database, and session options. A couple (QUOTED_IDENTIFIER and ANSI_NULLS) are stored as part of the bitwise value in status column of the sysobjects table for stored procedures and triggers. You can see their values when you edit these objects in Query Analyzer. Example below.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

Other options (e.g. ANSI_PADDING, etc.) are session options that are controlled by the type of connection provider used (OLEDB, etc.). See Tools/Options/Connection Properties in Query Analyzer for examples. These option states are stored in the setopts column of the syscacheobjects table when procedures and triggers are compiled.

When certain of these options are out of synch with the database states, recompiles are forced. I know the two shown above are capable of this. On is the default condition of both. If you see either as OFF, suspect this.

You can find the bitwise values of these in the master..spt_values table.

Hope this helps.

Larry Ansley