Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert and indexes Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 11:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
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

Post #1492997
Posted Tuesday, September 10, 2013 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Post the query.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1493087
Posted Tuesday, September 10, 2013 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 11,321, Visits: 13,112
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1493165
Posted Tuesday, September 10, 2013 7:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1493181
Posted Monday, September 16, 2013 7:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
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


  Post Attachments 
query.txt (14 views, 1.02 KB)
Post #1495085
Posted Monday, September 16, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 11,321, Visits: 13,112
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




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1495100
Posted Thursday, September 19, 2013 4:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
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 :)
Post #1496304
Posted Friday, September 20, 2013 5:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1496805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse