Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query CPU Performance


Query CPU Performance

Author
Message
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
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
KcV
KcV
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 222
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.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
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
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 3432
Can you post both execution plans?



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
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(DEFINESad[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 EXPRESSIONSad(100000)))
|--Parallelism(Gather Streams, ORDER BYSad[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))
|--Nested Loops(Inner Join, WHERESad[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, WHERESad[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 BYSad[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGESad[pc].[GpYear], [pc].[GpPeriod], [Expr1235], [Expr1234])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID]), RESIDUALSad[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 BYSad[pc].[GpYear] ASC, [pc].[GpPeriod] ASC, [Expr1230] ASC, [Expr1234] ASC))
| | | |--Bitmap(HASHSad[r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234]), DEFINESad[Bitmap1345]))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234]))
| | | |--Hash Match(Inner Join, HASHSad[pc].[GpYear], [pc].[GpPeriod])=([r].[GPYear], [r].[GPPeriod]), RESIDUALSad[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(OBJECTSad[NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc]))
| | | |--Merge Join(Inner Join, MANY-TO-MANY MERGESad[Expr1230])=([Expr1235]), RESIDUALSad[Expr1230]=[Expr1235]))
| | | |--Sort(ORDER BYSad[Expr1230] ASC))
| | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNSSad[Expr1230]))
| | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | |--Compute Scalar(DEFINESad[Expr1230]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))
| | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC))
| | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC))
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]))
| | | | |--Hash Match(Partial Aggregate, HASHSad[r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUALSad[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]) DEFINESad[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, HASHSad[r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUALSad[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 COLUMNSSad[r].[EstCode], [r].[BudgetYear]))
| | | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))
| | | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | | |--Sort(ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
| | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[EstCode], [r].[BudgetYear]))
| | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))
| | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
| | | |--Sort(ORDER BYSad[Expr1235] ASC))
| | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNSSad[Expr1235]))
| | | |--Nested Loops(Inner Join, WHERESad[Expr1232]=[Expr1233]))
| | | |--Compute Scalar(DEFINESad[Expr1233]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0)))
| | | | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka]))
| | | |--Compute Scalar(DEFINESad[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(OBJECTSad[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em]))
| | |--Compute Scalar(DEFINESad[Expr1229]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.)))
| | |--Sort(ORDER BYSad[ei].[GPYear] ASC, [ei].[GPPeriod] ASC, [ei].[EstCode] ASC, [ei].[EstDebtorID] ASC))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID]))
| | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei]), WHERESadPROBE([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(OBJECTSad[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd]))
|--Top(TOP EXPRESSIONSad(100000)))
|--Compute Scalar(DEFINESad[Expr1231]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))
|--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
|--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))


OLD Server:


|--Compute Scalar(DEFINESad[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 EXPRESSIONSad(100000)))
|--Parallelism(Gather Streams, ORDER BYSad[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))
|--Nested Loops(Inner Join, WHERESad[Expr1254]=[Expr1255]))
|--Nested Loops(Inner Join, OUTER REFERENCESSad[ei].[GPYear], [ei].[GPPeriod]))
| |--Nested Loops(Inner Join, WHERESad[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]=[Expr1257] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]=[Expr1256]))
| | |--Sort(ORDER BYSad[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC))
| | | |--Merge Join(Inner Join, MANY-TO-MANY MERGESad[ei].[GPYear], [ei].[GPPeriod])=([ytd].[GPYear], [ytd].[GPPeriod]), RESIDUALSad[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 BYSad[ei].[GPYear] ASC, [ei].[GPPeriod] ASC))
| | | | |--Bitmap(HASHSad[r].[GPYear], [r].[GPPeriod]), DEFINESad[Bitmap1369]))
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod]))
| | | | |--Hash Match(Inner Join, HASHSad[r].[GPYear], [r].[GPPeriod], [ei].[EstCode])=([r].[GPYear], [r].[GPPeriod], [Expr1349]), RESIDUALSad[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, HASHSad[r].[GPYear], [r].[GPPeriod], [Expr1348])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode]), RESIDUALSad[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(DEFINESad[Expr1348]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))
| | | | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC))
| | | | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC))
| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]))
| | | | | | |--Hash Match(Partial Aggregate, HASHSad[r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUALSad[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]) DEFINESad[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, HASHSad[r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUALSad[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 COLUMNSSad[r].[EstCode], [r].[BudgetYear]))
| | | | | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))
| | | | | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | | | | |--Sort(ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
| | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | | | | |--Compute Scalar(DEFINESad[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(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
| | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[EstCode], [r].[BudgetYear]))
| | | | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))
| | | | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
| | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | | | |--Compute Scalar(DEFINESad[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(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
| | | | | |--Compute Scalar(DEFINESad[Expr1253]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.)))
| | | | | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei]))
| | | | |--Compute Scalar(DEFINESad[Expr1349]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0)))
| | | | |--Parallelism(Distribute Streams, RoundRobin Partitioning)
| | | | |--Top(TOP EXPRESSIONSad(100000)))
| | | | |--Parallelism(Gather Streams, ORDER BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC))
| | | | |--Stream Aggregate(GROUP BYSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINESad[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 BYSad[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC))
| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]))
| | | | |--Compute Scalar(DEFINESad[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(OBJECTSad[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
| | | |--Sort(ORDER BYSad[ytd].[GPYear] ASC, [ytd].[GPPeriod] ASC))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[ytd].[GPYear], [ytd].[GPPeriod]), WHERESadPROBE([Bitmap1369])=TRUE))
| | | |--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd]))
| | |--Table Spool
| | |--Compute Scalar(DEFINESad[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(OBJECTSad[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em]))
| |--Clustered Index Seek(OBJECTSad[NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc]), SEEKSad[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(DEFINESad[Expr1255]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0)))
|--Clustered Index Scan(OBJECTSad[NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka]))

Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
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


matt.gyton
matt.gyton
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 295
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?
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 3432
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search