Post reply

Too Many Indexes, Deleting those not needed

  • November 18, 2008 at 8:49 am

    #75366

    So, I'm pretty sure that some of my huge tables in some databases have many unused indexes. I think they got created because the databases were cloned from other databases that did need the indexes. I've been looking through all the Stored Procs, and almost all the joins and "order by" statements just use a couple of columns. Any suggestions for other things to look at ?

    Once I identify and delete the indexes, are there any other steps I should take to optimize their deletion ?

  • November 18, 2008 at 10:22 am

    #900061

    I'm not sure how you'd identify unused indexes; hopefully someone has an idea on how to do that.

    if you use the search function and poke around the Scripts section for "duplicate indexes" , there's a quite a few useful items in there;

    two that i liked were the once that found duplicates, where you have one index that has something like Firstname,Lastname, but another that has those columns plus others, like Firstname,LastName,State

    http://www.sqlservercentral.com/scripts/Miscellaneous/31004/

    there's another that i used before that found reverse indexes...like one index is Firstname,Lastname, and another is LastName,Firstname.

    Hope that helps

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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