SP cache plan not generated

• sqlcool

  SSC Eights!

  Points: 967

  More actions

  February 1, 2011 at 11:12 pm

  #144971

  Hello people

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 2, 2011 at 1:17 am

  #1281272

  Duplicate post. No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1057197-146-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass