Help with query....

I have this query which I've left running for around 8 hours and does not
return (but is eating a lot of CPU and DISK IO).
The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
above 25% used, 1.1 disk queue. This server is not being used by anyone
else accept my query.
Integrations_activity has 25million rows, candidate has 1.4 million rows.
Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
using MAXDOP 1 to see if parallelism made a difference and it still ran for
8 hours before I killed it.
The query plan is shown below
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock)
join Candidates (nolock) ON candidates.indnum = Integrations_Activity.IndNum
AND candidates.indofficenum = Integrations_Activity.Indofficenum
WHERE Integrations_Activity.typeofactivity = 'CAN' AND
Integrations_Activity.IndNum IS NOT NULL
StmtText
----
----
----
----
----
Update Integrations_Activity
Set CandidateID = Candidates.CandidateID
From Integrations_Activity (nolock), Candidates (nolock)
Where Integrations_Activity.IndNum IS NOT NULL
And Integrations_Activity.typeofactivity = 'CAN'
And Integrations_Activity.IndNum = Candidates.IndNum
And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
(1 row(s) affected)
StmtText
----
----
----
---
|--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH:([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000])
DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([Bmk1000]))
|--Hash Match(Inner Join,
HASH:([Candidates].[IndNum],
[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
[Expr1006]=[Candidates].[IndOfficeNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
| |--Hash Match(Inner Join,
HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Bmk1002]))
| | |--Index
Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
| |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Bmk1002]))
| |--Index
Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
|--Compute
Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
|--Table
Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
WHERE:([Integrations_Activity].[IndNum]<>NULL AND
[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
(15 row(s) affected)Answered in .programming. Please don't multi-post as we can't guess
everywhere where you posted the question.
Regards
Mike
"Paul" wrote:
> I have this query which I've left running for around 8 hours and does not
> return (but is eating a lot of CPU and DISK IO).
> The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
> 8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
> above 25% used, 1.1 disk queue. This server is not being used by anyone
> else accept my query.
> Integrations_activity has 25million rows, candidate has 1.4 million rows.
> Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
> using MAXDOP 1 to see if parallelism made a difference and it still ran for
> 8 hours before I killed it.
> The query plan is shown below
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum => Integrations_Activity.IndNum
> AND candidates.indofficenum => Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
> Integrations_Activity.IndNum IS NOT NULL
>
> StmtText
> ----
> ----
> ----
> ----
> ----
>
> Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock), Candidates (nolock)
> Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
> (1 row(s) affected)
> StmtText
> ----
> ----
> ----
> ---
> |--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
> SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH:([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000])
> DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([Bmk1000]))
> |--Hash Match(Inner Join,
> HASH:([Candidates].[IndNum],
> [Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
> RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
> [Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
> HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Bmk1002]))
> | | |--Index
> Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Bmk1002]))
> | |--Index
> Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
> Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
> Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
> WHERE:([Integrations_Activity].[IndNum]<>NULL AND
> [Integrations_Activity].[typeofactivity]='CAN') ORDERED)
> (15 row(s) affected)
>
>
>|||Can you post the CREATE TABLE AND CREATE INDEX statements, and the
non-parallel plan? Also, are there any constraints, indexed views, or
other dependent objects?
Do you have any idea how many rows this query will update?
Steve Kass
Drew University
Paul wrote:
>I have this query which I've left running for around 8 hours and does not
>return (but is eating a lot of CPU and DISK IO).
>The server is pretty powerful, Windows 2003, SQL 2K (all patched/hot fixed)
>8 Gig Memory, 4 CPU, connected to an EMC disk array. The server never goes
>above 25% used, 1.1 disk queue. This server is not being used by anyone
>else accept my query.
>Integrations_activity has 25million rows, candidate has 1.4 million rows.
>Candidates contains a clustered index on INDNUM, INDOFFICENUM. I've trying
>using MAXDOP 1 to see if parallelism made a difference and it still ran for
>8 hours before I killed it.
>The query plan is shown below
>Update Integrations_Activity
> Set CandidateID = Candidates.CandidateID
> From Integrations_Activity (nolock)
> join Candidates (nolock) ON candidates.indnum =>Integrations_Activity.IndNum
> AND candidates.indofficenum =>Integrations_Activity.Indofficenum
> WHERE Integrations_Activity.typeofactivity = 'CAN' AND
>Integrations_Activity.IndNum IS NOT NULL
>
>StmtText
>----
>----
>----
>----
>----
>
>Update Integrations_Activity
>Set CandidateID = Candidates.CandidateID
>From Integrations_Activity (nolock), Candidates (nolock)
>Where Integrations_Activity.IndNum IS NOT NULL
> And Integrations_Activity.typeofactivity = 'CAN'
> And Integrations_Activity.IndNum = Candidates.IndNum
> And Integrations_Activity.IndOfficeNum = Candidates.IndOfficeNum
>(1 row(s) affected)
>StmtText
>----
>----
>----
>---
> |--Table Update(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
>SET:([Integrations_Activity].[CandidateID]=[Candidates].[CandidateID]))
> |--Top(ROWCOUNT est 0)
> |--Parallelism(Gather Streams)
> |--Hash Match(Aggregate, HASH:([Bmk1000]),
>RESIDUAL:([Bmk1000]=[Bmk1000])
>DEFINE:([Candidates].[CandidateID]=ANY([Candidates].[CandidateID])))
> |--Parallelism(Repartition Streams, PARTITION
>COLUMNS:([Bmk1000]))
> |--Hash Match(Inner Join,
>HASH:([Candidates].[IndNum],
>[Candidates].[IndOfficeNum])=([Integrations_Activity].[IndNum], [Expr1006]),
>RESIDUAL:([Candidates].[IndNum]=[Integrations_Activity].[IndNum] AND
>[Expr1006]=[Candidates].[IndOfficeNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([Candidates].[IndNum], [Candidates].[IndOfficeNum]))
> | |--Hash Match(Inner Join,
>HASH:([Bmk1002])=([Bmk1002]), RESIDUAL:([Bmk1002]=[Bmk1002]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS:([Bmk1002]))
> | | |--Index
>Scan(OBJECT:([RMTEST].[dbo].[Candidates].[pk_Candidates]))
> | |--Parallelism(Repartition
>Streams, PARTITION COLUMNS:([Bmk1002]))
> | |--Index
>Scan(OBJECT:([RMTEST].[dbo].[Candidates].[idx_Candidates_IndNum]))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([Integrations_Activity].[IndNum], [Expr1006]))
> |--Compute
>Scalar(DEFINE:([Expr1006]=Convert([Integrations_Activity].[IndOfficeNum])))
> |--Table
>Scan(OBJECT:([RMTEST].[dbo].[Integrations_Activity]),
>WHERE:([Integrations_Activity].[IndNum]<>NULL AND
>[Integrations_Activity].[typeofactivity]='CAN') ORDERED)
>(15 row(s) affected)
>
>
>