Post reply

Unused Indexes, tables, views, SPs

  • June 2, 2003 at 7:29 am

    #58502

    Is there any way for me to find out if there are any indexes, tables, views, Stored procedures etc which are not being used by application? Say in the last 3 months or 6 months?

    Thanks,

    AD

  • June 2, 2003 at 7:39 am

    #459332

    Hi AD,

    what about sp_depends <objectname>, though it does not work on indexes.

    See also BOL for sp_depends!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 2, 2003 at 8:28 am

    #459351

    You have to run Profiler for the period of time as you like to trace which tables, views and SPs have been used and compare the results to your database.

    Use Index Wizard on your trace results to see the recommanedation which indexes sould be added and removed.

  • June 3, 2003 at 1:43 am

    #459468

    Does the Index Tuning wixard effect performance whilst you are running it? or should I wait until out of hours to run it ?


    Growing old is mandatory, growing up is optional

  • June 3, 2003 at 3:53 am

    #459488

    quote:

    Does the Index Tuning wixard effect performance whilst you are running it? or should I wait until out of hours to run it ?

    It does put a load on the system so I would suggest an after hours approach.

  • June 3, 2003 at 7:40 am

    #459536

    If you wanted to know whether or not an index had been used within a 6 month time period, wouldn't you have to run profiler for 6 months? Maybe I'm missing something.

    Thanks...

    Kevin Lawrence

  • June 3, 2003 at 7:56 am

    #459544

    I suppose its not a definitive report. I'm going down the route of running traces at various times of day over a period of a week or so. This will hopefully give me a list that I can then go back to the developers with, they can then review what is still in use or not. I'm assuming they will know of course !


    Growing old is mandatory, growing up is optional

  • June 3, 2003 at 8:00 am

    #459547

    what I do if I want to clean:

    - change name of SP,table,view with "delete_" string in front- after you are SURE you have talked this thru

    - make sure that your developers have a list of those changed names.

    - if no one complains for a given time period(that depends on your database type and use) get rid of them

    - for indexes run SQL till you drop(from the APP of course).If they are not used then change the index to fit the requirements

    Regards

    lmt

Viewing 8 posts - 1 through 7 (of 7 total)

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