Results 1 to 3 of 3

Thread: index issues

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: index issues

    I would like to know what are the index exists on which table in my entire database.And also i want to rebuild some of them.
    How can i do these two things.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can use sp_helpindex tablename to find the indexes on a particular table. This will also give you the columns and the order of them in the index. As for rebuilding them, you have three options. Drop and rebuild, dbcc dbreindex, and dbcc indexdefrag. All three have their pluses, minus, and idiosyncracies. Generally I use them in the following way:

    dbcc dbreindex: Small tables. This will create a copy of all of the indexes (remember clustered indexes are really the table itself), and do a quick switch to the new copies.

    dbcc indexdefrag: Large indexes where the index is alone in a filegroup. Also, if the database is in simple recovery mode. I am not terribly impressed with the efficiency of indexdefrag, but it does not lock up the table. This is highly valuable for systems that can not be brought down for maintenance. On the downside, it will run your transaction log pretty well, if you are not in simple recovery mode.

    drop/rebuild. A decent all purpose tool, but you need to have a bit of downtime available to you, as the tables will have select locks on them, while you rebuild the indices. You also need to have the scripts for the indices handy (which is why I encourage the use of sp_helpindex), in case anything goes wrong. Remember also, the clustered index should be the last to be dropped, and the first to be rebuilt. Also, note. This does not work on Primary Key indices that have a foreign key referencing them.

    Hope this helps.

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    Thank u very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •