insert into / select – blocks resource | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

insert into / select – blocks resource

Hi, We are facing a weird problem, we run the following statement insert into #tbl
select fld1, fld2 from table Here, it takes plenty of time for execution, cpu utilization is touching 100% and we are not able to view lock/process information in enterprise manager. When we run the select statement separately, it takes only 30-40 seconds to display the output. The select statement is slightly a complex one, it contains couple of derived tables and a subquery and things like that, more over this code had been working in the production server for more than 6 months. It started giving some trouble only now, would it be due to data, any suggestions is highly helpful and appreciated, thanks in advance. Regards,
Deva
Hi,
in QA run sp_who2 to identify block and all running processes , sp_lock to get lock information.
how often you run index tunning – defragment !? are you exaamining EP for your query ?!
we need some more information to isolate your problem :
1) is your server patched up with latest SP and MDAC ?
2) is all statistic updated ?
3) is insert into / bulkcopy permited !?
4) are queries have complex calulations !?
and also search refer forum for relavent topics: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8957 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3013 http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9488
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

What kind of number of rows are we talking about? Does the temp table have any columns that are particularly long? Seeing that temp tables are created in the system database called tempdb, you should also check the file sizes of tempdb, and the file growth settings for tempdb. Finally you could look at disk fragmentation issues. Does the temp table have indexes and constraints defined? Constraints are not really needed: the data insert should already take care of PK and FK stuff. You need a Primary Key and indexes only once you start working with the data, so you could add them after the data insert – this may save time during the data insert. Finally, you could check if it helps to add WITH (NOLOCK) after each of the tables in your FROM clause. You should not do this in case you are dealing with time-critical data — but then again you are copying the data to a temporary table for further processing, so that is probably a mute point.
Hi Hemant, After running the above mentioned insert into/select statement, server hits 100% cpu utilization, from EM, it allowed me once to monitor the process/lock details, later even I could not do that, it said request time out exceeded. 1) is your server patched up with latest SP and MDAC ? Yes, it is a production server.
2) is all statistic updated ? Yes, because, the select statement when I run separately, it gets over in 30-40 seconds.
3) is insert into / bulkcopy permited !? Yes.
4) are queries have complex calulations !? No, only it uses a few derived tables, but pretty ok. Hi Adriaan, What kind of number of rows are we talking about? Does the temp table have any columns that are particularly long? No, output is suppose to go to the production table directly, since we faced the problem, I tried with temporary table. Seeing that temp tables are created in the system database called tempdb, you should also check the file sizes of tempdb, and the file growth settings for tempdb. Finally you could look at disk fragmentation issues. I believe they are all fine, otherwise, I could get this issue in other places as well, it is happening regularly here alone. Does the temp table have indexes and constraints defined? Constraints are not really needed: the data insert should already take care of PK and FK stuff. You need a Primary Key and indexes only once you start working with the data, so you could add them after the data insert – this may save time during the data insert. Absolutely, since, it is a temp. table, main purpose is to hold data during process, no constraints, keys, etc. Finally, you could check if it helps to add WITH (NOLOCK) after each of the tables in your FROM clause. You should not do this in case you are dealing with time-critical data — but then again you are copying the data to a temporary table for further processing, so that is probably a mute point. Here, unfortunately, this cannot be done, any way, I will give a try.
Please tell me, how come the select statement runs so fast, when I put that to a # table, the server struggles, help me out Thanks to both of you. Regards,
Deva
Hi, I resolved this problem by splitting the select statement into two select statements, I populated the output of first select statement to a temporary table and then I used it to populate the output the main table by joining with rest of the tables, it works well,
thank you both adriaan and hemant. Regards,
Deva
]]>