Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: Sysprocesses says: wait forever

    I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
    The col column is mostly null.

    Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

    I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

    I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

    So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

    So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

    What should I do?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How about extacting the keys of the rows where coll is not null into a temp tabke and then use that as a direct entry into the data table to set the coll null. I f you want, you could even do them in small batches to avoid filling up the tran log.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    1. create a view that would return the desired result
    2. bcp...out from the view
    3. script out ALTER TABLE ... ADD/DROP CONSTRAINT ...
    4. run DROP CONSTRAINT script
    5. truncate table
    6. BULK INSERT from file created in step 2
    7. run ADD CONSTRAINT script
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Two things you can check to make sure the process is running along. You can check the cpu or physical_io columns in sysprocesses to see if they are climbing. Or you can check the waitresource column to make sure that the process is continuing on to the next page. Your process is likely waiting on a page latch for each page, which is normal. Just on most updates, there are only so many page latches to wait for.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    So it looks like the progress is just slower than I expected: it took 78 minutes to update 49.284.988 records in 10 steps of 10. Changed the statement to use a view instead, what seemed to help out (15m in 15 minutes). Using the same view I updated the remaining 50m records which ran 85 minutes. oh wel... I'm done, its over with.

    Could have tried using a temp-table tomh53 suggested; I didn't have the patience to wait for the select to finish.

    178 minutes for 113 m records, perhaps not too bad afterall.

    thanx for the help...

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    what about

    update tab set col = null
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    I had similar problem some time ago. It turned out that the solution was create/update statistics on the tables involved. The problem was that threads running on multiple processors couldn't synchronize. I would try that. mojza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •