Hi Experts

Please see the execution plan of a SP , it take nearly 2 hours to finish. While its running I did profiler trace to see what it is doing. As per the trace its doing an scan( Sccan:Started, Scan:Stoped ) on ix_Accruals_AccrualsExtractIDAccrualDirection. But I can not see ix_Accruals_AccrualsExtractIDAccrualDirection scan in the execution plan.

I have confirmed there was no blocking at point in time. 

Trace

SQL Plan

Where and how to start investigate this.. please help

This is an UPDATE query, does it update CI (keys)? Can you show the query? How much data does it mod

Thanks Uri

please see the query bellow

Update Accruals
set QuantityVariance = 0
from 
(
		select claimid,
			isnull(masterserviceid,0) MasterServiceId,
			subhoctypeid,
			accrualdirection,
			sum(QuantityVariance) QuantityVariance
		from Accruals a
		where accrualsextractid = @accrualsextractid
		and accrualdirection = 'C'
		and exists (select 1 from accruals where claimid = a.claimid
											and isnull(masterserviceid,0) = isnull(a.masterserviceid,0)
											and subhoctypeid = a.subhoctypeid
											and accrualdirection = a.accrualdirection
											and accrualsourceid = 1
											and totalvariance < 0 -- in accruals this time with wip being varied out
											and quantity = 0
											and accrualsextractid = @accrualsextractid)	
		and exists (select 1 from accruals where claimid = a.claimid
											and isnull(masterserviceid,0) = isnull(a.masterserviceid,0)
											and subhoctypeid = a.subhoctypeid
											and accrualdirection = a.accrualdirection
											and accrualsourceid = 3
											and quantity = 0
											and quantityvariance > 0 -- in accruals this time variancing out of RFP
											and accrualsextractid = @accrualsextractid)
		and accrualsourceid in (1,3)					
		group by claimid,
			isnull(masterserviceid,0),
			subhoctypeid,
			accrualdirection
			having sum(TotalVariance) = 0
) b -- total variance across sources = 0
	INNER JOIN Accruals a on a.Claimid = b.Claimid
							AND isnull(a.MasterServiceId,0) = b.masterserviceid
							and a.subhoctypeid = b.subhoctypeid
							and a.AccrualDirection = b.AccrualDirection
    INNER JOIN accrualsextractssubmodules s
              on a.inserteddatetime between s.starttime and s.endtime
              and s.AccrualsExtractId = @accrualsextractid							
where a.accrualsextractid = @accrualsextractid
and a.accrualsourceid in (1,3) -- in new wip, billed	
and s.module = 'spAccrualsVariancesNoId'

Schema

