Query CPU Performance
-
Hi there,
We have been having issues with CPU performance of a new server when running queries. The worst example was running a query to return the top 100,000 rows from a fairly basic View, which included an ORDER BY clause on 3 of the columns. This took upwards of 15 minutes to execute, during which time the CPUs were almost constantly running at 100%, even though there was nothing else running on the server. However, when we removed the ORDER BY statement from the view altogether, it executed in only a couple of seconds.
Can anyone suggest why the ORDER BY clause might be affecting the performance this much?
The disks, memory, I/O etc. were all under very minimal load for the duration. The database is a direct copy from a production environment, although I have already tried rebuilding/reorganising indexes and updating stats etc. just to see if it made a difference - still exactly the same though unfortunately.
Any thoughts?
Thanks
-
Hi,
can you tell me what are the data type of filed ?
and check logical fragmentation of index. it should be less then 20.
I am not an expert but trying to help.
-
How big is the underlying data set?
If you have an order by it has to return all the data, sort it and then look at the top 100,000, where as if you remove the order by it just gives you the first 100,000 rows that it comes across.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
There shouldn't be an issue with index fragmentation or data volumes etc., as this is a direct copy of a database from our production environment, where it runs a lot faster (albeit not at lightening speed as it is much older kit!). I believe the largest table involved in the query is around 130,000 rows, so the underlying dataset isn't too big anyway.
Struggling! :blink:
-
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]))
-
Thanks,
Can you also post the query that you're running as it will help us understand the plan as well.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Here is the code from the view itself....Thanks!
SELECT TOP (100000) ei.Cash, ei.CashU, ei.Condemn * - 1 AS Condemn, ei.DocumentNo, ei.EstCode, ei.Functions, ei.FunctionsU, ei.GPYear, ei.GPPeriod, ei.HoursStd,
ei.HoursStdOU, ei.InvRec, ei.ManFeeOU, ei.MFree, ei.MFreeU, ei.MWheels, ei.MWheelsU, ei.PurFood, ei.PurFoodOU, ei.PurHO, ei.PurNFood, ei.PurNFoodOU,
ei.StockClose, ei.StockOpen, ei.Taxpoint, ei.TDays, ei.TranInDC, ei.TranOutPK, ei.TransIn, ei.TransOut, ei.VatRecPC, ytd.SUMCash, ytd.SUMCashU, ytd.SUMCondemn,
ytd.SUMFunctions, ytd.SUMFunctionsU, ytd.SUMHoursStd, ytd.SUMHoursStdOU, ytd.SUMInvRec, ytd.SUMManFeeOU, ytd.SUMMFree, ytd.SUMMFreeU,
ytd.SUMMWheels, ytd.SUMMWheelsU, ytd.SUMPurFood, ytd.SUMPurFoodOU, ytd.SUMPurHO, ytd.SUMPurNFoodOU, ytd.SUMPurNFood, ytd.SUMStockClose,
ytd.SUMStockOpen, ytd.SUMTranInDC, ytd.SUMTranOutPK, ytd.SUMTransIn, ytd.SUMTransOut, ytd.SUMVat, ytd.SUMVatRecPC, em.EstCodeName, em.ManArea,
pc.MonthName, b.bCash, b.bCondemn, b.bFunctions, b.bHoursStd, b.bHoursStdOU, b.bInvRec, b.bManFee, b.bMFree, b.bMWheels, b.bPurcHO, b.bPurFood,
b.bPurNFood, b.bStockClose, b.bTDays, b.bTranInDC, b.bTranOutPK, b.bTransIn, b.bTransOut, b.bVAT, b.bVatRecPC, btd.btdCash, btd.btdCondemn, btd.btdFunctions,
btd.btdHoursStd, btd.btdHoursStdOU, btd.btdInvRec, btd.btdManFee, btd.btdMFree, btd.btdMWheels, btd.btdPurcHO, btd.btdPurFood, btd.btdPurNFood,
btd.btdStockClose, btd.btdTDays, btd.btdTranInDC, btd.btdTranOutPK, btd.btdTransIn, btd.btdTransOut, btd.btdVatRecPC
FROM dbo.EISInvoice AS ei INNER JOIN
dbo.TSEstMast AS em ON ei.EstDebtorID = em.EstDebtorID COLLATE SQL_Latin1_General_CP1_CI_AS AND
ei.EstCode = em.EstCode COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.EISProcCal AS pc ON ei.GPYear = pc.GpYear AND ei.GPPeriod = pc.GpPeriod INNER JOIN
dbo.EISKAM AS ka ON em.ManArea = ka.KAMArea INNER JOIN
dbo.EISInvoiceYTD AS ytd ON ei.GPYear = ytd.GPYear AND ei.GPPeriod = ytd.GPPeriod AND ei.EstCode = ytd.EstCode INNER JOIN
dbo.EisBudgetGP AS b ON ei.GPYear = b.GPYear AND ei.GPPeriod = b.GPPeriod AND ei.EstCode = b.EstCode INNER JOIN
dbo.EISBudgetGPYtd AS btd ON ei.GPYear = btd.GPYear AND ei.GPPeriod = btd.GPPeriod AND ei.EstCode = btd.EstCode
ORDER BY ei.GPYear, ei.EstCode, ei.DocumentNo
-
Just a bit more info for you....
The database was being transferred from a SQL 2005 server to a SQL 2008 R2 server. I have now determined, after trying it out on several servers, that it appears to only be a problem when the database is restored onto the SQL 2008 R2 servers.
Don't know if that might give any clues?
-
matt.gyton (10/30/2012)
Just a bit more info for you....The database was being transferred from a SQL 2005 server to a SQL 2008 R2 server. I have now determined, after trying it out on several servers, that it appears to only be a problem when the database is restored onto the SQL 2008 R2 servers.
Don't know if that might give any clues?
Have you rebuilt indexes or updated stats?
-
Artoo22 (10/30/2012)
matt.gyton (10/30/2012)
Just a bit more info for you....The database was being transferred from a SQL 2005 server to a SQL 2008 R2 server. I have now determined, after trying it out on several servers, that it appears to only be a problem when the database is restored onto the SQL 2008 R2 servers.
Don't know if that might give any clues?
Have you rebuilt indexes or updated stats?
Matt did say in his first post that he had rebuilt/the indexes and run an update stats.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Indeed I have...it's a bit of a mystery at the moment, the sort is just killing the CPU. I have also checked the indexes are set up the same on both servers etc. and all appears to be in order. When I restored a copy on to another SQL 2005 server it worked fine first time without any index rebuilds or stats updates etc.
-
Are the boxes the same in terms of Memory, CPU's and disks?
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (10/30/2012)
Are the boxes the same in terms of Memory, CPU's and disks?The new server is a virtual server whereas the old one is physical. In terms of hardware though the new box is much more powerful, with faster CPUs, more than twice the memory, high speed discs in the RAID arrays etc....essentially everything on it should be better, which is why this is even more surprising! The new server is also running Windows Server 2008 R2, whereas the old one is Server 2003.
Most queries are running very fast, this is the only one I've come accross so far which is causing this problem.
Appreciate all of your time guys...!
-
Thanks matt,
I take it that the SQL server is a 64bit box and doesnt have any throttling going on in terms of Max Degree of Parallelism, max memory usage.
How many processors does the box have?
Also that the Temp Db, source DB and logs are located on different physical disks rather than being on the same disk.
I personally cant see anything wrong with the query and if it works ok on other boxes, then it really can only be hardware configuration that is a problem.
Out of curiosity have you tried running the query with the Option(MAXDOP 1) to see if that helps, as i noticed a lot of parallelism going on in the plans and sometimes it can impact the query signficantly.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply