August 10, 2011 at 1:51 pm
I am using SQL Server 2005. From one day last week the server is taking too much tim to run a routine. It is taking about 4 times more time everyday.
I have checked the system performance in i/o, processor, memory and have checked the network speed as well as the routine is executed from application server.
Everything is perfect in both database and application server. can you suggest where else should I look to find the solution?
Thanks!
August 10, 2011 at 1:54 pm
When in doubt, if its *1* procedure that's the problem, you start with the procedure, not the server. Go to the execution plan, determine what it's doing. Review the code, see if it's something funky. Check for deadlocks or concurrency. Find the spid running the proc, and check its wait types.
If you want some help with the query itself, check out the second link down in my signature on the left, it'll walk you through what we'd need to help you optimize a particular proc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 10, 2011 at 1:59 pm
Please post the actual execution plan of the query in question.
Either the load on the server changed drastically, amount of data to process or the plan changed...
August 11, 2011 at 5:43 am
something that's getting worse over time like that, it might be statistics that need to be updated. Try running the UPDATE STATISTICS command on the tables that are accessed by that query.
There are possibly also issues with the query. Like the others have said, post the query and an execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2011 at 6:55 am
Thanks Gyus,
The problem has been resolved. As pointed out by some of you that the problem might be with the routine, we were using MS project server DLL in the routine whose functions inserts data in the database. Some of the insert requests had prodused an error in MS Project but those insert jobs were queued in the Project server and were creating conflict. So the problem was outside the SQL Server. we have deleted those jobs and now it is working perfectly.
Thanks.
August 11, 2011 at 7:04 am
Are you sure you didn't delete something critical??
August 11, 2011 at 7:22 am
Yes I am, b/c I delete the jobs after discussing it with the team members who are working on it. Secondly, I did not delete the data directly from the database. I went into the page of Job Queue list of MS Project Server and delete the jobs which were producing error.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply