1) DBCC HELP
공식적인 DBCC 명령문의 구문 정보를 반환
비공식적인 DBCC 명령문은 인터넷에서 검색하면 찾을수 있다.


2) DBCC TRACEON/TRACEOFF/TRACESTATUS
TRACEON과 TRACEOFF는 추적 플래그를 설정/해제

추적 플래그
 - 일사적으로 서버의 일부 특성을 설정하거나 특정 동작을 중지시키는데 사용하는 플래그
 - 3604 : 추적 결과를 화면으로 출력
 - 3605 : 추적 결괄ㄹ 로그에 기록
 - 1204 : 교착상태(deadlock)를 추적
 - 2528 : DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE에 의한 개체 병렬 처리를 금지
 - 3205 : 테이프 드라이버의 하드웨어 압축을 금지시킨다.


TRACESTATUS : 현재의 추적 플래그 설정 상태를 출력


3) DBCC SQLPERF(LOGSPACE/IOSTATS/LRUSTATS/NETSTATS)
LOGSPACE : 트랜잭션 로그 공간의 사용에 관한 통계 출력
IOSTATS : 입출력 통계 출력
LRUSTATS : 데이터 캐시(버퍼)의 통계 출력
NETSTATS : 네트워크 관련 통계 출력


4) DBCC OPENTRAN
현재 데이터베이스 또는 지정한 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 정보를 출력


5) DBCC CHECKDB
지정한 데이터베이스에서 모든 개체의 할당과 구조적 무결성을 검사


6) DBCC INPUTBUFFER/OUTPUTBUFFER
클라이언트로부터 마지막으로 받은 명령문과 클라이언트에게 마지막으로 보낸 결과 집합을 출력


7) DBCC PROCCACHE
프로시저 캐시에 대한 정보를 보여준다.


8) DBCC SHOWCONTIG
지정한 테이블의 데이터와 인덱스에 대한 조각화 정보를 출력


9) DBCC SHOW_STATISTICS
지정한 테이블에서 특정 대상의 현재 배포 통계를 출력


10) DBCC USEROPTIONS
현재 연결에서 설정된 옵션들을 출력


11) DBCC dllname(FREE)
지정한 (확정 저장프로시저) 동적 연결 라이브러리(DLL)를 메모리에서 언로드


12) DBCC INDEXDEFRAG
지정한 테이블의 인덱스 조작을 모운다.


13) DBCC PINTABLE/UNPINTABLE
지정한 테이블의 페이지를 데이터 캐시에 고정시킨다.
※ 전체 성능을 저하시키는 부작용이 있으므로, 특별한 이유가 없는 한 쓰지말것


14) DBCC DROPCLEANBUFFERS
데이터 캐시(버퍼)를 완전히 비움
데이터를 하드디스크에서 읽어오도록 초기화하여 성능테스트등을 할때 사용


15) DBCC FREEPROCCACHE
프로시저 캐시를 완전히 비움
저장 프로시저등을 모두 새로 컴파일하도록 초기하여 성능테스트등을 할때 사용

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 시스템 프로시저

1) sp_who, sp_who2
sp_who는 SPID별 상태, 로그인, 사용 데이터베이스 및 명령어, 블로킹 정보 등을 보여주는 시스템 저장 프로시저이며,  sp_who2는 sp_who보다 더 많은 벙보를 보여준다.
자신의 SPID에 대해서만 결과를 보려면 sp_who2 @@SPID를 실행한다.


2) sp_lock
잠금과 관련된 정보를 보여주는 시스템 저장 프로시저
자신의 잠금만을 보려면 sp_lock @@SPID를 실행한다.
연관된 두개의 SPID에 대한 잠금을 보려면 sp_lock spid1, spid2 를 샐항한다.


3) sp_monitor
SQL Server의 서버 작업량 통계를 보여주는 시스템 저장 프로시저

last_run : sp_monitor가 마지막으로 실행된 시간
current_run : sp_monitor가 이번에 실행된 시간
seconds : last_run, current_run 의 시간차
cpu_busy : 서버 컴퓨터의 CPU가 SQL Server 작업을 수행한 시간(초)
io_busy : SQL Server에서 입력 및 출력 작업을 수행하는 데 걸린 시간(초)
idle : SQL Server가 유휴 상태에 있던 시간(초)
packets_received : SQL Server에서 읽은 입력 패킷 수
packets_sent : SQL Server에서 쓰여진 출력 패킷 수
packets_errors : 패킷을 읽고 쓰면서 SQL Server에서 발생한 오류 수
total_read : SQL Server에서 이루어진 읽기 작업 수
total_write : SQL Server에서 이루어진 쓰기 작업 수
total_errors : 읽고 쓰는 중에 SQL Server에서 발생한 오류 수
connections : 로그인 또는 SQL Server에 시도한 로그인 수

