{"id":504,"date":"2010-04-28T12:20:00","date_gmt":"2010-04-28T12:20:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Clearing-the-cache-are-there-other-options.aspx"},"modified":"2013-01-11T23:10:40","modified_gmt":"2013-01-12T07:10:40","slug":"clearing-the-cache-are-there-other-options","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/clearing-the-cache-are-there-other-options\/","title":{"rendered":"Clearing the cache – are there other options?"},"content":{"rendered":"

\nOK, I've had a bit of a tangent going on optimizing stored procedures and its all revolved around adhoc plan caching and potentially wasted cache due to the single-use plans created (see the most recent posts titled:Plan cache and optimizing for adhoc workloads<\/a> and Plan cache, adhoc workloads and clearing the single-use plan cache bloat<\/a>). To be more specific, these are statements that when generated (via adhoc or prepared statements) don't end up being parameterized (in general – only relatively simple statements can be effectively parameterized and when your statements complex, SQL Server won't parameterize them – this can be good and in some systems this can be terribly bad). But, now you know how <\/em>bad… you know how much of your cache you're wasting but where is it going and are there any similarities to these statements? Should you (or can you?) bring them together to see if there is a commonality that can be leveraged?\n<\/p>\n

\nIn general, the answer is "it depends" (you knew <\/em>that was coming!). However, I do have good news, you can definitely gauge what's actually going on and how many of the statements are in fact, similar. This might allow you to leverage another database option (forced parameterization) and\/or determine that you really NEED stored procedures. However, that might end up being more complex as it will require rewriting the app (or at least some <\/em>of the app). Again, the good news here is that you'll be able to tell which <\/em>part of the app.\n<\/p>\n

\nTake for example the following:\n<\/p>\n

\n\tDBCC FREEPROCCACHE
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripp';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripped';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripper';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripps';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Falls';
\n\tgo <\/p>\n

\n\tSELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text –, *
\n\tFROM sys.dm_exec_cached_plans AS cp
\n\tCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
\n\tWHERE cp.objtype IN ('Adhoc', 'Prepared')
\n\t        AND st.text LIKE '%from dbo.member%' 
\n\t        AND st.text NOT LIKE '%SELECT cp.objecttype%'
\n\tORDER BY cp.objtype
\n\tgo\n\t<\/p>\n

\n\tobjtype       cacheobjtype          size_in_bytes   refcounts  usecounts     text
\n\t<\/strong>Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Falls'; 
\n\tAdhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripps'; 
\n\tAdhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripper'; 
\n\tAdhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripped'; 
\n\tAdhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripp'; \n\t<\/p>\n<\/blockquote>\n

\nThe first thing to notice is that each of these queries has a *different* statement in the adhoc plan cache. The second is to recognize that each of these statements take 24K in the plan cache. If we were to change to "optimize for ad hoc workloads" then things might be a bit better:\n<\/p>\n

\n\tsp_configure 'optimize for ad hoc workloads', 1
\n\tgo
\n\treconfigure
\n\tgo
\n\tDBCC FREEPROCCACHE
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripp';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripped';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripper';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Tripps';
\n\tgo
\n\tSELECT * FROM dbo.Member WHERE Lastname = 'Falls';
\n\tgo <\/p>\n

\n\tSELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text –, *
\n\tFROM sys.dm_exec_cached_plans AS cp
\n\tCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
\n\tWHERE cp.objtype IN ('Adhoc', 'Prepared')
\n\t        AND st.text LIKE '%from dbo.member%' 
\n\t        AND st.text NOT LIKE '%SELECT cp.objecttype%'
\n\tORDER BY cp.objtype
\n\tgo\n\t<\/p>\n

\n\tobjtype       cacheobjtype         size_in_bytes   refcounts  usecounts     text
\n\t<\/strong>Adhoc         Compiled Plan Stub  320                 2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Falls'; 
\n\tAdhoc         Compiled Plan Stub  320                 2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripps'; 
\n\tAdhoc         Compiled Plan Stub  320                 2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripper'; 
\n\tAdhoc         Compiled Plan Stub  320                 2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripped'; 
\n\tAdhoc         Compiled Plan Stub  320                 2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripp'; \n\t<\/p>\n<\/blockquote>\n

\nBut, this is not the only issue. Even though each statement will only be in the cache once and will only take 320 bytes (instead of 24K) the cumulative effect of this might be significant. So, how can you see this? If you try to aggregate over these statements then you'll only be able to use the first 40-50 characters while that works here it definitely won't work for more complex statements. Until – SQL Server 2008. In 2008, you can use the query_hash to find similar statements and their overall affect on the query cache.\n<\/p>\n

<\/p>\n

\n

\n\tSELECT qs2.query_hash AS [Query Hash]
\n\t        , SUM(qs2.size_in_bytes) AS [Total Cache Size]
\n\t        , SUM(qs2.total_worker_time)\/SUM(qs2.execution_count) AS [Avg CPU Time]
\n\t        , SUM(qs2.total_elapsed_time)\/SUM(qs2.execution_count) AS [Avg Duration]
\n\t        , COUNT(*) AS [Number of plans] 
\n\t        , MIN(qs2.statement_text) AS [Statement Text]
\n\tFROM (SELECT qs.*, cp.size_in_bytes, SUBSTRING(ST.text, (QS.statement_start_offset\/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
\n\t                ELSE QS.statement_end_offset END – QS.statement_start_offset)\/2) + 1) AS statement_text 
\n\t                FROM sys.dm_exec_cached_plans AS cp 
\n\t                        JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
\n\t                CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as qs2
\n\tGROUP BY qs2.query_hash ORDER BY [Avg Duration] DESC;\n\t<\/p>\n<\/blockquote>\n

\nThis is incredibly important because this will give you the information to determine what SIMILAR query (or queries) are consuming the cache and which ones have the highest cumulative effect. And, if you see a statement that is consistent and can be optimized using indexes (and consistently using the same indexes) what you might try first is "forced parameterization." This is a DATABASE-level option (available in 2005+) that significantly improves but still doesn't guarantee that a statement will be parameterized. See the Books Online for Forced Parameterization (SQL Server 2008 Forced Parameterization<\/a> and SQL Server 2005 Forced Parameterization<\/a>) for the rules on when SQL Server parameterizes. However, there are many statements that will. If you have A LOT of plan cache bloat AND you think your plans might benefit from forced parameterization – this is definitely something you can try.\n<\/p>\n

\nAgain, the good news here is that the QP will try harder to parameterize but there are still many cases where it won't. In this case, SQL Server parameterizes this statement (it becomes a Prepared statement) as:\n<\/p>\n

\n

\n\t(@0 varchar(8000))select * from dbo . Member where Lastname = @0\n\t<\/p>\n<\/blockquote>\n

\nYou can see this through the following query\/results:\n<\/p>\n

<\/p>\n

\n

\n\tSELECT st.text, cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, qp.query_plan –, *
\n\tFROM sys.dm_exec_cached_plans AS cp
\n\t        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
\n\t        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
\n\tWHERE cp.objtype IN ('Adhoc', 'Prepared')
\n\t        AND st.text LIKE '%member%'
\n\tORDER BY cp.objtype
\n\tgo\n\t<\/p>\n<\/blockquote>\n

\n
\n\n\n\n\n\n\n\n\n\n
text<\/strong><\/td>\nobjtype<\/strong><\/td>\ncacheobjtype<\/strong><\/td>\nsize_in_bytes<\/strong><\/td>\nrefcounts<\/strong><\/td>\nusecounts<\/strong><\/td>\nquery_plan<\/strong><\/td>\n<\/tr>\n
SELECT * FROM dbo.Member WHERE Lastname = 'Falls';  <\/span><\/td>\nAdhoc<\/td>\nCompiled Plan<\/td>\n24576<\/td>\n2<\/td>\n1<\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM dbo.Member WHERE Lastname = 'Falls';&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" ParameterizedPlanHandle="0x0600050088DDF510B8C0630A000000000000000000000000" ParameterizedText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" \/><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n
SELECT * FROM dbo.Member WHERE Lastname = 'Tripps';  <\/span><\/td>\nAdhoc<\/td>\nCompiled Plan<\/td>\n24576<\/td>\n2<\/td>\n1<\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM dbo.Member WHERE Lastname = 'Tripps';&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" ParameterizedPlanHandle="0x0600050088DDF510B8C0630A000000000000000000000000" ParameterizedText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" \/><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n
SELECT * FROM dbo.Member WHERE Lastname = 'Tripper';  <\/span><\/td>\nAdhoc<\/td>\nCompiled Plan<\/td>\n24576<\/td>\n2<\/td>\n1<\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM dbo.Member WHERE Lastname = 'Tripper';&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" ParameterizedPlanHandle="0x0600050088DDF510B8C0630A000000000000000000000000" ParameterizedText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" \/><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n
SELECT * FROM dbo.Member WHERE Lastname = 'Tripped';  <\/span><\/td>\nAdhoc<\/td>\nCompiled Plan<\/td>\n24576<\/td>\n2<\/td>\n1<\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM dbo.Member WHERE Lastname = 'Tripped';&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" ParameterizedPlanHandle="0x0600050088DDF510B8C0630A000000000000000000000000" ParameterizedText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" \/><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n
SELECT * FROM dbo.Member WHERE Lastname = 'Tripp';  <\/span><\/td>\nAdhoc<\/td>\nCompiled Plan<\/td>\n24576<\/td>\n2<\/td>\n1<\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM dbo.Member WHERE Lastname = 'Tripp';&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" ParameterizedPlanHandle="0x0600050088DDF510B8C0630A000000000000000000000000" ParameterizedText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" \/><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n
(@0 varchar(8000))select * from dbo . Member where Lastname = @0<\/strong><\/td>\nPrepared<\/strong><\/td>\nCompiled Plan<\/strong><\/td>\n40960<\/strong><\/td>\n7<\/strong><\/td>\n10<\/strong><\/td>\n<ShowPlanXML xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan" Version="1.1" Build="10.0.2531.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="(@0 varchar(8000))select * from dbo . Member where Lastname = @0" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00657038" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xDEF0805C7B74A31E" QueryPlanHash="0x785C8E320D853B97" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" \/><QueryPlan CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="136"><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="173" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="lastname" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="firstname" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="middleinitial" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="street" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="city" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="state_prov" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="country" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="mail_code" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="phone_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="photograph" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="issue_dt" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="expr_dt" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="region_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="corp_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="prev_balance" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="curr_balance" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_code" \/><\/OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><\/OuterReferences><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="lastname" \/><\/OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="lastname" \/><\/DefinedValue><\/DefinedValues><Object Database="[credit]" Schema="[dbo]" Table="[member]" Index="[IX_Member_Lastname]" IndexKind="NonClustered" \/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="lastname" \/><\/RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@0]"><Identifier><ColumnReference Column="@0" \/><\/Identifier><\/ScalarOperator><\/RangeExpressions><\/Prefix><\/SeekKeys><\/SeekPredicateNew><\/SeekPredicates><\/IndexScan><\/RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="161" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="10000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="firstname" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="middleinitial" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="street" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="city" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="state_prov" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="country" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="mail_code" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="phone_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="photograph" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="issue_dt" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="expr_dt" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="region_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="corp_no" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="prev_balance" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="curr_balance" \/><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_code" \/><\/OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="firstname" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="middleinitial" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="street" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="city" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="state_prov" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="country" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="mail_code" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="phone_no" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="photograph" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="issue_dt" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="expr_dt" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="region_no" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="corp_no" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="prev_balance" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="curr_balance" \/><\/DefinedValue><DefinedValue><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_code" \/><\/DefinedValue><\/DefinedValues><Object Database="[credit]" Schema="[dbo]" Table="[member]" Index="[member_ident]" TableReferenceId="-1" IndexKind="Clustered" \/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><\/RangeColumns><RangeExpressions><ScalarOperator ScalarString="[credit].[dbo].[member].[member_no]"><Identifier><ColumnReference Database="[credit]" Schema="[dbo]" Table="[member]" Column="member_no" \/><\/Identifier><\/ScalarOperator><\/RangeExpressions><\/Prefix><\/SeekKeys><\/SeekPredicateNew><\/SeekPredicates><\/IndexScan><\/RelOp><\/NestedLoops><\/RelOp><ParameterList><ColumnReference Column="@0" ParameterCompiledValue="'Tripp'" \/><\/ParameterList><\/QueryPlan><\/StmtSimple><\/Statements><\/Batch><\/BatchSequence><\/ShowPlanXML><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n<\/div>\n

\nIdeally, you'll end up with less wasted cache because all adhoc statements from here – will use the Prepared version of the statement. This also means that they'll run with a compiled plan (so you'll also save compile time) and – if the plans are consistent then all of this is good. However, if other values (for example 'Smith') require a completely different plan – then this could actually be worse (this is the "It Depends" part). But, if you still have statements that do have consistent execution plans (because you know the data and\/or you know exactly how this particular data is being used) then EITHER sp_executesql or a stored procedure would effectively force the parameterization as well and it would say to me that you really understand your data\/application. But, if you're wrong… anytime you force the parameterize (and increase re-use of a plan – you could end up forcing the re-use of a bad (and possibly REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY <\/strong>bad plan) :)).\n<\/p>\n

\nSo, while you solve one problem – you could create another one (see the blog post titled: Using the OPTION (RECOMPILE) option for a statement<\/a> for more information). If the plans are NOT consistent then forcing a plan (through either forced parameterization, sp_executesql OR a regular stored procedure) can result in poor performance. When writing the stored procedure (and through good testing processes), you'll be able to create better and more effective code. This is truly my preferred method. And, I'm just about to wrap up my series with roughly 3 more posts (at least that's what I currently have planned but you guys have certainly been steering me in this direction for a bit :-)).\n<\/p>\n

\nThanks for reading,
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

OK, I've had a bit of a tangent going on optimizing stored procedures and its all revolved around adhoc plan caching and potentially wasted cache due to the single-use plans created (see the most recent posts titled:Plan cache and optimizing for adhoc workloads and Plan cache, adhoc workloads and clearing the single-use plan cache bloat). To […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,50,53,62,65,66],"tags":[],"class_list":["post-504","post","type-post","status-publish","format-standard","hentry","category-dynamic-string-execution","category-optimizing-procedural-code","category-plan-cache","category-sp_executesql","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/504","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=504"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/504\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=504"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=504"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=504"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}