Post reply

which sp's are using

  • January 5, 2012 at 10:52 pm

    #262568

    Hi Folks,

    We have a SQL 2005 Database in that around 750 sp's are there, in those 750 sp's some are duplicated.

    in coming days we are planning to migrate database to other instance so i would like to know is there any query to find out which sp's are using in last six months.

    TIA..

  • January 5, 2012 at 11:56 pm

    #1429461

    Hi,

    there is no inbuilt functionality to do this. You have to setup some form of pro-active monitor.

    Dave


    Clear Sky SQL
    My Blog[/url]

  • January 6, 2012 at 12:01 am

    #1429462

    You may get this info by querying DMOs, if your server is not restarted in the last 6 months.

  • January 6, 2012 at 2:35 am

    #1429490

    There are no DMVs that store procedure usage info. Some people suggest using the plan cache, but that is fraught with problems - plans could have been aged out, the cache could have been cleared, the plan may never have been cached in the first place.

    About the only way to be absolutely sure of this is to add a line to all procedures that logs execution, then wait 6 months.

    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 4 posts - 1 through 3 (of 3 total)

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