cancel
Showing results for 
Search instead for 
Did you mean: 

how much CPU (in %) is being used by a database process?

former_member207908
Participant
0 Kudos

Hello,

Is there a way to know how much CPU (in %) is being used by a query that is currently being runninng?

or What is exactly using CPU in % from the database level or database process


I tried to look into MDA tables

select s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t
where s.SPID = t.SPID order by s.CpuTime DESC.

topas shows dataserver using morethan 50% CPU


Name           PID            CPU%   PgSp   Owner  

dataserv   10420394     55         27.4M   sybe30 
disp+wor   35389690    0.9        230M    e30adm 

Environment: SAP ASE 15.7 SP121 on AIX 7.1  for a mixed OLTP and OLAP


Appreciate any feedback.


Rajesh

former_member207908
Participant
0 Kudos

Hi Mark,

Thanks for your reply.

     SPID    CPUTime    WaitTime    PhysicalReads

-----------       -----------        -----------         -------------

        146     5221100  912400591        107628

         22     5181200    912477667        112063

         37     4757700    912902537         82384

Now, how can i calculate the percentage of value "5221100" constitute towards CPU% (55%) shown by topas command (AIX)

Name           PID       CPU%    PgSp    Owner   

dataserv   10420394     55         27.4M   sybe30 
disp+wor   35389690    0.9        230M    e30adm

Please enlighten me..

Mark_A_Parsons
Contributor
0 Kudos

You can't match spid activity (measured *inside* ASE) directly with cpu activity @ the OS level (measured *outside* of ASE).  Yes, there's usually a close relationship between spid activity and OS cpu usage, but ...

- at the OS level you'll see 'extra' cpu usage due to the ASE engines spinning looking for work before releasing a cpu resource back to the OS scheduler

- heavy spinlock contention can quickly ramp up cpu @ the OS level, but not all spinlock activity gets associated with a specific spid

---------------

Generally speaking I tend to ...

- look at spid activity in relationship to all spids (eg, monProcessActivity)

- look at spid activity in relationship to dataserver cpu usage as measured *inside* ASE (eg, monEngine)

- review sp_sysmon results for spinlock issues; newer ASE versions also have the monSpinlockActivity table which can provide more details on spinlock activity (as compared to sp_sysmon)

former_member207908
Participant
0 Kudos

Dear Experts,

Consider  the following MPA output.

1> select top 10 SPID, CPUTime, WaitTime, PhysicalReads, LogicalReads, MemUsageKB, LocksHeld, TableAccesses from master..monProcessActivity order by CPUTime desc
2> go
SPID        CPUTime    WaitTime   PhysicalReads LogicalReads MemUsageKB  LocksHeld   TableAccesses
----------- ----------- ----------- ------------- ------------ ----------- ----------- -------------
         247     5138800    77545205         60628    116713124        1366           0     105009955
          48     4682100   125551402        112140     91839038        1386           0      81361210
         193     4391300    95722955        120036     83165229        1350           0      73301698
         232     3651300    40294608         46687     82436680        1432           0      70385350
         259     3459200    79152229        118923     60029390        1370           0      51186667
         122     3383300    68652583         34539     76865772        1334           0      67185409
          17     3314600    38600315         37027     71593895        1350           0      61357847
          62     3263000    98369363         32136     75070946        1378           0      63213699
          80     3174200    40346275         61299     52608929        1392           0      45768175
         376     3137200    92539971        110342     60814620        1350           0      52744429

CPUTime -

int

counter

CPU time (in milliseconds) used by the process

WaitTime -

int

counter

Time (in milliseconds) the process has spent waiting

Now, calculating CPU time for SPID "247", 5138800 comes to 85 minutes. Is it really using CPU for 85 minutes?

calculating Waittime of 77545205 comes nearly 21 hours. is it really waiting for 21 hours? or my calculations.intrepretations are worng?

Appreciate you insights on "CPU time" calculations

Regards

Rajesh

Mark_A_Parsons
Contributor
0 Kudos

Many of the mPA numbers are totals of activity since the spid connected to ASE.

See monProcess.SecondsConnected and/or sysprocesses.loggedindatetime to deteremine how long a spid has been connected.

I'm guessing your spid 247 had been connected for ~22 hrs 25 mins (sum of total wait and cpu times) at the time you queried mPA.  So yes, it's quite possible it consumed 85 mins of cpu time since it logged in.

As for your original question re: cpu in use by a process ... please re-read my original post.  If you want to now how much cpu a spid has just used then you need to take 2x queries of mPA and then take the delta's (ie, subtract 1st reading from 2nd reading) to determine how much cpu was just used in the last XX seconds.

Accepted Solutions (0)

Answers (0)