1

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
  • Until the smarter ones comment/answer... The first index satisfies queries that look up based on IsSynchronized. The second looks up based on PackageID. You would need to rework your query to have one of those as part of the index. e.g. SELECT PE.PackageID FROM dbo.PackageEvents PE WHERE PE.IsSynchronized = 1 SELECT PE.EventDate FROM dbo.PackageEvents PE WHERE PE.IsSynchronized = 1 and PE.PackageId = 'x'` but I don't know whether your going to be able to always know the available IsSynchronized values for idx (IsSyn, PkgId). Suppose since it's binary, could UNION ALL the 1 & 0 version – billinkc Oct 6 '14 at 15:09
  • 2
    Good luck modifying an Entity Framework query. I think less time was spent building Hadrian's Wall. – Max Vernon Oct 6 '14 at 15:12
  • 1
    @CaseyWilkins so say fast. Isn't that much less ambiguous? – Aaron Bertrand Oct 6 '14 at 15:24
  • 1
    Fast can only mean one thing, regardless of your scale. "Faster" may be difficult for us to gauge, but it's still not ambiguous. Meanwhile, performant can mean a bunch of things, as can "more performant." – Aaron Bertrand Oct 6 '14 at 15:28
  • 2
    Don't just blindly create those "missing" indexes! will help. Bottom line is test it by comparing query plans and stat io - before and after the index change. use tool like Plan Explorer from SQLSentry. – Kin Shah Oct 6 '14 at 15:49
5

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])  
  INCLUDE ([EventDate], [EventDescription], [EventID], 
    [LastSyncDate], [Notes], [UserName], [Version]) 
  WHERE IsSynchronized = 0;

Of course you may want to make that even smaller and test to see the difference in impact if the query has to look up the data (should be pretty efficient if the number of rows is small), so - assuming PackageID is the clustering key:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])
  WHERE IsSynchronized = 0;

The overhead of maintaining this index may very well be worth the space savings compared to a full index, especially if it's only being used to optimize this query (or query pattern, at least).

Filtered indexes are not magic, though; JNK brought up some limitations below:

Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.

Also you'll want to read these posts:


If you don't want to use a filtered index, you can probably test variations of these:

CREATE NONCLUSTERED INDEX [IX_Covering_try1] ON [dbo].[PackageEvents] 
  ([PackageID], IsSynchronized)  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

CREATE NONCLUSTERED INDEX [IX_Covering_try2] ON [dbo].[PackageEvents] 
  (IsSynchronized, [PackageID])  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

(For a long time I thought that including BIT columns in the key was wasteful but Martin Smith demonstrated a case where it worked quite well - worth a try. I can't find the post now.)

Without your full schema, data, query patterns etc. we can only guide you and have you test our suggestions in your environment. We can't say, "Ding! This is the one that will work for you!"

  • Thanks Aaron. I'm not well versed in indexing, so I'm looking for ideas to try like the ones you have provided. I'm off to try these and the suggestion from Max Vernon. – Casey Wilkins Oct 6 '14 at 15:31
  • 2
    Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail. – JNK Oct 6 '14 at 15:35
  • Aaron, how much can we trust the SSMS missing index feature? With the first filtered index you give, SSMS still wants the covering index shown in my question, citing an impact of 97.45. Does this for certain indicate that the filtered index isn't as good as the two original indexes? – Casey Wilkins Oct 6 '14 at 15:40
  • 1
    I don't really trust that feature much at all (and I don't even know what that impact number is - unicorn hugs?). How many rows match the filter, vs. ? Are you sure the clause matches what the EF query says, or does the EF query say something like NOT (IsSnychronized = 1) or CONVERT(INT, IsSynchronized) = 0? Without knowing a lot more it's tough to gauge recommendations. As I said at the end of my answer, you need to test the different indexes (maybe using a copy of the table, or a dev environment) and see how it impacts the query, and don't forget about the write portion of your workload. – Aaron Bertrand Oct 6 '14 at 15:47
  • For the first non-filtered index you give, SSMS indicates that the IX_IsSynchronized index should be included. Interestingly, for the second non-filtered index, SSMS indicates that the IX_Covering index should be included. I still haven't found one index that prevents SSMS from wanting to include at least one of the original indexes. – Casey Wilkins Oct 6 '14 at 15:48
3

Without seeing the actual query and plan, we are shooting in the dark.

Depending on how the query is actually written, it might benefit from an index such as:

CREATE NONCLUSTERED INDEX [IX_IsSynchronized_PackageID_etc] ON [dbo].[PackageEvents]  
(
    [IsSynchronized]
    , [PackageID]
) 
INCLUDE 
(
    [EventDate]
    , [EventDescription]
    , [EventID]
    , [LastSyncDate]
    , [Notes]
    , [UserName]
    , [Version]
);

This could help a query that returns the fields mentioned in the INCLUDE clause with a WHERE clause something like:

WHERE IsSynchronized = 1 AND PackageID = 'ABC'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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