0

Here I have a query which is spilling to tempdb.

enter image description here

I understand that the cardinality estimates are wrong and causing this spill. I've read few articles that mentions updating the statistics and rebuilding the indexes. I've done the update statistics with full scan along with rebuilding the index then I cleared the cache on my test environment but the query is still spilling to tempdb.

What else can I do? Help me please!! The query is being used heavily daily, it is not very heavy but it is being used a lot and it is hammering the tempdb...

Query plan

Available indexes on table Register_event_details_slts:

/****** Object:  Index [REGISTER_EVENT_DETAILS_SLTS_PK]    Script Date: 24/10/2019 12:27:49 ******/
ALTER TABLE [dbo].[REGISTER_EVENT_DETAILS_SLOTS] ADD  CONSTRAINT [REGISTER_EVENT_DETAILS_SLTS_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_BEHAVIOUR_ID_FK]    Script Date: 24/10/2019 12:27:50 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_BEHAVIOUR_ID_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [ASSOCIATED_BEHAVIOUR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_CONT_STAT_ID_FK]    Script Date: 24/10/2019 12:27:50 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_CONT_STAT_ID_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [CONTRACT_STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_CONTRACTS_ID_FK]    Script Date: 24/10/2019 12:27:51 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_CONTRACTS_ID_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [CONTRACT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_I1]    Script Date: 24/10/2019 12:27:51 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_I1] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [REGISTER_EVENT_DETAIL_ID] ASC,
    [PLANNED_START_DATE] ASC,
    [PLANNED_END_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [R_EVT_DET_SLT_I2]    Script Date: 24/10/2019 12:27:53 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_I2] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [OBJECT_ID] ASC,
    [OBJECT_TYPE] ASC,
    [PLANNED_START_DATE] ASC,
    [PLANNED_END_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_I3]    Script Date: 24/10/2019 12:27:54 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_I3] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [ACTUAL_START_DATE] ASC,
    [ACTUAL_END_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_R_EVENT_ID_FK]    Script Date: 24/10/2019 12:27:54 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_R_EVENT_ID_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [REGISTER_EVENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [R_EVT_DET_SLT_R_EVT_SLT_ID_FK]    Script Date: 24/10/2019 12:27:55 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_R_EVT_SLT_ID_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [REGISTER_EVENT_SLOT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [R_EVT_DET_SLT_SESS_COD_FK]    Script Date: 24/10/2019 12:27:55 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_SESS_COD_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [SESSION_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [R_EVT_DET_SLT_USAGE_OBJ_FK]    Script Date: 24/10/2019 12:27:56 ******/
CREATE NONCLUSTERED INDEX [R_EVT_DET_SLT_USAGE_OBJ_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [USAGE_CODE] ASC,
    [OBJECT_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [REGISTER_EVENT_DETAILS_SLOTS_FOREMA]    Script Date: 24/10/2019 12:27:56 ******/
CREATE NONCLUSTERED INDEX [REGISTER_EVENT_DETAILS_SLOTS_FOREMA] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [OBJECT_TYPE] ASC,
    [OBJECT_ID] ASC
)
INCLUDE([REGISTER_EVENT_ID],[REGISTER_EVENT_DETAIL_ID],[REGISTER_EVENT_SLOT_ID],[USAGE_CODE]) WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [REGISTER_EVENT_DETAILS_SLTS_I1]    Script Date: 24/10/2019 12:27:57 ******/
CREATE NONCLUSTERED INDEX [REGISTER_EVENT_DETAILS_SLTS_I1] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [OBJECT_TYPE] ASC
)
INCLUDE([REGISTER_EVENT_DETAIL_ID],[OBJECT_ID],[PLANNED_START_DATE],[PLANNED_END_DATE],[REGISTER_EVENT_SLOT_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [RGSTR_VNT_DTLS_SLTS_IC_FK]    Script Date: 24/10/2019 12:27:58 ******/
CREATE NONCLUSTERED INDEX [RGSTR_VNT_DTLS_SLTS_IC_FK] ON [dbo].[REGISTER_EVENT_DETAILS_SLOTS]
(
    [INSTITUTION_CONTEXT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.