April 15, 2012 at 3:40 am
Hi,
We have an important query that runs early in the morning. It collects all day from the day close from the previous business day. It always takes ~3-4 minutes to run.
On Saturday, they called me from work: "all reports are empty". I check and the situation is - no data loaded and query is still running, so obviously it is stuck. It was >7 hours and I had to kill it.
I tried to investigate using SQL Profiler and several DMVs but I don't have enough experience to find out the problem.
Then, I re-run and it took 27 minutes compared to 3 min. before, but finished and loaded everything.
What would cause this to happen?
I have to say, no related changes made to the database and query was running for more than 1 year without problems.
Thank you!
April 15, 2012 at 6:13 am
Could be any number of things. To start, try updating statistics on the table. If that doesn't help, we're probably going to need more details about the procedure.
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
April 16, 2012 at 8:51 am
I agree with Gail - LOTS of potential culprits here (statistics, locking, IO issues, virtual machine stuff if on VM, bad plan in cache, other stuff running on server either inside or outside sql server, etc, etc). And we can't begin to help you without more information that likely doesn't even exist when the event isn't occurring. I recommend you engage a good tuning professional to be available to remote into your server to determine the root cause while it is ongoing. There are several such candidates here on SSC.com, and many others out there you can use.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 16, 2012 at 10:01 am
Before killing the process that had been running for 7 hours, did you check see if it was was in a blocked state, what was blocking it, and wether it had an usually high cpu or i/o usage count? You can at least get those 3 basic things by running sp_who2.
The difference in runtime duration between 27 minutes and the usual 3 minutes could simply be accounted because it was started off schedule (during the day with more activity versus the usual early morning).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply