title: SQL Server Stored Procedure Execution stuck runnable
body: <p>I have a stored procedure that has two transactions. The first is to delete rows from a table based on an inner join to a control table. The second is to insert rows back into the same table using another inner join on the same control table.</p>
  
  <p>The performance of this stored procedure is variable. Sometimes I can execute it to insert a few thousand rows (&lt;15,000) in a couple of seconds. The very next time I try to execute it moves straight to runnable with the last wait being SOS_Scheduler_Yield. I've been searching this wait and somewhat understand. </p>
  
  <p>I've checked things like:</p>
  
  <ul>
  <li><p>the power settings on the server (max frequency on CPU). The CPU is not busy before I try to execute - it is typically at less than 1% and jumps to 13% while the execution is 'hung'.</p></li>
  <li><p>Decreasing the number of results it needs to process down to a few hundred and sometimes works quickly - but sometimes hangs the same way</p></li>
  <li><p>I've tried recompiling the query plan with no improvement</p></li>
  <li><p>Rebuilt the indexes on the 2 involved tables with no improvement (clustered index)</p></li>
  <li><p>Checked the query plan. The most expensive operation is the clustered index insert. I don't think that this is the issue though, because sometimes it executes fine.</p></li>
  </ul>
  
  <p>I'm on SQL Server 2012 within a VM.</p>
  
  <p>Things to try?</p>
  
  <p>Table creation:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [A].[SummLoads](
      [shiftID_L] [int] NULL,
      [Excav] [nvarchar](20) NULL,
      --50 more columns here
      [ExcelDate] [int] NULL,
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
   CONSTRAINT [PK_SummLoads] PRIMARY KEY NONCLUSTERED 
  (
      [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  </code></pre>
  
  <p>Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [B].[sp_SSISsumLoads]
  
  AS
  BEGIN
  --EXEC sp_recompile 'MDM.[B].[sp_SSISsumLoads]'  Force Recompile
  --BEGIN TRAN
  --Delete shifts to be replaced
  DELETE sl 
  FROM mdm.a.summLoads sl 
  INNER JOIN mdm.tmp.ALD_Control AC 
      ON sl.shiftID_L = MDM.A.fn_SI_to_ShiftID(l_shiftindex) 
  
  --Re-insert removed data
  INSERT INTO MDM.A.SummLoads 
  SELECT 
       [shiftID_L]
      ,[Excav]
      ,[OperEx]
      --some columns omitted
      ,sum([GrOx]*ALD.TonnesLoaded) AS 'GrOx'
      ,datediff(d,'1899-12-30',MDM.A.fn_shiftID_to_date([shiftID_l]))
  
    FROM [MDM].[A].[ALDsun] ALD
    INNER JOIN MDM.B.Materials GM
      ON ALD.MatCode# = GM.MatCode#
  INNER JOIN mdm.tmp.ALD_Control AC 
          ON ShiftID_L = MDM.A.fn_SI_to_ShiftID(AC.l_shiftindex) 
  
  LEFT OUTER JOIN mdm.b.eGOH_Factors AS EF 
      ON  EF.eqmtModel# = ALD.EqmtModelTk#
  
  
  GROUP BY
  [shiftID_L]
  
    ,[Excav]
    ,[OperEx]
     ,EqmtModelEx
    ,EqmtModelEx#
      ,EqmtModelTk
    ,[EqmtModelTk#]
     ,[Mine]
    ,[Mine#]
     ,contractor
  ,contractor#
    ,GM.MatCode
    ,GM.MatCode#
    ,GM.MatType
    ,GM.MatDPR
    ,GM.Prod
    ,GM.Prod#
    ,[GradeID]
    ,[LocDig]
    ,[LocDump]
     ,[DumpArea];
  </code></pre>
  
  <p>Note: joined tables in sp are small helpers (&lt;50 rows) with clustered indexes. </p>
  
  <p>Edit 2: I checked VM resource allocation. CPU is not under sustained pressure and never goes over 25%. Memory is as 62Gb out of 65Gb, but performance monitor within the VM has essentially all of this being held by the SQL server service.</p>