Monday, December 03, 2012 3:49 PM
I faced a strange issue in Sql server 2008 R2.My application was slow and sluggish and unresponsive most of the time.But what i found strange was that i was able to execute all my stored procedure instantly from database server.However my database services was not restarted from past 1 month so i restarted it and everything now worked fine from application perspective also.Can anyone advice what could be the issue and what steps should i take in case i face this issue next time again.Please note none of the transaction were blocked at that tiime before restarting db services.
Tuesday, December 04, 2012 3:03 AM
what is the exact issue ->like your query was running slow or any bottleneck like cpu/mem ,I/o
Also did you checked SQL errorlogs & eventlogs..as well.. so hope when next time issue occurs then please look in to carefully what exactly you are facing the issue.
because the performance issue it totally different from other issues-
Troubleshooting Performance Problems in SQL Server 2008
Checklist for Analyzing Slow-Running Queries
Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.
Tuesday, December 04, 2012 6:01 AM
I agree with Udaya. There could be many reasons and hard to guess which one caused. Regular Maintanence is required.
like restarting servers, defragmentation, rebuilding indexes, update statistics etc...
I don't think your issue is from SQL server side....
Wednesday, December 05, 2012 2:43 AMModerator
Since the issue was gone after restarting SQL Server, this issue can occur if some tasks took up many system resource and didn’t release them, such as: deadlock. If the issue occurs again, I suggest execute sp_who2 to check whether there is any deadlock. Additionally, please check your max server memory setting to make sure it is not too limited.
For more detail information, please refer to the following link:
Server Memory Server Configuration Options:
TechNet Community Support
- Marked As Answer by Allen Li - MSFTModerator Monday, December 10, 2012 1:51 AM