September 28, 2011 at 1:19 pm
I see there is occasional delay in displaying the result in application. I have trace running and sometimes when i click on a task the call to the database is seen after 20 secs in profiler, most of the times it is sees immediately. Whenever i see latency in database calls on trace i see that the application is also running slow. Could it be due to some n/w connectivity? I dont haev acess to webserver at all, has anyone else experienced this issue before? How do i identify the issue?
September 29, 2011 at 9:34 am
When you see this problem occuring have a look at sys.dm_os_waiting_tasks, you will see what requests are comming in and what the wait type is. This will help you identify if the SQL Server runs slow if its waiting for DISK, network resources etc. If you see any NETWORK_IO_SYNC waits then its due to a delay in the data going from the sql server to the web server.
This may only help on the time taken to retrun the results to the request and not the time the request takes to get to the server.
For that there are IIS counters you can look at in Perfmon but I'm not familiar with what they are.
MCITP SQL 2005, MCSA SQL 2012
September 30, 2011 at 8:10 am
RTaylor2208 (9/29/2011)
When you see this problem occuring have a look at sys.dm_os_waiting_tasks, you will see what requests are comming in and what the wait type is. This will help you identify if the SQL Server runs slow if its waiting for DISK, network resources etc. If you see any NETWORK_IO_SYNC waits then its due to a delay in the data going from the sql server to the web server.This may only help on the time taken to retrun the results to the request and not the time the request takes to get to the server.
For that there are IIS counters you can look at in Perfmon but I'm not familiar with what they are.
Thanks.Problem is i never see any sproc taking more than 5 secs and whenever the app is slow i do not see the calls in the profiler immediately,there is a delay.
October 3, 2011 at 3:23 am
From what you have described that it looks like the problem could be at the web server application layer, do you have access to anyone who knows that well. As I mentioned before you should be able to get some stats from the IIS counters on you web server and also from the IIS Logs. (providing you use IIS).
You should be able to back this up with the sql counter "batch requests per second" in perfmon, as if you can establish what the baseline is during normal activity, if this falls during the period of slowness then less requests are comming from the web server which would indicate possible issues on the web server or on the network .
MCITP SQL 2005, MCSA SQL 2012
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply