DX Application Performance Management

We are having an issue with one of our applications and I have initiated a trace because we are having queries of up to 9 seconds.

The issue I have is that some of these queries are way longer that what I believe the limit to be on a metric length so i don't see the end that contains the "FROM table in DB" that the developers will need to fix their code

2 Questions

Can I increase this limit?

If I can't is there a way to have a metric with the SQL complete Query

Thanks Dave

Hi Dave,

The limit on the SQL statement is 990 characters.

It can be increased by setting this property in the agent profile:

introscope.agent.sqlagent.sql.maxlength=

setting a value higher than 990

However when we recommend use of that property, it is normally to reduce it. It can have an increased negative impact on the application in terms of response times.

There is a field extension that would show detailed bind parameters in transaction traces which I believe would help for your scenario, I'll see if I can find a link to it, it is on the officially supported list so you would have engineering backup on it.

Thanks,

David

Hi Dave,

I can't find an external link to it but the thing you are looking for is called SQLAgent WIth Parameters, if you can reach out to your CA account rep locally, they should be able to sort something out for you.

Thanks,

David

this is what we have instrumented at the moment from sqlagent.pbd

#######################

# Statement instrumentation

# ================

TurnOn: SQLAgentStatements

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute()Z StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;I)Z StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[I)Z StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[Ljava/lang/String;)Z StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;)Z StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate()I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;)I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;I)I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[I)I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[Ljava/lang/String;)I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeBatch()[I StatementBackendTracer "Backends|{database}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute()Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; ResultSetToSQLMappingTracer SQLAgent

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; ResultSetToDynamicSQLMappingTracer SQLAgent

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;I)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[I)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[Ljava/lang/String;)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate()I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;I)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[I)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[Ljava/lang/String;)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeBatch()[I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements getResultSet()Ljava/sql/ResultSet; ResultSetToSQLMappingTracer SQLAgent

Hi Dave:

  Is David's answer about a configuration setting and looking for a field extension. Or is further assistance is needed?

Thanks

Ha German

Hi Hallett

I am still looking for the field extension for the pad file, the manual doesn't offer much in adding sql query metric with the query as a value.

Dave

I don't find it in the agent profile so I'm guessing it needs to be added to the end.

I also agree it will cause performance degradation under usually circumstances but we have a client facing app with a 2 hour delay at the moment.

Dave

Hi Dave,

Yes the property needs to be completely added in to the file, it can go at the top, middle or bottom of the profile, the placing is really not important for it to work but if you want to put it at the end so you know where it will be, that's fine.

The extra thing I'm talking about is an extra pbd and extra jarfiles as well so it's more complicated than just this simple update.

Thanks,

David

Hi David

Yes I have a custom.pbd i configure for these situations.

HI Dave,

I'm raising a support case to pass on the custom extension, it's the best way to get this to you at the moment.

I'll send a mail from there so you have more info about it.

Thanks,

David