0

I am wondering about the best way to design indexes. For Example i have this table

CREATE TABLE [dbo].[CoursePrerequisiteAssignment](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PrerequisiteId] [int] NOT NULL,
[CourseId] [int] NULL,
[ProfileId] [int] NOT NULL,
[InstanceId] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[ProgramSelectedId] [int] NULL,
[OptOut] [bit] NULL,
 CONSTRAINT [PK_CoursePrerequisiteAssignment] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)
) ON [PRIMARY]

GO


CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment] ON [dbo].     [CoursePrerequisiteAssignment]
(
[ProfileId] ASC,
[InstanceId] ASC
)
INCLUDE (   [CourseId]) GO


CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_1] ON [dbo].[CoursePrerequisiteAssignment]
(
[CourseId] ASC
)
 GO


CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_2] ON [dbo].[CoursePrerequisiteAssignment]
(
[ProgramSelectedId] ASC
)
 GO


CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_3] ON [dbo].[CoursePrerequisiteAssignment]
(
[ProgramSelectedId] ASC,
[PrerequisiteId] ASC,
[InstanceId] ASC,
[ProfileId] ASC
)
 INCLUDE (  [CourseId],
[CreatedDate],
[UpdatedDate],
[OptOut]) 
 GO


CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_4] ON [dbo].[CoursePrerequisiteAssignment]
(
[CourseId] ASC,
[PrerequisiteId] ASC,
[ProfileId] ASC,
[InstanceId] ASC,
[ProgramSelectedId] ASC
)
GO

This Table is very active and has high reads and writes. this is the usage of these indexes

enter image description here

note : the counts for IX_CoursePrerequisiteAssignment is not accurate because i just rebuild it

These columns are the predicts for the top executed queries

ProgramSelectedId,ProfileId,PrerequisiteId,InstanceId
CourseId,PrerequisiteId,ProfileId,InstanceId,ProgramSelectedId
ProfileId,InstanceId
ProfileId,InstanceId,ProgramSelectedId
ProgramSelectedId,ProfileId,id,InstanceId
CourseId

I see that i don't need all these indexes as they overlapping. What is your recommendation for best performance ?

0

1 Answer 1

1

Analyze execution plan of most popular queries to see which indexes are in use and which can be removed. - https://dba.stackexchange.com/users/94941/alex-kudryashev

Based on the info here, I would think you could remove the following:

'ProfileId,InstanceId' and 'ProgramSelectedId,ProfileId,id,InstanceId' and 'CourseId'

But there may be specific queries that are using those indexes, they will start to use the ones that are left (With the same leading column) IO may go up slightly, so you should evaluate for your specific workload - https://dba.stackexchange.com/users/93035/chad-mattox

You could look into queries that use the predicates to see if the order of search conditions can be optimized further, and analyze the existing indexes to see if they properly support the popular queries. And eliminate unnecessary indices. This might help. https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes - https://dba.stackexchange.com/users/78842/kentaro

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.