I've inherited a database (MSSQL 2008R2) with many tables that either have NO primary key or primary key that looks like this:
OLD TABLE [dbo],[Distribution_Batch] ADD CONSTRAINT
[PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the table itself, these are the columns:
CHOOSE TOP (1000) [BATCH_ID] --- This is a varchar (100) field
This table queues only distribution jobs that are run by our application. Nothing special.
You have indexes on many columns:
Overall, the system is slow and the administrators' response has always been to add more indexes to each table. This table spits out data sequentially, depending on whether they are completed or not. I can not understand why there are so many indices for columns that are never sorted by. Am I missing something?
My question is 2 parts:
Part 1) Is this primary key useful? Should the primary key not just be an ID (int), starting with 1 with identity specification and auto-incrementing?
Part 2) I need confirmation that none of these indices makes sense and is not necessary. There are many more tables that have the same problem.
This database includes 190 GB of data and 101 GB of indices.
All comments and opinions are greatly appreciated.