February 12, 2015 at 6:28 am
robert.diley (2/11/2015) the space is not apparent when viewing the browser.
Ah, but the space at least (and tab would be another one) is visible if you highlight the text. Good practice when looking for odd whitespace characters.
February 12, 2015 at 7:48 am
Didn't know it was that sensitive.
February 13, 2015 at 1:35 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2015 at 9:49 am
Good question, a pity the typing made the answer wrong because it altered something. Like several others I highlighted the text so that I could see spaces, and ended up counting 5 different queries.
Of course decent parsing that eliminated reduncant white-space would result in only one cached plan, I wonder whether doing that would lead to better perormance or poorer (trade off between extra work in the parser and less cache required and potentially less compilation) - actually I suspect it would be poorer on most workloads.
Tom
February 18, 2015 at 5:53 pm
One thing to consider is that if you turn on the configuration option "optimize for ad hoc workloads" none of these different queries get cached as ad-hoc plans, so the answer would be 0:
EXEC sys.sp_configure
@configname = 'optimize for ad hoc workloads'
, @configvalue = 1
RECONFIGURE WITH OVERRIDE
DBCC FREEPROCCACHE;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Plans]') AND type in (N'U'))
DROP TABLE [dbo].[Plans]
GO
CREATE TABLE Plans (
PlanId INT IDENTITY,
PlanName VARCHAR(64)
);
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
GO
select *
from Plans
where PlanName = 'Bob'
GO
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
GO
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
/* Same as first select */
GO
SELECT *
FROM Plans
WHERE PlanName = 'Bob'
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
The compiled plan would still be there, however, as:
(@1 varchar(8000))SELECT * FROM [Plans] WHERE [PlanName]=@1
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply