The SP is timing out after addition of 2 new filters of state and City
I have this stored proc, which is timing out after two new filters of state and city were added.
We have tried to avoid the key lookups by adding adding required column to the existing NC index being used as mentioned below.
Also, we see lot of warning on SELECT into operator, not sure the reason for it, but this proc was working fine, little slow but was not timing out before the addition of two new filters of state and city.
Also, what i can notice is that the Actual no of executions varying a difference of larger number as compared to the estimated executions for the key look up operator. Could this be because of Implicit conversation being done or is something else.
Any help is greatly appreciated. Thanks
Edit: I have attached the plan ran from Plan explorer for the SP. As per comments i have added Monthly total column to NC IX mentioned, but plan looks the same with query getting timed out
1:CREATE NONCLUSTERED INDEX [ix_tblBillingCSCEquipments_Org-Tier2_includes] ON [dbo].[tblBillingCSCEquipments]
(
[Org-Tier2] ASC
)
INCLUDE ( [City],
[State]) 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
2: CREATE NONCLUSTERED INDEX [ix_tblBillingCSCEquipments_CycleID_BusinessUnit_Org-Tier1_UserEmployeeId_includes] ON [dbo].[tblBillingCSCEquipments]
(
[CycleID] ASC,
[BusinessUnit] ASC,
[Org-Tier1] ASC,
[UserEmployeeId] ASC
)
INCLUDE ( [FileRowID],
[Period],
[AssetID],
[AssetTypeID],
[OperatingUnit],
[City],
[State],
[UserName],
[OwnerName],
[OwnerEmployeeId],
[AssetTypeDescription],
[UsageDescription],
[MonthlyTotal],
[Org-Tier2],
[ChildCycleID]) 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
Also it doesnt look as if the entire sql command is there. Can you copy the code into SQL Sentry Plan Explorer and click Get Actual Plan ?
One thing to look at is your SplitString function. Unless written as an iTVF they are notorious for causing time issues