Post reply

How to get the query which need to be compiled

  • 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

  • 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

  • November 14, 2009 at 2:23 pm

    #1079413

    ... SQL:StmtRecompile for custom SQL.

  • 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

  • November 16, 2009 at 6:55 pm

    #1079995

    For full details click here

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply