SQL Query Performance

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 1, 2012 at 8:22 am

  #1520308

  GilaMonster (8/1/2012)

  ---

  Yes.

  Great Stuff - Thanks

  I'm finding the performance part of SQL Server to be a very challenging side

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 2, 2012 at 5:11 am

  #1520736

  Grant Fritchey (7/31/2012)

  ---

  SQLSACT (7/31/2012)

  ---

  Grant Fritchey (7/31/2012)

  ---

  The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

  I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

  Am I on the right track here?

  Thanks

  Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.

  Hi

  How do I get the last date/time used for the plans currently in cache? I'm not sure which column to use.

  Thanks

• pooyan_pdm

  Hall of Fame

  Points: 3007

  More actions

  August 2, 2012 at 11:31 am

  #1521033

  You can query sys.dm_exec_query_stats and check the last_execution_time Column

  Pooyan