PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

How to find DUPLICATE STATS & HIGH CPU CONSUMING STATS in the datawarehouse ?

Q 30) How to find DUPLICATE STATS & HIGH CPU CONSUMING STATS in the datawarehouse ?

Answer >

SEL LOGDATE,DEFAULTDATABASE,USERNAME, ‘HIGH CPU STATS’ AS STATUS,QUERYTEXT, COUNT(*) AS COU
FROM EDW1_UTIL_BASEVIEW.DBQLOGTBL_HST
WHERE QUERYTEXT LIKE ‘%Collect%’
AND LOGDATE BETWEEN DATE-7 AND DATE
AND MAXAMPCPUTIME>2000
GROUP BY 1,2,3,4,5

UNION

SEL
LOGDATE,
DEFAULTDATABASE,
USERNAME,
‘DUPLICATE STATS’ as STATUS,
QUERYTEXT,
COUNT(*) as COU
FROM EDW1_UTIL_BASEVIEW.DBQLOGTBL_HST
WHERE QUERYTEXT LIKE ‘%COLLECT STAT%’
AND STATEMENTTYPE LIKE ‘%Collect%’
AND LOGDATE BETWEEN DATE-7 AND DATE
AND DEFAULTDATABASE LIKE ‘EDW1_%’
GROUP BY 1,2,3,4,5
HAVING COU > 1

 

Add a Comment

Your email address will not be published. Required fields are marked *