Help with query....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul (xx_at_nospam.com)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 07:12:35 -0500

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)

Relevant Pages

  • Help with query.....
    ... I have this query which I've left running for around 8 hours and does not ... Gig Memory, 4 CPU, connected to an EMC disk array. ... Candidates contains a clustered index on INDNUM, ...
    (microsoft.public.sqlserver.programming)
  • Re: output of qe90 Ingres version - (II 2.6/0305 (rs4.us5/00) operating system - AIX
    ... The problem I have is that a query runs on one of the machines almost ... I use qe90 the actual cpu time taken is over double on one of the ... you get estimates for Disk I/O and CPU ...
    (comp.databases.ingres)
  • Query is slow sometimes
    ... the query comes back in milliseconds. ... I've been watching CPU, Memory, Disk. ... spikes during hangs) I also checked to see if hangs were synching with ...
    (comp.databases.ms-sqlserver)
  • Re: Does a normalized design lead to complex queries?
    ... the reason is that if you have a person with skills A and B ... the join result of the query looking ... `materialid` intNOT NULL auto_increment, ... So candidates has attributes: first name, last name, id, DOB, height, ...
    (comp.databases)
  • RE: Has any Informix DBA had to do the following?
    ... I've never had that problem myself using IDS, granted then I was using c and esql/c for the connections so I knew every piece of code and had no thread issues. ... Disk access - busy disks but no predominant ... duration of the query. ... Has any Informix DBA had to do the following? ...
    (comp.databases.informix)