• 1

• 333
• 1
• 2
• 11

• 333
• 1
• 2
• 11

Armed with the article and information provided by @StriplingWarrior, I conducted an experiment and am posting it as an answer to my own question. Please feel free to comment if I have erred in my conclusions.

Assumptions

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

• Adhoc query caching
• Autoparameterization
• Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
• Stored procedures or other compiled objects (triggers, TVFs, etc.)

SQL Server stores it’s caching plans in a metadata table called sys.dm_exec_cached_plans

Examining the cache

LEGEND:

• Proc (Stored procedure) – "By default, the cached plan will be reused for all successive executions.... unlike the plans cached and reused with sp_executesql, you have an option with stored procedures and user-defined scalar functions to force recompilation when the object is executed."
• Prepared (Prepared statement) – "The stored procedure sp_executesql is halfway between adhoc caching and stored procedures. Using sp_executesql requires that you identify the parameters and their datatypes, but doesn’t require all the persistent object management needed for stored procedures and other programmed objects."
• Adhoc (Adhoc query) – "...even when SQL Server caches your adhoc queries, you might not be able to depend on their reuse. When SQL Server caches the plan from an adhoc query, the cached plan will be used only if a subsequent batch matches exactly."

DATA:

1. Determine what caching mechanisms are used outside the context of a stored procedure

Running the attached query NoStoredProcs.sql (see Resources section) yields the following results:

Query Results 1

You can see that the Query executed with sp_executesql has its query plan cached as a Prepared type while the inline statements were cached as Adhoc types.

2. Determine what caching mechanisms are used within the context of a stored procedure

Running the attached query WithStoredProcs.sql (see Resources section) yields the following results:

Query Results 2

SP_Get_Person1 – Proc & Prepared

SP_Get_Person2 – Proc

SP_Get_Person3 – Proc & Adhoc

CONCLUSION:

It would seem that since there was no additional Adhoc cache plan created for SP_Get_Person2 that its structure can stand on its own (within a Stored Procedure), in terms of database caching. This can be confirmed by the fact that an Adhoc cache plan was created for this query outside the context of the stored procedure, but not when placed inside the stored procedure.

Performance

LEGEND:

• Client processing time - The cumulative amount of time that the client spent executing code while the query was executed.
• Total execution time - The cumulative amount of time (in milliseconds) that the client spent processing while the query was executed, including the time that the client spent waiting for replies from the server as well as the time spent executing code.
• Wait time on server replies - The cumulative amount of time (in milliseconds) that the client spent while it waited for the server to reply.

DATA:

SP_Get_Person1 Query Results 3

SP_Get_Person2 Query Results 4

SP_Get_Person3 Query Results 5

CONCLUSION:

From the results above, I can only conclude that any performance gains or losses between SP_Get_Person1 and SP_Get_Person2 are inconclusive/negligible since the average total execution time consistently differs in terms of microseconds. However, it is interesting to note that the average time for SP_Get_Person3 is significantly lower. I would strongly caution that this is not necessarily evidence of a reliable performance gain since every search would result in a new Adhoc query (SQL server did not autoparamatarize this query during my tests). Therefore it is inconclusive what the effect of a growing set of Adhoc queries would have on the database. Furthermore, we lose the safety of typed parameters when using this method.

Resources

NoStoredProcs.sql

use DemoDatabase
dbcc freeproccache;
GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%' 
GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
EXECUTE sp_executesql N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''', N'@SearchText varchar(50)',
 @SearchText = @SearchText;
GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
EXECUTE ( 'Select * FROM PERSON Where LASTNAME Like ''%' + @SearchText + '%''')
 
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
 CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
 AND [text] NOT LIKE '%dm_exec_cached_plans%';

WithStoredProcs.sql

use DemoDatabase
dbcc freeproccache;
--DROP PROCEDURE [SP_Get_Person1]
--DROP PROCEDURE [SP_Get_Person2]
--DROP PROCEDURE [SP_Get_Person3]
GO
 CREATE PROCEDURE [dbo].[SP_Get_Person1]
 @SearchText varchar(50)
 AS
 BEGIN
 SET NOCOUNT ON;
 EXECUTE sp_executesql N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''', N'@SearchText varchar(50)',
 @SearchText = @SearchText;
 END
GO
 CREATE PROCEDURE [dbo].[SP_Get_Person2]
 @SearchText varchar(50)
 AS
 BEGIN
 SET NOCOUNT ON;
 Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%'
 END
GO
 CREATE PROCEDURE [dbo].[SP_Get_Person3]
 @SearchText varchar(50)
 AS
 BEGIN
 SET NOCOUNT ON;
 EXECUTE ( 'Select * FROM PERSON Where LASTNAME Like ''%' + @SearchText + '%''')
 END
GO
 --SET STATISTICS IO ON
 --SET STATISTICS TIME ON 
GO
 Execute [SP_Get_Person1] @SearchText = 'Abatemarco'
GO
 Execute [SP_Get_Person2] @SearchText = 'Abatemarco'
GO
 Execute [SP_Get_Person3] @SearchText = 'Abatemarco'
GO
 --SET STATISTICS IO OFF
 --SET STATISTICS TIME OFF 
GO
 SELECT usecounts, cacheobjtype, objtype, [text]
 FROM sys.dm_exec_cached_plans P
 CROSS APPLY sys.dm_exec_sql_text (plan_handle)
 WHERE cacheobjtype = 'Compiled Plan'
 AND [text] NOT LIKE '%dm_exec_cached_plans%';