Delete Query taking Long time

srinivas.b19 · January 11, 2016, 4:24pm

Hi Friends,

my delete query taking very long time even if we have 200 records in table, even I have seen stats were updated long back and we updated still not able to delete it is taking days. I have seen that table had forrign key relation with multiple table 12 tables but why taking that much of time.
please help em to resolve

gbritton · January 11, 2016, 4:29pm

Please post the create table statement

srinivas.b19 · January 11, 2016, 4:39pm

CREATE TABLE [dbo].[Table Name_Test](
[test_DATE] [datetime] NOT NULL,
[test_RUN_TYPE] varchar NOT NULL,
[AA_RUN_NUMBER] [numeric](2, 0) NOT NULL,
[AA_SSR_RUN_NUMBER] [numeric](7, 0) NOT NULL,
[ABC_BSC_PARTY_ID] varchar NOT NULL,
[A_CDCA_RUN_NUMBER] [numeric](2, 0) NOT NULL,
[AA_CDCA_SETTLEMENT_DATE] [datetime] NOT NULL,
[AA_CDCA_RUN_NUMBER] [numeric](2, 0) NOT NULL,
[SC_LIMITED_COST_ALLOCATION] [numeric](10, 2) NOT NULL,
[AM_UNIT_CASHFLOW] [numeric](10, 2) NOT NULL,
[ABC_IMBALANCE_CASHFLOW] [numeric](10, 2) NOT NULL,
[InfoN_IMBALANCE_CASHFLOW] [numeric](10, 2) NOT NULL,
[NO_DELIVERY_CHARGE] [numeric](10, 2) NOT NULL,
[RE_CASHFLOW_REALLOCATION_CHARGE] [numeric](10, 2) NOT NULL,
[DAILY_SYSTEM_OPERATOR_BM_CASHFLOW] [numeric](10, 2) NOT NULL,
[VERSION] [numeric](3, 0) NULL,
[VALIDITY_END_DATE] [datetime] NOT NULL,
[CREATE_USER] varchar NOT NULL,
[CREATE_DATETIME] [datetime] NOT NULL,
[UPDATE_USER] varchar NULL,
[UPDATE_DATETIME] [datetime] NULL,
CONSTRAINT [Test_table_PK] PRIMARY KEY CLUSTERED
(
[Test_DATE] ASC,
[test_RUN_TYPE] ASC,
[A_RUN_NUMBER] ASC,
[AA_SSR_RUN_NUMBER] ASC,
[ABC_BSC_PARTY_ID] ASC,
[vali_END_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70)
)

GO

SET ANSI_PADDING OFF
GO

srinivas.b19 · January 11, 2016, 4:41pm

Hi Gbritton,

Thank you for your replay and modified few columns and names in above script

gbritton · January 11, 2016, 4:48pm

OK -- so the tables that reference this one in FK relationships, Are those FKs set up with ON CASCADE DELETE? If so, then I think you can see that deleting one row in the table above could result in deleting many rows (even thousands or millions) in the related tables

srinivas.b19 · January 11, 2016, 5:01pm

Thanks gBritton for your quick response.
Even we do not have many rows on FK tables gBritton, those tables also have records between 250-300 records only but still taking longer time, we ran contaniouly 20 hours where tables records are 240 then still not completed and not deleted any single record.

Kristen · January 11, 2016, 5:08pm

Are there any suitable indexes on the FK tables? If not then even if there are zero rows to CASCADE DELETE from the FK tables, every FK table will have to be scanned to see if there are any matching rows. With a suitable index that fact will be able to be established very quickly.

srinivas.b19 · January 11, 2016, 5:13pm

Yes Kristen.
FK tables have been validated and none of the tables contains any records with same run number, we are deleting records based on run number, for example I am trying to delete where run nunber '123' with that run number I did not see any records but run number column contains more count is is the same rule is applying still.

Thanks,
Srini.

TaraKizer · January 11, 2016, 5:14pm

When you run the delete, what is it waiting on? Monitor sysprocesses for the respective spid and see what it's waiting on. Tell us what it is. Is it being blocked?

Show us the execution plan (xml).

Kristen · January 11, 2016, 5:20pm

So ... do you have INDEXES on those FK Columns, or not?

If not then it will take the same amount of time (more or less) to delete rows with NO FKs as it will for records WITH FKs. AND ... the bigger the table, the slower it will get, regardless of whether there are any matching FK rows or not

srinivas.b19 · January 11, 2016, 5:35pm

Hi Tara,

I have seen cx_packet waiting type I am not able to copy the execution plan since it contains client critical data and sorry for that.

TaraKizer · January 11, 2016, 5:38pm

What is your MAXDOP set to? You can strip out client info. But what do you see in there? Any scans? Is it a simple delete or one with joins, etc?

srinivas.b19 · January 11, 2016, 5:43pm

This machine configured run value as 4 Tara

srinivas.b19 · January 11, 2016, 5:44pm

Kristen,
all the tables contains PK and FK indexes but none of the table contains delete_Action as casecase all tables delete_action for FK had no action is that the cause of this issue?

Kristen · January 11, 2016, 5:48pm

I reckon we need to see the Query Plan.

SET SHOWPLAN_TEXT ON
GO

... your delete query here ...

SET SHOWPLAN_TEXT OFF
GO

will give you a text plan. You can then Global Replace any table / column name that is confidential. It might help a bit if the new name you provide is "meaningful" - e.g. "CLIENT" rather than "TABLE1"

James · January 11, 2016, 5:56pm

I have seen situations where a foreign key is not trusted and it results in poor plans being generated. Since you indicated that some of your cascade tables have no values for the PK you want to remove and it's still taking a long time, perhaps the optimizer doesn't know about it because the FK is no longer trusted.

This has some good info on the topic: Trusted FK's and the potential Impact on Performance

srinivas.b19 · January 11, 2016, 6:02pm

Hi Kristen, StmtText
|--Assert(WHERE:(CASE WHEN NOT [Expr1069] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1070] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1071] IS NULL THEN (2) ELSE CASE WHEN NOT [Expr1072] IS NULL THEN (3) ELSE CASE WHEN NOT [Expr1073] IS NULL THEN (4) ELSE CASE WHEN NOT [Expr1074] IS NULL THEN (5) ELSE CASE WHEN NOT [Expr1075] IS NULL THEN (6) ELSE CASE WHEN NOT [Expr1076] IS NULL THEN (7) ELSE CASE WHEN NOT [Expr1077] IS NULL THEN (8) ELSE CASE WHEN NOT [Expr1078] IS NULL THEN (9) ELSE CASE WHEN NOT [Expr1079] IS NULL THEN (10) ELSE CASE WHEN NOT [Expr1080] IS NULL THEN (11) ELSE NULL END END END END END END END END END END END END))
|--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[Client_Table2].[Client_DATE], [Client_DBName].[dbo].[Client_Table2].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_Table2].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_Table2].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_Table2].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_Table2].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[Client_Table2].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[Client_Table2].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[Client_Table2].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[Client_Table2].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[Client_Table2].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[Client_Table2].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
|--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[Client_DATE], [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]), DEFINE:([Expr1078] = [PROBE VALUE]))
| | |--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[Client_DATE], [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | |--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[Client_DATE], [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]), DEFINE:([Expr1075] = [PROBE VALUE]))
| | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]), DEFINE:([Expr1074] = [PROBE VALUE]))
| | | | | | |--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[Client_DATE], [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]), DEFINE:([Expr1072] = [PROBE VALUE]))
| | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[Client_DATE], [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE])=([Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[Client_DATE], [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[VALIDITY_END_DATE]), RESIDUAL:([Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | | | | | |--Sort(ORDER BY:([Client_DBName].[dbo].[Client_TableName].[Client_DATE] ASC, [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE] ASC))
| | | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Client_DBName].[dbo].[Client_TableName].[Client_DATE], [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE], [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER], [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID], [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]), DEFINE:([Expr1069] = [PROBE VALUE]))
| | | | | | | | | | | |--Clustered Index Delete(OBJECT:([Client_DBName].[dbo].[Client_TableName].[Client_TableName_PK]))
| | | | | | | | | | | | |--Top(ROWCOUNT est 0)
| | | | | | | | | | | | |--Parallelism(Gather Streams, ORDER BY:([PtnId1000] ASC, [Client_DBName].[dbo].[Client_TableName].[Client_DATE] ASC, [Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE] ASC))
| | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[Client_TableName].[Client_TableName_PK]), WHERE:([Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER]=(37989.)) ORDERED FORWARD)
| | | | | | | | | | | |--Index Spool(SEEK:([Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA].[S0142_APB_ACCOUNT_PERIOD_BMU_DATA_PK]))
| | | | | | | | | | |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[VALIDITY_END_DATE] ASC))
| | | | | | | | | | |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[VALIDITY_END_DATE] ASC))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_APD_ACCOUNT_PERIOD_DATA].[S0142_APD_ACCOUNT_PERIOD_DATA_PK]))
| | | | | | | | | |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[VALIDITY_END_DATE] ASC))
| | | | | | | | | |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[VALIDITY_END_DATE] ASC))
| | | | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT].[S0142_B06_BMU_PERIOD_BID_OFF_ACCPT_SPOT_POINT_PK]))
| | | | | | | | |--Index Spool(SEEK:([Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR].[S0142_BO2_BMU_PERIOD_BID_OFFER_VALUE_PAIR_PK]))
| | | | | | | |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[VALIDITY_END_DATE] ASC))
| | | | | | | |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[VALIDITY_END_DATE] ASC))
| | | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE].[S0142_BO3_BMU_PERIOD_BID_OFFER_ACCEPTANCE_PK]))
| | | | | | |--Index Spool(SEEK:([Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA].[S0142_BO4_BMU_PERIOD_BID_OFFER_DATA_PK]))
| | | | | |--Index Spool(SEEK:([Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_BP7_BM_UNIT_PERIOD_DATA].[S0142_BP7_BM_UNIT_PERIOD_DATA_PK]))
| | | | |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[VALIDITY_END_DATE] ASC))
| | | | |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[VALIDITY_END_DATE] ASC))
| | | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_FP2_FPN_VALUE_PAIR].[S0142_FP2_FPN_VALUE_PAIR_PK]))
| | | |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[VALIDITY_END_DATE] ASC))
| | | |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[VALIDITY_END_DATE] ASC))
| | | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_MD2_MARKET_INDEX_DATA_INFO].[S0142_MD2_MARKET_INDEX_DATA_INFO_PK]))
| | |--Index Spool(SEEK:([Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[Client_DATE]=[Client_DBName].[dbo].[Client_TableName].[Client_DATE] AND [Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[SETTLEMENT_RUN_TYPE]=[Client_DBName].[dbo].[Client_TableName].[SETTLEMENT_RUN_TYPE] AND [Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[SAA_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SAA_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[SVAA_SSR_RUN_NUMBER]=[Client_DBName].[dbo].[Client_TableName].[SVAA_SSR_RUN_NUMBER] AND [Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[BPH_BSC_PARTY_ID]=[Client_DBName].[dbo].[Client_TableName].[BPH_BSC_PARTY_ID] AND [Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[VALIDITY_END_DATE]=[Client_DBName].[dbo].[Client_TableName].[VALIDITY_END_DATE]))
| | |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_MVR_BM_UNIT_MVR_INFORMATION].[S0142_MVR_BM_UNIT_MVR_INFORMATION_PK]))
| |--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[VALIDITY_END_DATE] ASC))
| |--Sort(ORDER BY:([Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[Client_DATE] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[VALIDITY_END_DATE] ASC))
| |--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE].[S0142_SP7_PPC_AGG_PARTY_PERIOD_CHARGE_PK]))
|--Parallelism(Gather Streams, ORDER BY:([Client_DBName].[dbo].[Client_Table2].[Client_DATE] ASC, [Client_DBName].[dbo].[Client_Table2].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[Client_Table2].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_Table2].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_Table2].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[Client_Table2].[VALIDITY_END_DATE] ASC))
|--Sort(ORDER BY:([Client_DBName].[dbo].[Client_Table2].[Client_DATE] ASC, [Client_DBName].[dbo].[Client_Table2].[SETTLEMENT_RUN_TYPE] ASC, [Client_DBName].[dbo].[Client_Table2].[SAA_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_Table2].[SVAA_SSR_RUN_NUMBER] ASC, [Client_DBName].[dbo].[Client_Table2].[BPH_BSC_PARTY_ID] ASC, [Client_DBName].[dbo].[Client_Table2].[VALIDITY_END_DATE] ASC))
|--Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[Client_Table2].[Client_Table2_PK]))

Kristen · January 11, 2016, 6:22pm

Just picking one:

Clustered Index Scan(OBJECT:([Client_DBName].[dbo].[Client_TableName].[Client_TableName_PK]), 
WHERE:([Client_DBName].[dbo].[Client_TableName].
[SVAA_SSR_RUN_NUMBER]=(37989.)
) ORDERED FORWARD)

So on table [Client_DBName].[dbo].[Client_TableName] the Client_TableName_PK is being used (which is the Clustered Index) but the column being matched is

[SVAA_SSR_RUN_NUMBER]=(37989.)

If that column is NOT the Clustered Index column then the whole table is being scanned, rather than there being a suitable index to find a match for SVAA_SSR_RUN_NUMBER

I am presuming because it says "Clustered Index Scan" rather than "Clustered Index Seek" that SVAA_SSR_RUN_NUMBER is not the Clustered Index column.

All of them seem to be Clustered Index Scan, rather than Seek.

But I have to go out, so I've only looked at it very quickly, hopefully someone else can check my work and comment more usefully

Kristen · January 11, 2016, 6:25pm

Good point ... is that the thing where a double use of "CHECK" is required (i.e. "ALTER TABLE MyTableName WITH CHECK CHECK")? If so there is a useful link on Tara's (new ) website: