Exclude SQL statements from DPA

Certain long-running SQL statements might not be candidates for tuning (for example, SQL statements associated with database backups, replication, or data loads). To prevent these statements from dominating trend charts or producing tuning advisors that are not actionable, you can exclude them from DPA.

Before excluding SQL statements, consider the possible impacts. If an excluded SQL statement begins affecting your database performance, you will not see the issue in DPA because of the exclusion.

Determine which option meets your needs

DPA provides three options for excluding SQL statements. Read the following descriptions to determine which option meets your needs, and then see the following sections for implementation instructions.

Summary Option 1 Option 2 Option 3
Safe and easy    
Excludes the SQL statement from both past and future charts and advisors    
If the exclusion is reverted, DPA charts and advisors for the exclusion period show data about the excluded statement again    

Excludes SQL statements from all DPA views and analysis, including reports and anomaly detection

 
Exclusion criteria is not restricted to the hash value or ID of one SQL statement    

Option 1: Exclude a specific SQL statement from DPA charts and analysis

Use the SQL Properties dialog box to exclude the selected SQL statement from DPA trend charts, DPA tuning advisors, or both. When you exclude a statement from tuning advisors, DPA does not generate query advisors for it or consider it when generating table tuning advisors.

DPA recommends using this method if possible because it is safe (there is no risk of losing data) and easy (it is done through the interface).

  • Data about the statement is excluded from:

    • All DPA trend charts that represent one or more days, including charts that represent previous periods
    • All tuning advisors, including those generated for previous periods
  • Data about the statement is still included in:

    • Charts that represent less than one day
    • Reports
    • Anomaly detection

      If the statement runs on a regular schedule with a predictable amount of wait time, it would not cause DPA to detect an anomaly during the period when it runs. Higher wait times would be normal during that period.

  • If you revert the exclusion:

    DPA charts and advisors for the exclusion period show data about the excluded statement again. With this method, DPA continues to collect and store data about the SQL statement, and so the data is available.

Option 2: Prevent DPA from storing data about a specific SQL statement

Run a statement against the DPA repository database that prevents DPA from storing the data that it collects about the specified SQL statement. The excluded SQL statement is identified by its DPA hash value.

This option for excluding SQL statements requires admin privileges.

  • Data about the statement is excluded from:

    • Charts, tuning advisors, and reports that represent time periods after you ran the SQL statement
    • Anomaly detection
  • Data about the statement is still included in:

    • Charts, tuning advisors, and reports that represent periods before you ran the statement
  • If you revert the exclusion:

    DPA charts and advisors for the exclusion period do not show data about the statement because that data is not stored in the DPA repository database.

Option 3: Exclude SQL statements from collection based on criteria in the WHERE clause

Modify the WHERE clause of the DPA quickpoll query to prevent DPA from collecting information about SQL statements that meet certain criteria. With this method, you can exclude statements that come from a certain program, user, host, or a combination of factors. For example, you can exclude a specific SQL statement when it is run by a certain user, or you can exclude all SQL statements coming from a user on a certain computer.

This method cannot be used for statements that run on a Sybase monitored database instance.

In some cases, this method can improve the performance of the DPA quickpoll query on a busy database instance. However, adding too much logic to the WHERE clause can cause the query to run longer than expected or disrupt data collection.

If you use this option, DPA strongly recommends working with SolarWinds Support. Check CONTIME entries for QUICKPOLL_EXECUTE both before and immediately after you apply the change to determine if there is any difference in performance.

  • Data about the statement is excluded from:

    • Charts, tuning advisors, and reports that represent time periods after you modified the WHERE clause
    • Anomaly detection
  • Data about the statement is still included in:

    • Charts, tuning advisors, and reports that represent periods before you modified the WHERE clause
  • If you revert the exclusion:

    DPA charts and advisors for the exclusion period do not show data about the statement because that data was never collected.

Option 1: Exclude a specific SQL statement from DPA charts and analysis

  1. In any chart legend, click the name or hash value that represents the SQL statement.

    The Query Details page displays information about the SQL statement.

  2. In the top-right corner, click SQL Properties.

    The SQL Properties dialog opens.

  3. Under Advanced Settings, clear one or both of the following options:

    • Clear the Show in Trends charts setting to remove the SQL statement from multi-day or one-day trend charts. If you drill in to a time period less than one day, charts include the SQL statement.

    • Clear the Enable advisor analysis setting to exclude this statement from the analysis that DPA runs to generate query advisors and table tuning advisors. When analysis is disabled, DPA does not detect problems with the SQL statement.

      When you clear the Show in Trends charts setting, both options are cleared by default. To clear only the first option, click Enable advisor analysis to re-select it.

  4. Click Save.

You can add excluded SQL statements back to trend charts and analysis at any time.

Option 2: Prevent DPA from storing data about a specific SQL statement

  1. Log in to DPA using an account with admin privileges.
  2. Get the hash value that identifies the SQL statement:

    1. In any DPA chart legend, click the name or hash value that represents the SQL statement.

      The Query Details page displays information about the SQL statement.

    2. In the top-right corner, click SQL Properties.
    3. Copy the hash value from the top of the SQL Properties dialog box.
  3. Open the DB query tool in DPA: 
    1. On the DPA menu, click Options.

    2. Under Support > Utilities, click DB Query tool.
  4. To get the database ID, enter the following query and click Execute Query:

    select ID, name from cond;

    The query returns the names and IDs of all monitored database instances.

  5. Enter the following query (replacing the variables with your database ID and SQL hash value), and click Execute Query:

    INSERT INTO con_qp_exclude (dbid, type, value, origin) VALUES (databaseID, 'H', 'sqlHash', 'U');

    All future data collection excludes this SQL statement. Past data is not purged, so DPA charts and reports that represent previous time periods will still include the SQL statement.

To revert the exclusion, remove the SQL statement from the con_qp_exclude table by issuing a DELETE FROM statement: 

DELETE FROM con_qp_exclude WHERE dbid=databaseID AND type='H' AND value='sqlHash';

Option 3: Exclude SQL statements from collection based on criteria in the WHERE clause

  1. On the DPA menu, click Options.

  2. Under Administration > Configuration, click Advanced Options.

    The System Options tab lists options that apply to all database instances.

  3. Click DB Instance Options and select the database instance on which the SQL statements run.
  4. Select Support Options.
  5. Click the name of the QUICKPOLL_WHERE_CLAUSE to open the Edit Option dialog.
  6. Enter the phrase to include in the WHERE clause that specifies the SQL statements that should not be collected. Use the syntax appropriate for the database type. See the examples in the following sections.
  7. Click Update, and then restart DPA.

    All future data collection excludes the SQL statements. Past data is not purged, so DPA charts and reports that represent previous time periods will still include the statements.

To revert the exclusion, repeat this procedure to remove the criteria from the quickpoll WHERE clause.

Examples for SQL Server

Example 1: Exclude all SQL statements from the TSQL program logging in from the server HPSERVER:

and not (s.program_name='TSQL' and s.hostname='HPSERVER')

Example 2: Exclude the specified SQL statement if it comes from a certain user, but do not exclude it if it comes from other users. Use the SQL_handle (not the DPA hash value) to identify the SQL statement.

and not (s.loginame='Bob' and s.sql_handle=0x00987097097897)

Example 3: Exclude all SQL statements executed in the master database by the dataload program:

and not (db_name(s.dbid)='master' and s.program_name='dataload')

Examples for Oracle

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER.

and not ("u".ksusepnm ='SAP.exe' and "u".ksusemnm='HPSERVER')

Example 2: Exclude the specified SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not ("u".ksuudlna='Bob' and "u".ksusesqh =97097897)

Examples for DB2

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER:

and not (ai.appl_name ='SAP.exe' and ai.client_nname='HPSERVER')

Example 2: Exclude a dynamic SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not (ai.auth_id='Bob' and s.stmt_text like '%insert into bad_table%')

Example 3: Exclude a static SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not (ai.auth_id='Bob' and st.text like '%insert into bad_table%')

Examples for MySQL

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER:

and not (program_name = 'SAP.exe' AND host='HPSERVER')

Example 2: Exclude a dynamic SQL statement if it comes from a certain user, but do not exclude it if it comes from other users:

and not (user = 'BOB' AND statement_sql like '%insert into bad_table%')