Plan Cache Performance

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 14, 2012 at 5:41 am

  #267766

  Hi All

  I'm using the below query to investigate plan re-use on my SQL Server

  How accurate are the counters used here?

  Has anyone worked with this query or something similar

  select t1.cntr_value As [Batch Requests/sec],

  t2.cntr_value As [SQL Compilations/sec],

  plan_reuse =

  convert(decimal(15,2),

  (t1.cntr_value*1.0-t2.cntr_value*1.0)/t1.cntr_value*100)

  from

  master.sys.dm_os_performance_counters t1,

  master.sys.dm_os_performance_counters t2

  where

  t1.counter_name='Batch Requests/sec' and

  t2.counter_name='SQL Compilations/sec'

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 14, 2012 at 9:14 am

  #1537937

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  "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

• pooyan_pdm

  Hall of Fame

  Points: 3007

  More actions

  September 14, 2012 at 1:05 pm

  #1538099

  Initial Compilation='SQL Compilations/sec'-'SQL ReCompilations/sec'

  plan reuse= ( 'Batch Requests/sec' - 'Initial Compilation')/'Batch Requests/sec'

  this one is more accurate

  Pooyan

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 17, 2012 at 12:02 am

  #1538403

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 17, 2012 at 12:07 am

  #1538404

  pooyan_pdm (9/14/2012)

  ---

  Initial Compilation='SQL Compilations/sec'-'SQL ReCompilations/sec'

  plan reuse= ( 'Batch Requests/sec' - 'Initial Compilation')/'Batch Requests/sec'

  this one is more accurate

  Thanks

  How do I incorporate this into the script?

  I keep getting this error

  Operand data type varchar is invalid for subtract operator.

  Thanks

• pooyan_pdm

  Hall of Fame

  Points: 3007

  More actions

  September 17, 2012 at 12:56 am

  #1538415

  You should use the actual counter values from the sys.dm_os_performance counters for the three mentioned counters and for checking wheather the cache is bloating or not you can check the sum of size column from sys.dm_exec_cached_plans where objtype in('prepared','adhoc') and usecount=1 this shows the size of the all adhoc plans that just were used once and cause the cache to bloat.if the size of these plans are considerable you can turn on the 'optimize for adhoc workloads' database option .

  Pooyan

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 17, 2012 at 4:28 am

  #1538492

  pooyan_pdm (9/17/2012)

  ---

  You should use the actual counter values from the sys.dm_os_performance counters for the three mentioned counters and for checking wheather the cache is bloating or not you can check the sum of size column from sys.dm_exec_cached_plans where objtype in('prepared','adhoc') and usecount=1 this shows the size of the all adhoc plans that just were used once and cause the cache to bloat.if the size of these plans are considerable you can turn on the 'optimize for adhoc workloads' database option .

  Something like this

  SELECT SUM(size_in_bytes)/1024

  FROM sys.dm_exec_cached_plans AS cp

  where objtype in('prepared','adhoc') and usecounts = 1

  Is this accurate for Megabytes?

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 17, 2012 at 5:45 am

  #1538533

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

  Bloated? Not really. Under pressure. Yes. Look at compiles and recompiles. Compare them over time, especially between high volume & low volume times. If you're seeing lots of turnover on the plans, your cache is under pressure.

  "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

  September 17, 2012 at 5:55 am

  #1538536

  Grant Fritchey (9/17/2012)

  ---

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

  Bloated? Not really. Under pressure. Yes. Look at compiles and recompiles. Compare them over time, especially between high volume & low volume times. If you're seeing lots of turnover on the plans, your cache is under pressure.

  Thanks

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  September 17, 2012 at 6:40 am

  #1538563

  Grant Fritchey (9/17/2012)

  ---

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

  Bloated? Not really. Under pressure. Yes. Look at compiles and recompiles. Compare them over time, especially between high volume & low volume times. If you're seeing lots of turnover on the plans, your cache is under pressure.

  Thanks

  Would the following be a fair assessment of how volatile my cache is?

  select top 1 last_execution_time from sys.dm_exec_query_stats

  order by last_execution_time

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 17, 2012 at 6:46 am

  #1538569

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/17/2012)

  ---

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

  Bloated? Not really. Under pressure. Yes. Look at compiles and recompiles. Compare them over time, especially between high volume & low volume times. If you're seeing lots of turnover on the plans, your cache is under pressure.

  Thanks

  Would the following be a fair assessment of how volatile my cache is?

  select top 1 last_execution_time from sys.dm_exec_query_stats

  order by last_execution_time

  Thanks

  Nope, that just says the last time a query was executed in the cache. You could look at the create dates in the cache to get an idea of the age of the plans. That's something of an indicator, but only a very general one.

  "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

  September 17, 2012 at 8:38 am

  #1538645

  Grant Fritchey (9/17/2012)

  ---

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/17/2012)

  ---

  SQLSACT (9/17/2012)

  ---

  Grant Fritchey (9/14/2012)

  ---

  Those are valid counters and will show how your system is behaving. They're the same counters you get from running Performance Monitor. You can also look at sys.dm_exec_query_stats to see aggregate information about the queries that are in cache, including how many times they've been used.

  Thanks

  Is there a way I can tell whether or not my Cache is bloated and under pressure?

  Thanks

  Bloated? Not really. Under pressure. Yes. Look at compiles and recompiles. Compare them over time, especially between high volume & low volume times. If you're seeing lots of turnover on the plans, your cache is under pressure.

  Thanks

  Would the following be a fair assessment of how volatile my cache is?

  select top 1 last_execution_time from sys.dm_exec_query_stats

  order by last_execution_time

  Thanks

  Nope, that just says the last time a query was executed in the cache. You could look at the create dates in the cache to get an idea of the age of the plans. That's something of an indicator, but only a very general one.

  Thanks

  You could look at the create dates in the cache

  Is this the creation_date in the sys.dm_exec_query_stats DMV ?

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 17, 2012 at 8:41 am

  #1538647

  Yes

  "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

• pooyan_pdm

  Hall of Fame

  Points: 3007

  More actions

  September 17, 2012 at 9:44 am

  #1538682

  SQLSACT (9/17/2012)

  ---

  pooyan_pdm (9/17/2012)

  ---

  You should use the actual counter values from the sys.dm_os_performance counters for the three mentioned counters and for checking wheather the cache is bloating or not you can check the sum of size column from sys.dm_exec_cached_plans where objtype in('prepared','adhoc') and usecount=1 this shows the size of the all adhoc plans that just were used once and cause the cache to bloat.if the size of these plans are considerable you can turn on the 'optimize for adhoc workloads' database option .

  Something like this

  SELECT SUM(size_in_bytes)/1024

  FROM sys.dm_exec_cached_plans AS cp

  where objtype in('prepared','adhoc') and usecounts = 1

  Is this accurate for Megabytes?

  Thanks

  . That gives u the size in KB you should devide it again by 1024

  Pooyan