February 1, 2011 at 11:12 pm
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
February 2, 2011 at 1:17 am
Duplicate post. No replies to this thread please. Direct replies to http://qa.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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply