SQL Plan Cache

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 15, 2012 at 8:55 am

  #392954

  Hi All

  I've been using the below script to check my Plan Cache for Ad-hoc plans

  SELECT objtype

  , usecounts

  , COUNT(*) AS [no_of_plans]

  FROM sys.dm_exec_cached_plans

  WHERE cacheobjtype = 'Compiled Plan'

  AND usecounts = '1'

  GROUP BY objtype , usecounts

  ORDER BY objtype , usecounts

  How do I know what I high number is for my system?

  I've also used the below script to check Compilations/sec

  select * from sys.dm_os_performance_counters

  where counter_name like '%compilations%'

  My system reports an amount around 622364035 - Is this normal?, it seems high

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  August 15, 2012 at 7:40 pm

  #1526306

  That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.

  "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

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 15, 2012 at 11:54 pm

  #1526338

  Grant Fritchey (8/15/2012)

  ---

  That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.

  Thanks

  To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

  Thanks

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 15, 2012 at 11:56 pm

  #1526339

  Grant Fritchey (8/15/2012)

  ---

  That number does seem high, but only because I've primarily worked on systems with stored procedures. If you're working with lots of dynamic or ad hoc SQL, then that number can be "normal." The only way to know for sure is to observe the system over time, recording the values, and compare one state to another in order to determine what's "normal" for the situation.

  How do I know when my Cache is bloated? And cannot take anymore

  Does it depend on my memory available or is it dependant on CPU?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 16, 2012 at 2:07 am

  #1526377

  SQLSACT (8/15/2012)

  ---

  To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

  Thanks

  No. Ad-hoc SQL statements have their plans cached and reused just like stored procedures Only difference is the matching is on statement text not object id.

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 2:11 am

  #1526381

  GilaMonster (8/16/2012)

  ---

  SQLSACT (8/15/2012)

  ---

  To make sure I have this right, if a Query is submitted to SQL Server and it's not part of a Stored Procedure, SQL Server has to generate a plan for it everytime?

  Thanks

  No. Ad-hoc SQL statements have their plans cached and reused just like stored procedures Only difference is the matching is on statement text not object id.

  Thanks

  Regarding the 3 select statements below, would SQL use the same plan for the first 2 and a different plan for the 3rd one?

  select Col1 from Table1 where Col1 = '10';

  select Col1 from Table1 where Col1 = '10';

  select Col1 from Table1 where Col1 = '12';

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 16, 2012 at 2:13 am

  #1526383

  In that trivial case, if they are submitted in 3 separate batches, there will be one plan (autoparameterised) used 3 times.

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 2:25 am

  #1526387

  GilaMonster (8/16/2012)

  ---

  In that trivial case, if they are submitted in 3 separate batches, there will be one plan (autoparameterised) used 3 times.

  Thanks

  What would cause SQL Server to generate a seperate plan for the 3rd select statement?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 16, 2012 at 2:28 am

  #1526390

  A query complex enough that it doesn't qualify for autoparameterisation (and this is the point where you go to BoL to see what autoparam is)

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 2:42 am

  #1526398

  GilaMonster (8/16/2012)

  ---

  A query complex enough that it doesn't qualify for autoparameterisation (and this is the point where you go to BoL to see what autoparam is)

  Will do, thanks

  Please help me understand something

  I've executed those select scripts seperately. Then I ran the below script to check the cache:

  SELECT TOP ( 100 ) [text]

  , cp.size_in_bytes

  , cp.usecounts

  FROM sys.dm_exec_cached_plans AS cp

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

  WHERE cp.cacheobjtype = 'Compiled Plan'

  AND cp.objtype = 'Adhoc'

  ORDER BY cp.size_in_bytes DESC

  Results attached

  Then I ran this script to check Query Stats:

  SELECT

  SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

  ( ( CASE statement_end_offset

  WHEN -1 THEN DATALENGTH(t.text)

  ELSE s.statement_end_offset

  END - s.statement_start_offset ) / 2 ) + 1)

  AS statement_text,

  last_execution_time,

  execution_count ,

  statement_start_offset AS stmt_start_offset ,

  total_logical_reads / execution_count AS avg_logical_reads ,

  total_logical_writes / execution_count AS avg_logical_writes ,

  total_physical_reads / execution_count AS avg_physical_reads ,

  t.text,

  qp.query_plan

  FROM sys.dm_exec_query_stats AS s

  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

  CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp

  where SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

  ( ( CASE statement_end_offset

  WHEN -1 THEN DATALENGTH(t.text)

  ELSE s.statement_end_offset

  END - s.statement_start_offset ) / 2 ) + 1) like '%Col1%'

  ORDER BY avg_physical_reads DESC

  Results attached

  The cached_plan script shows 2 plans were generated, one plan shows a usecount of 2 and the other plan shows a usecount of 1

  The Query_stats shows one plan with an execution count of 3

  Which one is correct here or am I missing something?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 16, 2012 at 2:59 am

  #1526406

  Be careful, cached plans will show the unparameterised plan shells as well as the parameterised plans, your filter's eliminating the parameterised plans.

  -- AND cp.objtype = 'Adhoc'

  As I said, you need something more complex if you don't want autoparameterisation happening

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 3:06 am

  #1526411

  GilaMonster (8/16/2012)

  ---

  Be careful, cached plans will show the unparameterised plan shells as well as the parameterised plans, your filter's eliminating the parameterised plans.

  -- AND cp.objtype = 'Adhoc'

  As I said, you need something more complex if you don't want autoparameterisation happening

  Thanks

  I've excluded that filter from the script and it still shows 2 plans being used for the different selects

  What I'm trying to understand is why the cached plan script shows 2 plans and the query_stats script shows 1 plan

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 16, 2012 at 3:31 am

  #1526421

  They're not 2 plans for the selects. I already said what's happening.

  I strongly suggest you go and read SQL 2008 internals, chapter 9.

  Repo:

  DBCC FreeProcCache

  go

  CREATE TABLE t1 (

  Col1 INT,

  Col2 DATETIME DEFAULT GETDATE()

  )

  INSERT INTO t1 (Col1)

  VALUES (1), (2), (3)

  GO

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 3

  GO

  SELECT [text] ,

  cacheobjtype ,

  objtype ,

  cp.size_in_bytes ,

  cp.usecounts

  FROM sys.dm_exec_cached_plans AS cp

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

  WHERE text NOT LIKE '%sys.dm_exec_cached_plans%'

  AND cacheobjtype != 'Parse Tree'

  

  2 unparameterised plan shells and one parameterised plan that is the actual plan that's used

  The query to sys.dm_exec_query_stats just doesn't show the unparameterised shells, because they're not plans, they're just used to redirect the query to the parameterised form.

  As I said, you need something more complex if you don't want autoparameterisation happening and causing this kind of behaviour.

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 4:56 am

  #1526464

  GilaMonster (8/16/2012)

  ---

  They're not 2 plans for the selects. I already said what's happening.

  I strongly suggest you go and read SQL 2008 internals, chapter 9.

  Repo:

  DBCC FreeProcCache

  go

  CREATE TABLE t1 (

  Col1 INT,

  Col2 DATETIME DEFAULT GETDATE()

  )

  INSERT INTO t1 (Col1)

  VALUES (1), (2), (3)

  GO

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 3

  GO

  SELECT [text] ,

  cacheobjtype ,

  objtype ,

  cp.size_in_bytes ,

  cp.usecounts

  FROM sys.dm_exec_cached_plans AS cp

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

  WHERE text NOT LIKE '%sys.dm_exec_cached_plans%'

  AND cacheobjtype != 'Parse Tree'

  

  2 unparameterised plan shells and one parameterised plan that is the actual plan that's used

  The query to sys.dm_exec_query_stats just doesn't show the unparameterised shells, because they're not plans, they're just used to redirect the query to the parameterised form.

  As I said, you need something more complex if you don't want autoparameterisation happening and causing this kind of behaviour.

  Thanks for the help with this

  I'm just trying to understand the behaviour.

  2 unparameterised plan shells and one parameterised plan that is the actual plan that's used

  Shouldn't the usecount for the parameterised plan be 3 because there are 3 queries?

  Thanks

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 6:31 am

  #1526524

  GilaMonster (8/16/2012)

  ---

  They're not 2 plans for the selects. I already said what's happening.

  I strongly suggest you go and read SQL 2008 internals, chapter 9.

  Repo:

  DBCC FreeProcCache

  go

  CREATE TABLE t1 (

  Col1 INT,

  Col2 DATETIME DEFAULT GETDATE()

  )

  INSERT INTO t1 (Col1)

  VALUES (1), (2), (3)

  GO

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 1

  go

  SELECT Col1, Col2 FROM t1 WHERE Col1 = 3

  GO

  SELECT [text] ,

  cacheobjtype ,

  objtype ,

  cp.size_in_bytes ,

  cp.usecounts

  FROM sys.dm_exec_cached_plans AS cp

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

  WHERE text NOT LIKE '%sys.dm_exec_cached_plans%'

  AND cacheobjtype != 'Parse Tree'

  

  2 unparameterised plan shells and one parameterised plan that is the actual plan that's used

  The query to sys.dm_exec_query_stats just doesn't show the unparameterised shells, because they're not plans, they're just used to redirect the query to the parameterised form.

  As I said, you need something more complex if you don't want autoparameterisation happening and causing this kind of behaviour.

  When using the cached_plans DMV, is there any way to filter out system processes?

  Thanks