SQL Server Bug or Limit or Something else

Question

SQL Server Bug or Limit or Something else

Benny Lopez

Ten Centuries

Points: 1171
More actions
January 17, 2005 at 7:19 pm

#59889

I have never seen this before. SQL Server 2000 hangs when running this query:
select * from LoanFundSubsCallCondSummaryLaExtMo
where MonthYr = '12/04'
However, when I remove the search condition is executes in 15 secs.
select * from LoanFundSubsCallCondSummaryLaExtMo
For your information the view is calling other views/functions or just tables as shown below. Also, the total size of the database is 1GB. The execution plan has about 50 steps.
Any ideas?
Thank you,
Ben
Object View name:LoanFundSubsCallCondSummaryLaExtMo
     |
     | 1) Depends on Object Function name:MinSec
     |
     | 2) Depends on Object View name:LoanCondsSummaryLaExtMo
     |
     |     | which Depends on Object View name:LoanCondsLAMo
     |     | which Depends on Object Table name:LA
     |
     | 3) Depends on Object View name:LoanFundSummaryLaExtMo
     |     | which Depends on Object Table name:LA
     |
     | 4) Depends on Object View name:BusDaysPerMonth
     |     | which Depends on Object Table nameimTime
     |
     | 5) Depends on Object View name:VMSummaryLAExtMo
     |     | which Depends on Object Function; Obj name:SecMin
     |     | which Depends on Object Table name:VMSumLAExtMo
     |     | which Depends on Object Table name:LA
     |
     | 6) Depends on Object View name:CallSummaryLAExtMo
     |     | which Depends on Object Table name:CallDtlSum
     |     | which Depends on Object Table name:LA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Did you take a look and compare the excution plans with and without the condition. If the condition increases the time you will see something change in the execution plan that might give you an answer. IF you can use SHOWPLAN_TEXT and post both here someone maybe be able to give a bit of advice on what they see.

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 2

Antares686 suggested that I post the showplan. Attached in the showplan for the query with the search condition on. (the one that hangs)

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------

SET STATISTICS PROFILE ON 9 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL

(1 row(s) affected)

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

----------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------

SET SHOWPLAN_text ON 10 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETON 0 NULL

(1 row(s) affected)

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------

select * from LoanFundSubsCallCondSummaryLaExtMo

where MonthYr = '12/04' 11 1 0 NULL NULL 1 NULL 3.0 NULL NULL NULL 20.50799 NULL NULL SELECT 0 NULL

|--Sort(ORDER BY[Expr1019] ASC, [LA].[TeamCode] ASC, [LA].[Person] ASC, [LA].[Extension] ASC, [Expr1046] ASC)) 11 2 1 Sort Sort ORDER BY[Expr1019] ASC, [LA].[TeamCode] ASC, [LA].[Person] ASC, [LA].[Extension] ASC, [Expr1046] ASC) NULL 3.0 1.1261261E-2 1.0756963E-4 152 20.50799 [Expr1014], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1037], [Expr1038], [Expr1039], [Expr1040], [Expr1041], [Expr1042], [Expr1043], [Expr1044], [Expr1045], [Expr1046] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1037]=str([Expr1021]/isnull(If ([Expr1022]=0) then NULL else [Expr1022], 1), 6, 2), [Expr1038]=str([Expr1022]/Convert([Expr1011]), 6, 2), [Expr1039]=Convert([Expr1033])+[Expr1020], [Expr1040]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1026]), 6, 2), [Expr1041]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1011]), 6, 2), [Expr1042]=[dbo].[MinSec](Convert([Expr1034]/isnull(If ([Expr1033]=0) then NULL else [Expr1033], 1))), [Expr1043]=str([Expr1020]/Convert(isnull(If (Convert([Expr1033])+[Expr1020]=0) then NULL else (Convert([Expr1033])+[Expr1020]), 1))*100, 6, 2)+'%', [Expr1044]=[Expr1020]-[Expr1021], [Expr1045]=str(([Expr1020]-[Expr1021])/Convert(isnull(If ([Expr1020]=0) then NULL else [Expr1020], 1))*100, 6, 2)+'%', [Expr1046]=Convert(right([Expr1032], 2)+substring([Expr1032], 1, 2)))) 11 3 2 Compute Scalar Compute Scalar DEFINE[Expr1037]=str([Expr1021]/isnull(If ([Expr1022]=0) then NULL else [Expr1022], 1), 6, 2), [Expr1038]=str([Expr1022]/Convert([Expr1011]), 6, 2), [Expr1039]=Convert([Expr1033])+[Expr1020], [Expr1040]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1026]), 6, 2), [Expr1041]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1011]), 6, 2), [Expr1042]=[dbo].[MinSec](Convert([Expr1034]/isnull(If ([Expr1033]=0) then NULL else [Expr1033], 1))), [Expr1043]=str([Expr1020]/Convert(isnull(If (Convert([Expr1033])+[Expr1020]=0) then NULL else (Convert([Expr1033])+[Expr1020]), 1))*100, 6, 2)+'%', [Expr1044]=[Expr1020]-[Expr1021], [Expr1045]=str(([Expr1020]-[Expr1021])/Convert(isnull(If ([Expr1020]=0) then NULL else [Expr1020], 1))*100, 6, 2)+'%', [Expr1046]=Convert(right([Expr1032], 2)+substring([Expr1032], 1, 2))) [Expr1037]=str([Expr1021]/isnull(If ([Expr1022]=0) then NULL else [Expr1022], 1), 6, 2), [Expr1038]=str([Expr1022]/Convert([Expr1011]), 6, 2), [Expr1039]=Convert([Expr1033])+[Expr1020], [Expr1040]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1026]), 6, 2), [Expr1041]=str((Convert([Expr1033])+[Expr1020])/Convert([Expr1011]), 6, 2), [Expr1042]=[dbo].[MinSec](Convert([Expr1034]/isnull(If ([Expr1033]=0) then NULL else [Expr1033], 1))), [Expr1043]=str([Expr1020]/Convert(isnull(If (Convert([Expr1033])+[Expr1020]=0) then NULL else (Convert([Expr1033])+[Expr1020]), 1))*100, 6, 2)+'%', [Expr1044]=[Expr1020]-[Expr1021], [Expr1045]=str(([Expr1020]-[Expr1021])/Convert(isnull(If ([Expr1020]=0) then NULL else [Expr1020], 1))*100, 6, 2)+'%', [Expr1046]=Convert(right([Expr1032], 2)+substring([Expr1032], 1, 2)) 3.0 0.0 3.0000001E-7 152 20.49662 [Expr1014], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1037], [Expr1038], [Expr1039], [Expr1040], [Expr1041], [Expr1042], [Expr1043], [Expr1044], [Expr1045], [Expr1046] NULL PLAN_ROW 0 1.0

|--Hash Match(Left Outer Join, HASH[Expr1019], [Expr1032], [LA].[TeamCode], [LA].[Person], [LA].[Extension])=([Expr1036], [Expr1002], [LA].[TeamCode], [LA].[Person], [LA].[Extension]), RESIDUAL((([Expr1019]=[Expr1036] AND [Expr1032]=[Expr1002]) AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension])) 11 4 3 Hash Match Left Outer Join HASH[Expr1019], [Expr1032], [LA].[TeamCode], [LA].[Person], [LA].[Extension])=([Expr1036], [Expr1002], [LA].[TeamCode], [LA].[Person], [LA].[Extension]), RESIDUAL((([Expr1019]=[Expr1036] AND [Expr1032]=[Expr1002]) AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) NULL 3.0 0.0 4.0582828E-2 176 20.49662 [Expr1011], [Expr1014], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1026], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

|--Nested Loops(Left Outer Join, OUTER REFERENCES[LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032])) 11 5 4 Nested Loops Left Outer Join OUTER REFERENCES[LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032]) NULL 2.0 0.0 0.00001254 161 11.257868 [Expr1011], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1026], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| |--Nested Loops(Inner Join) 11 6 5 Nested Loops Inner Join NULL NULL 1.0 0.0 4.1799999E-6 157 2.4293218 [Expr1011], [LA].[TeamCode], [LA].[Person], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| | |--Stream Aggregate(DEFINE[Expr1011]=MAX([DimTime].[MBusDay]))) 11 7 6 Stream Aggregate Aggregate NULL [Expr1011]=MAX([DimTime].[MBusDay]) 1.0 0.0 1.3986288E-5 9 0.04259437 [Expr1011] NULL PLAN_ROW 0 1.0

| | | |--Filter(WHERE[Expr1010]='12/04')) 11 8 7 Filter Filter WHERE[Expr1010]='12/04') NULL 139.86288 0.0 3.4848001E-4 14 4.2580385E-2 [DimTime].[MBusDay] NULL PLAN_ROW 0 1.0

| | | |--Compute Scalar(DEFINE[Expr1010]=[dbo].[Make2](Convert([DimTime].[Month]))+'/'+right(Convert([DimTime].[Year]), 2))) 11 9 8 Compute Scalar Compute Scalar DEFINE[Expr1010]=[dbo].[Make2](Convert([DimTime].[Month]))+'/'+right(Convert([DimTime].[Year]), 2)) [Expr1010]=[dbo].[Make2](Convert([DimTime].[Month]))+'/'+right(Convert([DimTime].[Year]), 2) 726.0 0.0 0.0000726 14 4.2231902E-2 [DimTime].[MBusDay], [Expr1010] NULL PLAN_ROW 0 1.0

| | | |--Table Scan(OBJECT[LoanDataMart].[dbo].[DimTime])) 11 10 9 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[DimTime]) [DimTime].[MBusDay], [DimTime].[Year], [DimTime].[Month] 726.0 4.1282203E-2 8.7709998E-4 33 4.2159304E-2 [DimTime].[MBusDay], [DimTime].[Year], [DimTime].[Month] NULL PLAN_ROW 0 1.0

