Thursday, July 08, 2010

TPERF per user

Below script helps to check the amount of TPERF being used by a user. The eg. below uses 246 as the system TPERF, you have to change this to reflect your systems value.

SELECT dlt.UserName

,dlt.LogDate

/* Identify total CPU time used by queries executed by user for logdate */

,SUM(dlt.AMPCPUTime+ dlt.ParserCPUTime) AS Sum_CPUTime

/* Identify impact CPU time (ie to account for skew imbalance) used by queries executed by user for logdate */

,SUM((dlt.MaxAMPCPUTime*(HASHAMP()+1))+ dlt.ParserCPUTime) AS Sum_ImpactCPUTime

/* Identify total TPerfhours used by queries executed by user for logdate where system TPerf is 246 in this eg. */

,(Sum_ImpactCPUTime * 246.00) / (64.00 * 3600.00) AS Sum_ImpactTPerfHour

/* Remove 10% of avail TPerf for OS overhead */

,246.00 * 24 * 0.90 AS Sum_AvailTPerfHour


FROM dbqlogtbl_hst dlt


WHERE dlt.LogDate > CURRENT_DATE -95

AND dlt.UserName LIKE ANY ( 'UserName' )

GROUP BY 1,2

;

Labels: , , ,

4 Comments:

Blogger Shrinivas Sagare said...

What is number 64 in below calculation?

,(Sum_ImpactCPUTime * 246.00) / (64.00 * 3600.00) AS Sum_ImpactTPerfHour

Is it typo and needs to be 24 (24 hours per day)?

4:57 pm  
Blogger Chandu said...

This comment has been removed by the author.

7:21 am  
Blogger Vinywood said...

Hello

Very interesting request.

Could you explain the meaning of 64 in your calculation ?

Thanks
Vincent from FRANCE

9:48 pm  
Blogger Unknown said...

I'd question the 64 in the calculation. If I'm not wrong, too, the 64 should be 24 times number of virtual cores (so taking hyperthreading into account)..

8:42 pm  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home