Could I adjust the interval of SQL statement history?

[former_member211576]

Hi experts,

   sql server dbacockpit -> performance -> history -> sql statement history is very useful to troubleshoot performance issue. Could I adjust the interval of SQLstatement history? 20 minutes is a little too long to wait when performance degrades. I want to monitor it more real-time, like one minute or 5 minutes.

  Could I adjust the interval? Or could I use stored procedure or DMVs?

[Former Member · Accepted solution]

Hi Dennis,

In your DBACOCKPIT  Click on GOTO in the menu -> Collector State -> There you have an option Re-occurance period where you can change your desired value .

Once updated .. from the menu Collector -> Activate new values.

THanks,

Jagadish.

[former_member211576 · Accepted solution]

Hi Jagadish,

  Sorry, I can't find the menu item you talked bout. Screenshot, please.

[leslie_moser · Accepted solution]

Hello Dennis,

As of today, you cannot change the scheduling interval of the SQL Statement History job alone. You would have to change the schedule of the general MSSQL Collector job that fills all the sap_* tables.

But you must be very careful when deciding to change this job interval - it will obviously collect the data more frequently and even though we try to minimize the data collected, the sap_* tables could grow larger much more quickly.

Therefore, I would only recommend making such a change for short term periods and preferably never on a production system but only on a test system.

If you still wish to do it, you can change the frequency for both the SQL Statement History job and the MSSQL_Collector job in the following way.

1. Open DBA Cockpit
2. Go to Configuration -> Monitoring Environment
3. Choose the tab "Monitor Configuration"
   1. In the Group=DBCOLL you will see a parameter "DBCOLL frequency [min]" is set to 20. Change the 20 to a 5 (for your 5 minute job interval). Then press the "Activate" button.
4. In the same screen, click the tab "SQL Script Execution".
   1. Click the button "Execute single SQL script" and in the input field enter the script name: sap_perfjobs. Press the "Execute SQL Script(s)" button.
5. The script will run and you should get a success message.
6. If you login to SQL Server Management Studio, you can check the job frequency for the SAP_<SID>_<schema>_MSSQL_COLLECTOR job and you should see it is now set to 5 minutes.
   1. This job performs the general DBCOLL history collection and it also calls the SQL Statement History stored procedure.

After you do this, I suggest you carefully monitor the sap_* tables to be sure they do not grow too quickly or cause too much load. I highly doubt this would happen, but of course we don't want our monitor tables to cause any performance problem.

Best regards,

Leslie

___________________________
Leslie Moser
Developer, Microsoft Platforms U.S.

  Netweaver Development Tools
SAP Labs, LLC