Presents your 
SQL SERVER E-NEWSLETTER for July 23, 2002

<------------------------------------------->

DETECT THE PRESENCE OF MULTIPLE PROCEDURE PLANS

In SQL Server 7.0 and SQL Server 2000, user connection options may cause
a new compile and execution plan of the procedure. Efficient uses of
procedure plans are in the reuse of the plans, but having multiple plans
decreases the efficiency of resources. Querying the syscacheobjects will
show you whether multiple query plans exist based on different user
connection options.

The following is a sample script, which demonstrates the presence of
multiple procedure plans in the cache, based on different user connection
options.

USE pubs
GO
SET CONCAT_NULL_YIELDS_NULL ,ANSI_WARNINGS ON
SELECT CASE WHEN au_fname LIKE 'ann%' THEN NULL ELSE au_fname END
au_fname, au_lname
INTO temp_authors
FROM authors
GO
CREATE PROCEDURE GetAuthors
AS
SELECT ISNULL(au_lname + ', ' + au_fname, REPLICATE('*',80)) AuthorName
FROM temp_authors
GO
SET CONCAT_NULL_YIELDS_NULL OFF
EXEC GetAuthors
EXEC GetAuthors
EXEC GetAuthors
EXEC GetAuthors
EXEC GetAuthors
GO
SET CONCAT_NULL_YIELDS_NULL ON
EXEC GetAuthors
EXEC GetAuthors
GO
SET ANSI_WARNINGS OFF
EXEC GetAuthors
EXEC GetAuthors
EXEC GetAuthors
GO
SELECT sc.setopts, sc.usecounts, sc.dbid, so.id, sc.cacheobjtype, so.name, sc.*
FROM master..syscacheobjects sc
JOIN master..sysdatabases sd ON sd.dbid = sc.dbid AND sd.name = 'pubs'
JOIN pubs..sysobjects so ON sc.objid = so.id AND so.name = 'GetAuthors'
GO
DROP PROC GetAuthors
GO
DROP TABLE temp_authors
GO

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