CODEWITHSUNDEEP
sqlsql-serversql-execution-plan
JDonahoe
JDonahoe

Reputation: 21

Long running procedure at times

I am a DBA at a healthcare automation company. We have 1 client that is using our application, this client is the only one affected mind you out of 1400 clients. At random times, there is one stored procedure that takes HOURS to run. Myself, and another DBA have been troubleshooting and diagnosing issues as they come up, but it happened again overnight and we are kind of at a loss.

I believe when it gets into this "rut" it is because of a bad execution plan cached. What I would want to do next is run a SP_Recompile for that stored procedure and have it recompile each time, but then who's to say if it does not cache a bad plan again. I have added MAXDOPS because we thought it might be an issue with it going parallel, but those have not helped.

We narrowed it down to one "Step" and we created a debugging database to capture data. It normally took this step less than 5 minutes to complete. It is now taking about 4-5 hours. I have included the TSQL snippet below of the code. Any suggestions, criticism, or flat out help is MUCH appreciated! Thanks!

insert into DBAdiag.dbo.dumbcodedebug (note, createddate)
values ('PatientAuditLog Insert Start',getdate())

 SET @StartTime = GETUTCDATE();
 SET IDENTITY_INSERT [XTArchive].dbo.PatientAuditLog ON;
 INSERT INTO [XTArchive].dbo.PatientAuditLog (a.[PatientAuditLogID],a.[PatientHistoryCrossRefID],a.[PatientFieldNameTypeID],a.[OldValue],a.[NewValue],a.[CreatedDate])
 SELECT a.[PatientAuditLogID],a.[PatientHistoryCrossRefID],a.[PatientFieldNameTypeID],a.[OldValue],a.[NewValue],a.[CreatedDate] FROM dbo.PatientAuditLog a WITH (NOLOCK)
 LEFT JOIN [XTArchive].dbo.PatientAuditLog b on b.PatientAuditLogID = a.PatientAuditLogID
 WHERE a.CreatedDate <= @ArchiveProcessStartTime AND b.PatientAuditLogID IS NULL  
 AND EXISTS(SELECT 1 FROM [XTArchive].dbo.PatientHistoryCrossRef e1 where e1.PatientHistoryCrossRefID = a.PatientHistoryCrossRefID)
 option (maxdop 6)


 SELECT @RowCount = @@ROWCOUNT;
 SET IDENTITY_INSERT [XTArchive].dbo.PatientAuditLog OFF;
 PRINT N'Insert into PatientAuditLog;' + convert(varchar(11), @StartTime, 101) + ' ' + convert(varchar(13),@StartTime,114) + ';' + convert(varchar(11), getutcdate(), 101) + ' ' + convert(varchar(13),getutcdate(),114) + ';' + CAST(@RowCount AS VARCHAR(10)) + ';' + 'PatientAuditLog' + ';' + '530'

insert into DBAdiag.dbo.dumbcodedebug (note, createddate)
values ('PatientAuditLog Insert end',getdate())

Upvotes: 0

Views: 53

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

This is a tough question to answer - what I'm about to write is more opinion than "answer".

However...

If you have hundreds of clients all executing the same query (presumably on the same schema, using the same DDL etc.), the "wrong query plan" hypothesis would require further explanation.

I'd say it's more likely that there is some other difference between your clients which explains this. Does this client have more data? Is there a difference in the frequency with which they run this query (it seems to be an archiving process; presumably, running it once a day means each run has fewer rows to consider).

If you can show a query plan for the query, we might be able to help.

Finally - and I'm tired, so may be missing something obvious - I think the left join means you're always inserting records with a NULL PatientAuditLogID; is that really right? That looks like it wants to be a primary key.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270763

I have experienced similar problems with running individual queries. In those cases, the problem seemed always to be inefficiencies in the query plan, where nested loops joins were used instead of some other algorithm.

I fixed this problem by using query hints on the offending queries: option (merge join, hash join). Unless there is an index or one of the tables is really, really small, I find that nested loop joins are the worst algorithm.

I'm not sure if this is your problem. You can stash the execution plans for the queries and check if they are changing for this particular client.

Upvotes: 0

Related Questions