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
1 Answer
-
Former MemberPosted on Dec 03, 2014 at 02:36 PMHello 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
Add a comment