임시테이블과 테이블변수 SQLWorld Study Group - 최명환 -

임시 저장소 임시 저장소 - 임시 테이블, 테이블 변수, 뷰, 인라인 테이블값 사용자 정의 함수, 파생 테이블, 공통 테이블식(CTE) 임시 저장의 필요성 - 성능상의 이슈 - 중간 결과의 보존 필요 - 프로그램 구현상 용이성 - 로직 해독의 편이 임시 저장 사례 - 일정 기준에 따라 집계된 데이터를 통한 재처리 쿼리 - 페이징 처리용 쿼리 - 중간 결과를 활용한 쿼리 - CUBE/ROLLUP의 재사용 - 계층구조(BOM) 구현 - 오류나 특정 로직에 의한 예외 처리용

임시 테이블과 테이블 변수 임시 테이블 - 임시로 데이터를 저장하기 위한 테이블 Object - CREATE TABLE “#table_name”으로 생성 - Tempdb에 저장(?) - OBJECT_ID로 확인 임시 테이블의 종류 - 로컬 임시 테이블 : # 사용 세션에 독립적 세션 연결이 끊기면 삭제 - 전역 임시 테이블 : ## 사용 모든 세션에서 사용 생성 세션이 끊기고 사용중인 세션 없을 시 삭제 테이블 변수 - 임시 테이블을 저장하기 위한 테이블 Variable - “DECLARE @table_name TABLE” 사용 - 메모리에 저장(?) - 확인 불가(?)

Tempdb로의 물리적 저장 저장 여부 저장 확인 • 임시 테이블과 테이블 변수 모두 - 현재 DB Context와 상관없이 tempdb에 생성 - 메모리 여유 있을 시 캐시에, 없을 시 tempdb에 저장 저장 확인 • 임시 테이블 - tempdb.dbo.sysobject에서 확인 • 테이블 변수 - tempdb.information_schema.tables에서 확인

참조범위? 가시성? SET CONTEXT_INFO? 참조 범위와 가시성 참조 수준 • 임시 테이블 - 생성된 세션에서만 사용 가능 - 생성된 세션에서 호출되는 하위 수준의 배치도 사용 가능 - 상위 수준에서는 사용 불가 - 세션 종료 시 자동 삭제 • 테이블 변수 - 생성된 세션에서만 사용 가능 - 상위/하위 수준 배치에서 접근 불가 - 세션 종료 시 해제 참조범위? 가시성? SET CONTEXT_INFO?

트랜잭션 컨텍스트 트랜잭션과 잠금 • 임시 테이블 - 외부 트랜잭션의 일부로 동작 - 롤백 지원, 롤포워드 지원 않음 - 로그 기록 - 잠금은 최소한으로 유지 • 테이블 변수 - 외부 트랜잭션으로 동작하지 않음 - 문장 단위 롤백 기능 지원 - 로그 기록 외부 트랜잭션? 문장 단위 롤백?

카디널리티? 재컴파일 임계값(RT)? KEEP PLAN? 통계 통계 정보 관리 및 성능 • 임시 테이블 - 분포 통계 정보 및 카디널리티 정보 관리 - 실행 계획 최적화, 성능에 유리 - 임시 테이블 통계로 인한 재컴파일 유발 - 통계 관리 비용, 재컴파일 비용 비교 • 테이블 변수 - 통계 정보 유지 하지 않음 - 비효율적 실행 계획 생성 - 재컴파일 유발 가능성 적음 카디널리티? 재컴파일 임계값(RT)? KEEP PLAN? 2000과 2005의 recompile수준?

임시 테이블 이름 확인(resolution)? 사용 제한 제한사항 • 다른 세션의 호출 - 임시 테이블, 테이블 변수 모두 불가 • 하위 배치 사용 - 임시 테이블만 가능 - Resolution으로 혼란 발생 • 인덱스 생성 - 임시 테이블은 모두 가능 - 테이블 변수는 PK, UK외에 인덱스 생성 불가 • 스키마 변경 - 임시 테이블은 생성 후 alter로 스키마 변경 가능 - 테이블 변수는 declare 이후 변경 불가 • insert~ exec 사용 - 임시 테이블 가능 - 테이블 변수도! 가능 임시 테이블 이름 확인(resolution)?

Tempdb 고려 사항 Tempdb 최적화 • Tempdb 최적화 필요성 - 다양한 저장 내역으로 병목 현상 발생 - tempdb에 대한 경합 발생 • 전용 디스크 어레이 사용 - 전용 디스크 어레이 고려(spindle 높임) - RAID 1+0 최적 • tempdb 증가에 따른 자동 증가 부하 발생 - 재시작시 적절한 기본 크기 미리 설정 • 재시작시 tempdb는 재생성 - tempdb의 모든 개체는 휘발성 - 영구 전역 임시 테이블 필요시 sp_procoption에서 설정 • 다수 사용자, 동시 세션에서의 경합 현상 해결 - CPU개수만큼 tempdb 데이터 파일 생성 - CPU당 스레드 생성으로 경합 감소