Post reply

recompiled stored procedures

  • October 25, 2006 at 10:17 am

    #97886

    Hi all,

    Is there is some way to get set of recompiled stored procedures of some

    dataBase

    in some period of time??

    I heard that this posible through system views in Sql Server 2005.

    Any suggestions??

    TNX in advance???

    . . .

  • October 25, 2006 at 11:03 am

    #667926

    My recomendation would be to use profiler.

    The event you want is under stored procedures, sp:recompile

    You want to catch the object name, the event subclass (which gives you the reason for the recompile) and the start time.

    There's probably a dm view for this in 2005, I don't know one off hand. Profiler will work in both 2000 and 2005.

    HTH

    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

  • October 25, 2006 at 3:49 pm

    #668028

    TNX Gail,

     

    But i'm looking something to run and get results.

    There are some new views in sql 2005, however i can't find any

    stuff.

     

     

    . . .

  • October 25, 2006 at 11:41 pm

    #668089

    I glanced through the dm views and I can't find anything there either.

    You can set profiler to log to a table (provided your server's not too busy) and then just query the table.

    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

  • October 26, 2006 at 12:09 am

    #668090

    TNX Gail,

     

    It's good idea. Can u please provide the needable

    steps for performing logging (inserting to table) from profiler, and also catch only so:recompile event, cause i'm newbie in DB

     

     

    Thanks in advance !!!

     

    . . .

  • October 26, 2006 at 12:42 pm

    #668329

    It's fairly simple

    Open profiler and create a new trace.

    Connect to the server you want to profile.

    On the first screen check the box that says save to table. Enter the table that the trace is to be saved to. The table shouldn't exist.

    Be careful and don't do this on a server that's under heavy load. It's heavier than any of the other ways to save a trace.

    If your server is under heavy load you can save the trace to a file and later import it into a table. (to do this load a saved trace into profiler and select file->save as-> trace table)

    If all you're looking for is recompiles, then the sp:recompile event and the columns I indicated earlier are sufficient.

    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

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

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