Website down but db usage is not high

Question

Hi,

I got an issue here. my website keep on down but when i see the log from sql server database, cpu is most time idle, db usage is not high, the hit ratio is 100%. but the wait is high..and got few sql process block. any idea what should i check if got this issue. i've check the sql statement each time got this issue, and it shows different sql statement. so could not be the sql statement. can please advice me what to do.

---

kneeah

Answer 1

Hi Kneeah, 

1) First Make sure that you do not have any issues with the Web Server where you hosted your website. Then work from db side to improve the performance of the database. 

2) Make sure that your database tables are not fragmented, If fragmented de-fragment them. 

3) Update Statistics for all the tables. 

4) Find out huge tables in your database and try to implement partition as required - This option is optional case to case it differ. 

http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx

5) Please monitor the blocked sessions from SQL Server and try to avoid blocking. Have a look at below URL for How to avoid blocking

http://sqlserverpedia.com/blog/sql-server-bloggers/okay-you%E2%80%99ve-found-blocking-now-what/

http://msdn.microsoft.com/en-us/library/aa178087(v=sql.80).aspx

http://support.microsoft.com/kb/224453

http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx

6) Please run the server side trace in peak hours and save the trace file, then use DTA to analyze the load file then follow the recommendations given by DTA implement the DTA recommendations on test machine and see observe the performance. 

http://msdn.microsoft.com/en-us/library/ms173494.aspx

http://msdn.microsoft.com/en-us/library/ms188639.aspx

Hope this helps your problem

7) Find out top 10 costly queries and try to tune those queries. 

 

 

 

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Answer 2

Download Adam's utility 

http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx

And check out blocking

/*BLOCKING*/

EXEC dbo.sp_WhoIsActive @find_block_leaders =1,

@output_column_list ='[blocked_session_count][blocking_session_id][session_id][CPU][status][wait_info]

                      [database_name][sql_text][host_name][open_tran_count]',

@sort_order='[blocked_session_count]DESC'

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/