I have the below Insert statement query as part of an stored procedure that is taking some time to be complete (20-30) minutes, when normally in another server with same hardware specifications takes 1-2 minutes


     , [FIRST_NAME]
     , [MIDDLE_NAME]
     , [TIN]
     , [date_of_birth]
     , [CREATED]
     , [CREATED_BY]
     , [CTR_FK]
     , [CUSTOMER_FK]
     , [cust_type_FK]
     , [gender]
     , [naics_code]
     , [name_suffix]
     , [alternate_name]
     , [extra_type]
     , [id_issuing_state]
     , [ID_TYPE]
     , [ID_NUMBER]
     , [FLEX_1]
     , [FLEX_2]
     , [FLEX_3]
     , [FLEX_4]
     , [person_involved_source]
     , [person_involved_date]
     , [DEPOSITOR]
     , [INSERTED].[customer_FK]
     , [cust].[first_name]
     , [cust].[middle_name]
     , [cust].[COMPANY_NAME]
     , [foi].[identification_number]
     , [cust].[birth_incorporation_date]
     , [cust].[occupation]
     , NTRX.[CTR_TRX_ID]
     , @FilingDateTime
     , @Filer
     , @FilingDateTime
     , @Filer
     , NTRX.[CTR_ID]
     , [cust].[id]
     , [cust].[customer_type_FK]
     , [cust].[GENDER]
     , [cust].[NAICS_CODE]
     , [cust].[SUFFIX]
     , NULL
     , CASE
       WHEN [cashmap].[cb_type] = 'C'
       THEN 'Conductor'
       WHEN [cashmap].[cb_type] = 'O'
       THEN 'Common Carrier'
       ELSE ''
     , [ctry1].[id]
     , [cust].[id_issuing_state]
     , [cust].[id_type]
     , [cust].[id_number]
     , [cashmap].[FLEX_1]
     , [cashmap].[FLEX_2]
     , [cashmap].[FLEX_3]
     , [cashmap].[FLEX_4]
     , 1
     , [cust].[LAST_MODIFIED]
     , [cashmap].[DEPOSITOR]
    FROM  [DBO].[cashcust] AS [cashmap]
      INNER JOIN [DBO].cust AS [cust] ON [cashmap].[CUSTOMER_FK] = [cust].[id]
      LEFT JOIN [DBO].custf AS [foi] ON [foi].[customer_FK] = [cust].[id]
      LEFT JOIN [DBO].country AS [ctry1] ON [cust].[id_issuing_country] = [ctry1].[fincen_code]
      INNER JOIN [DBO].trans AS [TXN] ON [txn].[UNIQUE_TRANS_ID] = [cashmap].[transaction_id]
               AND txn.INSTITUTION_FK=cashmap.INSTITUTION_FK
      LEFT JOIN [DBO].custacc AS [custmap] ON [txn].[account_FK] = [custmap].[account_FK]
                AND [custmap].[mapping_type] = 1
                AND [custmap].[IS_ACCOUNT_HOLDER] = 1
                AND [cashmap].[customer_FK] = [custmap].[customer_FK]
    WHERE  [cashmap].[conductor_bene] = 'C'
      AND @SourceConBenFromTellerFile = 0
      AND ISNULL([cashmap].[has_error], 0) = 0;

Execution Plan: I noticed that the heaviest operator is the Clustered Index Insert.

enter image description here

Note: First set of inserted row was: 4810 Second set of inserted row was: 4886 Third set of inserted row was: 62

So not sure why is taking that time to insert them.

Important Server Info: MaxDOP = 4. Cost Treshold of Parallelism = 5

Top Waittypes: enter image description here

Page life expectancy = 60

Also I noticed some implicit conversion warnings in some of the insert statements:

enter image description here

Full Execution Plan: Part 1: enter image description here

Part 2: enter image description here

Part 3: enter image description here

Memory Clercks for Blocking:

enter image description here

  • 1
    Can you post the full execution plan? Also, is RESOUCE_SEMAPHORE happening while this query runs, or just generally on the server? – Erik Darling Apr 12 '18 at 21:46
  • Hey @sp_BlitzErik, I could not get the plan by the URL that you did advise me due to size issues, but I added in 3 separate screenshots, RESOUCE_SEMAPHORE is happening in general. – giantLincecum Apr 12 '18 at 22:24
  • Okay, cool, is anything blocking the insert? – Erik Darling Apr 12 '18 at 22:26
  • I have not noticed any blocking so far. – giantLincecum Apr 12 '18 at 22:36
  • @sp_BlitzErik I also noticed that the execution plan is the same in both servers. – giantLincecum Apr 13 '18 at 4:58

It's hard to be sure what's happening without a sample of data, but I can throw a few ideas out there:

  1. Have you isolated whether the problem is with the INSERT or the SELECT? It could be that the query is running slower on the one machine, and it's not the insert per se that's the problem.
  2. Do the two servers have the same schema? I once spent a week tracking a poor performance issue until discovering that I had temporarily deleted an index and forgot to restore it (oops). There are several good tools for automatically comparing schemas. For example, if they have the same fields in their clustering index, but in a different order, that could make a big difference.
  3. Do the two tables have similar record counts, fragmentation, and fill factors? Those shouldn't make a huge difference, but they will affect insert speed.
  4. To avoid the implicit insert, consider writing the result of your SELECT into a temp table, with data types and clustering fields like the target. Then INSERT from there into your target. This will let you separate the cost of the INSERT vs the SELECT, and will minimize the time the target table is locked.
| improve this answer | |
  • 1
    @giantLincecum, plus there can be difference in DB configuration,compatiblity or permission issue . – KumarHarsh Apr 13 '18 at 7:04

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