cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

This product reached the end of support date on March 31, 2021.

Need to chart SQL Queries count taking more than X Seconds

Hi All,

I'm trying to chart SQL Queries count taking more than X Seconds.

I've created a Measure of Metric Type Time (Database) and kept the Threshold as 10.0 ms so that I will have more data on chart.

Then I created a BT and included the above measure.

But I'm not seeing any data when I chart it. Am I missing something here ??

Thanks

Hari Kumar Alampuru

9 REPLIES 9

JamesKitson
Dynatrace Leader
Dynatrace Leader

To start with can you chart the measure you're using as the filter to see if anything is showing up? I'm not sure if it matters but I usually use something like 'contains' instead of 'equals' when trying to match everything.

James

Cleveland representing

rick_boyd
Mentor

Not sure if it's just whited-out but your configuration seems to imply configuration of "equals <blank>" which wouldn't match any of your executed queries.

A secondary concern here is that your BT output will be the number of PurePaths whose last SQL query is > 10ms. So the "count" metric here will be strictly less than or equal to the number of such sql statements. What you can do if you want the number of sql statements that matches your criteria is use a second "violation" type measure which is configured on your ZZZ-Time measure to count the actual SQL queries themselves.

Hope that helps,

Rick B

Hi Rick,

I've created a "Violation" type measure and included the DB Time measure in it.

But I would like to get stats like :

Count of DB SQL Queries for whose response time is < 200ms

Count ofDB SQL Queries for whose response time is between 200ms to 1s

Count of DB SQL Queries for whose response time is between 1s to 3s

Count of DB SQL Queries for whose response time is between 3s to 5s

Count of DB SQL Queries for whose response time is between 5s to 10s

Count of DB SQL Queries for whose response time > 10s

Finally, I want to chart the above. Can you please help me on how to proceed on this.

Thanks

Hari Kumar Alampuru

Hi,

I've created a "Violation" type measure and included the DB Time measure in it.

But I would like to get stats like :

Count of DB SQL Queries for whose response time is < 200ms

Count ofDB SQL Queries for whose response time is between 200ms to 1s

Count of DB SQL Queries for whose response time is between 1s to 3s

Count of DB SQL Queries for whose response time is between 3s to 5s

Count of DB SQL Queries for whose response time is between 5s to 10s

Count of DB SQL Queries for whose response time > 10s

Finally, I want to chart the above. Can you please help me on how to proceed on this.

Thanks

Hari Kumar Alampuru

This answer is a different approach than what you're asking, so sorry if it does not meet your needs. What you're looking for seems to be "relative" rather than "absolute" sql counts for the various timings. I would imagine your best bet then is to take advantage of the "percentile chart" option available in the Chart dashlet, by enabling "calculate percentile data" in the details tab of the DB Time measure, and then adding it to a chart.

In case you need absolute numbers for each partition you listed above, what you want to accomplish can be done but i wouldn't recommend it in terms of maintenance, in case you want to change the division of timings, etc. What you would need to do is create a business transaction for each division and use "AND" paired filter measures that are created for e.g. DB Time lower threshold 200ms, then (second measure) DB Time upper threshold 201ms AND (third measure) DB Time lower threshold 1000ms, etc.

Hope that helps,

Rick B

@Rick B.

Hi Rick,

I did a Percentile chart as per your instructions. But I'm feeling hard to interpret or understand the graph.

Can you please help me on this.

given 'x' is a number on the x-axis and 'y' is a number on the 'y' axis, x% of sql calls are faster than 'y' ms. your chart there is not a particularly interesting example but it shows that 100% of sql calls are <= 697 ms, and 99% of sql calls are <= about 50 ms. If you have it running for a longer time the distribution may be able to tell you a better story

Hello Hari,

Have you tried creating a Threshold - Count measure for your measure? See here in the snapshot. Let us know if this works for you.