Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Slow UPDATE - Running for 16 hours and counting...

    The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.

    Code:
    UPDATE Recipients SET UndeliverableTime = getdate()
    FROM Recipients
    INNER JOIN Domains ON (Recipients.DomainID = Domains.ID)
    INNER JOIN Undeliverables ON (
         Recipients.UserName + '@' + Domains.Domain = 
         Undeliverables.EmailAddress)
    Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?

    Both tables (Recipients and Undeliverables) have approximately 80 million records

    I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.

    I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?

    thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to seriously consider a new server man. I can update 80 million rows in no time. Tell your company if they want to stay in business to buy a real computer.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    May I ask what constitutes a "real" computer.

    That system is using:

    Windows 2000
    SQL Server 2000 SP3a
    Intel P4 3.0 GHz (Hyperthreaded)
    2 GB RAM
    80 GB IDE disc (boot, OS, SQL Server application)
    133 SCSI RAID 5 composed of 9 physical drives (for data files, tempdb)
    250 GB IDE disc (for log file)

    What is the most necessary upgrade?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    RAM, RAM, RAM, then more RAM. The "off the shelf" versions of Windoze can only use 8 Gb, which makes that a good starting point in my opinion! While I will conceed that it is possible, I've never seen a database system that wouldn't do better with more RAM.

    -PatP

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    Is there any way to determine progress on 24 hour+ long mega-queries? My boss is eager for progress and it is so frustrating so be able to say or do nothing. I have two very unattractive options:

    a) wait for the query to finish. we are losing thousands of dollars a day, my boss is riding me to get this working, and we have no idea what SQL Server is doing. Will this take another hour or another year? I have no idea. I hate to have this exact same decision in front of me tomorrow night with absolutely no tangible progress whatsoever. It's like throwing time and money into a black hole!
    b) cancel, suffer the rollback, and try something different. This decision on a similar scenario on another server resulted in an estimated rollback time of 23 days!!!! 1.5 days into this so far; this is a devastating failure. We need that server up and running.

    These issues make me want to toss out SQL Server and do everything with custom tools that we program ourselves; at least we can cancel a query in less than a week! And get some kind of progress info on activities...

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Pat Phelan
    RAM, RAM, RAM, then more RAM. The "off the shelf" versions of Windoze can only use 8 Gb, which makes that a good starting point in my opinion!
    -PatP
    If I had some tangible evidence that such a move would result in a significant improvement, I could convince my management. But as it is, that sounds like throwing money into a huge gapnig black hole. I can't make anything remotely close to a realistic estimate on performance and turnaround times.

    For all we know, we could sink a fortune into 512GB RAM and the damn database would still take absurdly long 24 hour+ blocks of time to do simple operations.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Well, you're wrong about the RAM. You will throw much more money into trying to get these queries to run on that machine. RAM is the most important piece of a SQL Server.

    Another big upgrade would be to buy another three drives for your log files and set up RAID 10 for it. Buy a RAID 1, or RAID 10 set, for tempdb if you can afford it. And another processor wouldn't hurt either. Surely they can afford a little 2-way 1u box at least.

    What versions of SQL Server and Windows do you have?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Big updates like this should be done in parts and stages, so that if something goes wrong you don't have to roll back the whole thing. Don't blame SQL Server for lack of planning. It wouldn't be any faster to use a custom utility, because this is what SQL server is optimized to do.

    ...and your RollBack won't take any longer than restoring the database backup you performed just prior to starting this process, because in a worst-case scenario that's what you could to.
    Last edited by blindman; 06-08-04 at 00:13.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Is this some third-party piece of software or can you modify this thing? That recipients table should really hold the UserName and email address instead of having to reference the domain to get it. Either that, or you need to have a table that has all of them and the UserName. If you could get rid of that crazy +'@'+ and have a couple good indexes, I bet you could do wonders with this query.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you go back to one of the VLDB sessions I hosted for Microsoft ages ago, all of the experts got hit over and over and over again with "how can I make my SQL Server faster" phrased in various ways. The hardware answer in almost every case was "More RAM".

    The question hasn't changed. The answer hasn't changed. Disk topography is important, especially if you can bring a real SAN to bear on the problem. CPU is important for computations, but that is better handled by application servers than SQL Servers anyway. RAM is by far the best "bang for the buck" in the hardware area.

    keep in mind that smarter use of the SQL Server will buy you more performance than ANY hardware can. A well-designed app running on meager hardware will easily outrun a poorly written app on the best hardware that money can buy. Take a hard look at what you've already done, and what you are asking your SQL Server to do. That is where the biggest performance gains probably lie.

    -PatP

  11. #11
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Doesn't the standard version of SQL Server only use 2Gb of RAM? So if you going to throw money at RAM, wouldn't you need to upgrade to Enterprise edition (if not running it already?)

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point, but I'm pretty sure that we're Ok on this one.

    At least according to the BOL section on Managing AWE Memory, all versions of SQL-2000 can use it. I can't say that I've tried using AWE with anything but the Enterprise Edition, but I can't see any reason why it shouldn't work (I need to try this some day when I'm looking for ways to entertain myself, but that won't be very soon).

    If you have hardware that doesn't support PAE, or for some reason you can't use AWE (I've actually seen some sites where their stated security policy prevented it), then MS-SQL can only make use of at most 3 Gb of RAM.

    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Excuse me?

    80 MILLION records!

    And you're saying additional Ram and disk space?

    Why not boil the ocean?

    Either do it in Bathces or bcp the data with a view and reload the table.

    Wonder what the log would look like with 80 million updates....can you imagine a rollback?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

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

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And Pat...3:44 in the am?

    Are you drunk?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

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

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Kaiser dude!

    First you accuse me of being on crack, and now Pat is drunk? Who knew substance abuse was so rampant among dbas?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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