출력값 보는 방법 예) 4250(125)-68% 일경우
4250은 SQL Server가 마지막으로 시작된 후 경과한 초의 수(4250초 동안 사용되었다.
125는 sp_monitor가 마지막으로 실행된후 초과된 초의 수(125초 동안 사용되었다.)
68%는 sp_monitor가 마지막으로 실행된 후 사용시간이 총시간의 69%를 차지 한다는 의미


4) sp_spaceused
데이터베이스 또는 테이블의 각종 공간 사용 정보를 보여주는 시스템 저장 프로시저


5) sp_statistics
테이블에 관한 인덱스 및 통계 목록을 보여주는 시스템 저장 프로시저


6) sp_helpdb
데이터베이스에 관한 정보를 보여주는 시스템 저장 프로시저



2. 시스템 함수

1) @@spid
현재 사용자 프로세스의 서버 프로세스 식별자(ID)를 반환


2) @@cpu_busy
SQL Server가 마지막으로 실행된 후의 CPU작업 시간을 밀리 초 단위로 반환


3) @@connections
SQL Server가 마지막으로 실행된 후의 사용자가 연결을 시도한 횟수(연결된 횟수 포함)를 반환


4) @@error
최근에 실행된 T-SQL 문의 오류 개수를 반환(오류가 없으면 0을 반환)
※ T-SQL문을 실행할 때마다 @@error가 반환되는 값은 초기화 된다.
    @error의 반환 값을 로컬 변수에 저장한 후 이를 활용해야 한다.


5) @@procid
현재 저장 프로시저의 식별(ID)를 반환
일반적으로 저장 프로서저 안에서 실행한다.
저장 프로시저 밖에서 실행할 때는 0을 반환



3. T-SQL

1) set statistics io
T-SQL문이 생성한 디스크 입출력 작업량에 대한 정보를 출력
※ set statistics io on 으로 설정해서 출력되는 결과에서 논리적 읽기 수를 우선을 둔다.


2) set statstics time
명령문을 구문 분석, 컴파일 및 실행하는데 소용된 시간을 출력


3) set showplan_text
명령문을 실행하는 대신 텍스트 형식의 실행 계획을 출력
※ set showplan_text on 으로 설정후 명령문을 실행해도 명령문을 실행하지 않는다.


4) set showplan_all
set showplan_text 보다 더 자세한 내용을 출력
※ set showplan_all on 으로 설정후 명령문을 실행해도 명령문을 실행하지 않는다.


5) set statistics profile
set showplan_all 와 다른 점은 명령문을 실제로 실행한다는 것과, Row, Executes 열이 추가된다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

프로파일러 파일 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

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 이벤트 뷰어

시작 -> 프로그램 -> 관리도구 -> 이벤트 뷰어에 위치하는 Windows 도구로서, 시스템, 보안 및 응용 프로그램 로그에 각종 오류, 경고, 정보 등을 기록해 준다.
시스템이나 SQL Server에 문제가 있을때 제일 먼저 이벤트 뷰어를 확인해 볼 필요가 있다.
이벤트 내의 오류번호로 마이크로소프트 지식 베이스에서 관련된 설명을 확인해 볼수 있다.


2. 성능 모니터

1) 시스템 모니터

시작 -> 프로그램 -> 관리도구 -> 성능(2008 Server에서는 안정성 및 성능 모니터)을 실행하면 시스템 모니터가 실행된다.
시스템 모니터는 기본적으로 현재의 시스템 동작을 보여준다. 그래프, 히스토그램, 보고서 세종류로 보여주며 기본값은 그래프이다.
그래프 보기는 배율이 서로 다른 카운터들을 함께 모니터하는 것이 힘들기 때문에 이런 경우에는 보고서 보기로 보는것이 좀 더 낮다. 그러나 그래프, 히스토그램, 보고서 세가지다 현재 값만 볼수 있고 최대값, 최소값, 평균값 등은 확인할 수 없기 때문에 카운터 로그를 만드는것을 더 권장한다.

2) 카운터 로그

시스템 모니터보다 카운터 로그를 권장한다.

1. 카운터 로그에서 새 로그 설정을 선택한다.

2. 카운터 로그의 이름을 입력한다.

3. 로그 속성창에서 카운터 추가 버튼을 클릭하여 카운터를 추가한다.
   카운터가 추가 되면 될수록 부하가 커지기 때문에 불필요한 카운터는 추가하지 않는다.

4. 데이터 샘플 간격을 조절한다.
   기본 설정은 15초이지만 베이스라인이 없을 경우에는 15초가 아니라 15분 1시간등오로 증가시켜야 한다.

