danpoong blog

자주 사용하는 쿼리를 모듈화 시켜서 필요할때 호출하여 사용하도록 제작 한 것

하나의 개체로서 데이터베이스 내부에 저장됨

create procedure 프로시저 이름

as

  쿼리

형식으로 생성하며

exec 프로시저 이름

형식으로 사용 함

수정은 alter procedure, 삭젠ㄴ drop procedure

실행 시 입력 매개변수 지정이 가능

@입력매개변수이름 데이터 형식 [ = 디폴트 값]

exec 프로시저 이름 [입력매개변수]

형식으로 사용 함

출력 매개변수 지정도 가능

@출력매개변수이름 데이터형식 OUTPUT(또는 OUT)

exec 프로시저 이름 @변수명 OUTPUT

형식으로 사용 함

프로시저 내부에서 sql 프로그래밍이 가능

exec sp_helptext 프로시저 이름

을 사용하면 프로시저 코드를 볼 수 있으며 프로시저 생성 시 with encryption 옵션을 주면 암호화가 되어 코드를 볼 수 없음

저장 프로시저를 사용하면 서버 성능을 향상시킬 수 있음

처음 프로시저를 실행하면 최적화, 컴파일 등의 과정을 거치고 그 결과가 메모리에 저장됨

다시 프로시저를 실행하면 메모리에 올라간 것을 사용하므로 최적화, 컴파일 과정이 생략 됨

쿼리를 저장 프로시저로 저장해 놓으면 쿼리 관리, 실행이 수월해짐

사용자에게 테이블 접근 권한을 주지 않고 프로시저 실행 권한만 주어 보안을 강화 할 수 있음

긴 쿼리를 저장 프로시저로 만들면 네트워크 전송량도 줄일 수 있어 네트워크 부하가 줄어듬

저장 프로시저의 종류

사용자 정의 프로시저

T-SQL 저장 프로시저

사용자가 직접 create proc 으로 생성한 프로시저

CLR 저장 프로시저

T-SQL 프로시저 보다 효율적이고 강력한 프로그래밍이 가능

.net framework 어셈블리 클래스에 공용의 정적 메소드로 구현되며, 사용자로부터 매개변수를 입력받아 결과를 반환하는 .net franework clr 메소드의 참조로 사용됨

시스템 저장 프로시저

시스템 관리를 위해 sql server에서 제공하는 프로시저

주로 sp_ 라는 접두어가 붙어있음

저장 프로시저의 작동

일반 T-SQL문을 실행하면 다음 순서에 따라 작동 됨

구문분석 : select, from 등 단어에 오류가 있는지 분석, 예약어나 오타를 체크

개체 이름 확인 : 테이블, 데이터베이스, 컬럼 등 이름이 정상적으로 있는지 확인

사용권한 확인 : 현 사용자가 접근 권한이 있는지 확인

최적화 : 쿼리의 가장 좋은 성능을 낼수있는 결로를 결정, 주로 인덱스 사용 여부를 결정

컴파일 및 실행계획 등록 : 최적화된 결과를 컴파일 하고 결과(실행계획)을 메모리에 등록

실행 : 컴파일된 결과를 실행

동일한 T-SQL문을 다시 실행하면 메모리에서 실행계획을 읽어와 바로 실행

동일한 쿼리의 조건은 대소문자 띄어쓰기 등이 완전 같아야 함 select와 Select 만 달라도 구문분석부터 시작 됨

저장 프로시저의 정의 순서는 다음과 같음

저장 프로시저 정의 (create proc)

구문 분석 : 구문의 오타나 문법 오류 등을 파악

지연된 이름 확인 : 프로시저를 정의 하는 시점에서 개체 (테이블 등)가 없어도 관계가 없음, 개체의 존재 여부는 프로시저 실행 시 확인하게 됨 (이미 있는 테이블의 컬럼 명이 다르면 안됨)

생성권한 확인 : 현 사용자가 저장프로시저를 생성할 권한이 있는지 확인

시스템 테이블에 등록 : 저장프로시저의 이름 및 코드가 시스템 테이블에 등록 됨 (sys.objects, sys.sql_modules 카탈로그 뷰로 확인 가능)

저장 프로시저를 정의한 후 최초 1회의 작동 방식은 다음과 같음

개체 이름 확인 → 사용권한 확인 → 최적화 → 컴파일 및 실행계획 등록 → 실행

프로시저 정의 시 진행한 구문 분석을 포함하면 일반 쿼리 실행 순서와 유사함

