GIS 개발자

sql server에서 제공하는 시스템 데이터베이스 중 tempdb에 대한 간략한 설명 및 튜닝 방법에 대해 설명하고자 한다. 

SQL Server의 인스턴스에서 연결된 모든 사용자가 사용할 수 있는 전역 리소스이다.

tempdb에는 저장되는 대상은 아래와 같다. 

명시적으로 생성되는 임시 사용자 개체

• 전역, 로컬 임시테이블과 임시테이블 인덱스 (ex : #PRODUCT_TABLE)
• 임시 저장 프로시저
• 테이블 변수 (ex : @PRODUCT_TABLE)
• 테이블 변환 함수에서 반환되는 테이블
• 커서

데이터베이스 엔진에서 만든 내부 개체

• 커서, 정렬, 임시 대용량 객체(Large Object) 등의 작업 중간 데이터
• 해시 조인(hash join)이나 해시 집계 작업(hash aggregate operation)
• SORT_IN_TEMPDB가 지정된 인덱스 생성,
• GROUP BY, ORDER BY, UNION 쿼리 같은 작업의 중간 정렬 결과

tempdb는 위와 같은 다양한 작업을 수행하므로 운영 시스템의 규모에 비해 작은 크기로 설정하게 되면, SQL Server가 재시작할때마다 tempdb의 크기를 증가시키기 위해 물리적인 시간이 소비될 수 있다. 또한, tempdb가 운영 시스템이 설치된 디스크와 동일한 디스크에 설정하게 되면, 디스크의 I/O를 같이 사용하게 되므로 디스크 병목으로 인한 성능 저하가 발생할 수 있다. 또한 CPU가 여러개의 논리 프로세스를 가지고 있으나, 하나의 tempdb를 구성하게 될때도 디스크 병목으로 인한 성능 저하가 발생할 수 있다. 아래는 MSDN에서 제시한 tempdb의 설정하는 방법에 대한 내용이다. (SQL Server 2019 이후의 버전에서는 메모리 최적화 tempdb를 제공하므로 아래와 같은 설정 방법이 유의미할지는 추가적인 검토가 필요하다.)

tempdb의 저장 디스크를 운영 시스템과 분리하고 논리 프로세스의 개수만큼 tempdb를 분할한다.

www.sqler.com/bColumn/320898

--1. tempdb의논리파일이름확인
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO

--2. ALTER DATABASE 사용하여파일위치변경
ALTER DATABASE tempdb
MODIFY FILE(NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')

ALTER DATABASE tempdb
MODIFY FILE(NAME = templog, FILENAME = 'e:\mssql\templog.ldf')
GO

--3. CPU의 논리프로세스의 개수만큼 파일 분할 및 사이즈 변경 및 파일 사이즈, 증가옵션설정
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'd:\mssql\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'd:\mssql\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )
GO

--4.SQL Server를중지하고다시시작

--5.SQL Server 서비스가시작된것을확인후, 제대로이동이되었는지확인.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO

※ 논리프로세서의 개수 확인하는 방법

1. 실행 (windows + R)
2. msinfo32 실행
3. 시스템 요약 - 프로세서의 논리 프로세서 개수 확인

개발을 한지 어느새 5년이상의 시간이 지났지만, 아직까지도 개발의 영역이 끝도 없다는 생각을 한다. 

부끄럽지만 개발하면서 실수하거나 코드리뷰에서 수행했던 내용들을 하나둘 정리해보고자 한다. 

아래는 코드리뷰를 수행하며 안타깝게 놓쳤던 내용을 정리한 것이다.  

아래와 같이 1~9 사이의 숫자 중 2번째 BIT값이 1인 값을 찾을 때, 아래와 같이 코딩을 하였다.

int main()
{
    // 1~9사이의 숫자 중 2번째 Bit값이 1인 값을 찾는다고 가정한다. 
    for (unsigned int i = 0; i < 10; ++i)
    {
        if (i & 2 == 2)
        {
            std::cout << i << "는 2번째 Bit가 1입니다.\n ";
        }
    }
}

/*
기대값 : i = (2, 3, 6, 7) 일때 구문이 출력될 것이다. 
1  = 0001(2)
2  = 0010(2) --> *
3  = 0011(2) --> *
4  = 0100(2)
5  = 0101(2)
6  = 0110(2) --> *
7  = 0111(2) --> *
8  = 1000(2)
9  = 1001(2)
*/

그러나 실제 실행결과는 전혀 예상하지 못한 값이 나왔다.

왜 이런 결과가 발생했을까?

답은 연산자의 우선순위에 있었다.

비트 AND 연산(&) 보다 같음 연산(==)이 연산의 우선순위가 높다.  

따라서 위의 코드는 아래와 같이 해석될 수 있다. 

int main()
{
    // 1~9사이의 숫자 중 2번째 Bit값이 1인 값을 찾는다고 가정한다. 
    for (unsigned int i = 0; i < 10; ++i)
    {
        /* 2==2가 &보다 우선하기 때문에 2=2의 결과인 1(True)가 먼저 계산된다. 
        이후 i & 1 이 계산되므로, 첫번째 Bit가 존재할 때 반환값이 1이 반환된다. (ERROR)*/
        if (i & 2 == 2) 
        {
            std::cout << i << "는 2번째 Bit가 1입니다.\n ";
        }
    }
}

연산자의 우선순위에 대해 생각하지 못하면, 위와 같은 치명적인 실수가 발생할 수 있다.

아래는 Microsoft에서 제공한 C연산자의 우선순위표이다. 

이는 바이너리로 구성된 포맷이나 프로토콜에서 전달되는 데이터의 용량을 줄여 압축적으로 데이터를 전달하기 위해 사용되는 경우가 대부분이지만 현업에서 데이터베이스를 접하다보면 비트마스크를 활용하여 하나의 필드에 여러가지 의미를 담아놓은 필드를 발견하는 순간이 있다.

예를 들어 식당을 저장한 테이블이 존재하고 각각의 식당은 업종에 대한 코드값을 가지고 있다고 생각해보자.

• 사천마라탕 (중식)
• 파스타하우스(양식)
• 닌자초밥(일식)
• 그루브하우스 (카페, 양식)

업종 코드 테이블

식당 테이블

먼저 업종코드 테이블은 2의 배수 형태로 데이터를 저장하고 있고,

식당 테이블에서 그루브하우스를 확인하면, 업종코드테이블에 존재하지 않는 10이라는 값으로 데이터가 저장되어 있다. 

이 때, 12라는 의미는 비트마스킹의 개념을 도입한 필드로 볼 수 있다. 

양식에 해당되는 값인 2와 카페에 해당되는 8값을 더하여 10이라는 값으로 표현된 것이다. 

• 그루브하우스(10) = 양식(2) + 카페(8)

예를들어 하나의 식당에서 모든 업종에 포함되는 메뉴를 제공한다고 가정하면 코드값은 17이라는 값을 가지게 된다.

• 메가식당(17) = 중식(1) + 양식(2) + 일식(4) + 카페(8)

아래와 같이 어떤 코드값의 조합을 하더라도 기존의 코드값을 침해하지 않는 것을 확인할 수 있다. 

비트 마스킹 필드를 사용하는 것의 단점은 아래와 같은 것들이 존재한다. 

• 업종코드 테이블과 식당 테이블을 물리적인 관계를 생성할 수 없다.
• 업종을 기준으로 데이터를 조회할 때 비트 연산이나 IN 쿼리를 통한 범위 질의를 수행해야만 한다.
  비트 연산을 할 경우 인덱스 사용이 불가하여 성능의 저하가 발생하게 되고,
  IN 쿼리를 통한 질의를 할 경우 쿼리의 복잡도가 매우 상승할 수 있다. 

-- 양식(2)에 해당하는 레코드를 가져오게 될 때
SELECT *
FROM [식당 테이블]
WHERE [업종 코드] & 2 = 2
--> 좌변에 비트 연산을 해야 하므로 인덱스 사용이 불가하여 성능이 저하된다.


SELECT *
FROM [식당 테이블]
WHERE [업종코드] IN (2, 3, 6, 7, 10, 11, 15)
--> 업종코드의 비트 연산을 머리속으로 계산하여 IN 연산을 수행할 경우 쿼리의 복잡도가 높아진다.
--> 실수를 발견하기가 매우 여럽다. 

다만, 현업에서 비트 마스킹 필드를 사용하는 이유는 아래와 같은 장점이 존재하기 때문이다. 

• 기존의 스키마 변경 없이 코드값의 변경으로 문제를 해결 할 수 있다.
  위와 같은 문제를 해결하려면, 테이블이나 필드를 분해하는 대규모 수정 작업을 수행해야 하므로 많은 시간이 소요되는 작업이다.

가장 올바른 해결책은

• 코드의 개수가 작다면 각각의 코드를 필드로 분해하는 것이 타당하다.
• 코드의 개수가 많다면 [식당-업종 테이블]과 같이 [식당 테이블]과 [업종 테이블]을 연결하는 테이블로 분해하는 것이 타당하다.

하지만, 개발자에게 시간은 한정적이며, 문제 해결이 시급한 순간들이 존재한다. 이 때 비트마스크 필드를 활용하여 빠르게 문제 상황을 해결하고 이 후 시간적 여유를 가지고 수정 작업을 수행하는 것도 하나의 방법이 될 수 있을 것이라고 생각한다.