January 25, 2003 at 12:51 pm
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.....
January 26, 2003 at 4:56 pm
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.
January 27, 2003 at 4:13 am
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.
January 27, 2003 at 11:59 am
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.
January 28, 2003 at 2:33 pm
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
January 28, 2003 at 2:46 pm
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