Introduction to the Mimer SQL monitor

The Mimer SQL monitor application can be used as a helper tool if you are experiencing performance issues on the database server, to find out if one or more specific SQL statements are causing the situation. It can also be used if you’re curious about what SQL statements that are run, and facts about when, how often, by whom, how expensive they are performance-wise and what resources they are using.

The SQL monitor application provides statistical numbers that require some understanding of how the database server operates in order to be interpreted accurately. What they represent and how they are to be interpreted is described in the System Management Handbook in the Mimer SQL Documentation Set, found at our documentation page.

Below, this article will provide some example scenarios where the Mimer SQL monitor application might help, and how to use it. It presents examples where the statistics provided can be used to draw conclusions on the usage of the database server.

SQL monitoring examples

Scenario 1 – solving performance issues

The case:

During the past week, every day after lunch there’s a slight performance decrease on your database server for about an hour, and then it goes back to normal. During the night the database server was restarted for another reason, so today all the SQL monitoring statistics are at a clean slate, which you plan to take advantage of.

Referring to the case above, when the clock strikes 1 PM, i.e. when the latency usually starts, the SQL monitor application is attached to the database server, using the system administrator login. It will immediately start continuously monitoring the system.

To see what is happening during this specific hour, the application is asked to take a snapshot of all the executed SQL statements every 30 seconds 20 times, stopping after 10 minutes. As the load is quite heavy, only the top 5 statements with the most table operations are monitored.

> sqlmonitor mydatabase --username=SYSADM --password=sysadm_password --interval=30 --stop=20 --top=5
Mimer SQL Monitor Version x.y.z
Copyright (C) Mimer Information Technology AB. All rights reserved.
============================
SNAPSHOT 2020-01-01 13:00:00
----------------------------
   Table  Elapsed
    oper     time
  ations   (total
 (total)    secs)   SQL ID
----------------------------
       x x.xxxxxx       67
       x x.xxxxxx       54
       x x.xxxxxx       73
       x x.xxxxxx       64
       x x.xxxxxx       82
-----------------------------------
   Table  Current  Elapsed
    oper table op     time
  ations erations   (delta
 (delta)  /second    secs)   SQL ID
-----------------------------------
--2020-01-01 13:00:30--------------
       x x.xxxxxx x.xxxxxx      102
       x x.xxxxxx x.xxxxxx      111
       x x.xxxxxx x.xxxxxx       67
       x x.xxxxxx x.xxxxxx      107
       x x.xxxxxx x.xxxxxx       54
-----------------------------------
--2020-01-01 13:01:00--------------
       x x.xxxxxx x.xxxxxx      107
       x x.xxxxxx x.xxxxxx      102
       x x.xxxxxx x.xxxxxx      111
       x x.xxxxxx x.xxxxxx       54
       x x.xxxxxx x.xxxxxx       67
-----------------------------------
--2020-01-01 13:01:30--------------
       x x.xxxxxx x.xxxxxx      107
       x x.xxxxxx x.xxxxxx      111
       x x.xxxxxx x.xxxxxx      102
       x x.xxxxxx x.xxxxxx       32
       x x.xxxxxx x.xxxxxx       54
-----------------------------------

You find that the SQL statements with id 102, 107 and 111 keep reappearing during monitoring, and they perform a lot more table operations than the other statements that are executed. You now ask the application to take a snapshot of these statements specifically.

> sqlmonitor mydatabase --username=SYSADM --password=sysadm_password --sqlid=102 --sqlid=107 --sqlid=111
Mimer SQL Monitor Version x.y.z
Copyright (C) Mimer Information Technology AB. All rights reserved.
==============================================================
SNAPSHOT 2020-01-01 13:11:00
--------------------------------------------------------------
SQL ID SQL statement
--------------------------------------------------------------
   102  select x from y
   107  update x set y = z where a = b
   111  insert into z (y) values (x)
==============================================================
                                      Trans-
   Table                              action  Elapsed
    oper  Prepare  Execute   Server   record     time
  ations    count    count requests    count   (total
 (total)  (total)  (total)  (total)  (total)    secs)   SQL ID
--------------------------------------------------------------
       x        x        x        x        x x.xxxxxx      102
       x        x        x        x        x x.xxxxxx      107
       x        x        x        x        x x.xxxxxx      111
==============================================================

Based on the figures you saw while monitoring continuously, the total number of table operations for each of these statements, since the server restart the past night, approximately correspond to what would be expected of about 10 minutes of execution. This should mean that the statements weren’t executed earlier that day, they just started executing at 1 PM.

The solution:

You now have enough information to ask your colleague about these SQL statements. They recognize the SQL statements shown by the SQL monitor as being part of the major batch job they added last week, that they thought had been configured to be run every night at 1 AM. You reconfigure the batch job to actually run at 1 AM instead of 1 PM, and the performance drops during the day will stop occurring.

Scenario 2 – benchmarking an application

The case:

You have written an application program that executes as a Mimer SQL program ident on your database server. As your network speed is the bottleneck of your system, you would like to know how many server requests are performed by the application when it runs.

In this case you should start the SQL monitor in benchmarking mode. You order the results by server requests, and choose detail level 2 in order to see the number of server requests which is hidden by default. You specify the program ident your application uses in order to only get the SQL statements the application program executes and nothing else. The SQL monitor waits for your input.

> sqlmonitor --program=ExampleProgram --benchmark --order=server_requests –detail=2
Mimer SQL Monitor Version x.y.z
Copyright (C) Mimer Information Technology AB. All rights reserved.

Username: xyz
Password:
Program password:
Press [ENTER] to take the first snapshot.

You provide input to the SQL monitor and it takes an initial snapshot of the SQL statements run by the program ident ExampleProgram on your database server so far. The SQL monitor then again waits for your input.

==============================================================
SNAPSHOT 2020-01-01 12:00:00
--------------------------------------------------------------
                                      Trans-
   Table                              action  Elapsed
    oper  Prepare  Execute   Server   record     time
  ations    count    count requests    count   (total
 (total)  (total)  (total)  (total)  (total)    secs)   SQL ID
--------------------------------------------------------------
       x        x        x        x        x x.xxxxxx       67
       x        x        x        x        x x.xxxxxx       66
==============================================================

Press [ENTER] to take another snapshot.

You run your application program, and then ask the SQL monitor to continue taking another snapshot for comparison. It now presents the SQL statements’ number of server requests between then and now, while you executed your application program. The number is presented for each executed SQL statement in column five.

=======================================================================
DELTA
-----------------------------------------------------------------------
                                               Trans-
   Table  Current                              action  Elapsed
    oper table op  Prepare  Execute   Server   record     time
  ations erations    count    count requests    count   (delta
 (delta)  /second  (delta)  (delta)  (delta)  (delta)    secs)   SQL ID
-----------------------------------------------------------------------
--2001-01-01 12:05:00--------------------------------------------------
       x        x        x        x        x x.xxxxxx       67
       x        x        x        x        x x.xxxxxx       66

Note! In the example above, the user xyz must have execute privilege on the program ExampleProgram for SQL monitor to allow the monitoring operation, or it will present an authorization failure.

Links

For details and further information on the SQL monitor application, see the System Management Handbook in the Mimer SQL Documentation Set, html navigation or PDF-file.

Graphic Element - Cube