두번째 실행 시에는 메모리에 저장된 것을 그대로 사용

저장 프로시저의 재컴파일

첫 실행에서 최적화된 저장프로시저가 다시 실행될 때 다른 최적화 방식을 사용해야 할 때는 재컴파일 되어야 함

4가지 방법이 있음

-프로시저 실행 시 with recompile 옵션을 추가

-'sp_recompile 테이블 이름' 시스템 프로시저를 사용하면 해당 테이블과 관련된 프로시저를 다시 사용할 때 재컴파일 됨

-dbcc freeproccache 를 사용하면 메모리 프로시저 캐시 영역을 비우고 프로시저 사용시 재컴파일 함

-저장프로시저를 실행할 때마다 자동으로 재컴파일되도록 프로시저 작성

데이터에 빠르게 접근할 수 있도록 도와줌

데이터베이스 튜닝에 중요

잘못 사용하면 성능 저하

인덱스를 저장할 추가 공간 필요

데이터 변경 작업 (insert, update, delete)가 자주 일어나면 성능 저하

-clustered index

영어사전과 같은 형식

테이블당 1개만 생성 가능

생성 시 데이터 전체 정렬

행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬

루트 페이지와 중간 페이지, 그리고 리프 페이지는 인덱스로 구성되어 있으며 리프 페이지 자체가 데이터 페이지

-nonclustered index

인덱스가 별도로 있고 인덱스를 찾은 후 해당 부분으로 가야 실제 내용 확인 가능

여러개 생성 가능

데이터 페이지를 건드리지 않고 별도의 인덱스 페이지를 생성

리프 페이지에 인덱스로 구성한 열을 정렬하고 데이터 위치 포인터를 생성

데이터 위치 포인트는 페이지번호+#오프셋 으로 기록되어 이는 데이터 영역의 데이터의 위치를 가리킴

일정 수 이상의(전체 데이터의 약 1~3%) 스캔을 할 경우 테이블 스캔을 실시함

-clustered와 nonclustered index가 테이블에 같이 있을 경우

clustered의 인덱스 페이지는 동일하지만 nonclustered는 리프 페이지에 포인터 대신 clustered의 키 값을 가지고 있음

기존처럼 포인터로 찾아가지 않고 clustered 키값을 가지고 clustered index 페이지에서 검색을 함

인덱스는 컬럼단위로 생성

하나 또는 여러개의 컬럼에 생성 가능

제약조건 없이 테이블 생성 시에 인덱스를 만들 수 없으며 인덱스가 자동 생성되기 위한 열의 제약조건은 primary key와 unique 뿐

-primary key

1개만 생성 가능

다른 clustered index로 지정된 인덱스가 없으면 기본적으로 clustered index로 생성 됨

생성 시 nonclustered 옵션을 주면 nonclustered 인덱스로 생성 됨

-unique

여러개 생성 가능

기본적으로 nonclustered index

clustered index로 설정 가능

인덱스는 균형 트리와 유사한 방식으로 동작

sql 데이터는 페이지라는 단위로 저장됨

select에 유리한 방식으로 저장됨

insert, update, delete 등 작업이 일어나면 트리로 연결되어있는 노드(페이지)의 정렬, 페이지 공간 부족시 페이지 분할 이 일어나기때문에 느려질 수 있음

인덱스 삭제시에는 drop index [테이블].[인덱스] 형식을 사용

제약조건으로 생성된 인덱스는 drop index 로 삭제 불가

alter table [테이블] drop constraint [인덱스] 로 삭제 해야함

데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다

외래 키가 사용되는 열에는 인덱스를 생성해주는 것이 좋다

join에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다

사용하지 않는 인덱스는 제거

계산 열 (persisted)도 인덱스를 만들 수 있다

포괄 열이 있는 nonclustered 인덱스를 활용하면 쿼리 성능을 높일 수 있다

포괄 열을 nonclustered로 인덱스를 만들면 clustered 처럼 리프 노드에 데이터가 존재하게 됨

기존 데이터 형식에 별칭을 붙여 사용이 가능

ex) nchar(10) => mychartype

ssms의 개체 탐색기에서

데이터베이스 -> 해당 데이터베이스 -> 프로그래밍 기능 -> 유형 -> 사용자정의 데이터 형식

메뉴를 따라가 정의 가능함

또는

EXEC sp_addtype '사용자정의형식이름', '시스템데이터형식', 'NULL여부', '소유자';

와 같은 형식의 프로시저를 사용해 정의 가능