How to determine index needed to remove hash match from exec plan

Sql Server Database on Azure (Sql Server 2014/12.0.2000.8)

I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed everything. Then in the individual stored procedures I'd do the extra filtering.

I just noticed today that my view's execution plan is showing two different hash match (inner join) parts that are slowing down my query. I'm having trouble determining from the execution plan what column(s) or join(s) I need to look at in order to optimize, or figure out what indexes I could potentially add.

Linked is the execution plan in question: https://drive.google.com/file/d/1VeVrfD_usowEiPEn0KuVmREHl1CWCtxx/view?usp=sharing

Looking at the exec plan, it mentions "Hash Keys Probe", and "Probe Residual" sections, I tried adding another index using the two columns in the probe section, but that didn't work. These tables are already heavily indexed, so I'm at a loss.

Here's the query to go along with the execution plan:

```
select s.Division,
wr.Season,
wr.WeekNumber,
wr.RankingDate,
wr.IsCurrent,
wr.WeightClass,
wr.[Rank],
wr.WrestlerId,
w.LastName,
w.FirstName,
s.SchoolId,
s.[Name] [SchoolName],
coalesce(tr.DualRank, tr.TournamentRank, tr.[Rank]) [SchoolRank],
s.Conference,
s.ConferenceSeo,
dbo.uf_GetEligibilityString(r.WrestlerId, r.Season) [EligibilityYear],
w.HasRedshirted,
r.Starter,
r.IsRedshirting,
r.IsInjured,
dbo.uf_GetRecordString(st.Wins, st.Losses) [Record],
dbo.uf_GetRecordString(st.ConfWins, st.ConfLosses) [ConferenceRecord],
dbo.uf_GetPercentageString(st.Wins, (st.Wins + st.Losses)) [WinPercentage],
dbo.uf_GetPercentageString(st.MajorsFor + st.TechFall4For + st.TechFall5For + st.FallsFor, st.Wins) [BonusPercentage],
st.Wins,
st.Losses,
wr.EloRank [EloPoints],

In an actual query plan you can examine most operators to get an approximation for how much CPU and elapsed time each operator uses. That can be helpful in identifying the part of the query plan which took the most time. For row mode plans the numbers shown are the total of the entire subtree. This means that both hash matches use less than 13 ms of CPU:

Your query uses a total of 192 ms of CPU. If you need to tune the performance of the query I would focus on the user defined functions in your query. The actual plan suggests that about 80% of runtime is spent on those functions. It may be possible to optimize them. The best solution would be to remove them entirely, as scalar UDFs can have a large performance penalty.

Going back to your original question, if you wish to see what performance looks like with a different plan then I suggest creating an index with the following key columns: Season, WrestlerId, WeekNumber. You'll need to add included columns as well so that the index is covering. Ideally you want one index to have all of the key and indexed columns necessary for the query. Adding another index with some of the key columns might not be the best approach for this query.

Note that the idx_NC_WrestlerRanking_Season_WeekNumber_Division index is already a covering one, so it may be possible to simply modify that index. The definition of the index isn't included in your question so it's hard to say more.

StackExchange Database Administrators Q#214409, answer score: 2