cancel
Showing results for 
Search instead for 
Did you mean: 

MaxDB I/Os on Windows

LukasM
Explorer
0 Kudos

Hello,

I'm looking for some hints, which can help me to analyse the disk acitvity for a single "Left Outer Join" SQL statement. Unfortunately, the business is not able to enter an additional Where - clause.

Our database (version: 7.9.08.14 / OLTP)  is running on a virtual Windows 2008 R2 Server (ESXi 5.1.0). All datavolumes (50 pieces) are on a single LUN.

If I use the "x_cons <DB> show active 1 25", I get this reponse from, where T128 is my ID. During the whole runtime of the statement, there is no high cpu (Task Manager) or high disk activity (Resource Monitor of Windows). If I copy a file (3,5 GB) on the LUN, the performance is OK.

SERVERDB: xxx

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     27        39077695(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T54    9 0x1A60 User      6884* IO Wait (R)             0     15        121292233(s)

T128  11 0x13DC User      1240* IO Wait (R)             0     24        39077820(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T35    8 0x1770 User      1812* IO Wait (R)             0     26        32578072(s)

T54    9 0x1A60 User      6884* IO Wait (R)             0     47        121292754(s)

T128  11 0x13DC User      1240* IO Wait (R)             0      9        39078040(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T54    9 0x1A60 User      6884* IO Wait (R)             0     20        121292947(s)

T128  11 0x13DC User      1240* IO Wait (R)             0     26        39078296(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     29        39078437(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     13        39078563(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0      4        39078667(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     20        39078759(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0      8        39078856(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0      3        39079019(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     37        39079176(s)

ID   UKT  Win   TASK       APPL Current        Timeout/ Region     Wait

          tid   type        pid state          Priority cnt try    item

T128  11 0x13DC User      1240* IO Wait (R)             0     11        39079282(s)

I know there are many components, but maybe with some hints I'm able to exclude a one or more component(s). I already check the good scripts from http://maxdb.sap.com/traning, but unfortunately without success.

Thank you!

best regards

Lukas Meusburger

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Lukas,

you know the Task which is runing the command which has to do the IO - it is T128 -

The database console program has additional optionen to get detailed information about what is task T128 doing in detail related to runtime environment.

you can run x_cons <DBNAME> show T_C T128

Option T_C means task details.

May be is should be better to reset all x_cons counters before you start the Join statement again:

x_cons <DBNAME> reset_all

Here you get more information about IO and times.

Additionally you can switch on the command monitor in version 7.9.08.14 you'll get detailed information how many IOs have to be done exactly for this command.

Please start command monitor BEFORE you run the SQL Statement.

Regards, Christiane

LukasM
Explorer
0 Kudos

Hello Christiane,

I used the commands x_cons <DB> dev_io and x_cons <DB> T_C xxx, but for me the output isn't really clear.

1) DEV_IO - Command

1.1) What does the AvgRead-Time in ms means?

E.g first row: Readed pages = 4 and AvgReadTime / Page about 7 millisec. True or False?

Is this a more or less good value or more bad?

2) T_C - Command

2.1) Which parameter is most important or most meaningful? And which DB-parameter can impacted this?

Unfortunately the command monitor doesn't works correctly. I didn't find the statement in the table, although I activated the monitor before starting the query. I have to check this too.

Thanks.

best regards,

Lukas

Former Member
0 Kudos

Hello Lukas,

the x_cons output show Dev_IO gives information about the asynchronous IO statistics.
MaxDB is oding asynchronous IO means that the User Task which is executing the SQL command does not do the IO by itself but forward the IO request to the Dev_THreads . They are responsible for the IO forwarding to the IO subsystem.

You have several IO Threads or IO worker Threads  per Data Volume therefore you have several entries for the same Data volume. The statistics shows how many worker threads were active since the last reset of the console or restart of the database.And you get information about the average read /write time per read and write via the Dev-threads. So it does not look so bad on your system.

The Task Details give us more detailed information about what ressources the Task is consuming.
In Your example Task T128 has Application PID 1240, has executed 32 SQL commands since reset of the console. You'll get the dispatch count of the internal tasking and you'll see the reads were done on DISKD0041 more than 830.000 read IO were executed and per read IO 1 Page was read. And you get the time for read Io - the relative and abs dev read time are fine - we do not have a gap here, emans additional wait time.

The command monitor only loggs SQL commands which are related to the thresholds when you started the Command monitor. If the statement does not fulfill the threadsholds it is not logged.
The you must use the SAP transaction ST05 SQL Trace to log the Commands. Here you can execute the EXPLAIN plan of the long running statement. May be you have to optimize this statement with additional indexes.

So you'll see this is a very complex analysis which cannot be done in detail via SDN. If you are a SAP customer I recommend to open an incident and request performance analysis of your statement.

Regards, Christiane

LukasM
Explorer
0 Kudos

Hello Christiane,

Thank you for helping.

I'll close this thread and open an incident on the SAP SupportPortal.

best regards,

Lukas