1

I have an application residing on the same host as a SQL Server database. There is a scenario in which this application is slow and due to co-existence of IIS and SQL Server, I cannot get which one wastes what fragment of time.

My assumption is that I can save trace to a table and sum completed RPCs time to get microseconds of time that is spent on DB. yet I doubt it very much. I don't know compilation time or any other times is included in this trace time or not.

Question: how can I tell which fragment of time is spent when an application request is sent to the SQL Server database?

Thanks in advance

1 Answer 1

2

On server side trace DURATION is the total execution time (in ms) of a query. CPU column will provide you time on CPU (total of one or multiple CPUs). While DURATION - CPU = IO Time

So If RPC-Completed time for single procedure call or total time is according to benchmarks then problem could be on network side.

Where is actual problem. To get this you must keep close eye on Wait Statistics. Which will help you to detect real problem whether its on database side or network.

Note: 1-In case of parallelism (a query resolved by multiple CPUs) DURATION could be less then CPU. 2-Profiler will show you DURATION in milliseconds while inserting same trace data in a table, you will get DURATION in microseconds.

1
  • Let me clarify my understanding: I should capture profiler traffic to a table, sum duration column and that is all of the time spent on the SQL server side (in microsecond), right? To find out what has caused this slow response, I must go for Wait Statistics, Latches, SPIN locks, etc. That is exactly what I have done so far. I can see many option (recompile) phrases in the captured trace and I doubt if it could be causing some problems. Though it is another problem and may be solved by disabling parameter sniffing. Commented Oct 21, 2014 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.