Can I consolidate missing indexes recommendations from SQL 2008

I'm getting some timeout errors and SQL Server suggested several missing indexes. Here's the table definiton for the columns mentioned in the missing indexes. I've left off all other columns (a few dozen).
CREATE TABLE [dbo].[MyTable](
	[RowID] [int] IDENTITY(1,1) NOT NULL,
	[Deleted] [bit] NOT NULL,
	[Requested] [datetime] NULL,
	[Received] [datetime] NULL,
	[OtherID] [int] NULL,
	[UserID] [int] NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

Open in new window

SQL 2008 suggested the following missing indexes which overlap somewhat. The first one is probably for the query with the biggest impact.

-- Suggested MyTable Missing Indexes:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([Deleted],[Requested])
INCLUDE ([RowID],[OtherID],[UserID],[Received])
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([Deleted],[Requested])
INCLUDE ([RowID],[OtherID],[UserID])
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([OtherID])
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([OtherID],[Deleted])
GO

Open in new window


Which of the suggested indexes are redundant or which ones can I combine (and if so, how)?

Thanks in advance.
LVL 1
ZekeLAAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
on the face of it these 2 indexes would probably suffice

to cover all the range of instances encountered...

however i'm not clear on how useful the deleted column will be indexed
since its a bit column and they don't (only 3 possible values null 0,1) make good
index sources...

is deleted only 1 for a very small proportion of the rows? (in which case it may become useful in some circumstances...) but probably shouldn't be included in the last index...
-- Suggested MyTable Missing Indexes:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([Deleted],[Requested])
INCLUDE ([RowID],[OtherID],[UserID],[Received])
GO


CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MyTable] ([OtherID],[Deleted])
GO

Open in new window

0
 
ZekeLAAuthor Commented:
Yes, Deleted is only used for a small number of samples. I tried out the first index and only gained about 10% or so. I suspect missing indexes is not the approach to this problem. I think I'll need to restructure some views since there's too much logic occurring on the fly.

But you get the points anyway. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.