Inconsistent query performance

[jjlbmcnews]

Hi,

This is a really stupid question but I'm completely baffled. I have a query that runs, some days it takes a couple of hours to complete and some days it takes six hours to complete. I can't work out why, I've ran traces and all sorts of stuff but I can't explain why it performs better on some days than others. As anyone else experienced this sort of behaviour?

 

[JayKusch]

Is there any other queries, stored procedures, development ... happening in the databases(s). If so, you have competing tasks. Also, is the server itself dedicated soely to SQL Server or are there other apps running like Exchange, IIS, MS Office, 3rd party apps like Accounting apps, CRM apps, .... All of the applications will produce overhead and slow SQL Server and your process down.

Thanks

J. Kusch

 

[jjlbmcnews]

Thanks for the reply, I think I have competing tasks! Whats the best way to trouble shot these?

 

[JayKusch]

In Enterprise Manager, drill down to Management..Current Activity..Locks / Process ID and check if you have and SPIDs that are locked, locking or deadlocked.

Thanks

J. Kusch

 

[SQLSister]

Perhaps at times the query runs when there are scheduled jobs (like backups) running. I would look through the jobs and see if something is scheduled during the same time as the query when it runs slowly. Especially if there is a definite pattern of it works slower on Wednesdays or something like that. If the pattern is random and you can't control the other things happening on your network or database at the same time or schedule this big query for off hours, then you probably need to find a way to improve the performance of the query itself.

What is the query doing that it takes so much time to run, even the good time, 2 hours, is a long time for a query to run. Maybe you can improve it's performance all around.