*본 포스팅은 개인의 학습 내용을 정리한 것입니다.
- 저장 프로시저 : 일련의 쿼리를 하나의 함수처럼 처리하기 위한 쿼리의 집합.
뷰의 경우 조회전용 개체, 트리거는 수정전용 개체, 저장프로시저는 둘 다 가능하다.
- 저장 프로시저의 장점
1) 모듈별 프로그래밍이 가능하다.
2) 수정 및 배포가 쉽다.
3) 실행 계획을 재사용하여 더 빠르다. 저장 프로시저는 호출되면 메모리에 캐싱되어 있다.
4) 네트워크 소통량을 줄일 수 있다.
5) 보안 매커니즘으로 사용가능.
- 저장 프로시저 종류
1) 시스템 저장 프로시저 : 시스템이 제공. sp_ 접두어를 사용한다. 만약 같은 이름을 가진 저장 프로시저가 로컬에 있다면, 로컬에 있는 것을 우선한다.
2) 확장저장 프로시저 : SQL서버 외부로 ODS를 통해 작업을 처리하는 특수한 프로시저. 구문을 확인할 수 없다. 이 기능은 추후에 제거될 예정이라고 함(http://technet.microsoft.com/ko-kr/library/ms175200(v=sql.105).aspx)
3) 로컬 저장 프로시저 : 연결된 서버에 있는 사용자가 정의한 일반 저장 프로시저.
4) 임시저장 프로시저 : 임시테이블과 마찬가지로 로컬(#)과 글로벌(##)로 나눌 수 있다.
5) 원격저장 프로시저 : 원격서버에 있는 저장 프로시저.
1. 저장 프로시저의 정의와 사용
|
CREATE PROC[EDURE] <프로시저명> [;number] [{<@파라미터> data_type} [VARYING] [= default] [ OUTPUT]][,... n] [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION} ] [FOR REPLICATION] AS <SQL구문> |
(1) RECOMPILE : 캐싱해 두지 않고, 해당 프로시저 호출시마다 컴파일하여 사용.
(2) 매개변수 반환 방법
1) 저장프로시저에서 수행된 SELECT문 결과 집합(하위에서 수행된 것 포함)
2) 정수 또는 문자값 같은 OUTPUT매개변수
3) 정수를 반환할 수 있는 return값
4) 저장프로시저 밖에서 참조할 수 있는 전역커서
(3) INPUT매개변수의 사용
(4) RETURN값의 사용
RETURN에서 저장 프로시저 또는 일괄처리를 종료하기 위해 사용된다. 이 이후의 구문은 실행되지 않음. 정수형 값을 반환할 수 있다. 아무 값도 없을 경우 0을 반환한다.
(5) OUTPUT매개변수 사용
RETURN으로는 하나의 정수값만 반환이 가능하지만, OUTPUT으로는 갯수 제한이 없고, 다른 데이터 형식도 반환가능하다. 그러나, text,ntext,image는 안된다. (409 오류발생)
(6) 매개변수 전달 방법
- 매개변수 전달시에, 프로시저에서 선언한 순서대로 변수를 넘겨주거나, 변수명을 지정하여 호출할 수 있다.
(7) 저장 프로시저 결과 셋을 테이블에 삽입하기
- 아래와 같이 테이블에 프로시저의 결과를 삽입할 수 있다.
|
INSERT INTO #resTest EXEC up_sales @sDate='20141101', @eDate='20141210' |
- 그러나 SELCT INTO문 처럼 새로운 테이블을 생성하는 것은 안된다.
|
EXEC INTO #temp up_sales @sDate='20141101', @eDate='20141210' |
- 또한 이렇게 생성된 결과를 FROM절에서 사용 불가하다. FROM절에 사용하려고 할 경우 테이블을 반환하는 사용자 정의 함수를 만들어 사용하여야 한다.
2. 저장프로시저 컴파일과 실행계획 재사용
- 일반 쿼리와 저장프로시저의 차이는 캐싱을 통한 재사용 여부이다. SQL서버에서 필요에 따라 자동으로 컴파일 되는 기능이 제공된다. 하지만 매번 재컴파일이 필요하다면 프로시저 생성시 RECOMPILE옵션으로 사용할 수 있다.
(1) 실행계획 자동 재컴파일 기능
- DB의 내용이 변경되어 이전환경과 다를 때, 같은 쿼리가 실행되면 재컴파일하고 사용한다. 다음과 같은 경우에 재컴파일을 시도한다
1) 쿼리에서 참조하는 테이블이나 뷰의 구조가 변경된 경우 (ALTER)
2) UPDATE STATISTICS같은 문에서 명시적 또는 자동으로 새 배포 통계가 생성된 경우
3) 실행계획에서 사용한 인덱스가 삭제된 경우
4) 명시적으로 sp_recompile이 호출된 경우
5) 쿼리에서 참조하는 테이블에 INSERT또는 DELETE문으로 키가 많이 변경된 경우
6) 트리거가 있거나, INSERT,DELETE에서 행의 수가 크게 변경된 경우
(2) 저장 프로시저를 강제로 재 컴파일해야 하는 이유
1) 대입하는 변수의 변화 폭이 심한 경우
2) 변수의 대입 폭이 일정하나, 데이터의 분포도가 비정형적인 경우
(3) 저장 프로시저 재 컴파일
- SQL Server에서 저장프로시저를 재 컴파일하는 방법은 다음과 같이 3가지가 있다.
1) 저장프로시저 수행할 때마다 항상 재 컴파일
- 프로시저 생성시 WITH RECOMPILE을 지정하면 실행될 때 마다 재컴파일 한다.
|
CREATE PROC ... WITH RECOMPILE |
2) 개체와 관련된 저장 프로시저나 트리거를 재 컴파일
- sp_recompile을 이용하여 재컴파일 할 수 있다.
|
sp_recompile [@objname = ] 'object' |
3) 수행할 때 재 컴파일
- 프로시저 호출할 떄, WITH RECOMPILE을 붙인다
|
EXEC <프로시저명> <파라미터목록> WITH RECOMPILE |
3. 캐시 정보 살펴보기
(1) 저장 프로시저와 실행계획
- SQL 7.0부터는 모든 SQL문에서 실행계획을 재 사용하기위해 저장한다. 저장프로시저에 대해 프리컴파일 방식을 사용하지 않는다. 캐시 사용방법에 관련된 정보는 sys.syscacheobjects에 저장된다. 해당 내용은 아래링크 참조
http://msdn.microsoft.com/ko-kr/library/ms187815.aspx
(2) 임의 질의에 자동 매개 변수화
|
SELECT cacheobjtype, objtype, refcounts, usecounts, setopts, sql FROM master..syscacheobjects |
- 위 쿼리를 실행하여 보면, 현재 캐시정보를 확인할 수 있다.
|
DBCC FREEPROCCACHE -- 현재 캐시를제거 SELECT * FROM sales WHERE ord_num='901a' |
- 위에서 캐시 정보를 조회한 후, cacheobjtype을 보면 같은 sql에 대해 Executable Plan과 Compiled Plan이 있다.실행가능계획(Executable)은 컴파일된(Compiled)계획을 참조하여 만들기에, 실행가능의 참조카운트는 1, 컴파일된의 참조카운트는 2. 위 구문을 사용할수록 실행가능의 usecount는 증가한다.
- 만약 SET을 이용하여 옵션 설정값을 변경한 후, 위 쿼리를 다시 실행하면(DBCC구문 제외) 새로운 Executable Plan과 Compiled Plan이 생성되었음을 알 수 있다.
(3) 임의 질의에 대한 강제 매개변수화
- 임의 질의에 대해 복잡하거나 복합적인 작업으로 수행되는 경우에는 자동 매개변수화가 이루어지지 않는다. 하지만 이와 같은 것을 자주 사용한다면, 저장 프로시저를 사용하는것이 좋다. 또는 sp_executesql을 이용하여 강제 매개변수화 하여 사용할 수 있다.
(4) 쿼리 성능 향상을 위한 추천사항
1) 실행 계획을 재사용 할 수 있는 방법으로 구현. 가능한 저장프로시저를 사용하고, 문자열 쿼리를 할 경우 sp_executesql프로시저를 사용한다.
2) 파라미터의 대역폭이 크거나 데이터의 분포가 불규칙한 경우 WITH RECOMPILE을 이용하여 저장프로시저로 만듦
3) 성능 모니터를 이용하여 compile/recompile되는 공통적인 룰을 관찰하고 적용한다.
4. 저장 프로시저를 만들 때 고려사항
- 여러개의 테이블과 관계된 작업일 경우 적당한 수준의 집계된 자료를 임시 테이블이나 테이블 변수에 넣는편이 좋다
- 여러가지 매개변수로 가변적인 조건을 구현할 때에는 쿼리구문을 문자열로 만들어 수행하자
- 문자열 매개변수는 가능한한 varchar를 사용하자
- 저장프로시저에서 사용하는 객체는 실행되기 전까지만 만들어지면 된다. 저장프로시저 생성시에는 파싱만 수행한다.
- 저장프로시저의 매개변수에 기본값을 명시하여 유동성 있게 구현하자
- 하나의 프로시저명에 정수로된 차수를 명시하여 그룹화 할 수 있다.
- 결과가 크거나 행단위 작업에는 커서 사용을 검토하자
5. 시스템저장프로시저와 확장저장프로시저
(1) 자주 사용하는 시스템저장 프로시저
|
프로시저명 |
설명 |
|
sp_help |
DB테이블, 뷰, 저장프로시저등의 개체와 데이터형식에 대한 종합 정보를 제공 |
|
sp_helpindex |
테이블 또는 뷰의 인덱스에 관한 정보를 제공한다. |
|
sp_helptext |
암호화되지 않은 저장프로시저, 사용자정의함수, 트리거,뷰,규칙의 텍스트, 기본값등의 정의문자열을 조회 |
|
sp_helpdb |
지정된 데이터베이스 또는 모든 DB에 대한 정보를 보고 |
|
sp_renamedb |
DB의 이름을 변경 |
|
sp_spaceused |
테이블이나 인덱스의 행수, 예약 및 현재 사용중인 디스크 공간등을 조회한다 |
|
sp_attach_db |
물리적으로 저장된 DB파일을 서버에 DB로 첨부 |
|
sp_attach_single_file_db |
데이터 파일이 하나인 DB를 현재 서버에 첨부 |
|
sp_configure |
DB옵션을 표시하거나 변경. ALTER DATABASE를 사용하는것이 좋음 |
| sp_who | 현재 서버의 사용자 및 프로세스에 대한 정보를 제공 |
| sp_lock |
잠금 관련 정보를 조회 |
| sp_dbcmptlevel |
지정된 이전 버전과 호환될 DB의 동작을 설정 |
| sp_depends |
테이블 또는 뷰에 족속된 테이블, 뷰, 프로시저등에 대한 정보를 표시 |
| sp_rename |
테이블, 열, 사용자 정의데이터 형식등에 대한 이름을 변경한다 |
| sp_addmessage | sysmessages테이블에 새로운 오류메시지 추가함 |
(2) 확장저장프로시저
|
프로시저명 |
설명 |
|
xp_cmdshell |
정한 문자열을 수행하고(명령프롬프트), 결과를 text로 받아온다 |
|
xp_sendmail |
SQL메일이 설정되어 있어야 함. 쿼리결과 집합을 지정된 수신자에게 보낼 수 있다. |
|
xp_msver |
설정된 컴퓨터의 운영체제와 환경등의 상세한 정보를 얻음 |
- 출처 : SQL Server advanced programming
댓글