SQL Server Performance Forum – Threads Archive
Poorly performing insert (confused)
Hi,I have had the following query running for 2.5 hours: insert into base.dbo.customerhierarchy
select * from test.dbo.customerhierarchy Tis is a simple insert from one table in one db to another. The table structure is identical. I have dropped the indexes from the table being inserted into. The number of records is 232248. Not that many. If I do a select count(*) from base.dbo.customerhierarchy with (readuncommitted) all the records appear to be there. However the query still continues to run. I cant understand what is going on. The box is a win 2003 with sql2005 SP1 1Gb ram. It is a 4 way dual core xeon with external disk array attached. It should chew this up. I am on the one on the box. I have checked the Syprocesses table. There is no blocking. CPU and physical_io is still increasing. Have tried.
run sp_lock 1st then run sp_who2 — look for runnable or suspended spids that may have that table. kill spid, rerun query I did as you suggested. sp_lock returned over 300000 rows and 99% were for the SPID doing the select into and referenced a key of a table that was not related to the one I was inserting into. Admittedly I thought this table was empty. So after 15hrs I killed the query. I then nuked the keys sp_lock referenced and started running then query again. Suprise suprise instead of KEY locks on this table it went to RIDs. Stopped the query. Nuked the data in the other table and have started running the query again. I cant understand this the tables are totally unrelated. I’m going to give it ten minutes and restart SQL. If anyone has any other suggestions as to what this could be please fire away. I’d hazard a guess that the sytem is paging:
232062 row insert, lots of locks, plenty of activity in the log file for rollback etc, page splits, etc
The 232062 rows must be read in off disk as well into memory What about a batched insert? Use a WHILE loop and the PK and TOP 10000 etc The server has 20GB ram.
It is a pretty poor show if SQL 2005 and a server of this spec cant handle this task without resorting to a looping mechanism. Having said that, I tried insert with select top 10000 and this took about 6.5 minutes.
There is no paging, disk queues or CPU contention. Do you or anyone else have any ohter suggestions? Thanks for any help
J.
Thanks to those who took the time to read this topic.
I have now solved the issue. There was one tool I failed to use in my efforts to resolve this issue. PROFILER. This would have exposed my gaping oversight. TRIGGERS. Yes I know I should have checked if these existed.
There were 4 nasty triggers on the table. Once these were disabled the query ran in seconds.
J.
]]>