SQL Server Profiler – Measuring DB Time of Application

profilersql-server-2012ssms

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

Best Answer

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.

Related Question