MSSQL, SQL Server, sys.sysobjects, temp table, tempdb, 데이터베이스, 임시테이블

Temp table 객체 생성시 세션간 충돌하지 않는 이유

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

데이터베이스를 사용할 때 temp table(임시 테이블)을 많이 사용한다. 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 어떻게 충돌을 방지할 수 있을까? 다음 간단한 테스트를 통해서 임시테이블 생성과 충돌 방지에 대해서 알아본다.

 

아래 스크립트는 임시 테이블을 생성한다.

-- Session 1: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

테이블을 만든 후에 메타데이터에서 tempdb 데이터베이스 내부에 생성된 객체를 확인한다.

-- SHOW USER TABLE

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

 

 

생성된 임시 테이블은 부정적인 개체ID와 이름에 긴 밑줄과 함께 번호가 있는것으로 확인할 수 있다. SQL Server에서는 임시테이블에 번호를 부여하여 전체 인스턴스에 대한 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 충돌을 방지할 수 있다. 동일한 이름으로 두 번째 임시 테이블을 생성한다.

-- Session 2: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

 

메타데이터를 확인해 보면 다른 번호를 가지고 있는 객체가 생성된 것을 확인할 수 있다.

 

이렇게 생성된 임시테이블은 다른 세션에서 동일한 이름을 사용해도 충돌을 방지 할 수 있으며 세션이 종료될 때 자동으로 제거된다. 임시 테이블인 세션이 종료될 때 삭제되는 것을 제외하면 일반 테이블과 유사하다.

 

 

[참고자료]

http://blog.sqlauthority.com/2014/11/27/sql-server-inside-temp-table-object-creation/

 

2015-06-24 / 강성욱 / http://sqlmvp.kr

 

SQL Server, TempDB, Temp table, 임시테이블, mssql, 데이터베이스, sys.sysobjects

'SQL Server > SQL Server Tip' 카테고리의 다른 글

DBCC DROPCLEANBUFFERS가 작동하지 않을 때  (0) 2015.07.23
손상된 부트페이지 복구하기  (0) 2015.07.23
SQL Server 데이터베이스 메일 계정 수정  (0) 2015.07.23
XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기  (0) 2015.07.23
특정 사용자에 대한 트랜잭션 로그 찾기  (0) 2015.07.23
DB튜닝, MSSQL, SQL, SQL Server 2012 SP2, sql server service pack, 실행계획, 임시테이블, 쿼리튜닝, 테이블변수, 테이블변수 카디널리티

테이블 변수와 TF 2453

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

테이블변수에 데이터를 삽입하면 카디널리티는 항상 1 이다. 행이 적을 경우에는 큰 문제가 되지 않지만 행이 많을 경우에는 쿼리 계획을 효율적으로 생성하지 못하여 쿼리 성능이 저하될 수 있다.

dbcc traceoff(2453,-1)

go

dbcc freeproccache

go

set statistics profile off

go

use tempdb

go

if OBJECT_ID ('t2') is not null

drop table t2

go

create table t2 (c2 int)

go

create index ix_t2 on t2(c2)

go

--insert 100,000 rows into the perm table

set nocount on

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into t2 values (@i)

set @i = @i + 1

end

commit tran

go

--update stats

update statistics t2

 

go

 

set nocount on

declare @t1 table (c1 int)

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into @t1 values (@i)

set @i = @i + 1

end

commit tran

set statistics profile on

select * from @t1 inner join t2 on c1=c2

go

 

set statistics profile off

 

 

SQL Server 2012 SP2 에서는 테이블변수에 대한 카디널리티를 개선했다. 이 개선은 테이블변수에 많은 행이 있을 경우에도 도움이 된다. SQL Server 2012 SP2에서 TF2453을 활성화 하면 테이블 변수를 사용하더라도 정확한 카디널리티를 예측하여 효율적인 쿼리 계획을 생성 할 수 있다.

dbcc freeproccache

go

dbcc traceon(2453,-1)

 

 

set nocount on

declare @t1 table (c1 int)

begin tran

declare @i int

set @i = 0

while @i < 100000

begin

insert into @t1 values (@i)

set @i = @i + 1

end

commit tran

set statistics profile on

select * from @t1 inner join t2 on c1=c2

go

 

set statistics profile off

 

 

  • SQL Server 2012 SP2 다운로드 :

http://www.microsoft.com/ko-kr/download/confirmation.aspx?id=43340

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2014/08/11/if-you-have-queries-that-use-table-variables-sql-server-2012-sp2-can-help.aspx

 

 

 

2014-09-01 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server 2012 SP2, SQL Server Service pack, 테이블변수, 임시테이블, 테이블변수 카디널리티, 실행계획, 쿼리튜닝, DB튜닝, SQL, MSSQL, SQL

'SQL Server > SQL Server Tip' 카테고리의 다른 글

컬럼스토어 인덱스 동시성  (0) 2015.07.23
컬럼스토어 인덱스 ROW와 ROWGROUP 영향  (0) 2015.07.23
Sp_trace_create MaxfileSize 오류  (0) 2015.07.23
RANDBETWEEN 함수 만들기  (0) 2015.07.23
Optimize for hint 쿼리 최적화  (0) 2015.07.23

+ Recent posts

티스토리툴바