| | |--Compute Scalar(DEFINE[Expr1020]=If ([Expr1142]=0) then NULL else [Expr1143], [Expr1022]=If ([Expr1144]=0) then NULL else [Expr1145], [Expr1023]=If ([Expr1146]=0) then NULL else [Expr1147])) 11 30 6 Compute Scalar Compute Scalar DEFINE[Expr1020]=If ([Expr1142]=0) then NULL else [Expr1143], [Expr1022]=If ([Expr1144]=0) then NULL else [Expr1145], [Expr1023]=If ([Expr1146]=0) then NULL else [Expr1147]) [Expr1020]=If ([Expr1142]=0) then NULL else [Expr1143], [Expr1022]=If ([Expr1144]=0) then NULL else [Expr1145], [Expr1023]=If ([Expr1146]=0) then NULL else [Expr1147] 1.0 0.0 7.6000001E-6 155 2.386723 [LA].[TeamCode], [LA].[Person], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| | |--Stream Aggregate(GROUP BY[Expr1019], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr]) DEFINE[Expr1142]=COUNT_BIG([VMSumLAExtMo].[VMCalls]), [Expr1143]=SUM([VMSumLAExtMo].[VMCalls]), [Expr1021]=SUM([VMSumLAExtMo].[VMMess]), [Expr1144]=COUNT_BIG([VMSumLAExtMo].[VMAcc]), [Expr1145]=SUM([VMSumLAExtMo].[VMAcc]), [Expr1146]=COUNT_BIG([dbo].[SecMin]([VMSumLAExtMo].[VMAvgAccTime])), [Expr1147]=SUM([dbo].[SecMin]([VMSumLAExtMo].[VMAvgAccTime])), [Expr1032]=ANY([Expr1032]), [Expr1033]=ANY([Expr1033]), [Expr1034]=ANY([Expr1034]), [Expr1019]=ANY([Expr1019]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]))) 11 31 30 Stream Aggregate Aggregate GROUP BY[Expr1019], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr]) [Expr1142]=COUNT_BIG([VMSumLAExtMo].[VMCalls]), [Expr1143]=SUM([VMSumLAExtMo].[VMCalls]), [Expr1021]=SUM([VMSumLAExtMo].[VMMess]), [Expr1144]=COUNT_BIG([VMSumLAExtMo].[VMAcc]), [Expr1145]=SUM([VMSumLAExtMo].[VMAcc]), [Expr1146]=COUNT_BIG([dbo].[SecMin]([VMSumLAExtMo].[VMAvgAccTime])), [Expr1147]=SUM([dbo].[SecMin]([VMSumLAExtMo].[VMAvgAccTime])), [Expr1032]=ANY([Expr1032]), [Expr1033]=ANY([Expr1033]), [Expr1034]=ANY([Expr1034]), [Expr1019]=ANY([Expr1019]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]) 1.0 0.0 7.6000001E-6 155 2.386723 [LA].[TeamCode], [LA].[Person], [Expr1019], [Expr1021], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1019], [Expr1032], [Expr1033], [Expr1034], [Expr1142], [Expr1143], [Expr1144], [Expr1145], [Expr1146], [Expr1147] NULL PLAN_ROW 0 1.0

| | |--Sort(ORDER BY[Expr1019] ASC, [LA].[TeamCode] ASC, [LA].[Person] ASC, [LA].[Extension] ASC, [VMSumLAExtMo].[Extension] ASC, [VMSumLAExtMo].[MonthYr] ASC)) 11 33 31 Sort Sort ORDER BY[Expr1019] ASC, [LA].[TeamCode] ASC, [LA].[Person] ASC, [LA].[Extension] ASC, [VMSumLAExtMo].[Extension] ASC, [VMSumLAExtMo].[MonthYr] ASC) NULL 1.0 1.1261261E-2 1.00143E-4 143 2.3867154 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [LA].[Person], [LA].[Extension], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| | |--Filter(WHEREConvert([LA].[Extension])=[VMSumLAExtMo].[Extension])) 11 34 33 Filter Filter WHEREConvert([LA].[Extension])=[VMSumLAExtMo].[Extension]) NULL 1.0 0.0 5.8000001E-7 143 2.3753541 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| | |--Compute Scalar(DEFINE[Expr1033]=If ([Expr1138]=0) then NULL else [Expr1139], [Expr1034]=If ([Expr1140]=0) then NULL else [Expr1141])) 11 35 34 Compute Scalar Compute Scalar DEFINE[Expr1033]=If ([Expr1138]=0) then NULL else [Expr1139], [Expr1034]=If ([Expr1140]=0) then NULL else [Expr1141]) [Expr1033]=If ([Expr1138]=0) then NULL else [Expr1139], [Expr1034]=If ([Expr1140]=0) then NULL else [Expr1141] 1.0 0.0 9.0826325E-6 143 2.3753536 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032], [Expr1033], [Expr1034] NULL PLAN_ROW 0 1.0

