Which SQL statement is faster My co-worker claims that using a parameterized Like statement is equivalent to dynamic sql and won't have its execution plan cached for reuse. He says that using sp_executesql will allow the execution plan to be cached, increasing the performance of identical searches. However, in the context of this query, the argument doesn't seem to make sense. 

Any way to prove one way or the other? 

CREATE PROCEDURE [dbo].[SP_Get_Person]
        @SearchText varchar(50)
AS
BEGIN
        SET NOCOUNT ON;

        ------------------------------------------------------------------------------------
        Declare @SQLString nvarchar(500)
        Declare @ParmDefinition nvarchar(500)
        SET @ParmDefinition = N'@SearchText varchar(50)';

        SET @SQLString = N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''';

        EXECUTE sp_executesql @SQLString, @ParmDefinition,
                              @SearchText = @SearchText;
        ------------------------------------------------------------------------------------

        --OR

        ------------------------------------------------------------------------------------
        Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%'
        ------------------------------------------------------------------------------------
END
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:


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: 



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. 
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:



SP_Get_Person1 – Proc &amp; Prepared

SP_Get_Person2 – Proc

SP_Get_Person3 – Proc &amp; 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


SP_Get_Person2


SP_Get_Person3


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%';


Tools

Statistics were gathered using the Include Client Statistics button in SQL Server Management Studio. Only the last 10 query results are stored and averaged. The results can be cleared from the menu: Query -> Reset Client Statistics.