[펌/Tip]SQL Server 2012 (Denali) 데이터베이스 생성이 가능한 최소 용량은?
SQL Server 2008에서 잘 사용하던 쿼리가 SQL Server 2012 CTP3에서 수행을 해보니 1803 에러가 발생해서
왜 그런지 조사를 해보았다는 내용입니다.
설마 진짜 안되겠느냐라는 생각에 한번 해보았습니다. (2M 용량의 데이터베이스를 생성)
이유는 버전이 변경되어지면서 시스템 테이블이 추가되어지고, 변경되어지면서 데이터베이스 생성을 위한 기본용량이
더 커졌기 때문입니다.
상세한 내용에 대해서 궁금하신 분은 아래의 글 참고 부탁드립니다.
출처.
http://blogs.msdn.com/b/psssql/archive/2011/10/29/error-1803-and-model-size-change-in-sql-server-2012.aspx
SQL Server 2012 (Denali) 신규 기능 Columnstore Indexes 소개 (Part 2)
Columnstore 인덱스에 대해서 팀 내 소개를 드렸는데, 몇가지 질문을 받아서 관련하여 테스트해 본 내용을 정리해보았습니다.
아래에 진행된 테스트는 Part 1에서 만든 임시테이블을 기준으로 진행된 점 참고 부탁드립니다.
1. 일반 인덱스와 컬럼스토어 인덱스의 크기 비교
1-1. 인덱스 크기 비교 스크립트 (쿼리가 길어서 첨부파일로 올림)
1-2. 인덱스 크기 비교 결과
1-3. 의견
- 일반 인덱스의 경우 약 1500MB인데, 동일한 name 컬럼에 대해서 columnstore 인덱스로 구성한 경우 약 270M로 5.5배이상 축소되어짐.
- Columnstore 인덱스의 경우 압축등의 기술을 통하여 대량의 데이터 집계 및 분석에 최적화되어짐.
- 여기서 추가적인 의문이 생김. Columnstore 인덱스는 equal 검색에도 사용할 수 있을까?/적합할까?
2. equal 비교에서의 성능 비교
2-1. 일반 인덱스에서 equal 비교
-- 쿼리
select top 10 *
from ColumnstoreTable with (readuncommitted, index(idx_ColumnstoreTable_name_normal))
where name = 'ColumnstoreTable'
-- 실행계획
-- 쿼리 실행 비용
2-2. 컬럼스토어 인덱스에서 equal 비교
-- 쿼리
select top 10 *
from ColumnstoreTable with (readuncommitted, index(idx_ColumnstoreTable_name))
where name = 'ColumnstoreTable'
-- 실행계획
-- 쿼리 실행 비용
2-3. 의견
- 논리 읽기수를 비교하면 약 160배 이상 일반 인덱스가 빠른 것을 볼 수 있음. (일반인덱스 : 14 pages, 컬럼스토어인덱스 : 2255 pages)
- 컬럼스토어 인덱스의 경우 실행계획을 보면 Index scan으로 풀리는 것을 볼 수 있음.
- 컬럼스토어인덱스의 경우 대량 집계에 최적화된 구조로 index seek가 불가함.
3. 컬럼스토어 인덱스가 있는 테이블의 경우 DML이 안된다고 하는데 진짜 안될까?
3-1. Insert
-- 쿼리
insert into ColumnstoreTable
(name ,id ,xtype ,uid ,info ,status ,base_schema_ver ,replinfo ,parent_obj ,crdate ,ftcatid ,schema_ver ,stats_schema_ver ,type
,userstat ,sysstat ,indexdel ,refdate ,version ,deltrig ,instrig ,updtrig ,seltrig ,category ,cache )
select top 1
name ,id ,xtype ,uid ,info ,status ,base_schema_ver ,replinfo ,parent_obj ,crdate ,ftcatid ,schema_ver ,stats_schema_ver ,type
,userstat ,sysstat ,indexdel ,refdate ,version ,deltrig ,instrig ,updtrig ,seltrig ,category ,cache
from ColumnstoreTable
-- 결과
3-2. Delete
-- 쿼리
delete from ColumnstoreTable
-- 결과
3-3. Update
-- 쿼리
update ColumnstoreTable set name = 'hyunsoolim'
-- 결과
4. 결론/의견
- Columnstore index를 잘 활용하면 좋을 것 같습니다. 하지만 그만큼 주의를 요하는 부분도 있으니 실제 서비스에 적용하기 전에는 주의가 필요할 것 같습니다.
- 파티션테이블과 접목시키면 DML이 안되는 단점은 극복이 가능할 것 같습니다. (나중에 테스트해볼 예정임)
참고. Part 1 문서 하단 참고
SQL Server 2012 (Denali) 신규 기능 Columnstore Indexes 소개 (Part 1)
DW 시스템에서 대량 데이터 집계를 할 때 유용한 인덱스 유형이 추가되어 소개드립니다.
이번 글에서는 BOL 있는 Columnstore Indexes 특징/제약조건에 대해서 간단히 알아보고, 대량 데이터 집계 작업에서 얼마나 성능
차이가 있는지 테스트를 진행해보도록 하겠습니다.
추가적으로 Columnstore Indexes에 대해서 궁금하신 분이 계실 듯 하여 관련 자료를 하단에 첨부해 놓았으니 참고 부탁드립니다.
[특징]
1. Columnar data format
- 일반적으로 사용해왔던 rowstore format의 구조가 아니라 columnar data format을 구조를 가지는 인덱스입니다.
- Columnar data format 구조란 하나의 컬럼에 대해서 묶어서 저장한다는 점입니다.
(표현이 좀 모호한데, 아래의 그림 참고 부탁드립니다."Columnstore Indexes for Fast DW QP SQL Server 11" 문서에서 발취)
2. Faster query results (아래와 같은 이유로 빠르게 집계 및 연산이 되어짐)
- 필요한 컬럼만 읽는다. (디스크 I/O와 메모리 사용량을 최소화할 수 있음)
- 컬럼데이터에 대해서 압축이 된다. (인덱스 자체에 압축이 적용되어지기 때문에 페이지 압축을 사용불가함)
- 개선된 쿼리 처리 알고리즘을 사용하여 CPU 사용률이 낮다.
3. Clustered index key
- CL 인덱스가 있는 테이블의 경우 NC Columnstore Index 내에는 클러스터키 컬럼을 모두 가지고 있는다.
4. Partitioning
- 파티션 테이블에서도 사용 가능
5. Record size
- 인덱스 레코드 크기는 900 bytes를 초과할 수 없다.
[제약조건]
1. Columnstore index의 경우 CL로 만들 수 없다.
2. Unique index로 만들 수 없다.
3. View나 indexed view위에 만들 수 없다.
4. Primary key나 foreign key로써 사용할 수 없다.
5. Alert Index 구문으로 변경이 불가하다. Drop 후 re-create 로 처리해야 한다.
6. 인덱스 정렬을 위해서 asc, desc를 사용할 수 없다. (압축 알고리즘으로 인하여 정렬은 불가하다.)
7. 아래의 기능과 같이 사용 불가하다.
- Page and row compression
- Replication
- Change tracking
- Change data capture
- Filestream
[테스트]
1. 소개
- sys.sysobjects 테이블을 가지고 약 3500만건으로 뻥튀기한다.
- 해당 테이블에 일반 인덱스와 컬럼스토어 인덱스를 만든다.
- 각각의 인덱스를 사용하여 집계 쿼리를 수행하여 얼마나 시간이 소요되어지는지 확인한다.
2. 진행
2-1. 테스트 데이터 만들기
-- 테스트 데이터베이스 생성
create database testdb
use testdb
go
-- 임시 테이블 생성
-- drop table ColumnstoreTable
select
identity(int, 1, 1) as seqNo
, a.*
into ColumnstoreTable
from sys.sysobjects a
cross join sys.sysobjects b
cross join sys.sysobjects c
cross join (select top 500 * from sys.sysobjects) d
Result> 소요시간 : 3분 41초
(35153041개 행이 영향을 받음)
2-2. 인덱스 생성
-- 컬럼스토어 인덱스 생성
create columnstore index idx_ColumnstoreTable_name on ColumnstoreTable (name) ;
go
-- 일반 인덱스 생성
create index idx_ColumnstoreTable_name_normal on ColumnstoreTable (name) ;
go
2-3. name 컬럼 기준으로 몇건의 데이터가 있는지 집계 쿼리 수행
-- 컬럼스토어 인덱스 사용
select name, count(*)
from ColumnstoreTable with (readuncommitted)
group by name
-- 실행계획
-- 결과
-- 일반 인덱스 사용
select name, count(*)
from ColumnstoreTable with (readuncommitted, index(idx_ColumnstoreTable_name_normal))
group by name
-- 실행계획
-- 결과
3. 느낀점
- 집계 쿼리의 경우 일반인덱스와 비교자체가 불가할 정도로 빠릅니다. 위에 테스트 결과를 보더라도 거의 100배이상으로 빠른 것을 볼 수 있습니다.
일반 인덱스에 비하여 빠른 이유는 압축으로 인한 효과가 크지 않을까 생각해봅니다. 어차피 넌클러스터 인덱스도 해당 컬럼 데이터만 가지고 있기 때문에
해당 컬럼 데이터만 사용하기 때문이라는 이유로는 설명이 안됩니다. 중복데이터가 많은 경우 columnstore index의 효과가 더 높지 않을까합니다.
- CTP3 기준으로 "columnstore index가 있는 테이블의 데이터 변경이 불가하다"는 제약사항이 있어 운영환경에서 사용시 많은 제약이 따릅니다.
- 사이베이스에서 볼 수 있는 기능이였는데, 드디어 sql server에서도 볼 수 있게 되었네요.
- 개인적으로 오라클에서의 비트맵인덱스와 비슷하다고 생각을 하는데, 비트맵인덱스의 경우 온라인 상태에서 데이터 변경 및 추가가 가능하니까,
곧 columnstore index도 가능한 날이 오지 않을까 생각해봅니다.
(구조가 틀리기는 하지만 만들어지게 된 목적이나 컨셉은 비슷하다고 느껴집니다. 공감하기 힘들수도....)
[참고문서_columnstore Indexes]
[참고자료]
- http://msdn.microsoft.com/en-us/library/gg509105(v=SQL.110).aspx
- http://www.b-eye-network.com/blogs/mcknight/archives/2010/11/microsoft_unvei.php
- http://cs-www.cs.yale.edu/homes/dna/talks/Column_Store_Tutorial_VLDB09.pdf
- http://www.slideshare.net/abadid/columnstores-vs-rowstores-how-different-are-they-really
- http://www.benjaminnevarez.com/tag/column-store-indexes/
- http://sql-articles.com/articles/general/columnstore-indexsql-server-2011/
- http://www.infoq.com/news/2011/03/Columnstore-index
[펌/Tip]SQL Server 2012 (Denali) SSMS 클립보드 링 순환 기능
SQL Server 2008까지는 바로 전에 복사한 내용만 붙여넣기가 되었는데, SQL Server 2011부터는 클립보드에 있는 내용 중
선택이 가능하도록 되었습니다. 아래의 명령어를 누르면 이전에 복사했던 내용이 나오는 것을 볼 수 있습니다.
화면으로 캡쳐할 수 있는 내용이 아니라서 팝업창 내용과 단축키만 내용 공유드립니다.
[메뉴 화면]
[단축키]
Ctrl + Shift + V
출처.
- http://sqlblog.com/blogs/rob_farley/archive/2011/08/30/sometimes-it-s-the-little-things.aspx
[소개]SQL Server 2012 (Denali)에서 추가된 비교 함수(Logical Functions)
SQL Server 2012에서 신규로 추가된 비교 함수를 소개드리고, 간단히 성능 테스트해 본 결과 공유드립니다.
1. IIF 함수
1-1. 소개
- CASE 구문과 동일한 기능을 하며, true/false로 구분되어지는 구문을 CASE 구문에 비하여 단순한게 표현이 가능합니다.
- 오라클의 DECODE함수와 동일하다고 보시면 됩니다.
1-2. 문법
IIF ( boolean_expression, true_value, false_value )
1-3. 예제
SELECT IIF ( 45 > 30, 1, 0 ) AS Result;
-- 결과창
2. CHOOSE 함수
1-1. 소개
- Index 값 위치의 변수값을 돌려주는 구문 (제한된 범위 내에서 선택이 필요한 경우 유용함)
1-2. 문법
CHOOSE ( index, val_1, val_2 [, val_n ] )
1-3. 예제
SELECT CHOOSE (1, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
SELECT CHOOSE (2, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
SELECT CHOOSE (3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
-- 결과창
3. IIF 함수 성능 테스트
3-1. 테스트 목적
- CASE 구문을 사용하는 것이 IIF 함수를 사용하는 것보다 빠르지 않을까라는 의심을 하게 됨.
- 느리다고 생각한 이유는 내장함수더라도 함수니까 CASE 구문에 비해서 가독성이 높아지더라도 성능저하가 있을 것으로 추정해 봄.
3-3. 테스트 데이터 생성
-- tempdb로 이동
use tempdb
-- 테스트에 사용할 500만건 데이터를 생성하기
-- drop table iiftest
select top 5000000
identity(int, 1, 1) as seqNo
, a.name
, a.xtype
, a.crDate
, a.refDate
into iiftest
from sys.sysobjects a
cross join sys.sysobjects b
cross join sys.sysobjects c
cross join (select top 10 * from sys.sysobjects) d
-- 결과창
3-3. 테스트 쿼리 수행 (CASE 구문 2개와 IIF 함수 구문 1개 수행하여 수행시간 비교)
-- 쿼리 수행 시간 비교
set statistics time on
-- IIF 함수
select
sum(
iif(name='sysfiles1', iif((seqNo%2)=1, iif((seqNO%5)=1, 1, 0), 0), 0))
from iiftest with (readuncommitted)
-- CASE 구문_01
select sum(
case
when name = 'sysfiles1' and (seqNo%2)=1 and (seqNO%5)=1 then 1
else 0
end)
from iiftest with (readuncommitted)
-- CASE 구문_02
select sum(
case
when name = 'sysfiles1' then
case
when (seqNo%2)=1 then (case when (seqNO%5)=1 then 1 else 0 end)
else 0
end
else 0
end)
from iiftest with (readuncommitted)
-- 결과값
-- 소요시간 (거의 결과 시간이 동일함을 볼 수 있슴)
-- 실행계획 (동일한 실행계획이 나오는 것을 볼 수 있음)
3-4. 의견
- IIF 함수가 CASE 구문에 비해서 성능이 좋지 않을 것 같다는 생각은 보기좋게 빗나갔습니다.
- 실행계획이나 테스트 내용에서 볼 수 있듯이 CASE 구문과 동일하게 동작합니다.
- Depth 제한등도 CASE 구문과 동일한 것으로 보아, 가독성만 높인 것으로 보입니다.
- 개인적으로 2 depth 이상으로 들어가는 경우 IIF 구문보다는 CASE 구문을 권장하고 싶습니다. DECODE 구문을 사용해 본 경험상,
남용하게 되면 가독력이 CASE 구문에 비해서 떨어지고, 오류를 범할 확률도 높아진다고 생각하기 때문입니다 .(개인적으로!!!)
4. 참고자료
- IIF 함수 도움말
- CHOOSE 함수 도움말
- IIF 함수 블로그 글 01
- IIF 함수 블로그 글 02
[소개]SQL Server 2012 (Denali)에서 추가된 날짜 함수
1. EOMONTH 함수 (해당월의 마지막 날짜를 반환)
-- 날짜변수 선언/할당 (윤달로 테스트 진행)
declare @SampleDate datetime = '2012-02-14'
-- SQL Server 2008 이하 버전에서는 다음달을 구한 뒤 -1일을 해서 찾음
SELECT cast(convert(varchar(7), dateadd(month, 1, @SampleDate), 120) + '-01' as datetime) -1 AS LastDayOfMonth
-- SQL Server 2011 (Denali)
SELECT EOMONTH(@SampleDate) AS LastDayOfMonth
-- 결과
2. FORMAT 함수 (날짜/시간, 숫자값을 원하는 문자열 포맷으로 보여줌)
-- 날짜변수 선언/할당 (오늘로 할당)
declare @CurrentDay datetime = getdate()
-- SQL Server 2008 이하 버전에서 날짜,시간을 붙여서 보여주고 싶은 경우 replace 함수를 활용하여 구현
select replace(replace(replace(convert(varchar(16), @CurrentDay, 120), '-', ''), ':', ''), ' ', '') as CurrentDay
-- SQL Server 2011 (Denali)
select format(@CurrentDay, 'yyyyMMddHHmm', 'ko-kr') as CurrentDay
-- 결과
-- 의견
오라클에서는 날짜를 내가 원하는 포맷으로 변형하기가 쉬웠는데, mssql로 오면서 제한된 format 내에서 사용하려고 하니
약간 번거로운 면이 있었습니다. 이번에 format 함수가 나와서 한결 편하게 개발이 가능할 것 같네요.
1. SQL Server 2011 날짜함수 도움말
2. EndOfMonth Function Article
3. Format Function Article
테이블 컬럼 개수의 차이가 Insert/Update 쿼리의 CPU 부하에 영향을 줄까?
1. 개요
운영 중인 DB 서버 중 CPU 사용률이 높은 서버가 보여서 확인해보니 최근에 업데이트 쿼리가 추가되었더군요.
문제가 된 프로시져는 단순히 한 테이블에 업데이트하는 쿼리만 있었습니다. 단, 변경되는 컬럼수가 많다는 점이
눈의 띄였습니다. 쿼리의 실행수도 높기도 했지만 컬럼수가 많아서 CPU 사용량이 높아진 것은 아닌지 궁금해졌습니다.
그래서~ 아래와 같이 테스트를 진행해보았습니다.
2. 테스트 시나리오
> 10만개의 데이터를 100개의 세션에서 1000개씩 처리하는 동안 CPU 사용률과 처리시간 비교 (SQLQueryStress를 사용)
> 실제 사례와 유사하게 Insert/Update 대상 컬럼은 int 타입으로만 구성
> Insert/Update 대상 테이블의 컬럼수는 1, 10, 50, 100, 150, 200, 300개로 변화를 줌
> 각 테스트 종료 후 Checkpoint, DBCC DROPCLEANBUFFERS 수행을 통하여 다음 테스트에 영향을 안주도록 함
3. 테스트 내용
3-1. SQLQueryStress 실행 화면 (Insert/Update 에 대해서 컬럼별로 수행)
3-2. Update 테스트 결과
3-3. Insert 테스트 결과
4. 결론/의견
> [L님] Insert/Update 쿼리는 변경대상 컬럼수에 큰 차이 없이 CPU 사용을 많이 사용함. CPU 사용률을 줄이고 싶을 경우,
업데이트 등 DML 쿼리의 호출수르 줄여야 함. 테이블 분할로는 큰 개선을 보기 힘듦.
> [K님] 대상 컬럼수가 큰 영향을 안주는 이유는 레코드가 한 페이지내에 있기 때문에 컬럼수가 적거나 많은 것이 크게 영향을
안주는 것으로 보임.
5. 참고
> 고정형 컬럼인 int 만으로 테스트를 진행하였기 때문에 가변형 컬럼을 가지고 할 경우 다른 결과가 나올 수 있음
> 테스트 스크립트
[소개]Sqlquerystress Tool 소개
그냥 소개만 하면 재미 없을 듯 하여 간단히 사용을 어떻게 하면 되는지 정리해보았습니다. 해당 프로그램은 하단에 있는 출처 사이트에 방문하면 구할 수 있습니다. 사용법도 친절하게(?) 영어로 설명되어 있습니다.
그럼 이제 간단히 테스트 진행해보도록 하겠습니다.
1. 테스트 내용
- Tbl_param 테이블에 7000개의 데이터를 넣어놓는다.
- 100개의 세션에서 70번씩 tbl_Result 테이블에 tbl_param 테이블의 값을 조회하여 입력한다.
- 파라미터 테이블에서 개별적인 값이 한번씩만 호출되어지는지 확인한다. (테스트시 중복 발생이 안되는지 궁금했음)
2. Sqlquerystress를 설치한다.
3. 임시 테이블과 데이터를 생성한다.
-- 데이터베이스 생성
create database NX_WORK
-- 파라미터 테이블 생성
create table tbl_param (
seqNo int identity(1,1)
, comment varchar(100))
create unique clustered index idx_tbl_param_seqNo on tbl_param (seqNo)
-- 7000개의 데이터 입력
declare @i int
set @i = 1
while (@i <= 7000 )
begin
insert into tbl_param (comment) values ('11111')
set @i = @i + 1
end
-- 잘 만들어졌는지 확인
select count(*) from tbl_param
-- 결과 테이블 생성
create table tbl_result(comment varchar(100))
4. 실행창 띄우기
5. 준비 / 시작
5-1. Database 셋팅 (셋팅 후, Test connection 클릭해서 정상 연결되는지 확인)
5-2. 테스트할 쿼리를 Query 창에 입력 (파라미터값이 들어갈 부분은 변수 처리. 예> @comment)
5-3. 파라미터 셋팅
- 파라미터로 사용할 값을 가지고 오는 쿼리 입력
- Get Columns 버튼 클릭
- Column에서 선택을 하면 데이터 타입이 자동 매핑
5-4. 메인창으로 와서 Go 버튼을 클릭 (테스트 시작!!!)
6. 결과 확인
- 7000 개의 유니크한 값이 고르게 사용되었는지 확인
7. 예외 발생 건수는 우측 하단에 버튼을 눌러서 확인 가능
@@ 출처 : http://www.datamanipulation.net/sqlquerystress/
[펌/정리]SQL Server 2012 (Denali) 신기능
SQL Server 2012 (Denali) 에서 추가 및 변경된 내용에 대해서 자료가 있어 공유드립니다.
자세한 내용은 참조 URL로 가시면 좀 더 많은 정보를 얻으실 수 있습니다. 갈수록 많이 좋아지네요. ^^
1. 추가된 기능
| 카테고리 | 항목 | 설명 |
| Setup | PowerShell, no-reboot package prerequisites 32-bit 지원 (CTP1 기준) Itanium, 2000 업그레이드 지원 안함 |
|
| Availability | AlwaysOn (CTP1에서는 제한됨) Multi-subnet clustering across data centers |
|
| Manageability | Contained Databases FileTable (CTP1에서 제공안함) Startup option 설정이 쉬워짐 |
|
| Security | Cusotm server roles HASHBYTES() (SHA2_256, SHA2_512 지원) |
|
| Programmability | Developer Tools ("Juneau", CTP1 에서 제공안함) SSMS 개선 Metadata discovery EXECUTE ... WITH RESULT SETS OFFSET SEQUENCE THROW Full-Text Search New collatons : UTF-16 |
|
| Performance | Columnstore Index ("Apollo", CTP1에서 제공안함,) |
2. 배제된 기능
- SET ROWCOUNT for insert/update/delete
- SET FMTONLY
- DATABASEPROPERTY()
- osql.exe, sqlmaint.exe, SQL Mail, SQL-DMO
- 80 compatibility (CTP1 에서는 동작함)
- Old-style outer join syntax (*=, =*)
3. 기타
- 현재 SQL Server의 경우 2개의 메모리 관리자가 존재하고 있는데, Denali 부터는 1개의 관리자로 통합된다고 합니다.
> AWE 지원 안함
> DBCC memorystatus 결과값 변경
> DMVs 변경
> Max 메모리 최소값이 32 Bit 버전의 경우 32M, 64 bit 버전의 경우 128M..
> SQL Server 컴포넌트에서 사용하는 모든 메모리 영역은 max server memory 설정으로 관리됨
4. 출처
> Denali 신기능
- http://sqlblog.com/blogs/aaron_bertrand/archive/2011/05/13/sqlrally-slide-deck-samples.aspx
- http://sqlblog.com/blogs/aaron_bertrand/archive/2011/06/06/sqlcruise-what-s-new-in-denali.aspx
> 메모리관리자
- http://sqlblog.com/blogs/sqlos_team/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx
SQL Server 트랜잭션 관련 테스트 (@@trancount를 입력하면 "2"가 저장되는 이유)
팀 내에서 트랜잭션 관련 테스트를 하다고 호기심이 생겨서 서로 이야기를 나누었던 내용 정리해서 올립니다.
메일을 주고 받았던 느낌 그대로 정리해 보았습니다. 일부 내용은 제가 임의로 편집하였습니다. ^^
[호기심 발동 – A님 문의]
트랜잭션을 걸지 않은 상태에서 insert 문을 실행할 경우, 내부적으로는 자동 트랜잭션 처리가 되는데요,
이 상태라면 내부적으로는 @@trancount = 1 이 입력되어야 한다고 생각됩니다.
예를 들어 아래처럼 실행하면 결과가 2가 나옵니다. (1이 나와야 할 것 같은데.. 자동 트랙잭션 카운트가 있으니까..)
혹시 이유를 아시는 분이 계실까요? 결과만 보자면 insert 시에 내부적으로 중첩 트랜잭션을 사용하는 것 같습니다.
테스트 내용은 아래와 같습니다.
/* 임시 테이블 생성 */
create table tab01 (
title varchar(100)
, col01 int
, col02 int
)
/* @@trancount 입력 */
insert into tab01 (title, col01, col02) values ('@@Trancount 입력', @@TRANCOUNT, 0)
/* 조회 */
select *
from tab01 with (readuncommitted)
where title = '@@Trancount 입력'
[회신 1 – B님]
블로그를 찾아보니 내부적인 트랜잭션도 포함하여 계산하기 때문인 것으로 보입니다.
트랜잭션 로그 기록을 가지고 보면 좀 더 정확하게 볼 수 있네요. 다들 궁금해 하는 것이 비슷한 것 같습니다.
Ø https://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx
[회신 2 – C님] (@@trancount 외에 함수 활용 사례 공유)
Insert/update 문은 내부적으로 트랜잭션을 또 한번 사용하는 것 같습니다.
추가적으로 @@trancount는 명시적인 트랜잭션을 사용하지 않는 경우에 그리 적절하지 않다고 합니다.
중첩된 트랜잭션도 롤백 때는 한방이므로 트랜잭션을 중첩시켜서 얻는 이득도 없고 하니 그냥 @@trancount > 0 인 경우를 체크하거나,
말씀하신 것과 같은 오동작(?)을 막기 위해서는 xact_state() 함수를 사용하시는 것도 좋을 듯 합니다.
@@trancount와 xact_state() 비교한 테스트 내용은 아래와 같습니다.
/* 테이블 생성
create table tab02 (
title varchar(100)
, col01 int
, col02 int
)
/* INSERT: 암시적 트랜잭션 */
insert into tab02 values ('INSERT:암시적트랜잭션', @@TRANCOUNT, XACT_STATE())
/* INSERT: 명시적 트랜잭션 */
begin tran
insert into tab02 values ('INSERT:명시적트랜잭션', @@TRANCOUNT, XACT_STATE())
commit
/* UPDATE 테스트를 위한 임시 데이터 생성 */
insert into tab02 values ('UPDATE:암시적트랜잭션', 0, 0)
insert into tab02 values ('UPDATE:명시적트랜잭션', 0, 0)
/* UPDATE: 암시적 트랜잭션 */
update tab02
set col01 = @@TRANCOUNT, col02 = xact_state()
where title = 'UPDATE:암시적트랜잭션'
/* UPDATE: 명시적 트랜잭션 */
begin tran
update tab02
set col01 = @@TRANCOUNT, col02 = xact_state()
where title = 'UPDATE:명시적트랜잭션'
commit
/* 결과 조회 */
select
title
, col01 as [@@trancount 값]
, col02 as [xact_state() 값]
from tab02 with (readuncommitted)
[회신 3 – D님] (추가 호기심 발동)
내부적으로 트랜잭션이 발생한다면 아래의 경우는 어떻게 동작하는지 테스트 부탁드려요~ ^^
1. Delete 구분도 동일하게 동작할까요?
2. update 중에 replace 형태가 아닌 delete /insert 형태가 존재하는데 이런 경우도 동일할까요?
[회신 3 – E님]
1. Delete 구문도 동일하게 동작할까요?
/* 테이블 생성 */
create table tab03 (
title varchar(100)
, col01 int
, col02 int
)
/* @@trancount 입력 */
insert into tab03 (title, col01, col02) values ('DELETE 테스트1인경우', 1, 0)
insert into tab03 (title, col01, col02) values ('DELETE 테스트2인경우', 2, 0)
/* 삭제 */
delete from tab03
where col01 = @@TRANCOUNT
/* 어떤 값이 남았는지 조회 */
select *
from tab03 with (readuncommitted)
2. update 중에 replace 형태가 아닌 delete /insert 형태가 존재하는데 이런 경우도 동일할까요?
/* 테이블 생성 */
create table tab04 (
title varchar(100)
, col01 int
, col02 int
)
/* 클러스터 인덱스 추가 */
create clustered index idx_tab04 on tab04 (col01)
/* 업데이트 테스트를 위한 기초 데이터 입력 */
insert into tab04 values ('UPDATE:암시적트랜잭션', 0, 0)
insert into tab04 values ('UPDATE:명시적트랜잭션', 0, 0)
/* UPDATE: 암시적 트랜잭션 */
update tab04
set col01 = @@TRANCOUNT, col02 = xact_state()
where title = 'UPDATE:암시적트랜잭션'
/* UPDATE: 명시적 트랜잭션*/
begin tran
update tab04
set col01 = @@TRANCOUNT, col02 = xact_state()
where title = 'UPDATE:명시적트랜잭션'
commit
/* 어떤 값이 남았는지 조회 */
select *
from tab04 with (readuncommitted)
[정리 – B님]
여러가지 테스트를 해보았으니 이제 내용을 정리해보도록 하겠습니다.
결론적으로 SQL Server의 Locking은 two-phase locking입니다. 정확하게 이야기하면 Strong Strict two-phase locking(SS2PL)를 사용합니다.
따라서 암시적 트랜잭션 처리시 데이터 가공이 발생하는 시점에 @@trancount가 2로 보이는 것이 맞습니다.
2PL을 이용하는 경우 부산물로 데드락이 발생할 수 있는 구조입니다.
추가적으로 2PL과 SS2PL에 대해서 초간단 요약해보았습니다. 추가적으로 궁금하신 분은 참고 문헌을 봐주세요
1. Two-Phase Locking (2PL)
Ø Issuing lcok : 필요한 잠금을 획득
Ø Releasing lock : 사용한 잠금을 해제
2. Strong strict two-phase locking (SS2PL)
Ø 2PL의 요건을 만족
Ø Write(exclusive) lock은 commit, rollback 후 해제
[참고 문헌]
1. https://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx
