RE: Query CPU Performance – SQLServerCentral

SSCrazy

Points: 2679

October 30, 2012 at 7:09 am

Hi Artoo,

Here are the results of using the SHOWPLAN_TEXT command on both servers....hopefully this gives you what you want? Apologies for the long post....and also the smiley faces in the code, wasn't sure if I could get rid of them!!

NEW Server (where problem exists):

|--Compute Scalar(DEFINE:([Expr1043]=CONVERT(int,isnull([Expr1012],(0.00000)),0), [Expr1044]=CONVERT(int,isnull([Expr1013],(0.00000)),0), [Expr1045]=CONVERT(int,isnull([Expr1014],(0.00000)),0), [Expr1047]=CONVERT(int,isnull([Expr1016],(0.00000)),0), [Expr1048]=CONVERT(int,isnull([Expr1017],(0.00000)),0), [Expr1051]=CONVERT(int,isnull([Expr1020],(0.00000)),0), [Expr1053]=CONVERT(int,isnull([Expr1022],(0.00000)),0), [Expr1054]=CONVERT(int,isnull([Expr1023],(0.00000)),0), [Expr1056]=CONVERT(int,isnull([Expr1025],(0.00000)),0), [Expr1058]=CONVERT(int,isnull([Expr1027],(0.00000)),0), [Expr1059]=CONVERT(int,isnull([Expr1028],(0.00000)),0), [Expr1061]=CONVERT(int,isnull([Expr1030],(0.00000)),0), [Expr1064]=CONVERT(int,isnull([Expr1033],(0.00000)),0), [Expr1066]=CONVERT(int,isnull([Expr1035],(0.00000)),0), [Expr1067]=CONVERT(int,isnull([Expr1036],(0.00000)),0), [Expr1068]=CONVERT(int,isnull([Expr1037],(0.00000)),0), [Expr1069]=CONVERT(int,isnull([Expr1038],(0.00000)),0), [Expr1070]=CONVERT(int,isnull([Expr1039],(0.00000)),0), [Expr1071]=CONVERT(int,isnull([Expr1040],(0.00000)),0), [Expr1072]=CONVERT(int,isnull([Expr1041],(0.00000)),0)))

|--Top(TOP EXPRESSION:((100000)))

|--Parallelism(Gather Streams, ORDER BY:([ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))

|--Nested Loops(Inner Join, WHERE:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [Expr1231]=[Expr1235]))

|--Nested Loops(Inner Join, WHERE:([NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPYear] as [ytd].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPPeriod] as [ytd].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]=[Expr1235]))

| |--Sort(ORDER BY:([ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))

| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([pc].[GpYear], [pc].[GpPeriod], [Expr1235], [Expr1234])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear]=[NCS_TSDEVON].[dbo].[EISProcCal].[GpYear] as [pc].[GpYear] AND [NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISProcCal].[GpPeriod] as [pc].[GpPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1235] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]=[Expr1234]))

| | |--Sort(ORDER BY:([pc].[GpYear] ASC, [pc].[GpPeriod] ASC, [Expr1230] ASC, [Expr1234] ASC))

| | | |--Bitmap(HASH:([r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234]), DEFINE:([Bitmap1345]))

| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234]))

| | | |--Hash Match(Inner Join, HASH:([pc].[GpYear], [pc].[GpPeriod])=([r].[GPYear], [r].[GPPeriod]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISProcCal].[GpYear] as [pc].[GpYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISProcCal].[GpPeriod] as [pc].[GpPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod]))

| | | |--Parallelism(Distribute Streams, Broadcast Partitioning)

| | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc]))

| | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1230])=([Expr1235]), RESIDUAL:([Expr1230]=[Expr1235]))

| | | |--Sort(ORDER BY:([Expr1230] ASC))

| | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1230]))

| | | | |--Top(TOP EXPRESSION:((100000)))

| | | | |--Compute Scalar(DEFINE:([Expr1230]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))

| | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC))

| | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINE:([Expr1199]=SUM([partialagg1326]), [Expr1200]=SUM([partialagg1327]), [Expr1201]=SUM([partialagg1328]), [Expr1203]=SUM([partialagg1329]), [Expr1204]=SUM([partialagg1330]), [Expr1207]=SUM([partialagg1331]), [Expr1209]=SUM([partialagg1332]), [Expr1210]=SUM([partialagg1333]), [Expr1212]=SUM([partialagg1334]), [Expr1214]=SUM([partialagg1335]), [Expr1215]=SUM([partialagg1336]), [Expr1217]=SUM([partialagg1337]), [Expr1220]=SUM([partialagg1338]), [Expr1222]=SUM([partialagg1339]), [Expr1223]=SUM([partialagg1340]), [Expr1224]=SUM([partialagg1341]), [Expr1225]=SUM([partialagg1342]), [Expr1226]=SUM([partialagg1343]), [Expr1228]=SUM([partialagg1344])))

| | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]))

| | | | |--Hash Match(Partial Aggregate, HASH:([r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]) DEFINE:([partialagg1326]=SUM(CONVERT(int,isnull([Expr1138],(0.00000)),0)), [partialagg1327]=SUM(CONVERT(int,isnull([Expr1139],(0.00000)),0)), [partialagg1328]=SUM(CONVERT(int,isnull([Expr1140],(0.00000)),0)), [partialagg1329]=SUM(CONVERT(int,isnull([Expr1142],(0.00000)),0)), [partialagg1330]=SUM(CONVERT(int,isnull([Expr1143],(0.00000)),0)), [partialagg1331]=SUM(CONVERT(int,isnull([Expr1146],(0.00000)),0)), [partialagg1332]=SUM(CONVERT(int,isnull([Expr1148],(0.00000)),0)), [partialagg1333]=SUM(CONVERT(int,isnull([Expr1149],(0.00000)),0)), [partialagg1334]=SUM(CONVERT(int,isnull([Expr1151],(0.00000)),0)), [partialagg1335]=SUM(CONVERT(int,isnull([Expr1153],(0.00000)),0)), [partialagg1336]=SUM(CONVERT(int,isnull([Expr1154],(0.00000)),0)), [partialagg1337]=SUM(CONVERT(int,isnull([Expr1156],(0.00000)),0)), [partialagg1338]=SUM(CONVERT(int,isnull([Expr1159],(0.00000)),0)), [partialagg1339]=SUM(CONVERT(int,isnull([Expr1161],(0.00000)),0)), [partialagg1340]=SUM(CONVERT(int,isnull([Expr1162],(0.00000)),0)), [partialagg1341]=SUM(CONVERT(int,isnull([Expr1163],(0.00000)),0)), [partialagg1342]=SUM(CONVERT(int,isnull([Expr1164],(0.00000)),0)), [partialagg1343]=SUM(CONVERT(int,isnull([Expr1165],(0.00000)),0)), [partialagg1344]=SUM(CONVERT(int,isnull([Expr1167],(0.00000)),0))))

| | | | |--Hash Match(Inner Join, HASH:([r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]<=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]))

| | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([r].[EstCode], [r].[BudgetYear]))

| | | | | |--Top(TOP EXPRESSION:((100000)))

| | | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))

| | | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

| | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([r].[EstCode], [r].[BudgetYear]))

| | | | |--Top(TOP EXPRESSION:((100000)))

| | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))

| | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE:([Expr1138]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1139]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1140]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1142]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1143]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1146]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1148]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1149]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1151]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1153]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1154]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1156]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1159]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1161]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1162]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1163]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1164]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1165]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1167]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END)))

| | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

| | | |--Sort(ORDER BY:([Expr1235] ASC))

| | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1235]))

| | | |--Nested Loops(Inner Join, WHERE:([Expr1232]=[Expr1233]))

| | | |--Compute Scalar(DEFINE:([Expr1233]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0)))

| | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka]))

| | | |--Compute Scalar(DEFINE:([Expr1232]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[ManArea] as .[ManArea],0), [Expr1234]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as .[EstDebtorID],0), [Expr1235]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as .[EstCode],0)))

| | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS ))

| | |--Compute Scalar(DEFINE:([Expr1229]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.)))

| | |--Sort(ORDER BY:([ei].[GPYear] ASC, [ei].[GPPeriod] ASC, [ei].[EstCode] ASC, [ei].[EstDebtorID] ASC))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID]))

| | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei]), WHERE:(PROBE([Bitmap1345],[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear],[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod],[NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode],[NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID])))

| |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd]))

|--Top(TOP EXPRESSION:((100000)))

|--Compute Scalar(DEFINE:([Expr1231]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))

|--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE:([Expr1012]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1013]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1014]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1016]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1017]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1020]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1022]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1023]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1025]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1027]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1028]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1030]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1033]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1035]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1036]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1037]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1038]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1039]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1040]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VAT' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1041]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END)))

