Build an index on the 20 million row table. Is it PAC? Make it a composite index on PAC.CID and PAC.Sys.

You're getting killed by your disk subsystem. A queue length of 140!!! The rule of thumb is that it shouldn't go above 2*(the number of disks).

Having the transaction log on a seperate physical disk would help alot also.

Be patient. This kind of thing doesn't happen in micro-seconds.

-Mike

>From Query Analyzer I attempted to run an update to a 13GB unindexed table (about 20 million rows) in a database sized at 30GB containing only the table in question and a 400-row joining table. After 36 hours running I cancelled the query. Here's the query code:
>
>UPDATE P
>SET P.Sys = W.Sys
>FROM PAC P INNER JOIN WORK W ON (P.CID = W.CID)
>WHERE P.Sys IS NULL
>
>Specifics:
>WIN2K Standard Server
>SQL 2K Standard
>4GB memory
>Promise ATA RAID 10
>Dual Pentium 1.8 processors
>
>
>PerfMon samples:
>Processor: between 1 and 3% utilized
>Avg. Disk sec/transfer: between .008 and .800
>Avg. Disk queue length: between 1 and 140
>Avg. Disk sec/write: between .005 and .800
>Avg. Disk sec/read: between .007 and 1.500
>
>By these statistics I infer that my disk subsystem is woefully inadequate, but I'm also wondering if memory could be a problem since memory usage is at about 1.9GB during the attempted UPDATE.
>I'm pretty green around the ears when it comes to tables of this size, but a very similar update query ran successfully in about 40 minutes when the table (despite the lack of indexing) was about 1GB smaller.
>
>Any ideas about how I can resolve this problem without spending a ton of money on a big box, SQL Enterprise and 64GB of memory? Will a SCSI subsystem be enough? If so, should I use fiber or would Ultra160 be enough?