Post reply

Clean up indexes.

  • January 31, 2005 at 10:36 am

    #163180

    Greetings all,

    One the system I have taken over, I found out that someone created a bunch of indexes a while back.

    His method was

    "Select 'Create index IX_' + Name + ' on ' + Object_Name(ID) + ' (' + Name + ')' From SysCOlums bla bla bla Where name like '%ID%'

    This went and created thousands of indexes on all columns with the word "ID" in.

    Now, the system is suffering badly duing largisth updates, delete and inserts. As you could imagine with that many indexes. (Around 300-350 tables)

    In a couple of the queries I played with, the indexes are not being used (Book mark lookup to expensive) and it's doing a plain table scan.

    Anyone know of a funky way to see when last a index was used? Outdated stats maybe?

    Sort of going through all the code for each table (Near impossible) I dunno.

    Don't wanna drop them all and slowly recreate either. That would be worse.

     

    Cheers,

    Crispin

     

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • January 31, 2005 at 2:15 pm

    #539801

    - Are your stats up to date ? (can you execute sp_updatestats / dbcc updateusage )

    - I'd start off with all indexes which have a minor filter ratio (cardinality).

    - you might also capture acivity (profiler) with exe-plan, so you can find out which indexes are actualy in use. (is your capture periode representatif for db usage ?)

    I hope this gets you on the road ..

    Check out : http://www.sqlservercentral.com/columnists/AVigneau/boostyourperformancebadindexdetection.asp

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 31, 2005 at 2:50 pm

    #539810

    dbcc showconitg will also show you how fragmented the indexes are and could point out potential performance issues.

    Tom

  • February 1, 2005 at 9:24 am

    #539962

    Just to find out when things were created maybe something like:

     

    select name, crdate from sysobjects

    where name like 'IX_%ID%'

    order by crdate desc

     

    Not pretty or slick code. Just maybe what you asked for ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 3, 2005 at 1:32 pm

    #540502

    Might be wrong, but I think I have seen something useful on SQL Server Performance. Due to my f@%$&ng slow connection, I leave it up to you to look it up there. Cheap revenge for your you-know-which thread, Crappy.

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

  • February 3, 2005 at 2:06 pm

    #540516

    No but I would like to know which thread?

  • February 3, 2005 at 3:01 pm

    #540530

    No discussion. I think it was an article there.

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

  • February 3, 2005 at 9:10 pm

    #540582

    Do you still have the link?

  • February 4, 2005 at 8:40 am

    #540693

    Not sure if this is what Frank was referring to but it has some relevant information.http://www.sql-server-performance.com/lm_index_elimination_english.asp

  • February 7, 2005 at 1:26 am

    #540858

    Hi All,

    Thanks for the replies. Sorry I have not responded but things have been a bit hectic. Partly due to rubish indexing.

    The link / script that alzdba posted actually opened my eyes. While I have not explored the whole meaning of it, it did make my eyes pop out when I saw a table with an indid of 12. The table only has 5 columns on of which is a PK. When I looked at the table, duplicate indexes every where. Someone even has the sense to name it Branch_ID, Branch_ID_1, Branch_ID_2 etc etc.

    This got me wondering and I slapped together a script (Whch I will share once complete) which lists tables with more than one indexe on a column. Not bullet proof as this could be acceptable but points out potential problems.

    The simpilest thing for me to do now is list out all indexes / table which may have a problem, inspect the indexes and drop duplicates.

    I'll go through the other articles and see what they have to say.

    One addition I would like SQL 2005 to have is an extra column in sysindexes logging the name, address and phone number of the git who added them. This way I can send "friends" round to his house and "chat to him"

    Just kidding

     

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • February 7, 2005 at 3:00 am

    #540866

    Yes, that was the link. I must admit, I haven't read it though

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

Viewing 11 posts - 1 through 10 (of 10 total)

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