Skip to Content

MaxDB I/Os on Windows

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2014 at 02:36 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.