SQL Server T-SQL User-Defined Function of the Week

Find Stored Procedures that Always RECOMPILE

Volume 1 Number 25   		 May 6, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

I've been working on a new article for Database Journal on how
to minimize the recompilation of SQL Server stored procedures.
One of the ways to force a stored procedure to recompile is to
use the WITH RECOMPILE option on either the CREATE PROCEDURE
or ALTER PROCEDURE statements.  

The Books-Online (BOL) doesn't mention where SQL Server records
that a procedure has the WITH RECOMPILE option on it but a little
experimentation reveals that it's in the column 
sysobjects.status.  This little script creates two stored
procedures and then queries sysobjects to show you what I mean.
I executed it in my scratch database but you can execute it in
any database that you choose. 
/------ Start copying below this line --------------------------\
USE scratch
go

If object_id('usp_MyTestProc1') IS NOT NULL 
                                      DROP PROC usp_MyTestProc1
If object_id('usp_MyTestProc2') IS NOT NULL 
                                      DROP PROC usp_MyTestProc2
GO

CREATE PROCEDURE dbo.usp_MyTestProc1 
AS
 SELECT 1, 2, 3, 'abcd'
GO

CREATE PROCEDURE dbo.usp_MyTestProc2 WITH RECOMPILE

AS
 SELECT 4, 5, 6, 'efgh'
GO

SELECT [name], status
     FROM sysobjects 
     WHERE type = 'P'
        AND [name] LIKE 'usp_mytestproc%'
GO
\-------- Stop copying from above this line --------------------/
(Results - 1st group of columns)
name                                     status      
---------------------------------------- ----------- 
usp_MyTestProc1                           1610612736 
usp_MyTestProc2                           1610612740 
            
The difference between the status columns is four.  That's
because usp_MyTestProc2 was created with the WITH RECOMPILE
option, which happens to correspond to the 4s place in the
status int.

We can use the UDFs from the previous two issues #23 and #24
to break status down into bits.  Here the demonstration:
/------ Start copying below this line --------------------------\
SELECT [name], status
     , dbo.udf_BitS_FromInt(Status, 0) as [Status as Bit String]
     , dbo.udf_Bit_Int_NthBIT (Status, 2) as [Bit 2]
     FROM sysobjects 
     WHERE type = 'P'
        AND [name] LIKE 'usp_mytestproc%'
GO
\------ Stop copying from above this line ----------------------/
(Results - reformatted to fit)
name            status      Status as Bit String            Bit 2
--------------- ----------- ------------------------------- -----
usp_MyTestProc1 1610612736 01100000000000000000000000000000     0
usp_MyTestProc2 1610612740 01100000000000000000000000000100     1

As you can see from the right side of [Status as Bit String] it's
the 3rd bit (number 2) that is different.  I've been able to
confirm this by additional experimentation.

udf_Bit_Int_NthBIT makes it easy to craft a procedure that
returns all procedures that are compiled using WITH RECOMPILE.
Here's the CREATE FUNCTION Script.
/------ Start copying below this line --------------------------\
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.udf_Proc_WithRecompile ( 

    @Procedure_Name_Pattern as nvarchar(768) = NULL
       -- Procedure name or pattern of procedure
       -- names to restrict the search.
)   RETURNS TABLE -- 
    -- No SCHEMABINDING due to use of sysobjects
/* 
* Returns a table of names of procedures that have been created
* with the WITH RECOMPILE option.  These procedures are never
* cached and require a new plan for every execution.  This can
* be a performance problem.
*
* Example:
SELECT * FROM udf_Proc_WithRecompile (NULL)
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Vol 1 #25
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT [id]
     , [name] as [Procedure_Name]
    FROM sysobjects
    WHERE type = 'P'
       AND 1=dbo.udf_BIT_Int_NthBIT(Status, 2)

GO

GRANT SELECT on dbo.udf_Proc_WithRecompile to PUBLIC
GO
\------ Stop copying from above this line ----------------------/

It's important to put the test for a procedure (type = 'P') into
the WHERE clause.  Other object types use bit #2 for other 
purposes.

Here's the result of using udf_Proc_WithRecompile on my scratch
database:
/------ Start copying below this line --------------------------\
SELECT * FROM udf_Proc_WithRecompile (NULL)
GO
\------ Stop copying from above this line ----------------------/
(Results)
id          Procedure_Name                           
----------- ---------------------------------------- 
 1445580188 usp_MyTestProc2

Fortunately usp_MyTestProc2 is the only procedure that has been
created WITH RECOMPILE.  Had there been others they should be
examined to be sure that the option can't be removed.

The article on minimizing stored procedure recompiles will be 
published in about in a few weeks.  I'll let you know when it's
available.

Please share this newsletter with anyone interested in
SQL Server.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+