프로파일러 파일 DB입력
select convert(varchar(8000), TextData) as TextData
, duration, cpu, reads, writes, databaseId
, hostname, ApplicationName, loginname
, spid, StartTime
into tblTrace
from ::fn_trace_gettable('프로파일러 파일', default)
go
주요 컬럼 인덱스 생성
create index idx_tblTrace_duration on tblTrace(duration)
create index idx_tblTrace_cpu on tblTrace(cpu)
create index idx_tblTrace_reads on tblTrace(reads)
go
각기준에 맞는 부하가 큰 부분확인
duration
select top 20 *
from tblTrace
order by duration desc
cpu
select top 20 *
from tblTrace
order by cpu desc
reads
select top 20 *
from tblTrace
order by reads desc
간단한 통계로 확인
select left(TextData,40), count(*) as counts
, avg(duration) as duration_avg, avg(cpu) as cpu_avg, avg(reads) as reads_avg, avg(writes) as writes_avg
, max(duration) as duration_max, max(cpu) as cpu_max, max(reads) as reads_max, max(writes) as writes_max
, min(duration) as duration_min, min(cpu) as cpu_min, min(reads) as reads_min, min(writes) as writes_min
from tblTrace
group by left(TextData,40)
order by 2 desc
select convert(varchar(8000), TextData) as TextData
, duration, cpu, reads, writes, databaseId
, hostname, ApplicationName, loginname
, spid, StartTime
into tblTrace
from ::fn_trace_gettable('프로파일러 파일', default)
go
주요 컬럼 인덱스 생성
create index idx_tblTrace_duration on tblTrace(duration)
create index idx_tblTrace_cpu on tblTrace(cpu)
create index idx_tblTrace_reads on tblTrace(reads)
go
각기준에 맞는 부하가 큰 부분확인
duration
select top 20 *
from tblTrace
order by duration desc
cpu
select top 20 *
from tblTrace
order by cpu desc
reads
select top 20 *
from tblTrace
order by reads desc
간단한 통계로 확인
select left(TextData,40), count(*) as counts
, avg(duration) as duration_avg, avg(cpu) as cpu_avg, avg(reads) as reads_avg, avg(writes) as writes_avg
, max(duration) as duration_max, max(cpu) as cpu_max, max(reads) as reads_max, max(writes) as writes_max
, min(duration) as duration_min, min(cpu) as cpu_min, min(reads) as reads_min, min(writes) as writes_min
from tblTrace
group by left(TextData,40)
order by 2 desc
이올린에 북마크하기
