February 1, 2011 at 11:08 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:19 am
Can you elaborate on 'No cache plan is generated'? What exactly are you seeing and what's not happening that you think should be happening?
Edit: Could it be this?
A batch that contains any one literal longer than 8 KB is not cached. Therefore, query plans for such batches cannot be reused. (A literal's length is measured after constant folding is applied.)
from http://technet.microsoft.com/en-us/library/cc966425.aspx#XSLTsection130121120120
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
February 2, 2011 at 4:38 am
It's got to be something along the lines Gail suggests. I just ran through it and it creates a plan and stores it cache just fine.
Another reason you might not see a plan in cache was if it was a trivial plan, but that's not the case here.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2011 at 4:41 am
Grant Fritchey (2/2/2011)
Another reason you might not see a plan in cache was if it was a trivial plan, but that's not the case here.
In SQL 2005 even trivial plans are cached.
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
February 2, 2011 at 5:10 am
GilaMonster (2/2/2011)
Grant Fritchey (2/2/2011)
Another reason you might not see a plan in cache was if it was a trivial plan, but that's not the case here.In SQL 2005 even trivial plans are cached.
There must be days where I make you despair.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2011 at 5:52 am
Grant Fritchey (2/2/2011)
GilaMonster (2/2/2011)
Grant Fritchey (2/2/2011)
Another reason you might not see a plan in cache was if it was a trivial plan, but that's not the case here.In SQL 2005 even trivial plans are cached.
There must be days where I make you despair.
:unsure:
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply