Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Query CPU Performance Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 4:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
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
Post #1378685
Posted Tuesday, October 30, 2012 5:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:36 AM
Points: 65, Visits: 205
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.
Post #1378692
Posted Tuesday, October 30, 2012 5:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
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
Post #1378693
Posted Tuesday, October 30, 2012 5:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
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!
Post #1378699
Posted Tuesday, October 30, 2012 6:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 969, Visits: 3,006
Can you post both execution plans?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1378738
Posted Tuesday, October 30, 2012 7:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
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 [em].[ManArea],0), [Expr1234]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as [em].[EstDebtorID],0), [Expr1235]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as [em].[EstCode],0)))
| | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em]))
| | |--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]), WHEREPROBE([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]), WHEREPROBE([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 [em].[ManArea],0), [Expr1256]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as [em].[EstDebtorID],0), [Expr1257]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as [em].[EstCode],0)))
| | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em]))
| |--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]))
Post #1378752
Posted Tuesday, October 30, 2012 7:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
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
Post #1378754
Posted Tuesday, October 30, 2012 7:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
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

Post #1378758
Posted Tuesday, October 30, 2012 8:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
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?
Post #1378800
Posted Tuesday, October 30, 2012 8:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 969, Visits: 3,006
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?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1378831
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse