Chapter 08 Knowing Temp DB (1)

Chapter 08 Knowing Temp DB

Subject: 임시테이블과 테이블 변수

Question: 임시 테이블 대신 테이블 변수를 써도 되나요? 한번은 테이블 변수를 사용하다가 메모리 오버플로우가 생긴 적이 있습니다.

1. 임시 테이블과 테이블 변수 (Temp Tables Vs. Table Variables)

테이블 변수는 로컬 임시 테이블과 유사하다. 테이블 변수는 변수를 선언하여 사용하며, Batch 범위이다. 

차이점 1) 통계

임시 테이블과 테이블 변수의 주된 차이점은 테이블 변수는 통계를 생성하지 않는다는 것이다. 주요한 2개의 결과를 갖는데, 하나는 쿼리 옵티마이저는 테이블 변수는 포함된 데이터를 무시하고 row 수로만 고정된 견적 값을 사용한다. 데이터가 증가하거나 삭제되어도 이 값은 변하지 않게된다.

쿼리 옵티마이저는 하나의 row가 반환되는 계획을 기반하는 반면에 1 million rows가 사실상 실행된다. 테이블 변수에서 row의 수는 옵티마이저에서 항상 한 row로 간주되어 계산된다. 신뢰할만한 통계가 없기 때문에, 더 나은 비용의 계획이 발생하던지 또는 나쁜 실행계획이 사용될 수 있다. 

차이점 2) Indexes

테이블 변수에서는 제약조건을 생서할 수 있지만, 인덱스를 생성할 수는 없다. 즉, PK 또는 유니크 제약조건으로 인덱스를 가질 수 있다는 의미이다.

제약 조건으로 인해 인덱스를 만들고, 통계를 갖을 수 있지만, 인덱스를 쿼리의 컴파일 시 사용할 수 없다. 컴파일 시간이 존재하는 것도 아니고 재컴파일도 발생하지 않기 때문이다. 

차이점 3) 스키마 수정 (Schema Modifications)

스키마 수정은 임시테이블에서만 가능하다. 비록 스키마 수정은이 가능하지만 사용하는 것은 지양해야한다. 테이블 사용시 재 컴파일 이슈가 있기 때문이다. 

주요 차이점 정리표

	Temporary Tables	Table Variables
Statistics	가능	불가능
Indexes	가능	제약조건에 한해서
Schema Modifications	가능	불가능
Available in child routines including sp_executesql	가능	불가능
Use with INSERT INTO... EXEC	가능	불가능
In Memory Structures	불가능	불가능

테이블 변수나 임시 테이블을 사용하는 것은 테스트를 통해서 결정해야하는 것이지만, 기본적으로 임시테이블 사용하는 것이 좋지 않는 결과로 갈 확률이 적다. 

적은 row를 다루기 위해 테이블 변수를 사용하는 경우 임시테이블 보다 빠르지만 수 백개, 수천개의 row가 테이블 변수내에 있다면 performance는 아주 나빠진다. 그렇기 때문에 둘 중 하나를 결정하기 전에 여러개 용량계획을 검토할 필요가 있다.  (so, try and allow for some capacity planning when you make your decision!)

2. 테이블 변수는 메모리 내에 생기지 않는다

테이블 변수는 메모리 내 생성되기 때문에 임시테이블보다 빠르게 수행된다는 잘못된 개념이 있다. 

-> 실제 몇몇 블로그에서 임시테이블과 테이블 변수 차이점 검색 과정해서 테이블 변수는 메모리에 올려서 사용하기 때문에 빠르다고 언급한 곳이 있었다.

DMV 중 sys.dm_db_session_alloc_page_count; 를 통해서 세션의 의한 tempdb 사용 확인 가능하다. DMV 비우기 위해 SQL Server 재시작 하면 DMV 조회 시 0이 반환된다.

1) 임시 테이블이 얼마나 공간을 차지하는지 확인할 수 있다. 하나의 임시테이블을 생성하고 하나의 row을 입력하고 조회하면 session_id, database_id, user_objects_alloc_page_count 를 확인할 수 있다. 즉, 새로 생성된 테이블이 tempdb 내에 하나의 페이지로 위치하고 있음을 알 수 있다.

2) 같은 방법으로 테이블 변수로 생성한 후, DMV를 조회하면 user_objects_alloc_page_count가 +1 됨을 알 수있다. 즉, 테이블 변수는 tempdb 내 또 다른 페이지를 생성하고, 테이블 변수는 메모리 내 생성되는 것이 아님을 알 수 있다.

** 테이블 변수와 임시 테이블 모두 캐시되는 것과 연관 있지만 사실은 서버의 메모리를 제한하지 하지 않거나 특별히 큰 테이블을 사용하지 않는 한 메모리 내에서 작업할 수 있다.