CREATE TABLE [dbo].[Accruals]
(
[AccrualsID] [int] NOT NULL IDENTITY(1, 1),
[ClaimId] [int] NOT NULL,
[AccrualsExtractID] [int] NOT NULL,
[MasterServiceId] [int] NULL,
[SubHocTypeId] [int] NOT NULL,
[AccrualTransactionTypeID] [tinyint] NOT NULL,
[AccrualDirection] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContractId] [int] NOT NULL,
[ContractIdChanged] [bit] NOT NULL CONSTRAINT [df_Accruals_ContractIdChanged] DEFAULT ((0)),
[AIVehicleGroupId] [int] NULL,
[AIVehicleGroupIdChanged] [bit] NOT NULL CONSTRAINT [df_Accruals_AIVehicleGroupIdChanged] DEFAULT ((0)),
[TPInsurerCompanyId] [int] NULL,
[TPInsurerCompanyIdChanged] [bit] NOT NULL CONSTRAINT [df_Accruals_TPInsurerCompanyIdChanged] DEFAULT ((0)),
[InternalCompany] [bit] NULL,
[InsertedDate] [date] NOT NULL CONSTRAINT [df_Accruals_InsertedDate] DEFAULT (getdate()),
[InsertedTime] [time] NOT NULL CONSTRAINT [df_Accruals_InsertedTime] DEFAULT (getdate()),
[InsertedDateTime] AS ([InsertedDate]+CONVERT([datetime],[InsertedTime],0)),
[StartDate] [datetime] NOT NULL CONSTRAINT [df_Accruals_StartDate] DEFAULT (getdate()),
[EndDate] [datetime] NULL,
[StartDateVariance] [smallint] NOT NULL CONSTRAINT [df_Accruals_StartDateVariance] DEFAULT ((0)),
[EndDateVariance] [smallint] NOT NULL CONSTRAINT [df_Accruals_EndDateVariance] DEFAULT ((0)),
[Quantity] [smallint] NOT NULL CONSTRAINT [df_Accruals_Quantity] DEFAULT ((1)),
[QuantityVariance] [smallint] NOT NULL CONSTRAINT [df_Accruals_QuantityVariance] DEFAULT ((0)),
[Rate] [money] NOT NULL,
[RateVariance] [money] NOT NULL CONSTRAINT [df_Accruals_RateVariance] DEFAULT ((0)),
[ConfiguredRate] [money] NULL,
[Total] AS ([Quantity]*[Rate]),
[TotalVariance] AS ([QuantityVariance]*[Rate]),
[RateEqualsConfiguredRate] AS (CONVERT([bit],case [Rate] when [ConfiguredRate] then (1) else (0) end,0)),
[DayAdjustment] AS ([EndDateVariance]-[StartDateVariance]),
[HocTypeId] [int] NOT NULL,
[AccrualSourceId] [tinyint] NOT NULL CONSTRAINT [df_Accruals_AccrualSourceId] DEFAULT ((1)),
[Id] [int] NULL,
[EnteredIntoHouseKeeping] [bit] NULL
)
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[Accruals] ADD CONSTRAINT [pkAccruals] PRIMARY KEY NONCLUSTERED  ([AccrualsID])
GO

CREATE NONCLUSTERED INDEX [IDX_Accruals_AccrualDirection] ON [dbo].[Accruals] ([AccrualDirection], [AccrualSourceId]) INCLUDE ([AccrualsExtractID], [ClaimId], [HocTypeId], [MasterServiceId], [Total])
GO
CREATE NONCLUSTERED INDEX [_ix_AccrualDirection_AccrualSourceId_AccrualsExtractID_Quantity] ON [dbo].[Accruals] ([AccrualDirection], [AccrualSourceId], [AccrualsExtractID], [Quantity]) INCLUDE ([ClaimId], [MasterServiceId], [QuantityVariance], [SubHocTypeId]) WITH (STATISTICS_NORECOMPUTE=ON)
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_AccrualDirection] ON [dbo].[Accruals] ([AccrualDirection], [HocTypeId], [AccrualSourceId]) INCLUDE ([AccrualsExtractID], [MasterServiceId], [Total], [TotalVariance])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_AccrualsExtractID] ON [dbo].[Accruals] ([AccrualsExtractID])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_AccrualsExtractIDAccrualDirection] ON [dbo].[Accruals] ([AccrualsExtractID]) INCLUDE ([AccrualDirection], [AccrualSourceId], [ClaimId], [EndDate], [HocTypeId], [Id], [InsertedDate], [InsertedTime], [MasterServiceId], [Quantity], [QuantityVariance], [Rate], [StartDate], [SubHocTypeId], [Total])
GO
CREATE NONCLUSTERED INDEX [ix_PT_Accruals1] ON [dbo].[Accruals] ([AccrualsExtractID], [AccrualDirection], [AccrualSourceId], [ClaimId], [MasterServiceId], [AccrualTransactionTypeID], [InsertedDate], [InsertedTime]) INCLUDE ([AIVehicleGroupId], [ContractId], [EndDate], [Quantity], [Rate], [StartDate], [SubHocTypeId], [TPInsurerCompanyId])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_AccrualsExtractID_Id] ON [dbo].[Accruals] ([AccrualsExtractID], [Id]) INCLUDE ([AccrualDirection], [AccrualsID], [AccrualSourceId], [AccrualTransactionTypeID], [ClaimId], [MasterServiceId], [Rate], [SubHocTypeId], [Total])
GO
CREATE NONCLUSTERED INDEX [ix_PT_Accruals2] ON [dbo].[Accruals] ([AccrualsExtractID], [InsertedDate], [AccrualDirection], [InsertedTime], [AccrualTransactionTypeID], [ClaimId], [MasterServiceId], [AccrualSourceId]) INCLUDE ([AIVehicleGroupId], [ContractId], [EndDate], [Quantity], [Rate], [StartDate], [SubHocTypeId], [TPInsurerCompanyId])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_ClaimId] ON [dbo].[Accruals] ([ClaimId], [MasterServiceId], [AccrualTransactionTypeID], [AccrualDirection], [Rate], [AccrualsExtractID])
GO
CREATE NONCLUSTERED INDEX [ClaimIdSubHocTypeIdAccrualDirectionAccrualSourceIdAccrualsExtractIDQuantity] ON [dbo].[Accruals] ([ClaimId], [SubHocTypeId], [AccrualDirection], [AccrualSourceId], [AccrualsExtractID], [Quantity]) INCLUDE ([MasterServiceId])
GO
CREATE NONCLUSTERED INDEX [IDX_Accruals_EnteredIntoHouseKeeping] ON [dbo].[Accruals] ([EnteredIntoHouseKeeping]) INCLUDE ([AccrualDirection], [ClaimId], [HocTypeId], [MasterServiceId], [Total])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_HocTypeId] ON [dbo].[Accruals] ([HocTypeId]) INCLUDE ([AccrualDirection], [AccrualsExtractID], [MasterServiceId], [Total])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_Id_Include] ON [dbo].[Accruals] ([Id]) INCLUDE ([AccrualDirection], [AccrualsExtractID], [AccrualsID], [AccrualSourceId], [AccrualTransactionTypeID], [ClaimId], [MasterServiceId], [Rate], [SubHocTypeId], [Total])
GO
CREATE NONCLUSTERED INDEX [ix_Accruals_Id_AccrualSourceId] ON [dbo].[Accruals] ([Id], [AccrualSourceId]) INCLUDE ([AccrualDirection], [AccrualsExtractID], [ClaimId], [MasterServiceId], [Rate], [SubHocTypeId])
GO
CREATE CLUSTERED INDEX [ix_Accruals_InsertedDate] ON [dbo].[Accruals] ([InsertedDate], [InsertedTime])
GO

