SQL 2005 Unused high maintenance cost indexes

Last Post 26 Nov 2008 12:04 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

09 Sep 2008 12:47 PM
If unused index is also the high maintenance cost index then I assume it is better to drop it. I am not sure if I should remove an unsed index in our database. Here are the details...

We have an audit table with a primary key. This Audit table has a clustered index on the primary key. Mostly we only insert into this audit table. We query this table only once in a while when clients requests for some history data. In last 2 months we never queried this table so this index shows up in unused index and high maintenance cost index list.

SHOULD I REMOVE THIS INDEX (to improve the inserts performance) OR KEEP IT (for querying the table when clients request)??

My another concern is if I remove the only index from the table then in few days this table will be fragmented.

I used the scripts from "http://msdn.microsoft.com/en-us/mag...35978.aspx" to determine the high cost indexes
New Member
New Member

20 Nov 2008 02:53 AM
While I agree with gunneyk's logic, I am a performance freak.... I would remove the index on a trial basis.

If the occasional query performance would become an issue, it can easily be put back.

Kalman Toth, SQL Server 2008
New Member
New Member

26 Nov 2008 12:04 AM
I agree with Peter on PK requirements on OLTP tables.

However, non-OLTP tables (2nd hand data) generally do not require Primary Key and 3NF design. Example: audit, logging, history, reporting and staging tables.

While Primary Key requires a unique constraint, it does not have to be a clustered index. It can be an NC index.

If you are defining a table like:

create table Celebrity (
CelebrityID int identity(1,1) primary key,
Name varchar(64) )

SQL Server automatically creates a clustered index on CelebrityID.

I can define a second index on CelebrityID:

CREATE UNIQUE NONCLUSTERED INDEX [idxCelebrityUnique] ON [dbo].[Celebrity]
[CelebrityID] ASC

and remove the clustered index, and place it on a different column(s).

Kalman Toth, SQL Server 2008 Training

Acceptable Use Policy