SP cache plan not generated

Question

There is something weird in this statement COALESCE(@param_ids + ',', '')

@param_ids are passed in as param and it is @param_ids VARCHAR(MAX)

Any idea why the SQL-Server could not able to produce a cache plan for below scenario. This issue has been escalted to Microsoft they are still working on it.

Not working scenario - No cache plan is generated Step 1

Alter a SP

 IF (LTRIM(RTRIM(ISNULL(@param_ids,''))) = '')  
  BEGIN  
   SELECT @param_ids = COALESCE(@param_ids + ',', '') + CONVERT(VARCHAR(50),ID)  
   FROM [dbo].Content_Type WITH (NOLOCK)  
  END  

Step 2 Exec Sp with params

Step 3 (No cache plan)

SELECT *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [dbid] = DB_ID('databasename')
AND [objectid] = OBJECT_ID('databasename.dbo.us_spname')
GO

Working scenario - cache plan is generated Step 1

Alter a SP

 IF (LTRIM(RTRIM(ISNULL(@param_ids,''))) = '')  
  BEGIN  
   --SELECT @param_ids = COALESCE(@param_ids + ',', '') + CONVERT(VARCHAR(50),ID)  
   --FROM [dbo].Content_Type WITH (NOLOCK)  

    SELECT @param_ids = COALESCE('aaaaaaa' + ',', '') + CONVERT(VARCHAR(50),ID)  
    FROM [dbo].Content_Type WITH (NOLOCK)   
  END  

Step 2 Exec Sp with params

Step 3 (cache plan exits)

SELECT *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [dbid] = DB_ID('databasename')
AND [objectid] = OBJECT_ID('databasename.dbo.us_spname')
GO

Thankx

Answer 1

I added a WAITFOR and I consistently get a plan. This should be independent of parameters

CREATE PROC dbo.testplan @param_ids varchar(max) OUTPUT
AS
IF (LTRIM(RTRIM(ISNULL(@param_ids,''))) = '')  
  BEGIN  
   SELECT @param_ids = COALESCE(@param_ids + ',', '') + CONVERT(VARCHAR(50),ID)  
   FROM [dbo].Content_Type WITH (NOLOCK)  
  END  
GO
CREATE PROC dbo.testplan2 @param_ids varchar(max) OUTPUT
AS
IF (LTRIM(RTRIM(ISNULL(@param_ids,''))) = '')  
  BEGIN  
   SELECT @param_ids = COALESCE(@param_ids + ',', '') + CONVERT(VARCHAR(50),ID)  
   FROM [dbo].Content_Type WITH (NOLOCK)  
  END  
GO

EXEC dbo.testplan 'bar'
GO
WAITFOR DELAY '00:00:02'
SELECT OBJECT_NAME([objectid]), *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [dbid] = DB_ID()
AND [objectid] = OBJECT_ID('testplan')
GO

EXEC dbo.testplan2 ''
GO
WAITFOR DELAY '00:00:02'
SELECT OBJECT_NAME([objectid]), *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [dbid] = DB_ID()
AND [objectid] = OBJECT_ID('testplan2')
GO