Why would a stored procedure perform differently when executed remotely to locally?

Question

We've a stored procedure that happens to build up some dynamic SQL and execute via a parametrised call to sp_executesql.

Under normal conditions, this works wonderfully, and has made a large benefit in execution times for the procedure (~8 seconds to ~1 second), however, under some unknown conditions, something strange happens, and performance goes completely the other way (~31 seconds), but only when executed via RPC (i.e. a call from a .Net app with the SqlCommand.CommandType of CommandType.StoredProcedure; or as a remote query from a linked server) - if executed as a SQL Batch using SQL Server Management Studio, we do not see the degradation in performance.

Altering the white-space in the generated SQL and recompiling the stored procedure, seems to resolve the issue at least in the short term, but we'd like to understand the cause, or ways to force the execution plans to be rebuilt for the generated SQL; but at the moment, I'm not sure how to proceed with either?

---

To illustrate, the Stored Procedure, looks a little like:

CREATE PROCEDURE [dbo].[usp_MyObject_Search]
    @IsActive       AS BIT = NULL,
    @IsTemplate     AS BIT = NULL
AS
DECLARE @WhereClause NVARCHAR(MAX) = ''

IF @IsActive IS NOT NULL
BEGIN
    SET @WhereClause += ' AND (svc.IsActive = @xIsActive) '
END

IF @IsTemplate IS NOT NULL
BEGIN
    SET @WhereClause += ' AND (svc.IsTemplate = @xIsTemplate) '
END

DECLARE @Sql NVARCHAR(MAX) = '
    SELECT      svc.[MyObjectId],
                svc.[Name],
                svc.[IsActive],
                svc.[IsTemplate]

    FROM        dbo.MyObject    svc WITH (NOLOCK)

    WHERE  1=1 ' + @WhereClause + '

    ORDER BY    svc.[Name] Asc'

EXEC sp_executesql @Sql, N'@xIsActive BIT, @xIsTemplate BIT',
                   @xIsActive = @IsActive, @xIsTemplate = @IsTemplate

With this approach, the query plan will be cached for the permutations of NULL/not-NULL, and we're getting the benefit of cached query plans. What I don't understand is why it would use a different query plan when executed remotely vs. locally after "something happens"; I also don't understand what the "something" might be?

I realise I could move away from parametrisation, but then we'd lose the benefit of caching what are normally good execution plans.

Answer 1

Recompilation

Any time the execution of the SP would be significantly different due to conditional statements the execution plan which was cached from the last request may not be optimal for this one.

It's all about when SQL compiles the execution plan for the SP. They key section regarding sp compilation on Microsoft docs is this:

... this optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time that it executes

SQL does recompile execution plans at times, from Microsoft docs

SQL Server automatically recompiles stored procedures and triggers when it is advantageous to do this.

... but it will not do this with each call (unless using WITH RECOMPILE), so if each execution could be resulting in different SQL, you may be stuck with the same old plan for at least one call.

RECOMPILE query hint

The RECOMPILE query hint, takes into account your parameter values when checking what needs to be recompiled at the statement level.

WITH RECOMPILE option

WITH RECOMPILE (see section F) will cause the execution plan to be compiled with each call, so you will never have a sub-optimal plan, but you will have the compilation overhead.

Restructure into multiple SPs

Looking at your specific case, the execution plan for the proc never changes and the 2 sql statements should have prepared execution plans.

I would suggest that restructuring the code to split the SPs rather than have this conditional SQL generation would simplify things and ensure you always have the optimal execution plan without any SQL magic sauce.

Answer 2

I would suspect parameter sniffing. If you are on SQL Server 2008 you could try including OPTIMIZE FOR UNKNOWN to minimise the chance that when it generates a plan it does so for atypical parameter values.

RE: What I don't understand is why it would use a different query plan when executed remotely vs. locally after "something happens"

When you execute in SSMS it won't use the same bad plan because of different SET options (e.g. SET ARITHABORT ON) so it will compile a new plan that works well for the parameter values you are currently testing.

You can see these plans with

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%FROM        dbo.MyObject    svc WITH (NOLOCK)%' 
                                          and attribute='set_options'

Edit

The following bit is just in response to badbod99's answer

create proc #foo @mode bit, @date datetime
as
declare @Sql nvarchar(max)

if(@mode=1)
set @Sql = 'select top 0 * from sys.objects where create_date < @date /*44FC79BD-2AF5-4774-9674-04D6C3D4B228*/'
else
set @Sql = 'select top 0 * from sys.objects where modify_date < @date /*44FC79BD-2AF5-4774-9674-04D6C3D4B228*/'

EXEC sp_executesql @Sql, N'@date datetime',
                   @date = @date
go

declare @d datetime
set @d =  getdate()
exec #foo 0,@d
exec #foo 1, @d

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%44FC79BD-2AF5-4774-9674-04D6C3D4B228%' 
                                          and attribute='set_options'

Returns