| | |--Stream Aggregate(GROUP BY[LA].[Extension], [Rank1071]) DEFINE[Expr1138]=COUNT_BIG([CallDtlSum].[SumCalls]), [Expr1139]=SUM([CallDtlSum].[SumCalls]), [Expr1140]=COUNT_BIG([CallDtlSum].[SumDur]), [Expr1141]=SUM([CallDtlSum].[SumDur]), [Expr1031]=ANY([Expr1019]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]), [Expr1032]=ANY([Expr1032]), [VMSumLAExtMo].[VMCalls]=ANY([VMSumLAExtMo].[VMCalls]), [VMSumLAExtMo].[VMMess]=ANY([VMSumLAExtMo].[VMMess]), [VMSumLAExtMo].[VMAcc]=ANY([VMSumLAExtMo].[VMAcc]), [VMSumLAExtMo].[VMAvgAccTime]=ANY([VMSumLAExtMo].[VMAvgAccTime]), [VMSumLAExtMo].[Extension]=ANY([VMSumLAExtMo].[Extension]), [VMSumLAExtMo].[MonthYr]=ANY([VMSumLAExtMo].[MonthYr]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]), [Expr1019]=ANY([Expr1019]))) 11 36 35 Stream Aggregate Aggregate GROUP BY[LA].[Extension], [Rank1071]) [Expr1138]=COUNT_BIG([CallDtlSum].[SumCalls]), [Expr1139]=SUM([CallDtlSum].[SumCalls]), [Expr1140]=COUNT_BIG([CallDtlSum].[SumDur]), [Expr1141]=SUM([CallDtlSum].[SumDur]), [Expr1031]=ANY([Expr1019]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]), [Expr1032]=ANY([Expr1032]), [VMSumLAExtMo].[VMCalls]=ANY([VMSumLAExtMo].[VMCalls]), [VMSumLAExtMo].[VMMess]=ANY([VMSumLAExtMo].[VMMess]), [VMSumLAExtMo].[VMAcc]=ANY([VMSumLAExtMo].[VMAcc]), [VMSumLAExtMo].[VMAvgAccTime]=ANY([VMSumLAExtMo].[VMAvgAccTime]), [VMSumLAExtMo].[Extension]=ANY([VMSumLAExtMo].[Extension]), [VMSumLAExtMo].[MonthYr]=ANY([VMSumLAExtMo].[MonthYr]), [LA].[TeamCode]=ANY([LA].[TeamCode]), [LA].[Person]=ANY([LA].[Person]), [Expr1019]=ANY([Expr1019]) 1.0 0.0 9.0826325E-6 143 2.3753536 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032], [Expr1138], [Expr1139], [Expr1140], [Expr1141] NULL PLAN_ROW 0 1.0

| | |--Sort(ORDER BY[LA].[Extension] ASC, [Rank1071] ASC)) 11 38 36 Sort Sort ORDER BY[LA].[Extension] ASC, [Rank1071] ASC) NULL 1.583083 1.1261261E-2 1.017487E-4 112 2.3753445 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [LA].[Person], [LA].[Extension], [Expr1032], [Rank1071] NULL PLAN_ROW 0 1.0

| | |--Hash Match(Inner Join, HASH[Expr1019], [LA].[TeamCode], [LA].[Person], [VMSumLAExtMo].[MonthYr])=([Expr1031], [LA].[TeamCode], [LA].[Person], [Expr1094]), RESIDUAL(([Expr1031]=[Expr1019] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [Expr1094]=[VMSumLAExtMo].[MonthYr])) 11 39 38 Hash Match Inner Join HASH[Expr1019], [LA].[TeamCode], [LA].[Person], [VMSumLAExtMo].[MonthYr])=([Expr1031], [LA].[TeamCode], [LA].[Person], [Expr1094]), RESIDUAL(([Expr1031]=[Expr1019] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [Expr1094]=[VMSumLAExtMo].[MonthYr]) NULL 1.583083 0.0 6.6177905E-2 148 2.3639815 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032], [Rank1071] NULL PLAN_ROW 0 1.0

| | |--Rank 11 40 39 Rank Rank NULL NULL 442.0 0.0 4.4200002E-4 98 0.12540798 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019], [Rank1071] NULL PLAN_ROW 0 1.0

| | | |--Compute Scalar(DEFINE[Expr1019]=[LA].[DivID]-100)) 11 41 40 Compute Scalar Compute Scalar DEFINE[Expr1019]=[LA].[DivID]-100) [Expr1019]=[LA].[DivID]-100 442.0 0.0 4.4200002E-5 94 0.12496597 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[TeamCode], [LA].[Person], [Expr1019] NULL PLAN_ROW 0 1.0

| | | |--Hash Match(Inner Join, HASH[Expr1096])=([VMSumLAExtMo].[Extension]), RESIDUAL[VMSumLAExtMo].[Extension]=[Expr1096])) 11 42 41 Hash Match Inner Join HASH[Expr1096])=([VMSumLAExtMo].[Extension]), RESIDUAL[VMSumLAExtMo].[Extension]=[Expr1096]) NULL 442.0 0.0 4.1522209E-2 94 0.12492178 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr], [LA].[DivID], [LA].[TeamCode], [LA].[Person] NULL PLAN_ROW 0 1.0

