I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:
ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT [PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED ( [BATCH_ID] ASC, [DATE_CREATED] ASC, [CONTACT_ID] ASC, [LAB_CODE] ASC, [DISTRIBUTION_TYPE] ASC, [CREATOR] ASC )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:
SELECT TOP (1000) [BATCH_ID] --- this is a varchar(100) field ,[LAB_CODE] ,[DISTRIBUTION_TYPE] ,[CONTACT_ID] ,[CREATOR] ,[DESCRIPTION] ,[DATE_CREATED] ,[DATE_COMPLETED] ,[DATE_DEADLINE] ,[DATE_CONFIRMED] ,[ERROR_CODE] ,[CONTENT] ,[ADDED_BY] ,[SOURCE] ,[STATUS] ,[DELIVERY_METHOD] ,[FILE_SIZE] ,[DISTRIBUTION_FORMAT] ,[CONTACT_ID_ORIGINAL] FROM [dbo].[Distribution_Batch]
All this table does is queue up distribution jobs to be run by our application. Nothing fancy.
They have indexes on numerous columns:
[DistributionBatch-ContactId-DistributionType-20161108-121538] [idx_date_completed] [idx_date_created] [idx_DistribBatch_BatchId] [idx_Distribution_Contacts] [IX_Distribution_Batch] [IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED] [IX_Distribution_Batch_LAB_CODE_STATUS] [IX_Distribution_Batch_STATUS] [IX_Distribution_Batch_STATUS_DELIVERY_METHOD] [PK_Distribution_Batch_1__23]
The system overall is slow and the admins response has always been to add more indexes to each table. This table spits out data sequentially based on whether it is completed or not. I can't understand why they would have so many indexes on columns that are never sorted on. Am I missing something?
My question is 2 parts:
Part 1) Does that primary key make any sense? Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?
Part 2) I need confirmation that none of those indexes make sense and are not necessary. There are a lot more tables that have this same issue.
This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.