The SP is timing out after addition of 2 new filters of state and City

SQLDB 2016-02-01 14:46:18

Hello Experts,

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

/storage/attachments/1469-sp-timeout.queryanalysis
Aaron Bertrand 2016-02-01 15:13:07
Key lookups are not related to your concept of a "lookup" table, if that's what you're implying. Can you indicate if the plan attached to the question is before or after you added the additional NC index?
SQLDB 2016-02-01 15:25:01
@Aaron :Its after creating the indexes: the ones created are
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

Matak 2016-02-01 22:12:01
Try adding in MonthlyTotal as in included column for the index [IX-tblBillingCSCEquipments]. That should get rid of the 3 worst key lookups according to the plan.
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 ?
SQLDB 2016-02-05 05:50:00
@Matak , i just copied the same and edited the question
Matak 2016-02-04 22:05:57
By timeout are you talking about the SP running for a very long time and you cancel it ? Or are you talking about the timeout for the plan itself ?
One thing to look at is your SplitString function. Unless written as an iTVF they are notorious for causing time issues
SQLDB 2016-02-05 10:34:36
Well this is used for an application which actually times out and show no result. However when i execute the same SP via SSMS, the plan times out as shown attached, but somehow the results are displayed in 2-3 minutes. So seems something is not going correct here
February 1, 2016
No Comments