|--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

|--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

OLD Server:

|--Compute Scalar(DEFINE:([Expr1051]=CONVERT(int,isnull([Expr1020],(0.00000)),0), [Expr1052]=CONVERT(int,isnull([Expr1021],(0.00000)),0), [Expr1053]=CONVERT(int,isnull([Expr1022],(0.00000)),0), [Expr1055]=CONVERT(int,isnull([Expr1024],(0.00000)),0), [Expr1056]=CONVERT(int,isnull([Expr1025],(0.00000)),0), [Expr1059]=CONVERT(int,isnull([Expr1028],(0.00000)),0), [Expr1061]=CONVERT(int,isnull([Expr1030],(0.00000)),0), [Expr1062]=CONVERT(int,isnull([Expr1031],(0.00000)),0), [Expr1064]=CONVERT(int,isnull([Expr1033],(0.00000)),0), [Expr1066]=CONVERT(int,isnull([Expr1035],(0.00000)),0), [Expr1067]=CONVERT(int,isnull([Expr1036],(0.00000)),0), [Expr1069]=CONVERT(int,isnull([Expr1038],(0.00000)),0), [Expr1072]=CONVERT(int,isnull([Expr1041],(0.00000)),0), [Expr1074]=CONVERT(int,isnull([Expr1043],(0.00000)),0), [Expr1075]=CONVERT(int,isnull([Expr1044],(0.00000)),0), [Expr1076]=CONVERT(int,isnull([Expr1045],(0.00000)),0), [Expr1077]=CONVERT(int,isnull([Expr1046],(0.00000)),0), [Expr1078]=CONVERT(int,isnull([Expr1047],(0.00000)),0), [Expr1079]=CONVERT(int,isnull([Expr1048],(0.00000)),0), [Expr1080]=CONVERT(int,isnull([Expr1049],(0.00000)),0)))

|--Top(TOP EXPRESSION:((100000)))

|--Parallelism(Gather Streams, ORDER BY:([ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))

|--Nested Loops(Inner Join, WHERE:([Expr1254]=[Expr1255]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([ei].[GPYear], [ei].[GPPeriod]))

| |--Nested Loops(Inner Join, WHERE:([NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]=[Expr1257] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]=[Expr1256]))

| | |--Sort(ORDER BY:([ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))

| | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ei].[GPYear], [ei].[GPPeriod])=([ytd].[GPYear], [ytd].[GPPeriod]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPYear] as [ytd].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPPeriod] as [ytd].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]))

| | | |--Sort(ORDER BY:([ei].[GPYear] ASC, [ei].[GPPeriod] ASC))

| | | | |--Bitmap(HASH:([r].[GPYear], [r].[GPPeriod]), DEFINE:([Bitmap1369]))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod]))

| | | | |--Hash Match(Inner Join, HASH:([r].[GPYear], [r].[GPPeriod], [ei].[EstCode])=([r].[GPYear], [r].[GPPeriod], [Expr1349]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1349]))

| | | | |--Parallelism(Distribute Streams, Broadcast Partitioning)

| | | | | |--Hash Match(Inner Join, HASH:([r].[GPYear], [r].[GPPeriod], [Expr1348])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1348]))

| | | | | |--Compute Scalar(DEFINE:([Expr1348]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))

| | | | | | |--Top(TOP EXPRESSION:((100000)))

| | | | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC))

| | | | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINE:([Expr1223]=SUM([partialagg1350]), [Expr1224]=SUM([partialagg1351]), [Expr1225]=SUM([partialagg1352]), [Expr1227]=SUM([partialagg1353]), [Expr1228]=SUM([partialagg1354]), [Expr1231]=SUM([partialagg1355]), [Expr1233]=SUM([partialagg1356]), [Expr1234]=SUM([partialagg1357]), [Expr1236]=SUM([partialagg1358]), [Expr1238]=SUM([partialagg1359]), [Expr1239]=SUM([partialagg1360]), [Expr1241]=SUM([partialagg1361]), [Expr1244]=SUM([partialagg1362]), [Expr1246]=SUM([partialagg1363]), [Expr1247]=SUM([partialagg1364]), [Expr1248]=SUM([partialagg1365]), [Expr1249]=SUM([partialagg1366]), [Expr1250]=SUM([partialagg1367]), [Expr1252]=SUM([partialagg1368])))

| | | | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC))

| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]))

