Sql-server – High processor utilization when running a stored procedure

execution-plansql serversql-server-2016

I have a stored procedure which was running fine till 12/11 with average execution time under 2 hours. From Tuesday (12/11) onward, the stored procedure is running continuously for more than 3 hours and had to be killed and re-run again manually.

On 12/12, we went ahead and scripted out the stored procedure. Dropped the current procedure and recreated it and it worked fine.
On 12/13, when the procedure again took a long time to execute, we tried a similar approach of dropping and recreating it, but it didn't work this time. We had to manually run each line of the stored procedure.
Today also it is taking a long time to run.

One thing to note is whenever that particular stored procedure is run, the processor utilization shoots up to 95%. We have SQLMonitor tool which depicts the same –

To eradicate the possibility of parameter sniffing, I cleared out the proc cache using DBCC FREEPROCCACHE and re-ran the procedure at 5:30 AM EST again by dropping and re-creating it. Even after that there is a spike in processor utilization. Below is the current processor utilization after proc cache has been cleared –

Below is the stored procedure FYI –

CREATE PROCEDURE [dbo].[Build_Base02_Orders] 

AS
BEGIN

    SET NOCOUNT ON;

    declare @Start_Date datetime = getdate();
    declare @Last_Date datetime
    DECLARE @PROMOHIGHDATE INT = (SELECT MAX(FILE_DATE) FROM Stage1_Promotion);

 --Create the Orders from Items

   drop table WinstonBrand_Base.dbo.Orders;


With OrdersBase1 as
(
 Select * from WinstonBrand.dbo.Stage3_Orders Orders
 UNION ALL
 Select * from WinstonBrand.dbo.Stage1_Order_Header_HH HH
 WHERE YEAR(ORDDATE) = '2005' AND NOT EXISTS (SELECT 1 FROM WinstonBrand.dbo.Stage3_Orders SO WHERE HH.ORDNO = SO.ORDNO)
)

   Select *,

   Row_Number() over (partition by Orders.ORDNO order by Orders.Ordno,Orders.ORDDATE DESC) as lastposition
  into #baseorders
  from OrdersBase1 Orders
    where indid is not null
  and exists
  (select 1 from WinstonBrand_Base.dbo.Items items where items.ITMORDNO = orders.ORDNO)


