Sql-server – Stored Procedure started running slowly

performancequerysql servert-sql

I have a stored procedure in production which started running slower all of a sudden. I did some investigation and found out that it is using a massive table which has 75 million rows and is growing. Looking at the plan it doesn't show any major missing indexes in the warning. This procedure used to take few minutes earlier however its taking 12 mins now.

Below is the query plan:

https://www.brentozar.com/pastetheplan/?id=ryTS3fIiH

Any help is appreciated.

Best Answer

First issue that I have noticed in the provided plan is --> There are so many implicit conversions as listed below:

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0002'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0008'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0017'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0019'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0004'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0020'"/>

Due to this issue you have issue in cardinality estimate and also in Seek Plan.

Please read more on this from below links:

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11

https://www.sqlservercentral.com/forums/topic/type-conversion-may-affect-cardinalityestimate

https://www.sqlshack.com/implicit-conversion-in-sql-server/

Whenever you see yellow mark on the Select box in the query plan, please hover that and see what is the warning. Most of the time, it would give you very meaningful feedback.

Another issue I see is with the table CDB_MEMBER, its going for clustered index scan, meaning full table scan and if you would have noticed there is minor difference in estimates and actuals suggesting stats are a bit out of sync. Important aspect here is missing NC index which should have columns CDB_MBR_COV_REC_ID and SBSCR_ID

I think above two should help you a bit in fine-tuning the execution and reduce duration.

Please let us know if this helped.

Related Solutions

Sql-server – Stored procedure suddenly very slow, updating statistics not helping

I would suggest that you create a temp table or use common table expression to get the list of all longitudes you require from your remote Server and use the new temp table or common table expression to inner join with your FuelCalc table.

Something like below.

WITH CTE as 
(
select Longitude, MAX(SampleDateTime) as SampleDateTime, ShipLocationId, SHIPiD
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        GROUP BY Longitude
)
select PK_ShipDataSubsystemConfigID
,timestamp
,cte.Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
left outer join cte on cte.SHIPiD = FK_ShipId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc

Sql-server – Speed up INSERT procedure

First let's do a quick summary of the query plan for that insert query. SQL Server scans all rows from HVentas_1 to create the hash build and finds 0 relevant rows. It then scans through TR_TAB_OLAP_HVentas and stops if/when it finds 5 distinct rows such that v.fecha > @FechaMovimiento and that don't match a row in HVentas_1. You have an index defined on the table but SQL Server doesn't use it in the query plan that you uploaded. The worst case for performance should be when @FechaMovimiento is a value that doesn't match any rows in the table. In that case SQL Server will scan all 70 million rows from the table only to insert 0 rows. The number of rows inserted isn't relevant here. What's important is the amount of work that SQL Server did to find the rows to insert.

For the actual plan that you uploaded, SQL Server had to scan 70037142 rows from the heap before it found five distinct rows. It seems as if you had a very selective value for the variable, but you're using a local variable in a stored procedure without a RECOMPILE hint. You could be running into an issue with parameter sniffing. In any case, try it again with a RECOMPILE hint. That will allow SQL Server to build a query plan that accounts for the value of the local variable. This should be fine unless you're running this stored procedure hundreds of times per second. With better information, the query optimizer can make more informed decisions about when to use an index. Using an index here is likely to lead to better performance.

As a side node, you have a CCI that only has ten rows. Will that table continue to be that small? If so that may not be a good candidate for columnstore.

Best Answer

Related Solutions

Sql-server – Stored procedure suddenly very slow, updating statistics not helping

Sql-server – Speed up INSERT procedure

Related Question