| | | | | | |--Hash Match(Partial Aggregate, HASH:([r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]) DEFINE:([partialagg1350]=SUM(CONVERT(int,isnull([Expr1162],(0.00000)),0)), [partialagg1351]=SUM(CONVERT(int,isnull([Expr1163],(0.00000)),0)), [partialagg1352]=SUM(CONVERT(int,isnull([Expr1164],(0.00000)),0)), [partialagg1353]=SUM(CONVERT(int,isnull([Expr1166],(0.00000)),0)), [partialagg1354]=SUM(CONVERT(int,isnull([Expr1167],(0.00000)),0)), [partialagg1355]=SUM(CONVERT(int,isnull([Expr1170],(0.00000)),0)), [partialagg1356]=SUM(CONVERT(int,isnull([Expr1172],(0.00000)),0)), [partialagg1357]=SUM(CONVERT(int,isnull([Expr1173],(0.00000)),0)), [partialagg1358]=SUM(CONVERT(int,isnull([Expr1175],(0.00000)),0)), [partialagg1359]=SUM(CONVERT(int,isnull([Expr1177],(0.00000)),0)), [partialagg1360]=SUM(CONVERT(int,isnull([Expr1178],(0.00000)),0)), [partialagg1361]=SUM(CONVERT(int,isnull([Expr1180],(0.00000)),0)), [partialagg1362]=SUM(CONVERT(int,isnull([Expr1183],(0.00000)),0)), [partialagg1363]=SUM(CONVERT(int,isnull([Expr1185],(0.00000)),0)), [partialagg1364]=SUM(CONVERT(int,isnull([Expr1186],(0.00000)),0)), [partialagg1365]=SUM(CONVERT(int,isnull([Expr1187],(0.00000)),0)), [partialagg1366]=SUM(CONVERT(int,isnull([Expr1188],(0.00000)),0)), [partialagg1367]=SUM(CONVERT(int,isnull([Expr1189],(0.00000)),0)), [partialagg1368]=SUM(CONVERT(int,isnull([Expr1191],(0.00000)),0))))

| | | | | | |--Hash Match(Inner Join, HASH:([r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUAL:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]<=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]))

| | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([r].[EstCode], [r].[BudgetYear]))

| | | | | | | |--Top(TOP EXPRESSION:((100000)))

| | | | | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))

| | | | | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

| | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | | | | |--Compute Scalar(DEFINE:([r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID]))

| | | | | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

| | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([r].[EstCode], [r].[BudgetYear]))

| | | | | | |--Top(TOP EXPRESSION:((100000)))

| | | | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))

| | | | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE:([Expr1162]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1163]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1164]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1166]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1167]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1170]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1172]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1173]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1175]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1177]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1178]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1180]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1183]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1185]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1186]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1187]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1188]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1189]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1191]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END)))

| | | | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | | | |--Compute Scalar(DEFINE:([r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID]))

| | | | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

| | | | | |--Compute Scalar(DEFINE:([Expr1253]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.)))

| | | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei]))

| | | | |--Compute Scalar(DEFINE:([Expr1349]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))

| | | | |--Parallelism(Distribute Streams, RoundRobin Partitioning)

| | | | |--Top(TOP EXPRESSION:((100000)))

| | | | |--Parallelism(Gather Streams, ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))

| | | | |--Stream Aggregate(GROUP BY:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE:([Expr1020]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1021]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1022]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1024]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1025]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1028]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1030]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1031]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1033]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1035]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1036]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1038]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1041]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1043]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1044]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1045]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1046]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1047]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1048]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VAT' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1049]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END)))

| | | | |--Sort(ORDER BY:([r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))

| | | | |--Compute Scalar(DEFINE:([r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID]))

| | | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))

| | | |--Sort(ORDER BY:([ytd].[GPYear] ASC, [ytd].[GPPeriod] ASC))

| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ytd].[GPYear], [ytd].[GPPeriod]), WHERE:(PROBE([Bitmap1369])=TRUE))

| | | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd]))

| | |--Table Spool

| | |--Compute Scalar(DEFINE:([Expr1254]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[ManArea] as .[ManArea],0), [Expr1256]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as .[EstDebtorID],0), [Expr1257]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as .[EstCode],0)))

| | |--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS ))

| |--Clustered Index Seek(OBJECT:([NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc]), SEEK:([pc].[GpYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [pc].[GpPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]) ORDERED FORWARD)

|--Compute Scalar(DEFINE:([Expr1255]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0)))

|--Clustered Index Scan(OBJECT:([NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka]))