Can't get stored procedure to change index scan to index seek

Question

Background: I'm running on Sql Server 2014 (12.0.2000.8) on Azure...

I found a handy script the other day which shows the queries/stored procedures that are "touching" indexes. I've been looking for this for awhile because I have some indexes that were performing very poorly, but I couldn't find where they were called.

Now that I have that information, I've been trying to re-work the procs that are touching the index in question.

When looking at the execution plan of my query, it says it's doing a scan which is obviously not optimal.

Hovering over the index it shows the the output list of the join, but no predicates.

I went ahead and created an index with the exact fields that are in that output list.

Here's the query that is being run:

declare @season int = 2017

select  s.SchoolId,
        s.Name [SchoolName],
        s.Conference,
        tr.DualRank [Rank],
        convert(varchar(2), tr.DualWins) + ' - ' + convert(varchar(2), tr.DualLosses) [Record],
        tr.RankingDate,
        case    when tr.WeekNumber = 0 then null
                else
                    (select trx.DualRank from dbo.TeamRankings trx where trx.Season = tr.Season and trx.WeekNumber = (tr.WeekNumber - 1) and trx.SchoolId = tr.SchoolId)
                    - tr.DualRank
        end [Trend],
        (select trx.DualRank from dbo.TeamRankings trx where trx.Season = tr.Season and trx.WeekNumber = (tr.WeekNumber - 1) and trx.SchoolId = tr.SchoolId) [PreviousWeek]
from    dbo.TeamRankings tr
join    dbo.School s on s.SchoolId = tr.SchoolId
where   tr.Season = @season
and     tr.IsCurrent = 1
order by tr.DualRank

The only join in this list that has a scan instead of a seek is the one to the school table. It's joining on the SchoolId, and then in the select portion, it's outputing the Name and Conference. Seems pretty straight forward.

In my first try, I went ahead and created my index like this:

create nonclustered index idx_NC_School_SchoolId_incs on dbo.School (SchoolId asc) include (Name, Conference)

but that still resulted in a scan. My second attempt was to do it like this:

create nonclustered index idx_NC_School_SchoolId_Name_Conference on dbo.School (SchoolId asc, Name asc, Conference asc)

But that STILL is doing a scan while utilizing the index that I created.

What else should I be looking at to try to get this query to do a seek instead of a scan.

For more background info, here's a subset of the table definition:

dbo.School
SchoolId int identity(1,1) primary key,
Name varchar(100) not null,
Conference varchar(100) not null -- will soon change this to a lookup table
......

I know someone will ask, but I can't figure out how to do it; how do I attach my execution plan to the question?

Here's a link to the page where the data is displayed: http://www.wrestlestat.com/rankings/dual/live

Answer 1

Index scans are not always a bad thing, specially when you have a very small table.

But something that can definitely improve performance of your query is to move these sub-queries from your select clause to from and use a join.

Something like......

declare @season int = 2017

select  s.SchoolId,
        s.Name [SchoolName],
        s.Conference,
        tr.DualRank [Rank],
        convert(varchar(2), tr.DualWins) + ' - ' + convert(varchar(2), tr.DualLosses) [Record],
        tr.RankingDate,
        CASE WHEN tr.WeekNumber =  0 then null
             ELSE trx.DualRank - tr.DualRank end [Trend],
        trx.DualRank  [PreviousWeek]
from    dbo.TeamRankings tr
Inner join dbo.School       s   on s.SchoolId = tr.SchoolId

Left  join dbo.TeamRankings trx ON trx.Season = tr.Season 
                               and trx.WeekNumber = (tr.WeekNumber - 1) 
                               and trx.SchoolId = tr.SchoolId
where   tr.Season = @season
and     tr.IsCurrent = 1
order by tr.DualRank

When you have a sub-query in the select clause, the sub-query is executed for each row returned by the outer query, if you move it to from clause and use joins , it will be executed once and the result set will be joined with result set coming from other joins. Much more efficient and cleaner.