1

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.

  • I have updated stats, rebuilt indexes, cleared the plan cache – Fin Nov 10 '19 at 23:27
  • 1
    how frequent you update stats? – Learning_DBAdmin Nov 11 '19 at 8:58
2

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.

|improve this answer|||||
0

This procedure used to take few minutes earlier however its taking 12 mins now.

As your data has grown over time.Result are produce from each part of query.

SQL Query Optimiser choose cost effective and best possible plan quickly. Using too many OR conditions with nested queries or sub queries sometimes put too much burden on SQL Query Optimiser. Using too many OR conditions causes to fire part of query/condition to fire multiple times. Index seek is no more cost effective,SQL Query Optimiser chooses Index Scan.

Using Union ALL (don't use UNION) instead of OR is more performant. Also it is very easy to identify and fix further query performance.

My script is not tested, but it is easy to understand and can be easily corrected .

SELECT
   @dteReportDateMax = MAX( f.report_date )
  FROM
   fmr_daily_inventory_report f WITH ( NOLOCK )

As per your visible query you do not need columns of `customer_segment cs` in resultset.

-- put result of fmr_daily_inventory_report in temp table
Create table #Temp(all require column with correct data type)

insert into #Temp
SELECT
   f.code + ' ' + f.trigger_indicator AS code,
   LEFT( f.policy_number, 6 ) AS policy_number,
   f.ee_id,
   f.first_name,
   f.relationship_code,
   f.claim_icn_number,
   f.film_office,
   f.film_number,
   CAST( f.active_date AS DATE ) AS active_date,
   f.days_in_status,
   f.days_in_house,
   f.charge_amount,
   ' ' AS remark,
   '' AS NewFLN,
   '' AS SharePoint,
   '' AS Status1,
   f.claim_icn_number AS Filename1
  FROM
   fmr_daily_inventory_report f WITH ( NOLOCK )
   where f.customer_segment_number = @chCustomerSegmentNumber
 and  f.report_date = @dteReportDateMax
  and  exists(select 1 from  customer_segment cs WITH ( NOLOCK ) where f.customer_segment_number = cs.customer_segment_number 
   AND
   f.pend_code NOT IN ( 'TA', 'DW', 'DZ', '1S' )

SELECT
   *
  FROM #Temp
   where 

   (
    (
     f.customer_segment_number NOT IN ( '000904985', '000716570', '000714721' )

    )
    union all
    SELECT
   *
  FROM #Temp
   where 
    ( 
     f.customer_segment_number = '000904985'
     AND
     @vchFileName = 'Falck India'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code = '0002' 
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
    union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number =  '000904985'
     AND
     @vchFileName = 'Aprils'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code IN ( '0008', '0010', '0011' )
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
   union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number =  '000904985'
     AND
     @vchFileName = 'Swedish Expat'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code = '0017' 
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
    union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number =  '000904985'
     AND
     @vchFileName = 'Falck FGA PWC'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code = '0019' 
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
    union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number = '000904985'
     AND
     @vchFileName = 'Norway'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code = '0004' 
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
    union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number =  '000904985'
     AND
     @vchFileName = 'SURA'
     AND
     EXISTS 
     (
      SELECT 
        vf.subscriber_number 
       FROM 
        vw_falck_eligibility vf
       WHERE
        vf.plan_variation_subdivision_code = '0020' 
        AND
        vf.subscriber_number = f.subscriber_number
     ) 
    )
    union all
    SELECT
   *
  FROM #Temp
    ( 
     f.customer_segment_number = IN ( '000716570', '000714721' ) 
     AND
     f.charge_amount &gt;= 5000
    )
   )
   AND
   NOT EXISTS (
    SELECT
      cs.claim_icn_number
     FROM
      unet_claim_

As you can see there is no need of repeating From fmr_daily_inventory_report again and again.

Missing Index (Impact 10.2601): CREATE NONCLUSTERED INDEX [] ON [dbo].[falck_eligibility] ([load_date]) INCLUDE ([plan_variation_subdivision_code],[subscriber_number])

You can create index, on [dbo].[falck_eligibility] as per message above. If it do not help then there can be several reason.One of the reason can be Un-Optimize query inside view .And knowing Tables Schema detail is even better.

Warnings Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)) may affect "Cardinality Estimate" in query plan choice.

Never ignore Type Conversion warning message.Specially when records are in millions then this much Conversion will slow down query.

So if possible use varchar instead of nvarchar(50) .This is preferred.

Or use nvarchar in both place.

Though joining with data type like nvarchar(50) or varchar(50) is bad idea .

If subscriber_number are of fixed length like nchar(11) or char(11) then it is better choice than nvarchar(50)

|improve this answer|||||
  • @Fin, I have edited my answer. – KumarHarsh Nov 14 '19 at 3:47

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.