Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2007-07-16 : 17:50:48
|
| Hi thereWe have a VB application that sits on the Citrix box away from Shared SQL Server box. We found it's occasionally gets application (query) time out on the application logs and the application hang. It's suspect from the Application team that there is something wrong the database server then.We investigate this and check using the Perfomance Monitor. The result is a considered a healty box in term of (memory, CPU, IO and network). So then we move the next one to check SQL stuff as follow:1. Check Blocking : NONE2. Check Statistic : Up to date. We are running update statistic every week with sampling 50%.3. Check Index: We are not doing reindexing but we do INDEXDEFRAG instead every day. It's OK.4. Run a server trace to check the longest and CPU consumed query and the query that on database is running less than 2seconds during period of trace. I don't see any queries that causing an issue on this particular database that's why I don't do any tuning further on any queries cause they are running considered normal.My question is how to prove that there is no database related issue? Also, when you run a query that execute from the app server, is there anyway to trace that whether the on specific query is completely 100% finish so not just hanging? How to approve that the result (record set) is sending back to apps server from SQL box?Any input on this I am really appreciated.Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-16 : 22:38:42
|
| Checked table fragmentation? |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-07-17 : 16:04:59
|
| Continuing on:I ran the application within 5 minutes and the same time I run a SQL trace as well as checking the locking sql script that we have. During that period the application is hang (haourglass) but none of the RUNABLE query detected during that while the application is hang which that indication means that the query is completed during that time. So I check, the runable query during that period and then I sorted that by the longest running query and is only 30 seconds and they are all RPC:Completed which I believe means that the request is finish and send back to app.I am missing something here? Is there anyway from the App server to know that they are actually received this data package to approve that is not the database issue? I am just guessing here :).Thanks |
|
|
|
|
|
|
Application Timeout