Query para analise de performance

[vieriParticipante]

No sql server o principal recurso é a ferramente SQL PROFILER correto?

ou o SET STATISTICS no QA.

No oracle vc usa o enterprise manager , statspak, explain plan,
AWR , etc… sempre com recurso de WAITS por TEMPO.

algumas querys avulsas tb ajudam a indicar:

PROMPT ======================================================================
PROMPT ======================================================================
PROMPT Consumo de memoria por usuários
PROMPT ======================================================================

SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20
COLUMN osuser FORMAT A10

SELECT a.osuser,a.status,a.inst_id,
NVL(a.username,'(oracle)’) AS username,
a.module,
a.program,
trunc(b.value/1024) AS memory_kb
FROM gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = ‘session pga memory’
AND a.program IS NOT NULL
ORDER BY b.value DESC;

PROMPT ======================================================================
PROMPT ======================================================================
PROMPT PROCESSOS USUARIOS
PROMPT ======================================================================

Select p.spid, p.addr, s.sid, s.serial#, s.username, s.osuser, s.status, s.server, s.logon_time, s.sql_hash_value, TO_CHAR (s.last_call_et / 3600, ‘009.99’) inatividade, s.machine, s.program, p.pga_used_mem, p.pga_alloc_mem, p.pga_freeable_mem, pga_max_mem, sql.sql_text
From v$session s, v$process p, v$sql sql
Where s.username is not null
And s.paddr = p.addr (+)
And s.sql_address = sql.address
— and s.sid = 1082
Order by s.last_call_et desc;

PROMPT ======================================================================
PROMPT ======================================================================
PROMPT EXECUÇÔES LONGAS
PROMPT ======================================================================

SET LINESIZE 200
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.inst_id,
s.sid,
s.serial#,
s.username,s.osuser,
s.module,
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;

======

WAITS

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT s.inst_id,
NVL(s.username, ‘(oracle)’) AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id and sw.event not in (‘SQL*Net message from client’)
ORDER BY sw.seconds_in_wait asc;

isso é somente a ponta do iceberg, este assunto é extenso!!!!