I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework. I have run the query through SSMS, and it has suggested creating two missing indexes. The table in question:
CREATE TABLE [dbo].[PackageEvents] ( [EventID] [int] NOT NULL IDENTITY(1, 1), [PackageID] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [EventDate] [datetime] NOT NULL, [UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Notes] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [IsSynchronized] [bit] NOT NULL CONSTRAINT [DF_AmazonPackageEvents_IsSynchronized] DEFAULT ((0)), [LastSyncDate] [datetime] NULL, [Version] [timestamp] NOT NULL )
SSMS has suggested the following two indexes:
CREATE NONCLUSTERED INDEX [IX_IsSynchronized] ON [dbo].[PackageEvents] ([IsSynchronized]) INCLUDE ([PackageID]) CREATE NONCLUSTERED INDEX [IX_Covering] ON [dbo].[PackageEvents] ([PackageID]) INCLUDE ([EventDate], [EventDescription], [EventID], [IsSynchronized], [LastSyncDate], [Notes], [UserName], [Version])
I'm not posting the query I'm optimizing for because it is horrendous (generated by Entity Framework) and is almost unreadable. In general the query is looking for any rows where IsSynchronized = 0 and returning those rows.
Is there a way to combine these two indexes into one index that would provide the same or better performance benefit? Is this question impossible to answer without the exact query?
EDIT: The only existing index is the clustered index on the primary key, EventID.
I searched through the generated Entity Framework query. The following where clause occurs 4 times, but always in the same form:
WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[AmazonPackageEvents] AS [Extent46] WHERE ([Project30].[PackageID] = [Extent46].[PackageID]) AND (0 = [Extent46].[IsSynchronized]) )
Which seems to boil down to
WHERE PackageID=@PackageID AND IsSynchronized=0