| | | |--Compute Scalar(DEFINE[Expr1096]=Convert([LA].[Extension]))) 11 43 42 Compute Scalar Compute Scalar DEFINE[Expr1096]=Convert([LA].[Extension])) [Expr1096]=Convert([LA].[Extension]) 442.0 0.0 4.4199998E-5 52 4.0409621E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [Expr1096] NULL PLAN_ROW 0 1.0

| | | | |--Table Scan(OBJECT[LoanDataMart].[dbo].[LA])) 11 44 43 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LA]) [LA].[Extension], [LA].[DivID], [LA].[TeamCode], [LA].[Person] 442.0 3.9800722E-2 5.6469999E-4 122 4.0365424E-2 [LA].[Extension], [LA].[DivID], [LA].[TeamCode], [LA].[Person] NULL PLAN_ROW 0 1.0

| | | |--Clustered Index Scan(OBJECT[LoanDataMart].[dbo].[VMSumLAExtMo].[PK_VM])) 11 49 42 Clustered Index Scan Clustered Index Scan OBJECT[LoanDataMart].[dbo].[VMSumLAExtMo].[PK_VM]) [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr] 805.0 4.2022943E-2 9.6400001E-4 82 4.2986944E-2 [VMSumLAExtMo].[VMCalls], [VMSumLAExtMo].[VMMess], [VMSumLAExtMo].[VMAcc], [VMSumLAExtMo].[VMAvgAccTime], [VMSumLAExtMo].[Extension], [VMSumLAExtMo].[MonthYr] NULL PLAN_ROW 0 1.0

| | |--Compute Scalar(DEFINE[Expr1094]=Convert([Expr1032]))) 11 56 39 Compute Scalar Compute Scalar DEFINE[Expr1094]=Convert([Expr1032])) [Expr1094]=Convert([Expr1032]) 2549.8835 0.0 2.5498835E-4 65 2.1723826 [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032], [Expr1094] NULL PLAN_ROW 0 1.0

| | |--Filter(WHERE[Expr1032]='12/04')) 11 57 56 Filter Filter WHERE[Expr1032]='12/04') NULL 2549.8835 0.0 1.6721236E-2 58 2.1721275 [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1.0

| | |--Compute Scalar(DEFINE[Expr1031]=[LA].[DivID]-100, [Expr1032]=[dbo].[Make2](Convert(datepart(month, [CallDtlSum].[CallDate])))+'/'+right(Convert(datepart(year, [CallDtlSum].[CallDate])), 2))) 11 58 57 Compute Scalar Compute Scalar DEFINE[Expr1031]=[LA].[DivID]-100, [Expr1032]=[dbo].[Make2](Convert(datepart(month, [CallDtlSum].[CallDate])))+'/'+right(Convert(datepart(year, [CallDtlSum].[CallDate])), 2)) [Expr1031]=[LA].[DivID]-100, [Expr1032]=[dbo].[Make2](Convert(datepart(month, [CallDtlSum].[CallDate])))+'/'+right(Convert(datepart(year, [CallDtlSum].[CallDate])), 2) 34835.906 0.0 3.4835909E-3 58 2.1554062 [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1.0

| | |--Hash Match(Inner Join, HASH[LA].[Extension])=([CallDtlSum].[TermID]), RESIDUAL[LA].[Extension]=[CallDtlSum].[TermID])) 11 59 58 Hash Match Inner Join HASH[LA].[Extension])=([CallDtlSum].[TermID]), RESIDUAL[LA].[Extension]=[CallDtlSum].[TermID]) NULL 34835.906 0.0 1.2088606 67 2.1519227 [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [CallDtlSum].[CallDate], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

| | |--Table Scan(OBJECT[LoanDataMart].[dbo].[LA])) 11 60 59 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LA]) [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] 442.0 3.9800722E-2 5.6469999E-4 89 4.0365424E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

| | |--Clustered Index Scan(OBJECT[LoanDataMart].[dbo].[CallDtlSum].[PK_CallDtlSum])) 11 61 59 Clustered Index Scan Clustered Index Scan OBJECT[LoanDataMart].[dbo].[CallDtlSum].[PK_CallDtlSum]) [CallDtlSum].[TermID], [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [CallDtlSum].[CallDate] 172929.0 0.71239334 0.19030041 62 0.90269369 [CallDtlSum].[TermID], [CallDtlSum].[SumCalls], [CallDtlSum].[SumDur], [CallDtlSum].[CallDate] NULL PLAN_ROW 0 1.0

| |--Table Spool 11 236 5 Table Spool Lazy Spool NULL NULL 1.0 1.6756756E-2 2.7999999E-7 11 8.8285332 [Expr1026] NULL PLAN_ROW 0 3.0

| |--Filter(WHERE((([Expr1035]=[Expr1019] AND [Expr1006]=[Expr1032]) AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension])) 11 237 236 Filter Filter WHERE((([Expr1035]=[Expr1019] AND [Expr1006]=[Expr1032]) AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) NULL 1.0 0.0 7.6704384E-3 79 8.8117762 [Expr1026] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1035]=[LA].[DivID]-100)) 11 238 237 Compute Scalar Compute Scalar DEFINE[Expr1035]=[LA].[DivID]-100) [Expr1035]=[LA].[DivID]-100 4309.2349 0.0 4.3092351E-4 79 8.8041058 [Expr1006], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1026], [Expr1035] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1026]=If ([Expr1150]=0) then NULL else [Expr1151])) 11 239 238 Compute Scalar Compute Scalar DEFINE[Expr1026]=If ([Expr1150]=0) then NULL else [Expr1151]) [Expr1026]=If ([Expr1150]=0) then NULL else [Expr1151] 4309.2349 0.0 0.41340277 79 8.8036747 [Expr1006], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1026] NULL PLAN_ROW 0 1.0

