GIS 개발자

기본키 없이 인덱스가 존재할 때 인덱스와 관련된 필드가 업데이트되면 엄청난 성능 저하를 경험할 수 있다.

이는 하드디스크의 디스크 엄청난 부하를 가져오게 되나, DBMS에서 실행계획이나 프로파일러 등을 통해서 확인이 매우 어려우며, 쿼리문만을 분석해서는 도무지 답이 나오지 않는다. 

이는 MSSQL에서 인덱스를 생성할 때 기본키가 존재하는 지에 따라 생성방식의 차이를 보이기 때문이다. 

위에서는 기본키와 인덱스로 표기했으나, 정확한 명칭은 클러스터형 인덱스와 비클러스터형 인덱스라고 설명하는 것이 더욱 타당하다. 

비클러스터형을 생성할 때 클러스터형 인덱스가 존재하는 경우와 아닌 경우에 따라 내부적으로 저장하는 방식이 다르다고 볼 수 있다.  

비클러스터형 인덱스의 생성 방식에 대한 내용은 많은 블로그를 검색한 결과 아래 블로그에서 가장 상세하게 설명하고 있다. 

mozi.tistory.com/320

혹시 상위 블로그의 글이 삭제된다면, 아래 PDF에서 내용 확인이 가능하다. 

위 블로그에서 설명한 것과 같이 논클러스터형 인덱스가 클러스터형 인덱스 없이 생성될 경우에 데이터 페이지 번호와 슬롯 번호를 저장하게 된다. 데이터의 삽입/삭제/수정으로 인해 데이터 페이지가 변경되거나 갱신이 발생하게 된다면, 인덱스의 Leaf Node에 저장된 데이터 페이지 번호와 슬롯 번호의 광범위한 수정이 발생하게 된다. 또한, 여러수개의 레코드가 변경된다면 , 각 레코드가 업데이트 될 때마다 인덱스의 갱신이 발생하게 된다. 

ex: N개의 레코드가 업데이트 될 경우

(첫번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

(두번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

...

(N-1번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

(N번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신)

따라서 논클러스터형 인덱스를 생성하게 된다면, 반드시 클러스터형 인덱스를 사전에 가지고 있기를 추천한다.

SQL-Server의 테이블을 TXT 파일로 출력하는 방법에 대해 설명한다.

해당 기능을 사용하기 위해 bcp.exe(bulk copy program utility)가 필요하며, 이는 SSMS나 SQL-Server를 설치하면 자동으로 설치된다. 

먼저 본인의 pc에 bcp.exe가 존재하는 지 확인하기 위해서는 2가지 방법 중 한가지를 선택하여 확인한다.

• CMD 창에서 BCP입력

• 프로그램의 설치 경로에 접근하여 확인

설치가 되어 있다면, SSMS에서 실행하거나 cmd 창에서 실행을 통해 텍스트 파일을 추출할 수 있다

[SSMS에서 실행시]

-- xp_cmdshell 옵션을 활성화 (xp_cmdshell : SSMS에서 cmd창에 입력하듯이 사용하게 해주는 프로그램)
SP_CONFIGURE 'XP_CMDSHELL', 1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE
GO

-- BCP 실행
EXEC xp_cmdshell 'bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest.txt" -S -T -c'

[CMD 창에서 실행시 : 테이블을 txt 파일로 추출]

bcp [인스턴스명].dbo.[테이블명] out D:\BCPTest2.txt -T -c

[CMD 창에서 실행시 : 쿼리를 txt 파일로 추출]

// 기본 문법
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t [필드종결자] -r [행종결자]

// CSV 파일 형태로 텍스트 파일을 추출할 때
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t "," -r "\n"

// 탭으로 구분된 형태의 텍스트 파일 형태로 추출하고자 할때
bcp "SELECT * FROM [인스턴스명].dbo.[테이블명]" queryout "D:\BCPTest2.txt" -T -c -S [서버명] -U [아이디] -P [비밀번호] -t "\t" -r "\n"

예를들어 필드 구분과 행 구분을 원하는 형태대로 설정하고자 한다면 -t 옵션과 -r 옵션을 조절하여 추출한다. 

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. 시스템 요약 - 프로세서의 논리 프로세서 개수 확인

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

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

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

업종 코드 테이블

식당 테이블

먼저 업종코드 테이블은 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 연산을 수행할 경우 쿼리의 복잡도가 높아진다.
--> 실수를 발견하기가 매우 여럽다. 

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

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

가장 올바른 해결책은

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

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

SQL Server에서는 Convert() 함수를 통해 날짜 형식의 변경이 가능하다. 

구문

CONVERT([포맷(길이)],[날짜 값],[변환형식])

인수

반환 값 (Return)

[날짜 값]을  [변환형식]에 따라 [포맷(길이)] 타입으로 반환된 값

날짜와 시간의 포맷 변경 방법

DECLARE @DATE DATETIME2 = '1986-08-05 12:34'	-- 날짜 데이터의 선언
SELECT CONVERT(VARCHAR, @DATE)		--1986-08-05 12:34:00.0000000
SELECT CONVERT(VARCHAR, @DATE, 100)	--08  5 1986 12:34PM
SELECT CONVERT(VARCHAR, @DATE, 101)	--08/05/1986
SELECT CONVERT(VARCHAR, @DATE, 102)	--1986.08.05
SELECT CONVERT(VARCHAR, @DATE, 103)	--05/08/1986
SELECT CONVERT(VARCHAR, @DATE, 104)	--05.08.1986
SELECT CONVERT(VARCHAR, @DATE, 105)	--05-08-1986
SELECT CONVERT(VARCHAR, @DATE, 106)	--05 08 1986
SELECT CONVERT(VARCHAR, @DATE, 107)	--08 05, 1986
SELECT CONVERT(VARCHAR, @DATE, 108)	--12:34:00
SELECT CONVERT(VARCHAR, @DATE, 109)	--08  5 1986 12:34:00.0000000PM
SELECT CONVERT(VARCHAR, @DATE, 110)	--08-05-1986
SELECT CONVERT(VARCHAR, @DATE, 111)	--1986/08/05
SELECT CONVERT(VARCHAR, @DATE, 112)	--19860805
SELECT CONVERT(VARCHAR, @DATE, 113)	--05 08 1986 12:34:00.0000000
SELECT CONVERT(VARCHAR, @DATE, 114)	--12:34:00.0000000
SELECT CONVERT(VARCHAR, @DATE, 120)	--1986-08-05 12:34:00
SELECT CONVERT(VARCHAR, @DATE, 121)	--1986-08-05 12:34:00.0000000
SELECT CONVERT(VARCHAR, @DATE, 126)	--1986-08-05T12:34:00
SELECT CONVERT(VARCHAR, @DATE, 130)	--30 ?? ?????? 1406 12:34:00.000
SELECT CONVERT(VARCHAR, @DATE, 131)	--30/11/1406 12:34:00.0000000PM

SQL Server(MS-SQL) 현재 날짜와 시간 가져오기

SQL Server에서 사용 가능한 현재 시간을 가져오는 방법에 대해 설명한다. 

본 장에서 설명하고자 하는 함수는 아래와 같다. 

사용예시

SELECT GETDATE()
SELECT SYSDATETIME() 
SELECT SYSDATETIMEOFFSET() 
SELECT SYSUTCDATETIME() 
SELECT CURRENT_TIMESTAMP 
SELECT GETUTCDATE()