September 9, 2013 at 11:57 pm
Good morning
INSERT query is suddenly taking too long to run which used to run under 30 minutes is taking 5 hours to run.
The query selects data from two different table and then inserts into a results table where results table clustered index field is NULL
I don't think using index is a good practice while inserting records into table.
I have seen high CPU usage while this query is running but I don't see any blocks/locks on the database.
How to analyse this query and bring it back to normal run time of 30mins?
Thanks in advance
September 10, 2013 at 4:33 am
Post the query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2013 at 7:16 am
An execution plan would be helpful as well.
I don't think the issue is the clustered index, although it could be, but the only thing that would change by having removing the clustered index is that a SORT will likely be removed from the query plan just before the clustered index insert. But, based on the limited information provided, you might be losing a SEEK on the clustered index because you said you are inserting where it is NULL meaning you have a some kind of filter on that clustered index.
I'd look at statistics, foreign keys, and other indexes before being concerned about having the clustered index
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2013 at 7:31 am
If the clustered index name is null, then that's a heap, a table without a clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2013 at 7:48 am
Sorry for the delay I have now attached the query.
Query 1 and query 2 takes approximately two minutes to run but query 3 takes around 9 hours which was running under 30 minutes before.
Nothing has changed in recent times from the In-depth I can only see high CPU usage for query 3
September 16, 2013 at 8:06 am
Sqlsavy (9/16/2013)
Sorry for the delay I have now attached the query.Query 1 and query 2 takes approximately two minutes to run but query 3 takes around 9 hours which was running under 30 minutes before.
Nothing has changed in recent times from the In-depth I can only see high CPU usage for query 3
Those are pretty simple queries. Can you post the DDL for the destination table, Databasename2..EMPLOYEE2 and the Execution Plan for the long-running insert (estimated plan is fine)? For the execution plan please post the .sqlplan. Video on how to do that is here[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 19, 2013 at 4:46 am
INSERT INTO Databasename2..EMPLOYEE2(EMPID, STARTTIME, ENDTIME, TASK, EVENTID, ADDRESS1)
SELECT a.EMPID, a.STARTTIME, a.ENDTIME, a.TASK, a.EVENTID, a.ADDRESS1
FROM dbo.table1 a LEFT OUTER JOIN Databasename2..EMPLOYEE2 b
ON a.EMPID = b.EMPID
AND a.STARTTIME = b.STARTTIME
AND a.EVENTID = b.EVENTID
AND a.ADDRESS1 = b.ADDRESS1
WHERE b.EMPID IS NULL
There was one clustered index on Databasename2...EMPLOYEE2.EMPID and after analysing execution plan I created a non-clustered index on columns Databasename2..EMPLOYEE2 - STARTTIME, EVENTID, ADDRESS1 query performance improved by 2 hours (earlier it was taking 5 hours) and also execution plan was looking good too but I still couldn't reach the target of 30 mins. Then again I tried with different index combinations also included covering indexes but none of them seem to be helping the query performance.
So I copied backups onto Dev servers and then dropped all indexes and ran the query and it just took 5 minutes to complete.
As clustered index on EMPID is a must on the table for good searches I have decided to drop the clustered index at the start of the procedure run and then recreating it back at the end.
Drop and re-create clustered index takes 5 minutes in total. So my procedure is completing in just 10 minutes ?
Glad the issue is finally resolved 🙂
Thank you all for the help and support 🙂
September 20, 2013 at 5:14 am
Sqlsavy (9/19/2013)
Drop and re-create clustered index takes 5 minutes in total. So my procedure is completing in just 10 minutes
Have you tried to do the INSERT with Clustered Index again (i knoe without index it behaved well ) but just you can try.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply