Database Time contribution tier
The PurePath in the attached session named as Localhost - TF Contribution is calling the Database which is returning 10000000 rows of data and the PurePath Response Time Distribution is as such :
PurePath Response time : ~23s
Exec Total for the Method node in the Tree calling the Database : 21 s
ADO.NET API Contribution : ~22s
Database Summary : ~550ms including Acquisition & Query Execution
Running the same query in SSMS for SQL Server completes in ~21s which i found out is actually the time equivalent to the ADO.NET API Exec Time as indicated above.
The Transaction Flow is indicating that the Web Server(IIS)/.NET Agent Tier is contributing 98% of the Response Time and the Exec Time for this tier is shown as ~22s and the Database Tier is only contributing 2% which is actually wrong as the whole 22s is spent on the Database server executing the query
Now my main concern is in the correctness of such behavior ?
Because instead of the isolating the Database Tier as the fault domain ,from the Transaction Flow it appears that the .NET Agent Tier is the root cause of the performance issue and the Database Dashlet is not indicating that the query actually executed in 22s,
Hi.
I think the problem here is that the actual time in the ADO.NET.ExecuteStatement is probably very small, e.g: 87ms. But iterating through the resultset which ultiimately retrieves all the rows is taking most of the time. The Database Dashlet only shows the time spent in ExecuteStatement but not the time spent in processing the resultset. Thats why the time is contributed to the DefaultAppPool. What we do correctly is attributing the time to the ADO.NET Layer as you can see in the API Distribution Chart.
Check the PurePath and the Methods you see in there that consume the time. I am sure it is related to iterating through the result set.
We are aware of that fact that we dont show the "FULL DB TIME" (Exec + Fetch Time) in the Database dashlet. But we show all the details in the PurePath and in the correct API
We have plans to extend our Database Server so that we can correctly show the full time spent with DB Activity in the DB DAshlet
Let me know if this makes sense
Andi
It indeed is iterating through the result set but then most of the queries in our case are actually the same and just looking at the Transaction Flow and the Database Dashlet is not helping our case as it is pointing us in the wrong direction.
We would be able to work with PurePath's ADO.NET Layer's timing but to start with the Database Dashlet, we are not aware if to really drill down from the Query to the PurePath Dashlet or not as all the queries are only showing the ExecuteStatement Time as you mentioned.
The only way we are thinking out of this is to start with PurePath's dashlet and then isolate the PurePath's that have the most ADO.NET API time in order to find the Queries causing the performance issue but it might be obvious that in this case the root cause isolation would be very very time consuming.
Regarding the Full DB Time ,can you provide an approximate due date for such an extension in order for us to hold on to something 
Can't give you a date right now 
- But - I can give you a link to my next Live Q&A session that I do later today (Monday): http://bit.ly/onlineperfclinic
My main topic will be Diagnostics. I am happy to cover your scenario as well in case you are online. Another approach for your diagnostics scenario is to start with the Layer Breakdown and watch for the ADO.NET API over time. Then you can zoom into a timeframe and from there isolate your PurePaths with high ADO.NET Time. Then drill to the Methods Dashlet to figure out whether this is related to executing a statement or iterating through results sets
Andi