| |--Hash Match(Aggregate, HASH[LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1006]), RESIDUAL((([LA].[DivID]=[LA].[DivID] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) AND [Expr1006]=[Expr1006]) DEFINE[Expr1150]=COUNT_BIG([Expr1007]), [Expr1151]=SUM([Expr1007]))) 11 240 239 Hash Match Aggregate HASH[LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1006]), RESIDUAL((([LA].[DivID]=[LA].[DivID] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) AND [Expr1006]=[Expr1006]) [Expr1150]=COUNT_BIG([Expr1007]), [Expr1151]=SUM([Expr1007]) 4309.2349 0.0 0.41340277 79 8.8036747 [Expr1006], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1150], [Expr1151] NULL PLAN_ROW 0 1.0

| |--Hash Match(Inner Join, HASH[Expr1092])=([Expr1093]), RESIDUAL[Expr1093]=[Expr1092])) 11 241 240 Hash Match Inner Join HASH[Expr1092])=([Expr1093]), RESIDUAL[Expr1093]=[Expr1092]) NULL 18004.178 0.0 0.07856153 79 8.3902721 [Expr1006], [Expr1007], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1092]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL)))) 11 242 241 Compute Scalar Compute Scalar DEFINE[Expr1092]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL))) [Expr1092]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL)) 442.0 0.0 4.4199998E-5 99 4.0409621E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1092] NULL PLAN_ROW 0 1.0

| | |--Table Scan(OBJECT[LoanDataMart].[dbo].[LA])) 11 243 242 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LA]) [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] 442.0 3.9800722E-2 5.6469999E-4 122 4.0365424E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1093]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', ''))) 11 268 241 Compute Scalar Compute Scalar DEFINE[Expr1093]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', '')) [Expr1093]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', '') 1067.6005 0.0 1.0676005E-4 4019 8.2712984 [Expr1006], [Expr1007], [Expr1093] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1007]=If ([Expr1148]=0) then NULL else [Expr1149])) 11 269 268 Compute Scalar Compute Scalar DEFINE[Expr1007]=If ([Expr1148]=0) then NULL else [Expr1149]) [Expr1007]=If ([Expr1148]=0) then NULL else [Expr1149] 1067.6005 0.0 0.66502059 34 8.2711916 [LoanSummaryB].[LA], [Expr1006], [Expr1007] NULL PLAN_ROW 0 1.0

| |--Hash Match(Aggregate, HASH[Expr1006], [LoanSummaryB].[LA]), RESIDUAL[Expr1006]=[Expr1006] AND [LoanSummaryB].[LA]=[LoanSummaryB].[LA]) DEFINE[Expr1148]=COUNT_BIG([LoanSummaryB].[Cnt]), [Expr1149]=SUM([LoanSummaryB].[Cnt]))) 11 270 269 Hash Match Aggregate HASH[Expr1006], [LoanSummaryB].[LA]), RESIDUAL[Expr1006]=[Expr1006] AND [LoanSummaryB].[LA]=[LoanSummaryB].[LA]) [Expr1148]=COUNT_BIG([LoanSummaryB].[Cnt]), [Expr1149]=SUM([LoanSummaryB].[Cnt]) 1067.6005 0.0 0.66502059 34 8.2711916 [LoanSummaryB].[LA], [Expr1006], [Expr1148], [Expr1149] NULL PLAN_ROW 0 1.0

| |--Compute Scalar(DEFINE[Expr1006]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2))) 11 271 270 Compute Scalar Compute Scalar DEFINE[Expr1006]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2)) [Expr1006]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2) 94012.727 0.0 9.401273E-3 34 7.6061707 [LoanSummaryB].[Cnt], [LoanSummaryB].[LA], [Expr1006] NULL PLAN_ROW 0 1.0

