Interesting issue with transactiosn

  • Ok, I am using SQL 2000 and I have three tables. All three have at least 66000 rows of data. I use a stored procedure to update all three tables (66000 * 3 = 198000 rows, at least) and this is all being done within one transaction. When I commit I get the weirdest behavior. I can view the tables independently (SELECT * FROM Table1) but if I try to view them by a join (SELECT * FROM Table1, Table2 WHERE Table1.ID = Table2.ID) SQL server takes 99% of the CPU and all I can do is move my mouse...the system almost grinds to a halt doing something.

    Can anyone tell me why this is? I figure its the fact that I just did a 198000 row update but even after 5 min I get the same result. I know I should break the transaction size down (and I will) but I just want to know what is going on.

    Thanks.....

  • Try looking at the query plan.

    You may have to update statistics or rebuild indexes (defrag).


    Cursors never.
    DTS - only when needed and never to control.

  • You might also try using the current SQL standard format. That is the old way and not sure how they behave differently exactly yet.

    SELECT * FROM Table1 INNER JOIN Table2 ON Table1.[ID] = Table2.[ID]

    Could also be a missing index or missing statistics slowing you down. Since actually looking at the data is causing this you may want to do

    SET SHOWPLAN_TEXT ON

    GO

    YOUR_QUERY_HERE

    GO

    SET SHOWPLAN_OFF

    GO

    otherwise it will try to run the query but the plan is important to understand maybe why.

  • The problem is that the query doesn't even return (even with using INNER JOIN instead of the old way). I don’t know what is happening with the inner join because that never returns but when I do a select on one of the tables by it self it returns and it seems to be fine.

  • Sounds like you could be running your tempdb out of space and it is having to grow to meet the needs of joining all those rows in tempdb. I would show estmated execution plan in query analyzer first and see if there are any huge problems. Next I would watch tempdb or setup perfmon to watch your disk I/O or queue lenghts that will also point to file growth causing everything to shut down. Another issue may be memory paging also causing a huge slow down. It will also peg your cpu at 100%.

    Cheers,

    Wes

  • Agree, sounds like the box maybe running out of steam. Is a SQL only installation or any other applications installed aswell (resources)?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply