4

I have a query, where SQL Server 2012 suggested a create a nonclustered index as follows:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

I created the index with this code.

Running the query, it now is suggesting a similar index, but with the INCLUDE reversed:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

Excuse my ignorance... is there a (significant) difference between these 2, or is this one of the cases I am reading, where the SQL Server index suggestions may be off?

Many thanks

UPDATE:

Here is the query - its a pretty basic query, getting a list of courses, and the number of people (men/women/paid/not paid) for each course. Would be happy to hear if there is a better way...

Interestingly, adding in the eventID = 2 at the very end, made the query run in 2 mins+ rather than 4 seconds without that filter!

    select C.*
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
        ) as PaidParticipants
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'M'
        ) as Males
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'F'
        ) as Females
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID < 50
            and tblUserEvents.EventID = C.eventID
        ) as NonPaidParticipants
from [tblCourses] C
where (eventID = 2)
order by scheduledDate
    ,internalOrder
  • 1
    Can you post the query and execution plan - use sqlsentry's plan explorer ? Also read Aaron's excellent article - Don't just blindly create those "missing" indexes! – Kin Shah Jul 6 '15 at 20:37
  • sure - added the query. will look at the article and the explorer – kneidels Jul 6 '15 at 20:47
  • 1
    Can you try to create a non clustered index on EventID, CourseID INCLUDE (StatusID, Gender) and test the query performance. Also, UPPER(U.gender) will not allow sql server to use the index as it is NON-SARGable. Do you really need UPPER - is your database collation sensitive ? Do you need all the columns from C.* - if so, then explicitly mention the column names that you need instead of specifying C.* ? – Kin Shah Jul 6 '15 at 21:00
  • No, i probably don't need the UPPER. by the way - how do i post SQL sentry's execution plan here? as a screenshot? – kneidels Jul 6 '15 at 21:03
  • Just put it in pastebin.com and link it here. – Kin Shah Jul 6 '15 at 21:05
2

Yes there is a big difference between both indices.

The following index will use the courseID as a seek predicate. This will help SQL Server to find all pages with a defined courseID. In addition, the column userID is included in the pages of the index. This will allow SQL Server to serve those values directly from the index.

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

The creation of this index will help SQL Server to perform quick responses for Queries like this:

SELECT userID FROM yourTable WHERE courseID = 1

The courseID can be used for the search in the index. Where the userID is served from the leaf level pages from the index.

Where as the following index works exactly the same but the other way round. It will use the userID for the seek and can provide the courseID directly from the leaf level pages from the index.

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

The index will improve the performance of this query:

SELECT courseID FROM yourTable WHERE userID = 1

SQL Server may also use indices and join the results of both indices. In example, you can serve this query totally from the indices:

SELECT userID, courseID FROM yourTable

This will be served by a hash match using the both indices.

Another one which could benefit from both together may be this one:

SELECT COUNT(*) FROM yourTable WHERE userID=2 and courseID=10

It might happen, that your SQL Server provides an index hint and sees that he could perform better by using a special index. After you created those index, he recognizes (during the new index) that another one would be even better. This isn't uncommon.

This may also happen if your query optimizer runs into a timeout. This can happen very fast. In the case your query optimizer times out, SQL Server will use the best plan he found until the timeout. This can vary and this may have some bad effects. Using the indices, you help your query optimizer and he will use it's time more efficient. To provide good plans.

To really see if your indices are a good choice, you need to show a bit more of your code. Additionally you can try to evaluate your index usage. There is a good DMV for this called dm_db_index_usage_stats. It will provide you how many reads, seeks, updates, etc. happend on an index. This will help you to get your own decisions if it's good or bad. An index is mostly on then useful if you read it more than you write it.

SELECT * from sys.dm_db_index_usage_stats

Hopefully this will help to clear things up for you.

Edit Due to the lastly provided information, I would suggest to try an index based on EventID, CourseID (same order!). On your other table maybe an index over EventID, StatusID too.

  • Thank you for the detailed answer. I created the reverse index, As well as EventID, UserID and statusID (as suggested later by MSSQL and by you) and the query now runs in about 3 seconds. Thanks again! – kneidels Jul 6 '15 at 21:07
  • 1
    Nice to hear. It's all about Index Seeks or Index Scans. It's a bit like in your kitchen. If you know what your searching, a lunch would be made fast. :-D Nice to help you. – Ionic Jul 6 '15 at 21:12

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.