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
Best Answer
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_Divisionindex 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.