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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy