SQLTeam.com | Weblogs | Forums

Delete Query taking Long time

sql2008r2

#1

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


#2

Please post the create table statement


#3

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


#4

Hi Gbritton,

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


#5

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


#6

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.


#7

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.


#8

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.


#9

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).


#10

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 :frowning:


#11

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.


#12

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?


#13

This machine configured run value as 4 Tara


#14

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?


#15

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" :slightly_smiling:


#16

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


#17

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]))


#18

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 :slight_smile:


#19

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 :smiley: ) website:

http://www.brentozar.com/blitz/foreign-key-trusted/