How much data does it modify..

I am not sure. the whole table has 285188818 records

Is it possible to split the query and insert some result into a temporary table  and use that table to JOIN in Accruals table? How fast this SELECT  for example?

select claimid,
			isnull(masterserviceid,0) MasterServiceId,
			subhoctypeid,
			accrualdirection,
			sum(QuantityVariance) QuantityVariance
		from Accruals a
		where accrualsextractid = @accrualsextractid
		and accrualdirection = 'C'
		and exists (select 1 from accruals where claimid = a.claimid
											and isnull(masterserviceid,0) = isnull(a.masterserviceid,0)
											and subhoctypeid = a.subhoctypeid
											and accrualdirection = a.accrualdirection
											and accrualsourceid = 1
											and totalvariance < 0 -- in accruals this time with wip being varied out
											and quantity = 0
											and accrualsextractid = @accrualsextractid)	
		and exists (select 1 from accruals where claimid = a.claimid
											and isnull(masterserviceid,0) = isnull(a.masterserviceid,0)
											and subhoctypeid = a.subhoctypeid
											and accrualdirection = a.accrualdirection
											and accrualsourceid = 3
											and quantity = 0
											and quantityvariance > 0 -- in accruals this time variancing out of RFP
											and accrualsextractid = @accrualsextractid)
		and accrualsourceid in (1,3)					
		group by claimid,
			isnull(masterserviceid,0),
			subhoctypeid,
			accrualdirection
			having sum(TotalVariance) = 0

Uir

I believe subsequent procedures are updating columns that are in the where clause. 

Select stmt is not bringing any data back. 

I have created an audit table to audit the number of records affected by the update. 

Number of records affected would be roughly between 180000 190000 

Cheers

Well , can you try the below technique

WHILE 1 = 1
BEGIN

   UPDATE TOP (50000)  Accruals set QuantityVariance = 0

   FROM (your query here);

   IF @@ROWCOUNT < 50000 BREAK;

END

I modified the SP to audit the number of records affected by UPDATE.

As per the audit record it updated 0 records but sp took nearly 2hr ..

I am clueless now..