5. 카운터에 따라서 실행 권한을 설정해야 되는 경우도 있다.
    이런 경우에는 권한을 가진 계정을 입력하고 암호 설정을 해야한다.

6. 파일 로그 탭에서는 로그 파일의 이름을 설정할수 있다.


7. 일정 탭에서는 로그 시작/중지 일정을 설정할 수 있다.

8. 저장된 로그 파일은 C:\PrefLogs 폴더에 저장된다.

9. 저장된 로그 파일을 확인하는 방법은 시스템 모니터, HTML이 있다.

10. 시스템 모니터로 로그 확인
     시스템 모니터에 등록되어 있는 카운터들을 모두 삭제하고 3. 로그 속성상에서 추가한 카운터를 추가하고 원통형의 로그데이터보기 버튼을 클릭해서 데이터원본 -> 로그 파일 -> 추가 버튼을 클릭하여 저장된 로그 파일을 선택한다.

11. HTML 로 로그 내용 저장하기
     카운터 로그에서 마우스 오 른쪽 버튼을 클릭하고 다름이름으로 설정 저장을 클릭하면 html 파일 형식으로 저장할수 있다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1. 개요

서버 및 데이터베이스의 동작을 모니터링하고 튜닝하는 도구로서 교착상태, 치명적인 오류, 저장 프로시저와 Transact-SQL문, 로그인 동작, 일괄 처리, 트랜잭션 시작과 같은 엔진 프로세스 이벤트등을 추적하여 데이터를 테이블이나 파일로 저장할 수 있다.


2. 템플릿

어떤 이벤트의 어떤 항목들을 추적할것인지 미리 정의해 둔것으로서 기본 템플릿을 그대로 사용하면 과부하가 걸릴수 있다.


3. 저장

추적한 데이터는 테이블, 파일로 저장할 수 있다.

추적한 데이터가 많을때는 기본 설정(5MB)가 부족함으로 필요한 만큼 늘려줘야 한다.
데이터를 파일로 저장시 파일 롤오버 옵션을 사용하면 저장되는 파일의 확장자가 tr1, tr2, trc3... 형식으로 하드가 꽉 찰때까지 저장되기 때문에 주의해야한다. 
추적 중지 시간 설정을 권장한다.

데이터를 테이블로 저장시 서버에 이중으로 부하가 발생함으로 파일로 저장하는것을 권장한다. 파일로 저장된 데이터를 fn_trace_gettable로 테이블에 저장할수 있다.

EX)
SELECT IDENTITY(int, 1, 1) AS RowNumber, *
             INTO #temp
             FROM ::fn_trace_gettable('trc 파일', DEFAULT);


파일, 테이블에 저장하지 않고 추적을 할경우 메모리상에 추적 데이터가 저장되며 메모리가 꽉차면 추적을 중지한다.


4. 이벤트

Standard(기본값)을 선택시 Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting가 선택된다.

Audit Login, Audit Logout : SQL Server에 로그인/로그아웃시 발생, 거의 추적안함
ExistingConnection : 블로킹등 특수한 경우외에 사용안함
RPC:Completed : 클라언트에서 SQL Server로 호출한 프로시저를 종료할 때 발생
SQL:BatchCompleted : 일괄처리 종료시 발생
SP:StmtCompleted : 저장 프로시저안의 명령문들을 문장단위로 추적, 특별한 경우를 제외하고 사용안함


5. 오류 및 경고

Attention, ErrorLog, EventLog, Exception, Execution Warnings, Hash Warring, Missing Column Statistics, Missing Join Predicate, OLEDB Errors, Sort Warnings 이벤트 클래스가 있다.

Attention : 클라이언트 인터럽트 요청, 클라이언트 연결 끊어짐의 주의를 요하는 이벤트
Execution Warnings : 명령문이나 저장 프로시저 실행 중에 발생하는 경고 이벤트
Exceptoin : 가장 흔히 발생하는 각종 오류로 인해 발생하는 이벤트


6. 잠금

Lock:acquired, Lock:Cancel, Lock:Deadlock, Lock:Deadlock Chain, Lock:Escalation, Lock:Released, Lock:Timeout 이벤트 클래스가 있다.

Lock:Deadlock Chain : 교착상태가 어떤 개체와 관련되어 있는지 확인하는 이벤트
Lock:Deadlock : 잠금을 얻을려고 시도하는 동안 교착상태가 발생할하여 잠금을 얻을려고 하는 시도가 취소될때 발생하는 이벤트
Lock:Timeout : SET LOCK_TIMEOUT문으로 설정해놓은 잠금 시간 만료가 되었을때 발생하는 이벤트

