Identity issue in SQL Database using dynatrace

[ravishankar_jan]

We are doing Performance testing using .net Application having backend as SQL Server.

this application functionality is basically a bidding application. where user tries to submit Bid to SQL Database. during our testing we have identified most of the submit bids are consuming more response time. we have identified issue at method level and inform the Dev, but they say this applicaiton method is being tied up with SQL. they want us to identify issue which SQL is taking more time to execute.

We tried to Analyze complete transaction flow and find most of the webApp is taking more contribution time in terms of response but not at SQL DB level. how do i prove that Dev saying there is not issue at DB Side. could you please throw more light on how can i deep dive and provide them more Analysis.

[Julius_Loman · Accepted solution]

First - how does response time analysis of your service look like? Can you share it and also include a pure path?

Basically, I'd recommend putting a custom service (or services) in the application code to get a higher granularity unless it is a very simple service. If your service code calls some other methods, try to define custom services for those methods (if it makes sense of course).

Did your devs explain what do they mean by "applicaiton method is being tied up with SQL"? Recently I've encountered a similar case when identified issue was a large part of code handling database requests and was also defined as synchronized (this can be seen as lock wait time in the response time analysis). This caused massive slowdowns of requests (average response time is ~200ms, slowest 5% ~2s, some individual requests up to 1 minute and 99% of the time is lock wait).

Unless the response time analysis shows a large portion of time is being spent in DB or network, then it's likely a code issue.

[ravishankar_jan]

Thanks for your the reply. We don have DB Agent instrumented at SQL server.

We have few call as explained above while submitting bit. we have those calls internally calls with SQL query or store proc which might be taking more time to execute. So without having DB Agent is it possible to know which SQL or store procedure is consuming more time to execute?