General information
Forum:
Microsoft SQL Server
Title:
Runaway UPDATE on Large Table
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?
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only