위의 세가지 이외의 이벤트 클래스들은 특별한 경우가 아닌 한 사용하지 않는것이 좋다.

※ 실제 서버에서 프로필러로 교착을 추적하기 보다는 추적 플래그 1204를 이용하여 추적하는것을 권장한다.


7. 트랜잭션

DTCTransaction, SQLTransaction, TransactionLog 이벤트 클래스가 있다.

SQLTransaction : 트랜잭션의 시작, 커밋, 롤백, 저장등과 관련된 이벤트


8. 데이터 열

자주 사용하는 데이터 열
EventClass : 캡쳐된 이벤트 클래스를 보여주며, 이 데이터열은 제거할 수 없다.
CPU : 이벤트에 의해 소요된 CPU 사용 시간(밀리 초 단위)
Duration : 이벤트에 의해 소요된 경과 시간(밀리 초 단위)
Reads : 이벤트에 의해 서버에서 수행한 논리적 디스크 읽기수(페이지 단위)
Writes : 이벤트에 의해 서버에서 수행한 문리적 디스크 쓰기수(페이지 단위)
TextData : 캡처된 이벤트 클래스에서 보내진 텍스트 값으로, 실행되는 T-SQL문, 저장 프로시저 또는 일괄 처리의 텍스트를 보여준다.
Binary Data : 캡처된 이벤트 클래스에서 보내진 바이너리 값으로, 해당 이벤트 클래스에서 데이터 열로 요구할때만 추가하면 된다.
ClientProcessID : 호스트 컴퓨터가 클라이언트 응용 프로그램이 실행되고 있는 프로세스에 대해 할당한 ID
SPID : SQL Server가 클라이언트와 관련된 프로세스에 대해 할당한 고유한 ID
          50이하는 시스템, 51이상은 사용자가 사용
Application Name : SQL Server 인스턴스와 연결을 설정한 클라이언트 응용 프로그램의 이름
Host Name : 클라이언트를 실행중인 컴퓨터의 이름
Database ID : 주어진 연결에 대한 현재 데이터베이스의 ID

추적 분석을 할 때 Duration, CPU, Reads, Writes, SPID 또는 ClientProcessID 그룹화하면 좋다.


9. 필터

추적할 데이터 열에 조건을 부여하여 추적 대상을 필터링할 수 있도록 해준다.


10. 사용자 정의 템플릿 만들기

1) 프로파일러에서 파일 -> 새로 만들기 -> 추적 템플릿

2) 추적 템플릿 속성 대화상자의 이벤트 페이지에서 다음과 같은 이벤트 클래스를 선택
    * 저장 프로시저 - RPC:Completed
    * TSQL - SQL:BatchCompleted

3) 데이터 열 페이지에서 다음과 같은 데이터 열들을 선택한다.
    * Event Class
    * Duration
    * CPU
    * Reads
    * Writes
    * TextData,
    * ApplicationName
    * DatabaseID
    * ClientProcessID
    * HostName
    * SPID
    * StartTime

4) 필터 페이지에서 다음과 같이 추적 이벤트 조건을 설정한다.
   * ApplicationName -> 유사하지 않음 -> SQL Profiler

5) 일반 페이지에서 다른 이름으로 저장 버튼을 누른다.

※ 2000 기준이면 2005는 약간 틀리다.(많이 귀찮다..)


11. 추적 결과 분석

추적 결과 데이터를 테이블에 입력해서 추적 결과를 분석할 수 있다.

1) 인덱스 관련 문제 찾기
    Reads, Duration 값이 많이 클경우 인덱스와 관련된 문제일 가능성이 크다.
    Reads > 10000 AND Duration > 10000 ORDER BY Reads DESC

2) 블로킹 찾기
    Reads나 CPU값은 작으면서 Duration 값이 많이 크다면 블로킹일 가능성이 크다.
    Duration > 10000 AND reads < 1000 ORDER BY Duration DESC


12. 추적 재생

추적한 결과를 재실행함으로써 오류나 버그의 재현, 디버깅 등을 가능하게 해줍니다.

추적 재생을 하기 위한 최초 추적을 하려면 프로필러 템필릇으로는 반드시 SQLProfilerTSQL_Replay를 사용하여야 한다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

첨부된 문서는 SQL Server 프로파일러, 시스템 모니터, 그리고 SQL Server 2005의 새로운 동적 관리 뷰 등과 같은 공개된 도구들을 사용해서 공통적인 성능 문제를 진단 및 해결하기 위한 단계적인 지침을 제공합니다.

출처 : http://www.microsoft.com/korea/technet/prodtechnol/sql/2005/tsprfprb.mspx

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.