| |--Table Scan(OBJECT[LoanDataMart].[dbo].[LoanSummaryB]), WHERE[LoanSummaryB].[TYPE]='FUND')) 11 272 271 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LoanSummaryB]), WHERE[LoanSummaryB].[TYPE]='FUND') [LoanSummaryB].[Cnt], [LoanSummaryB].[TYPE], [LoanSummaryB].[CYCLEDATE], [LoanSummaryB].[LA] 94012.727 6.9346156 0.46101701 70 7.3956327 [LoanSummaryB].[Cnt], [LoanSummaryB].[TYPE], [LoanSummaryB].[CYCLEDATE], [LoanSummaryB].[LA] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1036]=[LA].[DivID]-100)) 11 369 4 Compute Scalar Compute Scalar DEFINE[Expr1036]=[LA].[DivID]-100) [Expr1036]=[LA].[DivID]-100 3461.2803 0.0 3.4612804E-4 79 9.1981535 [Expr1002], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1014], [Expr1036] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1014]=If ([Expr1154]=0) then NULL else [Expr1155])) 11 370 369 Compute Scalar Compute Scalar DEFINE[Expr1014]=If ([Expr1154]=0) then NULL else [Expr1155]) [Expr1014]=If ([Expr1154]=0) then NULL else [Expr1155] 3461.2803 0.0 0.38981929 79 9.1978083 [Expr1002], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1014] NULL PLAN_ROW 0 1.0

|--Hash Match(Aggregate, HASH[LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1002]), RESIDUAL((([LA].[DivID]=[LA].[DivID] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) AND [Expr1002]=[Expr1002]) DEFINE[Expr1154]=COUNT_BIG([Expr1003]), [Expr1155]=SUM([Expr1003]))) 11 371 370 Hash Match Aggregate HASH[LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1002]), RESIDUAL((([LA].[DivID]=[LA].[DivID] AND [LA].[TeamCode]=[LA].[TeamCode]) AND [LA].[Person]=[LA].[Person]) AND [LA].[Extension]=[LA].[Extension]) AND [Expr1002]=[Expr1002]) [Expr1154]=COUNT_BIG([Expr1003]), [Expr1155]=SUM([Expr1003]) 3461.2803 0.0 0.38981929 79 9.1978083 [Expr1002], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1154], [Expr1155] NULL PLAN_ROW 0 1.0

|--Hash Match(Inner Join, HASH[Expr1090])=([Expr1091]), RESIDUAL[Expr1091]=[Expr1090])) 11 372 371 Hash Match Inner Join HASH[Expr1090])=([Expr1091]), RESIDUAL[Expr1091]=[Expr1090]) NULL 22765.232 0.0 8.7426595E-2 79 8.8079882 [Expr1002], [Expr1003], [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1090]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL)))) 11 373 372 Compute Scalar Compute Scalar DEFINE[Expr1090]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL))) [Expr1090]=substring([LA].[Person], 1, 1)+' '+substring([LA].[Person], charindex(' ', [LA].[Person], NULL)+1, len([LA].[Person])-charindex(' ', [LA].[Person], NULL)) 442.0 0.0 4.4199998E-5 99 4.0409621E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension], [Expr1090] NULL PLAN_ROW 0 1.0

| |--Table Scan(OBJECT[LoanDataMart].[dbo].[LA])) 11 374 373 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LA]) [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] 442.0 3.9800722E-2 5.6469999E-4 122 4.0365424E-2 [LA].[DivID], [LA].[TeamCode], [LA].[Person], [LA].[Extension] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1091]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', ''))) 11 399 372 Compute Scalar Compute Scalar DEFINE[Expr1091]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', '')) [Expr1091]=replace(rtrim(substring([LoanSummaryB].[LA], charindex(',', [LoanSummaryB].[LA], NULL)+2, 1))+' '+substring([LoanSummaryB].[LA], 1, charindex(',', [LoanSummaryB].[LA], NULL)), ',', '') 1459.7344 0.0 1.4597343E-4 4019 8.6801491 [Expr1002], [Expr1003], [Expr1091] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1003]=If ([Expr1152]=0) then NULL else [Expr1153])) 11 400 399 Compute Scalar Compute Scalar DEFINE[Expr1003]=If ([Expr1152]=0) then NULL else [Expr1153]) [Expr1003]=If ([Expr1152]=0) then NULL else [Expr1153] 1459.7344 0.0 0.90276563 34 8.6800032 [LoanSummaryB].[LA], [Expr1002], [Expr1003] NULL PLAN_ROW 0 1.0

|--Hash Match(Aggregate, HASH[Expr1002], [LoanSummaryB].[LA]), RESIDUAL[Expr1002]=[Expr1002] AND [LoanSummaryB].[LA]=[LoanSummaryB].[LA]) DEFINE[Expr1152]=COUNT_BIG([LoanSummaryB].[Cnt]), [Expr1153]=SUM([LoanSummaryB].[Cnt]))) 11 401 400 Hash Match Aggregate HASH[Expr1002], [LoanSummaryB].[LA]), RESIDUAL[Expr1002]=[Expr1002] AND [LoanSummaryB].[LA]=[LoanSummaryB].[LA]) [Expr1152]=COUNT_BIG([LoanSummaryB].[Cnt]), [Expr1153]=SUM([LoanSummaryB].[Cnt]) 1459.7344 0.0 0.90276563 34 8.6800032 [LoanSummaryB].[LA], [Expr1002], [Expr1152], [Expr1153] NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1002]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2))) 11 402 401 Compute Scalar Compute Scalar DEFINE[Expr1002]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2)) [Expr1002]=[dbo].[Make2](Convert(datepart(month, [LoanSummaryB].[CYCLEDATE])))+'/'+right(Convert(datepart(year, [LoanSummaryB].[CYCLEDATE])), 2) 128543.98 0.0 1.2854397E-2 34 7.7772379 [LoanSummaryB].[Cnt], [LoanSummaryB].[LA], [Expr1002] NULL PLAN_ROW 0 1.0

