MSSQL - 시스템 프로시저

 

T-SQL

(1) 시스템 프로시저

① sp_who / sp_who2

- DB 엔진 인스턴스의 사용자, 세션 및 프로세스에 대한 정보를 제공한다.

- sp_who2는 sp_who보다 상세한 정보를 전달한다.

- sp_who @@SPID 는 현재 실행하고 있는 사용자만 보여준다.

 

② sp_lock

- 현재 락을 잡고 있는 spid를 보여준다.

- 블락킹을 볼 수 있는 시스템 프로시저는 없다. 사용자가 따로 생성하여야 한다.

 

③ sp_spaceused

- 현재 DB의 크기, 할당 된 공간, 남은 공간 등을 나타내 준다.

 

④ sp_helpdb

- 인스턴스 내 DB들의 정보를 나타내 준다.

 

(2) T-SQL문

① SET STATISTICS IO

- 실행 시 읽은 테이블 횟수, 페이지 횟수 등을 나타내 준다.

SET STATISTICS IO ON SELECT * FROM Customers WHERE CustName = N'관영'

 

 

② SET STATISTICS TIME

- 파싱, 컴파일, 실행 시 걸린 시간을 나타내준다.

SET STATISTICS TIME ON SELECT * FROM Customers WHERE CustName = N'관영'

 

 

③ SHOWPLAN_TEXT

- 텍스트 형식으로 실행 계획을 보여준다.

SET SHOWPLAN_TEXT ON GO   SELECT * FROM Customers WHERE CustName = N'관영'

 

(3) DBCC문

DBCC HELP(TRACESTATUS) -- DBCC 명령어를 어떻게 사용하는지 알려준다.   DBCC TRACEON(3604) -- 추적 플래그를 설정한다. DBCC TRACEON(3605) -- 추적 결과를 로그에 기록한다. DBCC TRACEON(1204) -- 교착상태를 추적한다. DBCC TRACESTATUS(-1) -- 현재의 추적 플래그 설정 상태를 보여준다.

 

결과

 

DBCC opentran -- 가장 오래된 활성 트랜잭션에 대한 정보를 표시해준다.

 

출처: https://elandda.tistory.com/28?category=571247

MSSQL - 인덱스 관리 [조각화]

 

▶인덱스 관리 [조각화]

- 내부 조각화 : 인덱스 페이지가 꽉 차있지 않고 비어있는 상황을 말한다. OLTP성 테이블의 인덱스라면 빈공간 확보(내부 조각화)가 필요하다. 하지만 OLAP성 테이블에서는 빈공간이 많으면 조회할 때 읽는 페이지 수가 증가하여 성능이 떨어질 수 있다.

- 외부 조각화 : 인덱스의 논리적인 순서와 물리적인 순서가 맞지 않은 경우로 성능에 도움이 되지 않으므로 제거해야 한다.

  제거 방법 : Rebuild(외부 조각화 30%이상), Reorganize(5-30%) 

- 외부 조각화와 내부 조각화 조회하는 쿼리

select a.index_id, name, avg_fragmentation_in_percent as N'외부조각화', avg_page_space_used_in_percent as N'내부조각화' from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Production.Product'), NULL,NULL,NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id and a.index_id = b.index_id

 

튜닝 사례

[문제] 다른 서버로 부터 가져온 Text file을 테이블에 넣을 때 너무 오래 걸렸다.
[원인] 클라이언트프로그램이 텍스트 파일을 열어서 하나씩 insert문으로 수행하고 있었다.
[해결] Bulk insert 사용해서 쿼리문에서 직접 텍스트파일을 읽어온다. (속도 6배향상)

 

[문제] SELECT *
[원인] 사용하지 않는 데이터 호출로 인한 부하가 생겼다.
[해결] 필요한 컬럼(date type의 byte가 적은 컬럼을 주로)을 사용하는 것이 좋다.

 

[문제] 커서와 임시테이블
[해결] 커서보다는 임시테이블, 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 좋다.
        커서로 처리하는 것은 모두 임시테이블과 테이블 변수로 처리 가능하므로 커서는 쓰지 않는다.

 

[문제] JOIN
[해결] 동일한 효과를 가지는 쿼리의 경우 INNER JOIN이 아닌 LEFT OUTER JOIN을 쓰는 경우가 있다. INNER JOIN을 써야한다.

 

[문제] 배치 작업 안에서 인덱스를 만들고 작업이 끝나면 지운다.
[해결] 불필요한 인덱스를 프로시저 안에서 만들지 않는다.

 

[문제] 디스크 조각이 심하게 나있었다.
[해결] DBCC dbreindex 혹은 DBCC indexdefrag를 주기적으로 실행하여 인덱스 단편화를 제거한다.

 

[문제] where 조건문의 변형된 column
[해결] where name + ‘’ = ‘조건’ 과 같이 왼쪽 조건을 변형하지 말고, where name = ‘’+’조건’과 같이 오른쪽에 조건선언을 해야 한다. 조건 일치를 매 로우마다 확인할 때 왼쪽 조건을 변형하면 그만큼 부하가 크다.

출처: https://elandda.tistory.com/21