CODEWITHSUNDEEP
sqlsql-server
ncooper09
ncooper09

Reputation: 113

How do you know if you need multiple indexes or one bigger index?

I have been tasked with optimizing a db that is mostly read, and I was given the lead of using DMV queries by Glenn Berry. Well, I'm looking at 3 "missing indexes" that might be helpful, but they look so similar. They're the same table, and it looks like one large index would cover everything needed across the 3 possible indexes.

How do I know if I should create one for all of them, or if I should create individual indexes (in which case I would only create 1 or 2 of them)?

Second, Is there a way to tell how many commonly executed queries would use a potential index?

I haven't as of yet gotten to learning how to actually create them, I have been focusing on if I should make them. What I am going off of is information from Berry's DMV script for Missing Indexes.

index_advantage
Index 1- 24291.95
Index 2- 830.71
Index 3- 21.51

equality_columns
Index 1- [MessageType],[ConnectionID],[EndDateTime],[ManualEndDateTime],[SupportClose]
Index 2- [MessageType],[EndDateTime],[ManualEndDateTime],[SupportClose]
Index3- [ConnectionID]

included_columns
Index 1- NULL
Index 2- NULL
Index 3- [ID], [Username], [MachineName], [Message], [MessageType], [ConnectionName], [ConnectionTypeName], [EndDateTime], [LoggedUserName], [GroupName], [StartDateTimeUTC], [EndDateTimeUTC], [Cost], [Comment], [Prompt], [Data], [ManualEndDateTime], [UserInfoID], [ManualClosedBy], [SecurityGroup], [SupportClose], [OpenMode], [CloseMode], [HostName], [IsEmbedded], [ClosePrompt], [Status], [ActivityDuration], [DataSourceID], [CreationDate], [ActiveTime], [Application], [Version], [IsPrivate], [PrivateUserID], [CustomerID], [ConnectionUsername]

Table is the same for all 3, inequality_columns is the same for all 3.

Upvotes: 1

Views: 53

Answers (1)

ncooper09
ncooper09

Reputation: 113

It seems as though the answer to this is going to be far more complex than I was hoping it would be. I think I'm going to have to learn a lot more myself and apply what I learn in order to do this successfully.

Thanks everyone for your comments!

Upvotes: 1

Related Questions