High processor utilization when running a stored procedure

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_

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.

StackExchange Database Administrators Q#224994, answer score: 4