|--Table Scan(OBJECT[LoanDataMart].[dbo].[LoanSummaryB]), WHERE[LoanSummaryB].[TYPE]='SUBS' AND [LoanSummaryB].[LoanWCond]='Y')) 11 403 402 Table Scan Table Scan OBJECT[LoanDataMart].[dbo].[LoanSummaryB]), WHERE[LoanSummaryB].[TYPE]='SUBS' AND [LoanSummaryB].[LoanWCond]='Y') [LoanSummaryB].[LoanWCond], [LoanSummaryB].[TYPE], [LoanSummaryB].[Cnt], [LoanSummaryB].[CYCLEDATE], [LoanSummaryB].[LA] 128543.98 6.9346156 0.46101701 70 7.3956327 [LoanSummaryB].[LoanWCond], [LoanSummaryB].[TYPE], [LoanSummaryB].[Cnt], [LoanSummaryB].[CYCLEDATE], [LoanSummaryB].[LA] NULL PLAN_ROW 0 1.0

(54 row(s) affected)

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

----------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------

SET STATISTICS PROFILE OFF 12 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL SETSTATON 0 NULL

(1 row(s) affected)

Angela But Hall of Fame Points: 3848 More actions · Answer 3

Have you updated your statistics before you run this? I have seen bizarre execution plans when stats aren't up to date.

Angela But Hall of Fame Points: 3848 More actions · Answer 4

Have you updated your statistics before you run this? I have seen bizarre execution plans when stats aren't up to date.

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 5

AngelaBut hasked if I had updated the table statistics.

Yes, I did run the update stats for all the base tables but the query still hangs.

I let it run for 10 min and watched the process reach the following numbers:

cpu - 600,000

i/o - 673

memory - 464

What was interesting is that the i/o and memory reached these numbers early on, within 20 secs and remained unchanged the rest of the time. The cpu counter kept increasing indicating that the system is doing a fair amount of thinking. When I run the query w/out the search condition the result comes back in 16 seconds and the counter values are:

cpu - 620,000

i/o - 971

memory - 510

This is telling me that perhaps I should let it run for as long as it needs to run as see what happens. I have been reluctant to this because it I feel that it should come back within 1 min.

What's your thinking?

Angela But Hall of Fame Points: 3848 More actions · Answer 6

Have you thought about adding an index to the MonthYr column?

I can see a lot of table scans happening on [LoanDataMart] you definately need to address that.

Just posting some ideas that you may want to try.

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 7

A quick update on this issue.

Good news!!!. I let the query run for as long as it needed. It did complete after 16 minutes. The final stats are shown below. As it turned out, it needed a lot of CPU to complete. I don't know in what units is the CPU counter expressed but it was almost 1,000,000.

Before starting to add indexes I created a memory table and stored the result set there and did a select from there. The whole thing runs in 16 secs. This meant wrapping the code in a stored proc.

I will look into adding indexes on the MonthYr column as suggested by AngelaBut.

Thank you,

-----------------------------------------------

Stats:

cpu physical_io memusage

----------- -------------------- -----------

930516 30 54

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Having just glanced through the plan and seen the extensive piggybacking of views on views and functions all over the place, I can just say that I'm not surprised this beast takes a heavy toll on server resources....

It's not uncommon that execute times gallop away in this type of scenarios. Should you have a particularly important or critical query that absolutely needs to come down in time, often (if not always) the way to do that is to re-write as 'clean' as possible. That is, no views, no function calls, no extra nothing - just the bare metal to get it doing what's needed.

On the other hand, some stuff demands more CPU than other stuff, it just depends. 16 minutes may be 'good enough' for the reasons this query is used.

/Kenneth

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 9

Two comments on Kenneth reply:

Kenneth said that he is not surprised to see that the beast is taking its toll on server resources. My question however, is why query : "select * from table where col="xyz"" takes 6,300% more time than "select * from table". What part of the execution plan is showing that this is what is going to happen?

By the way, I am building a reporting system using 100% SQL Server. This, of course, include views that call other views, etc. I bring the data from multiple sources, store in SQL Server, crunch it using view/functions, have a set of stored procs that create the final tables upon which the reporting tool runs.All the number runching runs in a under 3 mins once a day, and the response time to the user viewing the reports is a few seconds over HTTP!. This is pretty good, no let me paraphrase that, this is excellent!!.

In Keneth view, he would "often (if not always) the way to do that is to re-write as 'clean' ". There is nothing wrong with the views calling other views/functions. It performs very well, and this reporting project is a living proof of it. I would not recommed anything like this on an OLTP system but for reporting is perfect.

Anyway, to Kenneth, AngelaBut, and Antares686, thank you very much for your input. I hope I could tell you more about indexing but I haven't had the time to do it yet.

Ben

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 10

Try using the WITH(NOEXPAND) option in your query.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.