How to get the query which need to be compiled

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  November 13, 2009 at 12:34 pm

  #217621

  Hi all,

  I got high numbers in SQL Comiplations/Sec and lower number in SQL re-Compilatios/sec using Perfmon.

  Some articles said that high SQL Comiplations/Sec means low rate of plan reuse. I would like to Capture the queries which need to compile and never be reused. Then I might have a clue why this happens.

  SQL Server 2005, windows 2003. in this instance, there are more than 80 databases.

  Thanks.

  Judy

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  November 14, 2009 at 2:15 pm

  #1079411

  Judy-363345 (11/13/2009)

  ---

  Hi all,

  I got high numbers in SQL Comiplations/Sec and lower number in SQL re-Compilatios/sec using Perfmon.

  Some articles said that high SQL Comiplations/Sec means low rate of plan reuse. I would like to Capture the queries which need to compile and never be reused. Then I might have a clue why this happens.

  SQL Server 2005, windows 2003. in this instance, there are more than 80 databases.

  Thanks.

  Judy

  In SQL Profiler SP:Recompile will show you which stored procedures recompile.

  Alex S

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 14, 2009 at 2:23 pm

  #1079413

  ... SQL:StmtRecompile for custom SQL.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  Follow

  November 15, 2009 at 4:32 am

  #1079449

  Also check out the Plan cache miss and plan cache insert events (also in profiler)

  I don't recall if the SP:Recompile and TSQL:StmtRecompile show compiles or just recompiles. Test them out.

  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

• Paul White

  SSC Guru

  Points: 150468

  More actions

  November 16, 2009 at 6:55 pm

  #1079995

  For full details click here

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi