Configure the SQL Agent for .NET

The SQL agent is an agent extension that collects detailed database performance data by monitoring SQL statements. The SQL agent tracks the interaction between your managed application and database to provide individual SQL statement performance visibility.
apmdevops106
The SQL agent is an agent extension that collects detailed database performance data by monitoring SQL statements. The SQL agent tracks the interaction between your managed application and database to provide individual SQL statement performance visibility.
SQL Agent Overview
To provide meaningful performance measurements down to the individual SQL statement level, the SQL agent summarizes performance data. The SQL agent strips out transaction-specific data and converts the original SQL statements into Introscope-specific normalized statements. Because normalized statements do not include sensitive information, such as credit card numbers, this process protects your data security.
For example, the SQL agent converts this SQL query:
SELECT * FROM BOOKS WHERE AUTHOR = 'Atwood'
To this normalized statement:
SELECT * FROM BOOKS WHERE AUTHOR = ?
Similarly, SQL agent converts this SQL update statement:
INSERT INTO BOOKS (AUTHOR, TITLE) VALUES ('Atwood', 'The Robber Bride')
To this normalized statement:
INSERT INTO BOOKS (AUTHOR, TITLE) VALUES (?, ?)
Only text within quotation marks ('xyz') is normalized.
Metrics for normalized statements are aggregated and can be viewed in the JDBC node of the Workstation Investigator.
SQL Agent Files
The SQL agent is included in all agent installations. The files providing SQL agent functionality are:
  • <Aguent_Home>
    /ext/wily.SQLAgent.ext.dll
  • <Aguent_Home>
    /sqlagent.pbd
By default, agent extensions like the wily.SQLAgent.ext.dll file are installed in the <
Agent_Home
>/ext directory. You can change the location of the agent extension directory using the
introscope.agent.extensions.directory
property in the agent profile. If you change the location of the /ext directory, be sure to move the contents of the /ext directory too.
SQL Statement Normalization
Some applications may generate an extremely large number of unique SQL statements. If technologies like Hibernate are in use, the likelihood of long unique SQL statements increases. Long SQL statements can contribute to a metric explosion in the agent, leading to poor performance and other system problems.
How Poorly Written SQL Statements Create Metric Explosions
In general, the number of SQL agent metrics should approximate the number of unique SQL statements. Your SQL agent can show a large and increasing number of unique SQL metrics even though your application uses a small set of SQL statements. In this situation, the problem could be in how the SQL statement is written.
The following examples are several common situations in which SQL statements can cause metric explosions.
Example Comments in SQL Statements
One common reason for metric explosion is caused by how comments are used in SQL statements. For example, you have a SQL statement similar to this statement:
"/* John Doe, user ID=?, txn=? */ select * from table..."
The SQL agent creates a metric with the comment as part of the metric name:
"/* John Doe, user ID=?, txn=? */ select * from table..."
The comment that is embedded in the SQL statement is useful for the database administrator. The administrator can determine who is executing what query that the database executing the query is ignoring. The SQL agent, however, does not parse the comment string when it captures the SQL statement. Therefore, for each unique user ID, the SQL agent creates a unique metric, potentially causing a metric explosion.
This problem can be avoided by putting the SQL comment in single quotes. For example:
"/*' John Doe, user ID=?, txn=? '*/ select * from table..."
The SQL agent then creates the following metric where the comment no longer causes a unique metric name:
"/* ? */ select * from table..."
Example Temporary Tables or Automatically Generated Table Names
Another potential cause of metric explosion can be the result of applications that reference temporary tables or tables that have automatically generated names in SQL statements. For example, if you open the Investigator to view the metrics under
Backends|{backendName}|SQL|{sqlType}|sql
,
you might see a metric similar to this example:
SELECT * FROM TMP_123981398210381920912 WHERE ROW_ID = ?
This SQL statement is accessing a temporary table that has a unique identifier that is appended to the table name. The additional digits that are appended to the
TMP_
table name create a unique metric name each time the statement is executed. The large number of unique metric names being generated is causing a metric explosion.
Example Statements that Generate Lists of Values or Insert Values
Another common cause of metric explosion are SQL statements that generate lists of values or do mass modification of values. For example, assume that you have been alerted to a potential metric explosion and your investigation brings you to a review of this SQL statement:
#1 INSERT INTO COMMENTS (COMMENT_ID, CARD_ID, CMMT_TYPE_ID, CMMT_STATUS_ID,CMMT_CATEGORY_ID, LOCATION_ID, CMMT_LIST_ID, COMMENTS_DSC, USER_ID,LAST_UPDATE_TS) VALUES (?, ?, ?, ?, ?, ?, ?, "CHANGE CITY FROM CARROLTON,TO CAROLTON, _ ", ?, CURRENT)
In studying the code, you notice that
"CHANGE CITY FROM CARROLTON, TO CAROLTON, _ "
generates an array of cities.
Similarly, if you are investigating a potential metric explosion, you might review a SQL statement similar to this example:
CHANGE COUNTRY FROM US TO CA _ CHANGE EMAIL ADDRESS FROM TO BRIGGIN @ COM _ "
In studying the code, you notice CHANGE COUNTRY results in a long list of countries. In addition, the placement of the quotes for countries results in e-mail addresses getting inserted into SQL statements, creating unique metrics that could be the source of the metric explosion.
SQL statement normalization options
To address long SQL statements, the SQL agent includes the following normalizers for use:
Default SQL statement normalizer
The standard SQL statement normalizer is on by default in the SQL agent. The SQL statement normalizer normalizes text within single quotation marks ('xyz'). For example, the SQL agent converts this SQL query:
SELECT * FROM BOOKS WHERE AUTHOR = 'Atwood'
To this normalized statement:
SELECT * FROM BOOKS WHERE AUTHOR = ?
Metrics for normalized statements are aggregated and can be viewed in the Workstation Investigator.
Custom SQL statement normalizer
The SQL agent allows users to add extensions for performing custom normalization. To do so, you create a DLL file containing a normalization scheme that is implemented by the SQL agent.
To apply a SQL statement normalizer extension:
  1. Create an extension DLL file.
    The entry point class for the SQL normalizer extension file has to implement com.wily.introscope.agent.trace.ISqlNormalizer interface.
    Making a DLL extension file involves creating a manifest file that contains specific keys for the SQL normalizer extension. This procedure is detailed in step 2. However, for your extension to work, other general keys are required. These keys are the type that you would use to construct any extension file. The extension file that you create relates to database SQL statement text normalization, for example, metrics under the Backends|{backendName}|SQL|{sqlType}|{actualSQLStatement} node. The {actualSQLStatement} is normalized by the SQL normalizer.
  2. Place the following keys in the manifest of the created extension:
    The value of this first key can be anything. In this instance,
    testNormalizer1
    is used as an example. Whatever you specify as the value of this key, use it in the following keys too.
    com-wily-Extension-Plugins-List:testNormalizer1
    The key in the next example should contain the unique name of your normalizer, for example, normalizer1.
    com-wily-Extension-Plugin-testNormalizer1-Type: sqlnormalizer
    com-wily-Extension-Plugin-testNormalizer1-Version: 1
    com-wily-Extension-Plugin-testNormalizer1-Name: normalizer1
    com-wily-Extension-Plugin-testNormalizer1-Entry-Point-Class: <Thefully-qualified classname of your implementation of ISQLNormalizer>
  3. Place the extension file that you created in the
    <Agent_Home>
    \ext directory.
    Note:
    The
    introscope.agent.sqlagent.normalizer.extension.runPredefinedScheme
    property allows the default normalizer to run first before executing the regular expression SQL statement normalizer. This property is not included in the IntroscopeAgent.profile file; add it manually if you want to change the default setting to
    false
    .
  4. In the IntroscopeAgent.profile, locate and set the following property:
    introscope.agent.sqlagent.normalizer.extension
    Set the property to the
    com-wily-Extension-Plugin-{plugin}-Name
    from your created extension manifest file. The value of this property is not case-sensitive. For example:
    introscope.agent.sqlagent.normalizer.extension=normalizer1
    The
    introscope.agent.sqlagent.normalizer.extension
    property is hot. Changes to the extension name result in re-registration of the extension.
  5. In the IntroscopeAgent.profile, you can optionally add the following property to set the error throttle count:
    introscope.agent.sqlagent.normalizer.extension.errorCount
    If the errors that are thrown by the custom normalizer extension exceed the error throttle count, the extension is disabled.
    For more information about errors and exceptions, see Exceptions.
  6. Save the IntroscopeAgent.profile.
  7. Restart your application.
Exceptions
If the extension you created throws an exception for one query, the default SQL statement normalizer uses the default normalization scheme for that query. In this situation, two messages are logged. An error message states that an exception was thrown by the extension, and a debug message contains stack trace information. However, after five such exceptions are thrown, the default SQL statement normalizer disables your created extension. The default SQL statement normalizer also stops attempting to use the created extension for future queries until the normalizer is changed.
Null or empty strings
If the extension you created returns a null string or empty string for a query, the StatementNormalizer uses the default normalization scheme for that query. The StatementNormalizer also logs an INFO message stating that the extension returned a null value. However, after five such null or empty strings have been returned, the StatementNormalizer stops logging messages, but attempts to continue to use the extension.