;WITH  Orders AS
  (
   SELECT b.*,OrderLoadDates.File_Date,
   ROW_NUMBER() OVER(PARTITION BY HHID ORDER BY HHID,Ordno,Orddate) AS HOSEQNUM,
   ROW_NUMBER() OVER(PARTITION BY HHID ORDER BY Orddate DESC) AS HOSEQNUMD,
   P.PROMO_DESC,P.PROMO_QUALIFY_AMT
   FROM #BaseOrders b
   OUTER APPLY (SELECT * FROM ufnGetLastOrdHeader(b.ORDNO) a WHERE b.ORDNO=a.ORDER_NUMBER)  OrderLoadDates
   OUTER APPLY ufnGetSTAGE1_Promotion(Offer,@PROMOHIGHDATE) P 


   WHERE b.lastposition = 1
  ),

  ORD_ODATE AS (
  SELECT
  HHID HO_HHID, ORDDATE HO_ODATE
  FROM ORDERS 
  WHERE HOSEQNUM = 1
  ),

  SHPING_CRT_HEADER AS (
  SELECT
  ORDER_NUMBER,  MEDIUM_CODE,  SOURCE_CODE,  CAMPAIGN_CODE,  TERM_CODE,  CONTENT_CODE
  FROM(
  SELECT
  ORDER_NUMBER,
  MEDIUM_CODE,
  SOURCE_CODE,
  CAMPAIGN_CODE,
  TERM_CODE,
  CONTENT_CODE,
  ROW_NUMBER() OVER(PARTITION BY CART_ID,ORDER_NUMBER ORDER BY LOAD_DATE DESC) ROW_SELECT
  FROM WinstonBrand.DBO.LTD_Shopping_Cart_Header
  WHERE --MEDIUM_CODE <> ''
  (MEDIUM_CODE <> '' OR SOURCE_CODE <> '' OR CAMPAIGN_CODE <> '' OR TERM_CODE <> '' OR CONTENT_CODE <> '')
  )A
  WHERE ROW_SELECT = 1
  ) 

    SELECT 
    HHID,
    INDID,
    ADDRID,
    COMPID,
    ACCTNO,
    EMAIL_ADD EMAIL,
    Orders.ORDNO,
    CAST(HOSEQNUM AS INT) AS HOSEQNUM,
    CAST(HOSEQNUMD AS INT) AS HOSEQNUMD,
    ORDDATE,
    CAST(ORDTDOL AS NUMERIC(9,2)) AS ORDTDOL,
    ORDITEMQTY,
    ORDSHIPQTY,
    CAST(1 AS INT) AS ORDFREQ,
    SOURCE AS ORDSOURCE,
    Ordchanneltype AS ORDCHANNEL,
    CAST(ORDCOGS AS NUMERIC(9,2)) AS ORDCOGS,
    CAST(ORDCANCEL AS NUMERIC(9,2)) AS ORDCANCEL,
    CAST(ORDRETURN AS NUMERIC(9,2)) AS ORDRETURN,
    CAST(ORDSOLDOUT AS NUMERIC(9,2)) AS ORDSOLDOUT,
    CAST(ORDDISCNT AS NUMERIC(9,2)) AS ORDDISCNT,
    CAST(ORDERTAX_AMOUNT AS NUMERIC(9,2)) AS TAXAMT,
    ORDPAYMENT1,
    ORDPAYMENT2,
    ORDCCTYPE,
    OTYPE,
    offer AS ORDOFFER,
    PROMO_DESC,
    PROMO_QUALIFY_AMT,
    keycode AS ORDKEYCODE,
    ORDDER_ENTRY_DATE AS ORDER_ENTRY_DATE,
    CASE WHEN file_date = '20130829' OR file_date > '20130901' THEN TRY_CONVERT(NUMERIC(9,2),FREIGHT_CHARGES)
    ELSE TRY_CONVERT(NUMERIC(9,2) ,ORDERFREIGHT_AMOUNT) END
    AS ORDERFREIGHT_AMOUNT,
    CAST(PAYMENT_TYPECODE AS VARCHAR(20))  AS PAYMENT_TYPECODE,
    SERVICE_LEVEL,
    GIFT_FLAG,
    UNDISCOUNTED_FREIGHT AS ORDER_UNDISCOUNTED_FREIGHT_AMOUNT,
    FULLY_SHIPPEDFLAG,
    APPLIED_PROMOCODE,
    PROMO_GIVEN,
    PROMO_DISCOUNT,
    MEMBER_DISCOUNT,
    MEMBER_PROGRAM,
    MEMBER_PROGRAM_SEQ,
    UPSELL_SEQ,
    REPLACE(DETAIL_REFERRING_CAT,',','') DETAIL_REFERRING_CAT,
    REFERRING_DOMAIN,
    REPLACE(REFERRING_WEBSITE,',',' ') REFERRING_WEBSITE,
    REPLACE(SEARCH_PHRASE,',',' ') SEARCH_PHRASE,
    BRAND_CWD_EFFORT,
    BRANDFROM_ORDER,
    CASE WHEN file_date = '20130829' OR file_date > '20130901' THEN
    TRY_CONVERT(NUMERIC(9,2), ADDTL_ORDER_CHARGES) ELSE TRY_CONVERT(NUMERIC(9,2) ,EST_FREIGHTCOSTS ) END AS PROCESSING_FEE,
    CAST(ORDMKTCAT AS NUMERIC(9,2)) AS ORDMKTCAT,
    CAST (ORDMKTWEB AS NUMERIC(9,2)) AS ORDMKTWEB,
    TRY_CONVERT(NUMERIC(9,2),ACTUAL_FREIGHTCOSTS) AS ORDFREIGHT_EXPENSE,
    CASE
    WHEN CAST(ORDMKTCAT AS NUMERIC(9,2)) > 0 AND CAST (ORDMKTWEB AS NUMERIC(9,2)) > 0 THEN 'CATALOG & WEB'
    WHEN CAST (ORDMKTWEB AS NUMERIC(9,2)) > 0 AND CAST(ORDMKTCAT AS NUMERIC(9,2)) = 0 THEN 'WEB ONLY'
    WHEN CAST(ORDMKTCAT AS NUMERIC(9,2)) > 0 AND CAST (ORDMKTWEB AS NUMERIC(9,2)) = 0 THEN 'CATALOG ONLY'
    ELSE NULL
    END AS MRKTCHANNEL,
    HO_ODATE,
    ISNULL(MEDIUM_CODE,'') UTM_MEDIUM_CODE,
    ISNULL(SOURCE_CODE,'') UTM_SOURCE_CODE,
    ISNULL(CAMPAIGN_CODE,'') UTM_CAMPAIGN_CODE,
    ISNULL(TERM_CODE,'') UTM_TERM_CODE,
    ISNULL(CONTENT_CODE,'') UTM_CONTENT_CODE

 INTO WinstonBrand_Base.dbo.Orders
    FROM Orders 
    OUTER APPLY ufnGetOrderItemSum(ORDNO ,GIFT) CUSTITEM
    LEFT JOIN ORD_ODATE ON HHID = HO_HHID
    LEFT JOIN SHPING_CRT_HEADER S ON Orders.ORDNO = S.ORDER_NUMBER;


    /****** Object:  Index [NonClusteredIndex-20130929-123603]    Script Date: 9/29/2013 12:37:39 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130929-123603] ON WinstonBrand_Base.dbo.Orders
(
    [HHID] ASC,
    [INDID] ASC,
    [ACCTNO] ASC,
    [ORDNO] ASC,
    --[AOSEQNUM] ASC,
    [HOSEQNUM] ASC,
    [HOSEQNUMD] ASC,
    --[IOSEQNUM] ASC,
    [ORDDATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

END

Here is the user-defined function ufnGetLastOrdHeader used in the stored procedure –

USE [WinstonBrand]
GO

/****** Object:  UserDefinedFunction [dbo].[ufnGetLastOrdHeader]    Script Date: 12/14/2018 6:50:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[ufnGetLastOrdHeader](@ORDNO varchar(20))
    RETURNS TABLE
AS
RETURN (
select ORDER_NUMBER, ORDERFREIGHT_AMOUNT,UNDISCOUNTED_FREIGHT,ACTUAL_FREIGHTCOSTS,EST_FREIGHTCOSTS, ADDTL_ORDER_CHARGES,ADDTL_HANDLING_FEE,FREIGHT_CHARGES, file_date,lastposition from(
   SELECT ORDER_NUMBER, ORDERFREIGHT_AMOUNT,UNDISCOUNTED_FREIGHT,ACTUAL_FREIGHTCOSTS,EST_FREIGHTCOSTS,ADDTL_ORDER_CHARGES,ADDTL_HANDLING_FEE,FREIGHT_CHARGES,file_date
, row_number() over (partition by ORDER_NUMBER order by FILE_DATE DESC) as lastposition
FROM Stage1_Order_Header where ORDER_NUMBER=@ORDNO
) S1_OH where lastposition=1)


GO

My question is I am unable to understand why there is a sudden processor utilization when only this procedure is run. Is there anything else other than parameter sniffing which I should look at. The version of SQL Server is 2016-SP1. Let me know if any more details are required.

EDIT –

I ran the following 4 commands on my SSMS window

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

After that I started executing my query by making a few changes (since it was being done on a PRODUCTION box). Below is the used query and the execution plan –

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET NOCOUNT ON;

declare @Start_Date datetime = getdate();
declare @Last_Date datetime
DECLARE @PROMOHIGHDATE INT = (SELECT MAX(FILE_DATE) FROM Stage1_Promotion);


With OrdersBase1 as
(
 Select * from WinstonBrand.dbo.Stage3_Orders Orders with (nolock)
 UNION ALL
 Select * from WinstonBrand.dbo.Stage1_Order_Header_HH HH with (nolock)
 WHERE YEAR(ORDDATE) = '2005' AND NOT EXISTS (SELECT 1 FROM WinstonBrand.dbo.Stage3_Orders SO with (nolock) WHERE HH.ORDNO = SO.ORDNO)
)

   Select *,

   Row_Number() over (partition by Orders.ORDNO order by Orders.Ordno,Orders.ORDDATE DESC) as lastposition
  into #baseorders
  from OrdersBase1 Orders with (nolock) --WinstonBrand.dbo.stage3_Orders Orders
    where indid is not null
  and exists
  (select 1 from WinstonBrand_Base.dbo.Items items with (nolock) where items.ITMORDNO = orders.ORDNO)


;WITH  Orders AS
  (
   SELECT b.*,OrderLoadDates.File_Date,
   ROW_NUMBER() OVER(PARTITION BY HHID ORDER BY HHID,Ordno,Orddate) AS HOSEQNUM,
   ROW_NUMBER() OVER(PARTITION BY HHID ORDER BY Orddate DESC) AS HOSEQNUMD,
   P.PROMO_DESC,P.PROMO_QUALIFY_AMT
   FROM #BaseOrders b
   OUTER APPLY (SELECT * FROM ufnGetLastOrdHeader(b.ORDNO) a WHERE b.ORDNO=a.ORDER_NUMBER)  OrderLoadDates
   OUTER APPLY ufnGetSTAGE1_Promotion(Offer,@PROMOHIGHDATE) P 


   WHERE b.lastposition = 1
  ),

  ORD_ODATE AS (
  SELECT
  HHID HO_HHID, ORDDATE HO_ODATE
  FROM ORDERS 
  WHERE HOSEQNUM = 1
  ),

  SHPING_CRT_HEADER AS (
  SELECT
  ORDER_NUMBER,  MEDIUM_CODE,  SOURCE_CODE,  CAMPAIGN_CODE,  TERM_CODE,  CONTENT_CODE
  FROM(
  SELECT
  ORDER_NUMBER,
  MEDIUM_CODE,
  SOURCE_CODE,
  CAMPAIGN_CODE,
  TERM_CODE,
  CONTENT_CODE,
  ROW_NUMBER() OVER(PARTITION BY CART_ID,ORDER_NUMBER ORDER BY LOAD_DATE DESC) ROW_SELECT
  FROM WinstonBrand.DBO.LTD_Shopping_Cart_Header with (nolock)
  WHERE --MEDIUM_CODE <> ''
  (MEDIUM_CODE <> '' OR SOURCE_CODE <> '' OR CAMPAIGN_CODE <> '' OR TERM_CODE <> '' OR CONTENT_CODE <> '')
  )A
  WHERE ROW_SELECT = 1
  ) 

    SELECT 
    HHID,
    INDID,
    ADDRID,
    COMPID,
    ACCTNO,
    EMAIL_ADD EMAIL,
    Orders.ORDNO,
    CAST(HOSEQNUM AS INT) AS HOSEQNUM,
    CAST(HOSEQNUMD AS INT) AS HOSEQNUMD,
    ORDDATE,
    CAST(ORDTDOL AS NUMERIC(9,2)) AS ORDTDOL,
    ORDITEMQTY,
    ORDSHIPQTY,
    CAST(1 AS INT) AS ORDFREQ,
    SOURCE AS ORDSOURCE,
    Ordchanneltype AS ORDCHANNEL,
    CAST(ORDCOGS AS NUMERIC(9,2)) AS ORDCOGS,
    CAST(ORDCANCEL AS NUMERIC(9,2)) AS ORDCANCEL,
    CAST(ORDRETURN AS NUMERIC(9,2)) AS ORDRETURN,
    CAST(ORDSOLDOUT AS NUMERIC(9,2)) AS ORDSOLDOUT,
    CAST(ORDDISCNT AS NUMERIC(9,2)) AS ORDDISCNT,
    CAST(ORDERTAX_AMOUNT AS NUMERIC(9,2)) AS TAXAMT,
    ORDPAYMENT1,
    ORDPAYMENT2,
    ORDCCTYPE,
    OTYPE,
    offer AS ORDOFFER,
    PROMO_DESC,
    PROMO_QUALIFY_AMT,
    keycode AS ORDKEYCODE,
    ORDDER_ENTRY_DATE AS ORDER_ENTRY_DATE,
    --Freight_Charges,
    CASE WHEN file_date = '20130829' OR file_date > '20130901' THEN TRY_CONVERT(NUMERIC(9,2),FREIGHT_CHARGES)
    ELSE TRY_CONVERT(NUMERIC(9,2) ,ORDERFREIGHT_AMOUNT) END
    AS ORDERFREIGHT_AMOUNT,
    CAST(PAYMENT_TYPECODE AS VARCHAR(20))  AS PAYMENT_TYPECODE,
    SERVICE_LEVEL,
    GIFT_FLAG,
    UNDISCOUNTED_FREIGHT AS ORDER_UNDISCOUNTED_FREIGHT_AMOUNT,
    --ACTUAL_FREIGHTCOSTS as FREIGHT_EXPENSE,
    FULLY_SHIPPEDFLAG,
    APPLIED_PROMOCODE,
    PROMO_GIVEN,
    PROMO_DISCOUNT,
    MEMBER_DISCOUNT,
    MEMBER_PROGRAM,
    MEMBER_PROGRAM_SEQ,
    UPSELL_SEQ,
    REPLACE(DETAIL_REFERRING_CAT,',','') DETAIL_REFERRING_CAT,
    REFERRING_DOMAIN,
    REPLACE(REFERRING_WEBSITE,',',' ') REFERRING_WEBSITE,
    REPLACE(SEARCH_PHRASE,',',' ') SEARCH_PHRASE,
    BRAND_CWD_EFFORT,
    BRANDFROM_ORDER,
    CASE WHEN file_date = '20130829' OR file_date > '20130901' THEN
    TRY_CONVERT(NUMERIC(9,2), ADDTL_ORDER_CHARGES) ELSE TRY_CONVERT(NUMERIC(9,2) ,EST_FREIGHTCOSTS ) END AS PROCESSING_FEE,
    CAST(ORDMKTCAT AS NUMERIC(9,2)) AS ORDMKTCAT,
    CAST (ORDMKTWEB AS NUMERIC(9,2)) AS ORDMKTWEB,
    TRY_CONVERT(NUMERIC(9,2),ACTUAL_FREIGHTCOSTS) AS ORDFREIGHT_EXPENSE,
    CASE
    WHEN CAST(ORDMKTCAT AS NUMERIC(9,2)) > 0 AND CAST (ORDMKTWEB AS NUMERIC(9,2)) > 0 THEN 'CATALOG & WEB'
    WHEN CAST (ORDMKTWEB AS NUMERIC(9,2)) > 0 AND CAST(ORDMKTCAT AS NUMERIC(9,2)) = 0 THEN 'WEB ONLY'
    WHEN CAST(ORDMKTCAT AS NUMERIC(9,2)) > 0 AND CAST (ORDMKTWEB AS NUMERIC(9,2)) = 0 THEN 'CATALOG ONLY'
    ELSE NULL
    END AS MRKTCHANNEL,
    HO_ODATE,
    ISNULL(MEDIUM_CODE,'') UTM_MEDIUM_CODE,
    ISNULL(SOURCE_CODE,'') UTM_SOURCE_CODE,
    ISNULL(CAMPAIGN_CODE,'') UTM_CAMPAIGN_CODE,
    ISNULL(TERM_CODE,'') UTM_TERM_CODE,
    ISNULL(CONTENT_CODE,'') UTM_CONTENT_CODE

 INTO WinstonBrand_Base.dbo.Orders_DBA
    FROM Orders with (nolock)
    OUTER APPLY ufnGetOrderItemSum(ORDNO ,GIFT) CUSTITEM
    LEFT JOIN ORD_ODATE ON HHID = HO_HHID
    LEFT JOIN SHPING_CRT_HEADER S ON Orders.ORDNO = S.ORDER_NUMBER;


    /****** Object:  Index [NonClusteredIndex-20130929-123603]    Script Date: 9/29/2013 12:37:39 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130929-123603_DBA] ON WinstonBrand_Base.dbo.Orders_DBA
(
    [HHID] ASC,
    [INDID] ASC,
    [ACCTNO] ASC,
    [ORDNO] ASC,
    [HOSEQNUM] ASC,
    [HOSEQNUMD] ASC,
    [ORDDATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Below are the messages printed on SSMS Result window –

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Table 'Stage1_Promotion'. Scan count 1, logical reads 2248, physical
reads 4, read-ahead reads 2198, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 47 ms, elapsed time = 68 ms.
Table 'Items'. Scan count 6, logical reads 11895581, physical reads
8481, read-ahead reads 11793819, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0. Table 'Stage3_Orders'. Scan count 12,
logical reads 2258024, physical reads 41, read-ahead reads 2257333,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Stage1_Order_Header_HH'. Scan count 6, logical reads 435021,
physical reads 0, read-ahead reads 435021, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan
count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
'Workfile'. Scan count 50, logical reads 83864, physical reads 7283,
read-ahead reads 76581, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

SQL Server Execution Times: CPU time = 1370469 ms, elapsed time =
836897 ms.

SQL Server parse and compile time: CPU time = 5734 ms, elapsed time =
5827 ms. Table 'Worktable'. Scan count 203343290, logical reads
1778461093, physical reads 470694, read-ahead reads 2151647, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
'LTD_Shopping_Cart_Header'. Scan count 1, logical reads 404060,
physical reads 0, read-ahead reads 404060, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0. Table 'Workfile'. Scan count
3752, logical reads 9917704, physical reads 1903935, read-ahead reads
13387577, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0. Table 'Items'. Scan count 1, logical reads 11895581, physical
reads 6902, read-ahead reads 11292947, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0. Table 'Stage1_Order_Header'.
Scan count 2, logical reads 2068767, physical reads 4, read-ahead
reads 1034391, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0. Table
'#baseorders_________________________________________________________________________________________________________000000001C31'. Scan count 2, logical reads 4239764, physical reads 140, read-ahead
reads 1994015, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0. Table 'Stage1_Promotion'. Scan count 2, logical
reads 4496, physical reads 4, read-ahead reads 2198, lob logical reads
0, lob physical reads 0, lob read-ahead reads 0. Warning: Null value
is eliminated by an aggregate or other SET operation.

SQL Server Execution Times: CPU time = 7282594 ms, elapsed time =
8292072 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical
reads 0, read-ahead reads 521245, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0. Table 'Orders_DBA'. Scan count 1,
logical reads 2343169, physical reads 0, read-ahead reads 31057, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 298765 ms, elapsed time =
312090 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

Below is the Resource Utilization graph for the time period the query ran. The query started to run at 15:43 EST and took 2:37:27 hours to complete.

Here is the link to the query plan.

Best Answer

Without knowing more about the table structure, and having no test data these are the things you could try.

Part 1: insert into #baseorders ('less' important in comparison)

In this part, some changes can be made on the where clause, so a function is not applied to ORDDATE. This part takes 22Min of CPU time according to the stats.

With OrdersBase1 as
(
 Select * from WinstonBrand.dbo.Stage3_Orders Orders with (nolock)
 UNION ALL
 Select * from WinstonBrand.dbo.Stage1_Order_Header_HH HH with (nolock)
 WHERE ORDDATE > '2004-12-31 23:59:59.999' AND ORDDATE <  '2006-01-01 00:00:00.000' -- Functions on Columns are bad for indexing.
AND NOT EXISTS (SELECT 1 FROM WinstonBrand.dbo.Stage3_Orders SO with (nolock) WHERE HH.ORDNO = SO.ORDNO)
)

   Select *,

   Row_Number() over (partition by Orders.ORDNO order by Orders.Ordno,Orders.ORDDATE DESC) as lastposition
  into #baseorders
  from OrdersBase1 Orders with (nolock) --WinstonBrand.dbo.stage3_Orders Orders
    where indid is not null
  and exists
  (select 1 from WinstonBrand_Base.dbo.Items items with (nolock) where items.ITMORDNO = orders.ORDNO)

However, the changes will not do a lot unless we have an index on Stage1_Order_Header_H (ORDNO,ORDDATE)

Ofcourse, since we are doing a select *, The index will need to cover the entire table.

CREATE INDEX IX_Stage1_Order_Header_H
ON dbo.Stage1_Order_Header_H(ORDNO,ORDDATE)
INCLUDE(everything)

This is not ideal. As a side note, here is a group by session on improving select *

What you could try, is creating clustered or nonclustered columnstore indexes on the tables involved if you are just using it for processing, and not doing a lot of key lookups on it. Columnstore makes batch processing much faster.

CREATE CLUSTERED COLUMNSTORE INDEX CCX_Stage1_Order_Header_H
 ON dbo.Stage1_Order_Header_H

Part 2: Insert INTO WinstonBrand_Base.dbo.Orders_DBA

Or as i would like to call it, sortfest. This is the most important part, with 2:01:22.594 CPU Time.

Exhibit A:

Digging deeper into the evidence

On the index spool, we have an CONVERT IMPLICIT, we will need to remove that.

Could it be that ORDER_NUMBER is nvarchar, and that this:

 CREATE FUNCTION [dbo].[ufnGetLastOrdHeader](@ORDNO varchar(20))

should be changed to this:

 CREATE FUNCTION [dbo].[ufnGetLastOrdHeader](@ORDNO nvarchar(20))

However, it is converting on the parameter, not on the column, so performance based it is not the worst that this is happening. If it is fixable, it should be fixed.

After that, i would try adding this index:

CREATE INDEX IX_Stage1_Order_Header
on dbo.Stage1_Order_Header(ORDER_NUMBER,FILE_DATE)
INCLUDE( ORDERFREIGHT_AMOUNT,UNDISCOUNTED_FREIGHT,ACTUAL_FREIGHTCOSTS,EST_FREIGHTCOSTS,ADDTL_ORDER_CHARGES,ADDTL_HANDLING_FEE,FREIGHT_CHARGES)

To try and remove 2 of the sorts. Sorting is very expensive.

Just to be clear, looking into removing the function itself or rewriting is it also a thing to look into, but without table definitions and a data set, it would be hard to test.

Exhibit B:

  CREATE INDEX IX_LTD_Shopping_Cart_Header_ORDER_NUMBER_CART_ID_LOAD_DATE
  on DBO.LTD_Shopping_Cart_Header(ORDER_NUMBER,CART_ID,LOAD_DATE)
  INCLUDE(

  MEDIUM_CODE,
  SOURCE_CODE,
  CAMPAIGN_CODE,
  TERM_CODE,
  CONTENT_CODE,
  )

Exhibit C.1 :

This one is a bit trickier, some selects are selecting everything in the temp table, but you might see results by adding the following index in the procedure.

   CREATE INDEX IX_BaseOrders
ON #BaseOrders(HHID,Ordno,Orddate)
include(everything)

Exhibit C.2

In short

In short, the sorts are the first thing I would try to improve, since these will take a lot of CPU time. Rewriting the query, the functions, omitting unneeded data, ... would be my first approach, but indexing to remove these sorts should help nonetheless.

Subqueries in CASE expressions

Consider the following (perfectly legal) query:

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

The semantics of CASE are that WHEN/ELSE clauses are generally evaluated in textual order. In the query above, it would be incorrect for SQL Server to return an error if the ELSE subquery returned more than one row, if the WHEN clause was satisfied. To respect these semantics, the optimizer produces a plan that uses pass-through predicates:

Pass-through predicates

The inner side of the nested loop joins are only evaluated when the pass-through predicate returns false. The overall effect is that CASE expressions are tested in order, and subqueries are only evaluated if no previous expression was satisfied.

CASE expressions with an EXISTS subquery

Where a CASE subquery uses EXISTS, the logical existence test is implemented as a semi-join, but rows that would normally be rejected by the semi-join have to be retained in case a later clause needs them. Rows flowing through this special kind of semi-join acquire a flag to indicate if the semi-join found a match or not. This flag is known as the probe column.

The details of the implementation is that the logical subquery is replaced by a correlated join ('apply') with a probe column. The work is performed by a simplification rule in the query optimizer called RemoveSubqInPrj (remove subquery in projection). We can see the details using trace flag 8606:

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Part of the input tree showing the EXISTS test is shown below:

ScaOp_Exists 
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

This is transformed by RemoveSubqInPrj to a structure headed by:

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

This is the left semi-join apply with probe described previously. This initial transformation is the only one available in SQL Server query optimizers to date, and compilation will simply fail if this transformation is disabled.

One of the possible execution plan shapes for this query is a direct implementation of that logical structure:

NLJ Semi Join with Probe

The final Compute Scalar evaluates the result of the CASE expression using the probe column value:

Compute Scalar expression

The basic shape of the plan tree is preserved when the optimize considers other physical join types for the semi join. Only merge join supports a probe column, so a hash semi join, though logically possible, is not considered:

Merge with probe column

Notice the merge outputs an expression labelled Expr1008 (that the name is the same as before is a coincidence) though no definition for it appears on any operator in the plan. This is just the probe column again. As before, the final Compute Scalar uses this probe value to evaluate the CASE.

The problem is that the optimizer doesn't fully explore alternatives that only become worthwhile with merge (or hash) semi join. In the nested loops plan, there is no advantage to checking if rows in T2 match the range on every iteration. With a merge or hash plan, this could be a useful optimization.

If we add a matching BETWEEN predicate to T2 in the query, all that happens is that this check is performed for each row as a residual on the merge semi join (tough to spot in the execution plan, but it is there):

SELECT
    T1.ID,
    CASE
        WHEN EXISTS 
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1 
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

Residual predicate

We would hope that the BETWEEN predicate would instead be pushed down to T2 resulting in a seek. Normally, the optimizer would consider doing this (even without the extra predicate in the query). It recognizes implied predicates (BETWEEN on T1 and the join predicate between T1 and T2 together imply the BETWEEN on T2) without them being present in the original query text. Unfortunately, the apply-probe pattern means this is not explored.

There are ways to write the query to produce seeks on both inputs to a merge semi join. One way involves writing the query in quite an unnatural way (defeating the reason I generally prefer EXISTS):

WITH T2 AS
(
    SELECT TOP (9223372036854775807) * 
    FROM #T2 AS T2 
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT 
    T1.ID, 
    DoesExist = 
        CASE 
            WHEN EXISTS 
            (
                SELECT * FROM T2 
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

TOP trick plan

I wouldn't be happy writing that query in a production environment, it's just to demonstrate that the desired plan shape is possible. If the real query you need to write uses CASE in this particular way, and performance suffers by there not being a seek on the probe side of a merge semi-join, you might consider writing the query using different syntax that produces the right results and a more efficient execution plan.

Best Answer

Related Solutions

Sql-server – sql server partitioned view execution plan questions

Sql-server – Check existence with EXISTS outperform COUNT! … Not

Subqueries in CASE expressions

CASE expressions with an EXISTS subquery

Related Question