테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능

 

  • Version : 2008, 2008R2, 2012

 

테이블 반환 매개 변수는 사용자 정의 테이블 형식을 사용하여 선언 된다. 테이블 반환 매개 변수를 사용하면 임시 테이블이나 많은 매개 변수를 만들지 않고도 저장프로시저 또는 함수와 같은 T-SQL 문이나 루틴에 여러 행의 데이터를 보낼 수도 있다.

 

테이블 반환 매개변수는 OLE DB 및 ODBC의 매개변수 열과 유사하지만 보다 유연하고 집합 기반 작업에 사용할 수 있다.

 

T-SQL은 입력 데이터의 복사본을 만들지 않기 위해 참조로 루틴에 테이블 반환 매개 변수를 전달한다. 테이블 반환 매개 변수를 사용하여 T-SQL 루틴을 만들고 실행 다음 모든 관리 언어의 T-SQL 코드, 관리되는 클라이언트 및 기본 클라이언트에서 해당 루틴을 호출 할 수 있다.

 

테이블 반환 매개 변수의 범위는 다른 매개 변수와 똑같이 저장 프로시저, 함수, 동적 SQL 텍스트 이다. 변수의 범위 또한 DECLARE 문을 사용하여 만든 다른 지역 변수의 범위와 같다. 동적 T-SQL 문 내에서 테이블 반환 변수를 선언하고 이 변수를 저장 프로시저 및 함수에 테이블 반환 매개 변수로 전달 할 수 있다.

 

[테이블 반환 매개 변수의 이점]

  • 클라이언트의 데이터를 처음 채울 때 잠금 필요가 없음
  • 간단한 프로그래밍 모델을 제공
  • 단일 루틴에 복잡한 비즈니스 논리를 포함할 수 있음
  • 서버 왕복을 줄임
  • 카디널리티가 다른 테이블 구조를 가질 수 있음
  • 클라이언트가 정렬 순서 및 고유 키를 지정할 수 있음

 

 

[제한 사항]

  • SQL Server는 테이블 반환 매개 변수의 열에 대한 통계를 유지 관리하지 않음
  • T-SQL 루틴에 입력 READONLY 매개 변수로 전달되어야 함
  • 루틴 본문의 테이블 반환 매개 변수에 대해서는 UPDATE, DELETE, INSERT와 같은 DML 작업 수행 할 수 없음
  • SELECT INTO 또는 INSERT EXEC 문을 대상으로 사용할 수 없음

 

[SQL Server 2008 과 SQL Server 2012의 캐싱 임시테이블 비교]

테이블 반환 매개 변수(TVP)를 사용하면 SQL Server는 내부적으로 데이터를 저장하기 위해 임시 테이블을 사용한다.

  • SQL Server 2005를 시작하면 임시 테이블은 재사용을 위해 캐시 될 수 있다. 캐시는 임시 테이블이 빠른 속도로 만들어 지고 삭제 될 때 발생할 수 있는 시스템 테이블 페이지 래치의 경합을 줄일 수 있다.

 

  • SQL Server2008, 2008R2 이후부터는 TVP 저장 프로시저, 임시 테이블 TVP가 캐시 된다. 하지만 매개 변수화된 쿼리와 함께 사용한 TVP경우 캐시되지 않으며 시스템 테이블 래치 경합이 발생 할 수 있다.

 

  • SQL Server 2012 에서는 테이블의 테이블 매개 변수화가 있는 쿼리에 대해서도 캐시 된다.

 

아래 두 자료를 비교하여 SQL Server 2008과 SQL Server 2012의 캐시에 따른 임시 테이블의 사용을 살펴 보자. SQL Server 2008의 경우에는 임시테이블의 사용이 높은 것을 확인 할 수 있으며 SQL Server 2012에서는 처음 한번 생성되고 나머지는 0의 사용량을 나타낸다.

 

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/bb510489.aspx

http://blogs.msdn.com/b/psssql/archive/2013/02/26/temp-table-caching-improvement-for-table-valued-parameters-in-sql-server-2012.aspx

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38530
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20770
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1430
» 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1323
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1733
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1863
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4797
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1223
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1353
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2456
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2281
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4418
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1538
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1216
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1115
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2547
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1159
1799 Hot Add CPU jevida(강성욱) 2016.09.29 912
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1107
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1834
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1514
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1290





XE Login