Sql server 2008 R2 performance issue

Question

Dear Concern,

 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.

Regards

Rahul

Answer 1

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-

see-

Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789%28v=sql.100%29.aspx

Checklist for Analyzing Slow-Running Queries
http://msdn.microsoft.com/en-us/library/ms177500%28v=sql.105%29.aspx

---

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.

Answer 2

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....

Answer 3

Hi Trehan11,

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:

sp_who (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms174313.aspx

Server Memory Server Configuration Options:
http://msdn.microsoft.com/en-us/library/ms178067.aspx

 

---

Allen Li
TechNet Community Support