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

Question

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],
        0 [Trend],
        999 [PreviousWeek]
from    dbo.WrestlerRanking wr
join    dbo.Wrestler w on w.WrestlerId = wr.WrestlerId
join    dbo.Roster r on r.Season = wr.Season
    and r.WrestlerId = wr.WrestlerId
join    dbo.[Stats] st on st.Season = wr.Season
    and st.WrestlerId = wr.WrestlerId
join    dbo.School s on s.SchoolId = r.SchoolId
join    dbo.TeamRankings tr on tr.Season = wr.Season
    and tr.WeekNumber = wr.WeekNumber
    and tr.SchoolId = s.SchoolId
where   wr.[Rank] is not null

-- above here is the view, below here are the additional filters/where clauses added by the calling stored procedure

and     s.Division = 'Division I'
and     wr.Season = 2019
and     r.Starter = 1

Here are the table definitions:

WrestlerRanking

CREATE TABLE [dbo].[WrestlerRanking](
    [WrestlerRankingId] [int] IDENTITY(1,1) NOT NULL,
    [WrestlerId] [int] NOT NULL,
    [Season] [int] NOT NULL,
    [WeekNumber] [int] NOT NULL,
    [Rank] [int] NULL,
    [EloRank] [decimal](7, 2) NOT NULL,
    [RankingDate] [datetime] NOT NULL,
    [IsCurrent] [bit] NOT NULL,
    [WeightClass] [int] NOT NULL,
    [TournamentPoints] [decimal](3, 1) NOT NULL,
    [TournamentBonus] [decimal](4, 2) NOT NULL,
    [Division] [varchar](12) NOT NULL,
    [StarterRank] [int] NULL,
 CONSTRAINT [pk_WrestlerRanking_Season_WrestlerId_WeekNumber] PRIMARY KEY CLUSTERED 
(
    [Season] ASC,
    [WrestlerId] ASC,
    [WeekNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[WrestlerRanking] ADD  DEFAULT ((0.0)) FOR [TournamentPoints]
GO

ALTER TABLE [dbo].[WrestlerRanking] ADD  DEFAULT ((0.00)) FOR [TournamentBonus]
GO

ALTER TABLE [dbo].[WrestlerRanking] ADD  DEFAULT ('Division I') FOR [Division]
GO

ALTER TABLE [dbo].[WrestlerRanking]  WITH NOCHECK ADD  CONSTRAINT [fk_WrestlerRanking_Wrestler] FOREIGN KEY([WrestlerId])
REFERENCES [dbo].[Wrestler] ([WrestlerId])
GO

ALTER TABLE [dbo].[WrestlerRanking] CHECK CONSTRAINT [fk_WrestlerRanking_Wrestler]
GO

Wrestler:

CREATE TABLE [dbo].[Wrestler](
    [WrestlerId] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    [FirstName] [varchar](100) NOT NULL,
    [Aka] [varchar](100) NULL,
    [UpdatedBy] [varchar](50) NULL,
    [UpdatedDate] [datetime] NULL,
    [HasRedshirted] [bit] NOT NULL,
    [TwitterHandle] [varchar](56) NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [IsForfeit] [bit] NOT NULL,
 CONSTRAINT [PK_Wrestler] PRIMARY KEY CLUSTERED 
(
    [WrestlerId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[Wrestler] ADD  DEFAULT ((1)) FOR [HasRedshirted]
GO

ALTER TABLE [dbo].[Wrestler] ADD  DEFAULT ('service') FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Wrestler] ADD  DEFAULT (getutcdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[Wrestler] ADD  DEFAULT ((0)) FOR [IsForfeit]
GO

Roster:

CREATE TABLE [dbo].[Roster](
    [RosterId] [int] IDENTITY(1,1) NOT NULL,
    [Season] [int] NOT NULL,
    [SchoolId] [int] NOT NULL,
    [WrestlerId] [int] NOT NULL,
    [IsRedshirting] [bit] NOT NULL,
    [EligibilityYear] [varchar](2) NOT NULL,
    [WeightClass] [int] NOT NULL,
    [Starter] [bit] NOT NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedBy] [varchar](50) NULL,
    [UpdatedDate] [datetime] NULL,
    [IsInjured] [bit] NOT NULL,
    [NationalParticipant] [bit] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [RosterId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
 CONSTRAINT [unique_Wrestler_Season] UNIQUE NONCLUSTERED 
(
    [Season] ASC,
    [WrestlerId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ((0)) FOR [IsRedshirting]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ('UN') FOR [EligibilityYear]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ((0)) FOR [WeightClass]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ((0)) FOR [Starter]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ('service') FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT (getutcdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ((0)) FOR [IsInjured]
GO

ALTER TABLE [dbo].[Roster] ADD  DEFAULT ((0)) FOR [NationalParticipant]
GO

ALTER TABLE [dbo].[Roster]  WITH NOCHECK ADD  CONSTRAINT [fk_Roster_School] FOREIGN KEY([SchoolId])
REFERENCES [dbo].[School] ([SchoolId])
GO

ALTER TABLE [dbo].[Roster] CHECK CONSTRAINT [fk_Roster_School]
GO

ALTER TABLE [dbo].[Roster]  WITH NOCHECK ADD  CONSTRAINT [fk_Roster_Wrestler] FOREIGN KEY([WrestlerId])
REFERENCES [dbo].[Wrestler] ([WrestlerId])
GO

ALTER TABLE [dbo].[Roster] CHECK CONSTRAINT [fk_Roster_Wrestler]
GO

Stats:

CREATE TABLE [dbo].[Stats](
    [StatsId] [int] IDENTITY(1,1) NOT NULL,
    [WrestlerId] [int] NOT NULL,
    [Wins] [int] NOT NULL,
    [Losses] [int] NOT NULL,
    [MajorsFor] [int] NOT NULL,
    [MajorsAgainst] [int] NOT NULL,
    [TechFall4For] [int] NOT NULL,
    [TechFall4Against] [int] NOT NULL,
    [TechFall5For] [int] NOT NULL,
    [TechFall5Against] [int] NOT NULL,
    [FallsFor] [int] NOT NULL,
    [FallsAgainst] [int] NOT NULL,
    [OtherWins] [int] NOT NULL,
    [OtherLosses] [int] NOT NULL,
    [AvgPointsFor] [decimal](4, 2) NULL,
    [AvgPointsAgainst] [decimal](4, 2) NULL,
    [BonusPercentage] [decimal](7, 4) NULL,
    [WinsRank] [int] NULL,
    [MDsRank] [int] NULL,
    [T4sRank] [int] NULL,
    [T5sRank] [int] NULL,
    [FallsRank] [int] NULL,
    [BonusPctRank] [int] NULL,
    [AvgPointsForRank] [int] NULL,
    [AvgPointsAgainstRank] [int] NULL,
    [ConfWins] [int] NULL,
    [ConfLosses] [int] NULL,
    [ConfMajorsFor] [int] NULL,
    [ConfMajorsAgainst] [int] NULL,
    [ConfT4For] [int] NULL,
    [ConfT4Against] [int] NULL,
    [ConfT5For] [int] NULL,
    [ConfT5Against] [int] NULL,
    [ConfFallsFor] [int] NULL,
    [ConfFallsAgainst] [int] NULL,
    [ConfOtherWins] [int] NULL,
    [ConfOtherLosses] [int] NULL,
    [ConfAvgPointsFor] [decimal](4, 2) NULL,
    [ConfAvgPointsAgainst] [decimal](4, 2) NULL,
    [ConfBonusPercentage] [decimal](7, 4) NULL,
    [ConfWinsRank] [int] NULL,
    [ConfMDsRank] [int] NULL,
    [ConfT4sRank] [int] NULL,
    [ConfT5sRank] [int] NULL,
    [ConfFallsRank] [int] NULL,
    [ConfBonusPctRank] [int] NULL,
    [ConfAvgPointsForRank] [int] NULL,
    [ConfAvgPointsAgainstRank] [int] NULL,
    [Season] [int] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [StatsId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
 CONSTRAINT [unique_Season_Wrestler] UNIQUE NONCLUSTERED 
(
    [Season] ASC,
    [WrestlerId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [Wins]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [Losses]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [MajorsFor]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [MajorsAgainst]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [TechFall4For]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [TechFall4Against]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [TechFall5For]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [TechFall5Against]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [FallsFor]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [FallsAgainst]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [OtherWins]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((0)) FOR [OtherLosses]
GO

ALTER TABLE [dbo].[Stats] ADD  DEFAULT ((2015)) FOR [Season]
GO

ALTER TABLE [dbo].[Stats]  WITH NOCHECK ADD  CONSTRAINT [FK_Statistics_Wrestler] FOREIGN KEY([WrestlerId])
REFERENCES [dbo].[Wrestler] ([WrestlerId])
GO

ALTER TABLE [dbo].[Stats] CHECK CONSTRAINT [FK_Statistics_Wrestler]
GO

School:

CREATE TABLE [dbo].[School](
    [SchoolId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Division] [varchar](100) NULL,
    [Conference] [varchar](100) NULL,
    [Nickname] [varchar](100) NULL,
    [Aka] [varchar](100) NULL,
    [IsActive] [bit] NOT NULL,
    [TwitterHandle] [varchar](56) NULL,
    [CreatedBy] [varchar](100) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedBy] [varchar](100) NULL,
    [UpdatedDate] [datetime] NULL,
    [ConferenceSeo] [varchar](20) NULL,
    [ShopUrl] [varchar](2000) NULL,
PRIMARY KEY CLUSTERED 
(
    [SchoolId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[School] ADD  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[School] ADD  DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[School] ADD  DEFAULT (getutcdate()) FOR [CreatedDate]
GO

TeamRankings:

CREATE TABLE [dbo].[TeamRankings](
    [TeamRankingsId] [int] IDENTITY(1,1) NOT NULL,
    [Points] [decimal](7, 2) NOT NULL,
    [Rank] [int] NULL,
    [SchoolId] [int] NOT NULL,
    [Season] [int] NOT NULL,
    [WeekNumber] [int] NOT NULL,
    [IsCurrent] [bit] NOT NULL,
    [RankingDate] [datetime] NOT NULL,
    [TournamentPoints] [decimal](5, 2) NOT NULL,
    [TournamentRank] [int] NOT NULL,
    [DualWins] [int] NULL,
    [DualLosses] [int] NULL,
    [DualRank] [int] NULL,
    [Division] [varchar](12) NOT NULL,
 CONSTRAINT [pk_TeamRankings_Season_SchoolId_WeekNumber] PRIMARY KEY CLUSTERED 
(
    [Season] ASC,
    [SchoolId] ASC,
    [WeekNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((2014)) FOR [Season]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((0)) FOR [WeekNumber]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((0)) FOR [IsCurrent]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ('1900-01-01') FOR [RankingDate]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((0)) FOR [TournamentPoints]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((999)) FOR [TournamentRank]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((0)) FOR [DualWins]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ((0)) FOR [DualLosses]
GO

ALTER TABLE [dbo].[TeamRankings] ADD  DEFAULT ('Division I') FOR [Division]
GO

ALTER TABLE [dbo].[TeamRankings]  WITH NOCHECK ADD  CONSTRAINT [fk_TeamRankings_School] FOREIGN KEY([SchoolId])
REFERENCES [dbo].[School] ([SchoolId])
GO

ALTER TABLE [dbo].[TeamRankings] CHECK CONSTRAINT [fk_TeamRankings_School]
GO

Can you provide the indexes you have currently, along with relevant table definitions? — Erik Reasonable Rates Darling, Commented Aug 8, 2018 at 15:24

Joe Obbish · Accepted Answer · 2018-08-09 01:07:50Z

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.

Awesome response and helpful! I'll try the tweaks that you mentioned. I didn't really notice how long those two hash matches took, I just saw that they were 34% and 15% each, so I just assumed since they were the highest that they were the ones causing the performance degradation. Regarding one of those scalar functions, one of them is actually doing another query against the roster table, which could definitely hurt performance. I'll try to remove that and see how everything improves, along with tweaking the index that you mentioned. — ganders, Commented Aug 9, 2018 at 15:17

Stack Exchange Network

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

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
sql-server-2014
execution-plan
or ask your own question.

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-servert-sqlsql-server-2014execution-plan or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
sql-server-2014
execution-plan
or ask your own question.