April 20, 2017 at 3:44 pm
I have insert...select query.
Select part is fairly fast, it joins 2 tables and has ...where not exists ... in destination table. Select alone takes about 5 sec.
But if I run insert select, it takes 20 sec. I kept reducing fill-factor for indexes in destination table but it did not help. What else I can look? The destination table is 40GB, not partitioned, has 3 indexes, but one of them is ever-increasing clustered surrogate PK. I was testing all that in dedicated test server, not production, so concurrency not an issue.
Please advise..
Thanks.
April 20, 2017 at 4:11 pm
SQL Guy 1 - Thursday, April 20, 2017 3:44 PMI have insert...select query.
Select part is fairly fast, it joins 2 tables and has ...where not exists ... in destination table. Select alone takes about 5 sec.
But if I run insert select, it takes 20 sec. I kept reducing fill-factor for indexes in destination table but it did not help. What else I can look? The destination table is 40GB, not partitioned, has 3 indexes, but one of them is ever-increasing clustered surrogate PK. I was testing all that in dedicated test server, not production, so concurrency not an issue.
Please advise..
Thanks.
First question, how many rows of data are you inserting?
Second question, have you tried inserting the data that does not exist in the destination table into a temp table and then using that table as the source for the insert?
April 20, 2017 at 4:28 pm
1) Any triggers on the table inserting into?
2) Any keys on the table inserting into?
3) Did you use sp_whoisactive to see how the query is performing in real time, check for blocking, see the query plan, etc?
4) Did you do a file IO stall differential analysis and wait stats differential analysis while the insert was running?
5) Did you test the insert into a permanent test table in the same database?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2017 at 10:34 am
That's very interesting, and I would say very strange.
If I break it into two parts 1-select into #temp and 2nd - insert into my destination from #temp, the 2nd part takes just 1 sec, making the whole process just about 6 sec instead of 20.
That means that my destination table design is flawed?
It does not have triggers.
It has PK (identity) .
No FK's, defaults, check constraints, no computed columns.
I don't have sp_whoisactive (unfortunately we cannot download anything from internet)
April 21, 2017 at 10:48 am
Well, considering we can't see what you see, not much more we can do unless you at least post the query you are working with and the DDL for the table(s) involved.
April 21, 2017 at 11:02 am
If I break it into two parts 1-select into #temp and 2nd - insert into my destination from #temp, the 2nd part takes just 1 sec, making the whole process just about 6 sec instead of 20.
With that additional information, I will now guess that you have IO contention and reading and writing to the same file(s) in the same database are the bottleneck. Do the file IO stall differential analysis I mentioned, and repeatedly run sp_whoisactive during the 20 seconds an look at wait information.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2017 at 11:05 am
TheSQLGuru - Friday, April 21, 2017 11:02 AMIf I break it into two parts 1-select into #temp and 2nd - insert into my destination from #temp, the 2nd part takes just 1 sec, making the whole process just about 6 sec instead of 20.
With that additional information, I will now guess that you have IO contention and reading and writing to the same file(s) in the same database are the bottleneck. Do the file IO stall differential analysis I mentioned, and repeatedly run sp_whoisactive during the 20 seconds an look at wait information.
Except he said they don't have sp_whoisactive and can't download anything from the internet.
April 21, 2017 at 11:36 am
Should I select snapshots from sys.dm_io_virtual_file_stats() before and after and provide delta for all columns ? If this is something different, could you please provide a script?
About table creation script, I think it is rather impractical. It has 84 columns. If I will post it, I need to replace each column name with something meaningless. Same for insert/select query. And does empty table has any value in this situation? It has 86 m records.
April 21, 2017 at 11:45 am
Lynn Pettis - Friday, April 21, 2017 11:05 AMTheSQLGuru - Friday, April 21, 2017 11:02 AMIf I break it into two parts 1-select into #temp and 2nd - insert into my destination from #temp, the 2nd part takes just 1 sec, making the whole process just about 6 sec instead of 20.
With that additional information, I will now guess that you have IO contention and reading and writing to the same file(s) in the same database are the bottleneck. Do the file IO stall differential analysis I mentioned, and repeatedly run sp_whoisactive during the 20 seconds an look at wait information.
Except he said they don't have sp_whoisactive and can't download anything from the internet.
There are numerous ways to get sp_whoisactive onto a server that cannot download anything from the internet.
If for some odd reason none of those are possible he can open the sp_whoisactive create script and extract out the part that tracks and reports on waits and use that against the server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2017 at 11:49 am
SQL Guy 1 - Friday, April 21, 2017 11:36 AMShould I select snapshots from sys.dm_io_virtual_file_stats() before and after and provide delta for all columns ? If this is something different, could you please provide a script?About table creation script, I think it is rather impractical. It has 84 columns. If I will post it, I need to replace each column name with something meaningless. Same for insert/select query. And does empty table has any value in this situation? It has 86 m records.
A) Yes. Do the same for sys.dm_os_wait_stats.
B) We shouldn't need the script any longer. The insert runs fast from tempdb copy, ergo the structure of the table being inserted into shouldn't be of concern.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply