SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

 

·         Version : SQL Server

 

SQL Server AlwaysOn Availability Group으로 데이터베이스가 구성된 경우 해당 데이터베이스는 복원할 없다. 먼저 데이터베이스를 복원하기 위해서는 가용성 그룹의 SQL Server 데이터베이스를 제거해야 한다. 이번 포스트에서는 SQL Server Job Agent 사용하여 AlwaysOn 설정이 자동으로 구성되록 하는 방법을 살펴 본다. 단계별 스크립트를 Job Agent 등록하여 사용할 있다.

 

1. Primary 서버의 AlwaysOn 가용성 그룹에서 데이터베이스 제거

-- runs on primary server

 

USE master

GO

 

ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>];

GO

 

2. Primary 서버의 데이터베이스 복원

단계는 Primary 서버의 데이터베이스를 복원한다. 사용 권한 부여등 복원후 특정 요구사항에 대한 추가 단계를 추가하여 실행 있다. 아래 스크립트는 복원하려는 파일이 네트워크 상의 다른 서버에 있는 경우이다.

-- runs on primary server

 

USE master

GO

 

RESTORE DATABASE [<Database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE

GO

 

3. Primary 서버에 복원된 데이터베이스를 전체 복구 모델로 변경

-- runs on primary server

 

USE master

GO

 

ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT

GO

 

4. Primary 서버에서 데이터베이스 전체 백업 로그 백업 진행

복원 작업을 쉽게 수행하기 위해 Primary 서버보다 Secondary 서버가 액세스할 있는 네트워크 공유에 백업파일을 만드는것이 좋다. 이렇게 하면 Secondary 백업을 복사하지 않아도 되며 복사단계를 생략하여 시간을 절약할 있다.

-- runs on primary server

 

BACKUP DATABASE [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION

GO

 

BACKUP LOG [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION 

GO

 

5. Primary 서버에서 가용성 그룹에 데이터베이스를 추가

-- runs on primary server

 

USE master

GO

 

ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>]; 

GO

 

6. Secondary 서버에서 데이터베이스 복원

Secondary에서 가요성 그룹에 추가할 데이터베이스를 전체 백업 로그 백업 파일을 이용해서 복원한다.

-- runs on secondary server

 

USE master

GO

 

RESTORE DATABASE [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY

GO

 

RESTORE LOG [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY 

GO

 

7. 데이터베이스 상태를 확인한 다음 가용성 그룹에 추가

-- runs on secondary server

 

-- Wait for the replica to start communicating

begin try

   declare @conn bit

   declare @count int

   declare @replica_id uniqueidentifier

   declare @group_id uniqueidentifier

   set @conn = 0

   set @count = 30 -- wait for 5 minutes

 

   if (serverproperty('IsHadrEnabled') = 1)

      and (isnull((select member_state

                   from master.sys.dm_hadr_cluster_members

                   where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

      and (isnull((select state

                   from master.sys.database_mirroring_endpoints), 1) = 0)

   begin

      select @group_id = ags.group_id

      from master.sys.availability_groups as ags

      where name = N'<Availability Group>'

 

      select @replica_id = replicas.replica_id

      from master.sys.availability_replicas as replicas

      where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

 

      while @conn <> 1 and @count > 0

      begin

         set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

         if @conn = 1

         begin

            -- exit loop when the replica is connected, or if the query cannot find the replica status

            break

         end

         waitfor delay '00:00:10'

         set @count = @count - 1

      end

   end

end try

 

begin catch

   -- If the wait loop fails, do not stop execution of the alter database statement

end catch

 

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];

GO

 

지금까지의 단계를 Primary 서버 Secondary 서버에 Job Agent 추가한다. 순서를 정리하면 아래 표와 같다.

 

Primary

Secondary

1

가용성 그룹에서 데이터베이스 제거

 

2

데이터베이스 복원

 

3

데이터베이스 복구 모델을 FULL 설정

 

4

가용성 그룹에 데이터베이스 추가

 

5

데이터베이스 전체 백업 로그 백업 생성

 

6

(sp_start_job 사용하여 보저 서버의 작업을 호출)

 

7

 

데이터베이스 백업 로그 복원

8

 

가용성 그룹에 추가

 

Primary 서버에서 Secondary 서버의 작업을 호출하지 않는 경우 Primary 작업이 완료 Secondary 서버의 작업이 시작되도록 시간을 설정해야 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/

 

 

2018-11-07 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, AlwaysOn, SQL Server Availability Group, 가용성 그룹

SQL Server MySQL PostgreSQL 비교

 

·         Version : SQL Server MySQL PostgreSQL

 

RDBMS 많이 사용하는 SQL Server MySQL 그리고 PostgreSQL 특징 차이점에 대해서 살펴본다. 데이터베이스 기능이 너무나 많기 때문에 모두 다루지는 못하며 대표적인 내용 몇가지만 다루도록 한다.

·         MySQL PostgreSQL, SQL Server 데이터베이스 모두 ACID 완벽하게 지원하며 많은 양의 데이터와 높은 수준의 쿼리 동시성을 처리할 있다.

·         PostgreSQL 기능이 풍부하고 확장성이 뛰어나다

·         MySQL 많은 곳에서 사용하고 있으며 애플리케이션과 전자상거래 프로젝트에 적합하며, PostgreSQL 비해 블로그, 지원, 문서가 훨씬 많다

·         PostgreSQL 경우 연결마다 자체 메모리가 있기 때문에 연결수가 많은 환경의 경우 많은 메모리가 필요할수 있다. 그러나 PgBouncers 외부 연결 풀을 사용하여 이러한 문제를 해결할 있는 솔루션이 있다.

·         PostgreSQL 수동 파티션 관리는 너무 많은 오버헤드와 파티션에서 다른 파티션으로 행을 이동시키는 업데이트가 필요하다.

·         MySQL에는 쿼리당 1개의 CPU 사용하는 중첩루프 조인 알고리즘만 있어 MySQL 데이터웨어우스 시스템에는 적합하지 않다.

·         Check 제약 기능이 중요한 서비스라면 MySQL 적합하지 않다.

 

[MySQL, PostgreSQL, SQL Server일반 정보]

 

MySQL

PostgreSQL

SQL Server

Maturity

1995 릴리즈

1989 릴리즈

1989 MSMS OS/2 SQL Server릴리즈(Sybase 함께)

1995 SQL Server 6.0릴리즈

Language

C (일부 C++)

C

C++

Cost

오픈소스 / 오라클 소유의 유료버전

완전 무료 / 오픈소스

SQL Server Express 무료버전과 외의 유료버전

 

[MySQL, PostgreSQL, SQL Server 데이터 변경]

 

MySQL

PostgreSQL

SQL Server

Row Update

업데이트가 수행되고 변경된 데이터가 롤백 세그먼트로 복사. Vacuum 인덱스 압축이 매우 효율적. MySQL 읽기에는 속도가 느리지만 쓰기는 원자적이며 보조 인덱스의 열이 변경되어도 모든 인덱스를 변경할 필요가 없음

업데이트는 인서트 + 삭제 표시로 구현된다. 모든 색인에는 행의 실제 ID 대한 링크가 있다. 열이 업데이트되면 새로운 물리적ID 있는 행이 만들어지고 모든 행이 새로운 행의 실제ID 대한 포인터를 얻기 위해 변경된 열을 참조하지 않는 경우에도 모든 인덱스가  업데이트 되어야 하기 때문에 업데이트 오버헤드가 발생

Row-Store 데이터베이스 엔진 :

 

인메모리 데이터베이스 엔진 : 업데이트는 인서트 + 삭제 표시로 구현. 가비지 컬렉터는 블럭킹 병렬로 작업

 

Columnstore 데이터베이스 엔진 : in-place 업데이트

Vacuum / Defragmentation

Vacuum 인덱스 압축은 매우 효율적

Vacuum 전체 테이블 스캔을 수행하여 삭제 행을 찾는다. 프로세스/사용자의 작업에 오버헤드를 있다.

메모리 가비지 컬렉턴는 최대 15% 오버헤드가 발생할 있다.

 

[MySQL, PostgreSQL, SQL Server 데이터 쿼리]

 

MySQL

PostgreSQL

SQL Server

쿼리 요청에 대한 Buffer Pool / Cache

MySQL 캐시는사용자 쿼리를 버퍼풀이라고 한다. 캐시는 필요에 따라 크기로 설정할 있으므로 서버의 다른 프로세스에 충분한 메모리만 남겨둔다. 버퍼 풀을 여러 부분으로 분할하여 메모리 구조에 대한 경합을 최소화하고 테이블을 버퍼 풀에 고정할 있다. 테이블 스캔 또는 mysqldump 이전 데이터를 제거한다.

PostgreSQL 데이터 페이지를 위한 공유 메모리를 유지한다. 프로세스 기반 시스템이기 때문에 연결은 고유한 고유  OS 프로세스를 가지며 자체 메모리를 가지고 있다. 프로세스는 실행이 끝난 메모리를 해제 한다. 따라서 많은 연결을 확장하는데 문제가 발생할 있다.

SQL Server 메모리는 버퍼풀이라고 하며 크기는 필요에 따라 크게 설정할   있으며 여러 버퍼 풀을 설정하는 옵션은 없다.

제약 조건 지원

기본키, 외래키, not null 제약 조건, 고유 제약조건, 기본 제약조건을 지원. CHECK 제약 조건을 지원하지 않는다.

기본 , 외래키, not null 제약 조건, 체크 제약 조건, 유니크 제약 조건, 기본 제약 조건을 지원한다.

기본 , 외래키, not null 제약 조건, check 제약 조건, 고유 제약 조건, 기본 제약 조건을 지원한다.

임시 테이블

CTE 지원하고 전역 임시 테이블 테이블 변수를 지원하지 않음.

동일한 쿼리에서 TEMPORARY 테이블을 번이상 참조할 없음. ( : select * from tbl_temp t1 join tbl_temp as t2)

CTE, 전역 로컬 임시 테이블 테이블 변수를 지원.

동일한 이름을 가진 개의 테이블을 만들면 다른 하나는 일반 테이블이다.

Creatae temp table x (..)

Create table x (…)

Select * from X 항상 임시테이블에서 데이터를 가져옴

CTE, 전역 로컬 임시테이블 테이블 변수 지원

Windows /Analytical 함수

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE PERCENTILE_CONT, PERCENTILE_DISC

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE

병렬 쿼리 실행

MySQL 일반적으로 쿼리당 1개의 CPU 사용

쿼리 계획은 여러 CPU 활용할 있음

쿼리 계획은 여러 CPU 활용할 있음

인덱스

인덱스 구성 테이블을 지원 클러스터 인덱스.

지속된 인덱스/구체화된 뷰를 지원하지 않음

인덱스 구성 테이블 미지원.

지속된 인덱스 / 구체화된 지원

인덱스 구성 테이블을 지원 클러스터 인덱스

단일 쿼리에서 다중 인덱스 사용

단일 쿼리에 다중 인덱스 사용 가능

단일 쿼리에 다중 인덱스 사용 가능. Xy 별도 인덱스가 있는 경우 WHERE x=5 and y=6 같은 쿼리를 구현할 있는 방법 하나는 적절한 쿼리 절과 함께 인덱스를 사용한 다음 인덱스 결과를 AND 결합하여 결과 행을 식별하는 것이다.

단일 쿼리에 다중 인덱스 사용 불가능

다중 컬럼 인덱스

다중 인덱스에는 최대 16개의 열을 포함할 있음

다중 인덱스에는 최대 32개의 열을 포함할 있음

다중 인덱스에는 최대 16개의 열을 포함할 있음

부분 인덱스

부분 인덱스를 지원하지 않음

부분 인덱스 지원

부분 인덱스 지원

JOIN 알고리즘

MySQL 중첩 루프 알고리즘 또는 변형을 사용하여 테이블간의 조인을 실행

중첩 루프 조인, 해시 조인 병합 조인 알고리즘 지원

중첩 루푸 조인, 해시 조인 병합 조인 알고리즘 지원

쿼리 실행 계획 재사용

준비된 명령문 저장된 프로그램에 대한 캐시를 세션별로 유지. 세션에 대해 캐시된 명령문은 다른 세션에서 액세스할 없음

준비된 문이 열려있는 동안에만 쿼리 계획을 캐시함. 쿼리 계획은 준비된 문이 클로즈때 해제됨.

쿼리가 실행계획을 다시 사용할 있도록 공유 실행계획 캐시가 있음

통계

지속성 지속성 통계 유지 (서버 재시작시 지워짐)

 

 

메모리 최적화 테이블

MySQL 테이블을 메모리에 저장할 있다. 메모리에 작성된 테이블은 트랜잭션을 지원하지 않으므로 데이터가 손상될수 있다. 이러한 테이블은 임시 영역 또는 읽기 전용 캐시로 사용해야한다.

메모리 엔진을 제공하지 않음

메모리 OLTP SQL Server 데이터베이스 엔진에 통합되어 있음

Columnstore 또는 저장소

MariaDB 최근에 여러 서버가 있는 환경에서 대규모 병렬 데이터베이스로 설계된 MySQL 저장소 엔진을 추시. InnoDB 스토리지 엔진 대신 사용 가능

저장소, 컬럼형 스토리지 엔진을 제공하지 않음

SQL Server 테이블을 쿼리하기 위해 저장소 인덱스를 제공

 

[MySQL, PostgreSQL, SQL Server JSON 데이터 유형]

 

MySQL

PostgreSQL

SQL Server

JSON 데이터 유형

MySQL JSON 데이터 형식을 지원하며 전체 문서를 바꾸는 대신 JSON 통한 부분 업데이트를 지원. 그러나 많은 제한이 있다. JSON 대한 인덱스 생성은 지원하지 않음

PostgreSQL JSON 데이터 유형을 지원하며 부분 업데이트를 지원한다.

SQL Server JSON 데이터 형식을 지원하며 부분 업데이트를 지원

고급 데이터 형식

지형 공간 데이터 유형을 지원한다. 사용자 정의 유형이 없다.

지형 공간 다차원 배열, 사용자 정의 형식 등과 같은 많은 고급 데이터 형식을 지원

지형 공간 데이터 유형, 계층 데이터 지원

 

[MySQL, PostgreSQL, SQL Server 샤딩/파티셔닝/복제]

 

MySQL

PostgreSQL

SQL Server

파이셔닝 지원

HASH 파이셔닝(모든 컬럼에서 HASH 함수를 사용하여 테이블을 N개의 파티션으로 분할), 여러 컬럼을 기반으로 하는 RANGE 또는 LIST 파티셔닝, HASH 유사한 KEY 파티셔닝(자동생성된 숫자 기반) 지원

RANGE LIST 파티셔닝을 지원하지만 파티션 인덱스는 수동으로 생성해야하며 테이블 상속을 통한 구식 파티셔닝이 필요(부모 테이블을 쿼리할때 모든 하위 테이블도 쿼리가 될때 하위 테이블은 파티셔닝 컬럼에 제약이 있음)

하위 테이블에는 하위 테이블에 부모 테이블과 인덱스를 별도로 적용해야하는 열이 많을 있음

RANGE 파이셔닝을 지원

샤딩 지원

공유를 구현하지 못함(MySQL Cluster 많은 제한 사항 때문에 거의 배포되지 않음)

공유를 구현하는 Postgres 포크는 수십가지가 있지만 아직 커뮤니티 출시에 추가된 것은 없음

표준 공유 구현이 없음

복제

명령문 또는 변경된 행을 기반으로 하는 마스터-슬레이브 복제.

그룹 복제는 마스터 서버에서 자동으로 복제

변경된 로그 전달을 기반으로하는 마스터-슬레이브 복제

데이터베이스 수준 : 가용성 그룹의 마스터-여러 슬레이브

로그전달

On Data level : 마스터-슬레이브 /양방향 마스터-슬레이브 / 마스터-마스터(병합) 복제

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/

 

 

2018-10-30 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, MySQL, PostgreSQL


SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout

 

·         Version : SQL Server

 

SQL Server 비용 기반(cost-based) 쿼리 최적화 프로그램을 사용한다. 따라서 여러 쿼리 계획을 작성하고 검사한 비용이 가장 낮은 쿼리 계획을 선택한다. SQL Server 쿼리 최적화 프로그램(QO) 목적중 하나는 쿼리 실행과 비교하여 쿼리 최적화에 합리적인 시간 소비하는 것이다. 따라서 QO에는 최적화 프로세스를 중지하기 전에 고려해야 태스크 임계값이 내장되어 있다. QO 모든 계획은 아니지만 가능한 대부분의 계획을 고려하기 전에 임계값에 도달하면 Optimizer Timeout 한계에 도달한다. 이벤트 로그에는 “Reason For Early Termination of Statement Optimization.” 기록된다. 여기서 이해야하는 중요한 부분이 임계 값은 실제 시간이 아니라 고려된 가능성의 수를 기반으로 한다는 것이다. 현재 SQL QO 버전에서는 Timeout 도달하기 전에 50만개가 넘는 가능성이 고려된다.

 

최적화 프로그램 Timeout Microsoft SQL Server에서 디자인 하였으며 많은 경우 쿼리 성능에 영향을 주는 요소가 아니다. 그러나 경우에 따라 SQL 쿼리 계획 선택에서 최적화 프로그램 Timeout 적용될 있으므로 성능에 영향을 있다. 이러한 문제가 발생하면 SQL Server 최적화 프로그램 Timeout 메커니즘과 복잡한 쿼리가 영향을 받을 있음을 이해하면 성능 문제를 보다 효과적으로 해결하고 개선하는데 도움이 있다.

증상은 무엇인가?

·         여러 개의 조인된 테이블을 포함하는 복잡한 쿼리가 있다.( : 8 이상의 테이블이 조인됨)

·         쿼리를 다른 SQL Server 버전이나 다른 시스템과 비교하였을 느리게 실행되거나 느리게 실행될 있다. (성능 베이스라인이 필수적으로 있어야 한다.)

·         XML 쿼리 계획에서 StatementOptmEarlyAbortReason=”TimeOut” 정보를 확인한다. 또는 Microsoft SQL Server Management Studio에서 가장 왼쪽 계획 연산자의 속성을 확인해보면 “Reason For Early Termination of Statement Optimization” 값이 “TimeOut”인지 확인한다.

 

아래 결과는 옵티마이저 Timeout 보여주는 쿼리 계획의 XML 출력이다.

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

 <BatchSequence>

  <Batch>

   <Statements>

    <StmtSimple StatementCompId="6" StatementEstRows="419.335" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>

    ...

   <Statements>

  <Batch>

<BatchSequence>

 

아래 그림은 TimeOut값을 표시하는 실행계획의 그래픽 표현 이다.

 

어떻게 작동 할까?

옵티마이저 임계값에 도달하거나 초과하게 하는 조건을 판별하는 간단한 공식은 없다. 그러나 아래 요소는 QO 최상의 계획을 결정하는 가지 요소이다.

·         테이블을 조인할 순서 : (테이블 수가 많을 수록 가능성이 커짐)

o   Table1 Table2, Table3 조인

o   Table1 Table3, Table2 조인

·          (Heap) 또는 이진트리(HoBT) 테이블에서 행을 검색하는데 사용하는 액세스 구조 :

o   Nonclustered Index 1

o   Nonclustered Index 2

o   Clustered Index

·         액세스 방법 :

o   Index seek

o   Index scan

o   Table scan

·         물리적 조인 연산자?

o   Nested Loop

o   Hash Match

o   Merge Join (NL, HM, MJ)

·         병렬 계획 또는 직렬 계획을 사용?

 

이해를 돕기위해 한가지 예를 들어 본다. 3개의 테이블 (T1, T2, T3)간의 조인을 예로 들며 테이블에는 클러스터된 인덱스만 있다. 여기에는 개의 조인이 포함되며 개의 실제 조인 가능성 (NL, HM, MJ) 있으므로 조인을6(2*3) 방식으로 수행할 있다. 조인 순서도 고려해야한다.

·         T1 T2 조인되고 T3 조인됨

·         T1 T3 조인되고 T2 조인됨

·         T2 T3 조인되고 T1 조인됨

이제 6 *3 조인 명령을 곱하면 선택할 있는 최소 18가지 가능한 계획이 있다. HoBT Seek 또는 Scan 같은 병렬성 기타 요인의 가능성을 포하 시키면 가능한 계획이 훨씬 늘어난다. 만약 쿼리에 10개의 테이블이 포함될 수백만개의 가능한 순열이 있음을 있다. 따라서 많은 조인이 있는 쿼리가 조인이 적은 쿼리보다 많은 시간이 옵티마이저 Timeout 임계값에 도달하는 것을 있다.

참고 : 쿼리 조건자(WHERE 절의 필터) 제약 조건의 존재로 인해 고려되는 액세스 방법 수가 줄어들 가능성은 있다.

 

최적화 프로그램 Timeout 임계 값에 도달한 결과 SQL Server 최적화 가능성을 모두 고려하지 않았으므로 실행 시간이 단축 있는 계획을 놓쳤을 있다. QO 임계 값에서 멈추고 시점에서 가장 비용이 적게 드는 쿼리 계획을 고려한다.

 

간단한 쿼리로 Optimizer Timeout 재현할 있을까?

QO 단순하지 않다. 가능한 많은 시나리오가 있으며 복잡성이 너무 높아서 모든 가능성을 파악하기 어렵다. Query Optimizer 특정 단계에서 발견된 계획의 비용을 기반으로 제한 시간 임계값을 동적으로 조정/설정할 있다. 예를 들어 상대적으로 저렴한것으로 보이는 계획이 발견되면 나은 계획을 찾기 위한 작업이 제한 있다. 따라서 과소 평과된 카디널리티 추정은 옵티마이저 timeout 일찍 도달하게 하는 하나의 원인일 있다. 경우 조사의 초점은 카디널리티 추정이다. 이는 복잡한 쿼리 실행에 대해 이전에 논의 시나리오보다 드문 경우 이지만 가능하다.

 

 무엇을 해야하는가?

아무것도 하지 않아도 된다. 많은 경우에 있어서 얻는 계획은 상당히 합리적이며 실행중인 쿼리가 수행된다. 그러나 조정하고 최적화 필요가 있는 경우 다음 옵션을 고려해야한다.

·         SQL Server 다른 빌드에서 실행하거나 다른 CE 구성 또는 다른 시스템을 사용하는것과 비교할 조사중인 쿼리가 느려지는지 확인해야 한다.

·         복잡성을 판별할때 자세히 조회해야한다. 처음 검토 할때 쿼리가 복잡하고 많은 조인이 필요하다는 것은 분명하지않을 있다. 또는 테이블 반환 함수가 관련된 일반적인 경우다. 예를 들어 표면에 개의 뷰를 결합하기 때문에 쿼리가 간단해 보일 있다. 그러나 뷰를 검사할 뷰가 7개의 테이블을 조인한다는것을 있다. 개의 뷰가 조인되면 결국 14개의 테이블로 조인된다..

 

아래는 쿼리 성능을 향상 시키는데 도움이 되는 다양한 방법이다. 다시 한번 말하지만 쿼리 계획에 최적화 프로그램 Timeout 있다는 사실이 반드시 쿼리 속도가 느린 이유라는 것을 의미 하지는 않는다.

·         특정 계획 강제 실행 :  테스트를 통해 특정 계획이 쿼리에 적합하다고 판단되면 QO 해당 계획을 선택하도록 요청해야한다.

o   QDS(Query Data Store) 사용하여 계획을 강제로 실행하는 방법 : https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-2017

o   쿼리 힌트 옵션 (PLAN <XML PLAN HERE> 사용) : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

·         플랜 가이드 : QO 고려해야할 조인 가능성을 줄여야한다. 여기에는 다양한 옵션으로 쿼리를 테스트하는 것이 포함된다. QO 사용한 대부분의 결정과 마찬가지로 선택의 폭이 매우 다양하기 때문에 항상 결정적인 것은 아니다. 따라서 성공적인 전략을 보장한 사람은 없다. 이것들은 선택된 쿼리의 성능을 향상 시키거나 악화시킬수 있다. 자세한 내용은 쿼리 힌트를 참고한다.

o   쿼리 힌트 : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

o   조인 순서 고정 : OPTION (FORCEORDER)

o   JOIN 가능성을 줄이기 : OPTION( 해시 조인 , 병합조인), OPTION(해시 조인, 조인 루프), OPTION(병합조인)

·         Cardinality Estimation(CE) 구성 변경 : 레거시 CE에서 CE 또는 CE에서 레거시 CE 전환하여 카디널리티 예상 구성을 변경할 있다. Cardinality Estimation 구성을 변경하면 SQL Server 쿼리 계획을 평가하고 생성할 QO 다른 경로를 선택하게 있다. 따라서 최적화 프로그램 시간 초과 문제가 발생하더라도 대체 CE 구성을 사용하여 선택한 것보다 최적으로 수행하는 계획으르 수립할 있다. 자세한 내용은 SQL Server 시스템 최상의 카디널리티 추정 구성을 평가하고 선택하는 방법을 참조한다.

o   Cardinality Estimation : https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017&viewFallbackFrom=sql-server-2017%20

·         Optimizer fixes : T4199 통해 QO 수정을 사용하지 않았거나 SQL Server 2016 이상 또는 ALTER DATABASE SCOPED CONFIGURATION...QUERY_OPTIMIZER_HOTFIXES =ON 대해서 데이터베이스 호환성 수준을 사용하면 이러한 수정을 적용하는 것이 좋다. 이로 인해 최적화 프로그램이 계획탐색에서 다른 경로를 사용하게 되어 결국 최적의 쿼리 계획으로 끝날 있다.

·         쿼리를 다시 작성 : 임시 테이블을 사용하여 단일 다중 테이블 쿼리를 여러 쿼리로 분할하는 것을 고려해야한다. 그러나 근본적인 해결책은 아니다. 쿼리를 분리하면 최적화 프로그램의 작업을 단순화 하는 방법중 하나이다. 아래 샘플을 참고한다.

select ...

from t1

join t2

on t1.id = t2.id

join t3

on t3.id = t2.id

join t4

on t4.id = t3.id

 

최적화하려면 가지 쿼리로 나눈다.

select ...

into #temp1

from t1

join t2

on t1.id = t2.id

 

select ...

From t3

join #temp1

on t3.id = #temp1.id

join t4

on t4.id = t3.id

 

쿼리를 분할할때 중요한것은 여러 CTE (Common Table Expression) 사용하는것이 쿼리를 단순화하는 적절한 솔루션이 아니다. 여러 CTE 쿼리의 복잡성만 증가 시킨다. 따라서 비생산적이다. CTE 논리적으로 쿼리를 중단하지만 단일 쿼리로 결합되어 결국 테이블의 단일 조인으로 최적화 된다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2018/10/19/understanding-optimizer-timeout-and-how-complex-queries-can-be-affected-in-sql-server/

 

 

2018-10-26 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, SQL Optimizer, 옵티마이저, 쿼리 튜닝, DB 튜닝, Optimizer timeout

SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환

 

·         Version : SQL Server 2019

 

SQL Server 2019 CTP 2.0에서 문자열 잘림에 대한 에러메시지가 향상되었다. 일반적으로 ETL 작업 많이 겪는 문제중 하나가 “String or binary data would be truncated”이다. 오류 조건은 일치하는 데이터 유형 / 길이가 없는 소스와 대상간에 ETL 구현할 발생할 있다. 특히 대형 데이터 세트에서 가장 시간이 많이 걸리는 프로세스중 하나이다. 아래 스크립트는 크기가 충분하지 않는 열에 해당 열보다 데이터를 삽입하면 어떻게 되는지 확인하는 예제 스크립트이다.

DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]

GO

CREATE TABLE [Sales].[SalesOrderHeaderTest](

    [SalesOrderID] [INT] NOT NULL,

    [CustomerID] [INT] NOT NULL,

    [CreditCardApprovalCode] [nvarchar](13) NULL

)

GO

 

INSERT INTO [Sales].[SalesOrderHeaderTest]

SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]

FROM [Sales].[SalesOrderHeader]

GO

 

스크립트를 실행하면 아래와 같은 오류 메시지가 표시 되는데, 문제 해결에 크게 도움이 되지 않는다.

Msg 8152, Level 16, State 4, Line 10

String or binary data would be truncated.

The statement has been terminated.

 

SQL Server 2019 CTP 2.0에서는 추가 컨텍스트 정보와 함께 메시지를 나타낸다. 동일한 작업의 경우 오류 메시지는 아래과 같이 출력된다.

Msg 2628, Level 16, State 1, Line 14

String or binary data would be truncated in table 'AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest', column 'CreditCardApprovalCode'. Truncated value: '1231736Vi8604'.

The statement has been terminated.

 

새로운 오류 메시지는 많은 컨텍스트를 제공하고 결과 (소스값 아님) 보여준다. 결과를 보면 문제가 발생한 부분이 ‘1213736Vi8604’ 시작한다는 것을 있다. 길이가 13 이다. 다시 소스로 돌아와 해당 레코드의 길이를 살펴보면 14 인것을 있다. 그러므로 데이터가 잘리는 오류가 발생한다. 해당 오류를 확인하고 적절한 데이터 길이로 컬럼을 변경해서 해결해야한다.

 SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])

FROM [Sales].[SalesOrderHeader]

WHERE CreditCardApprovalCode LIKE '1231736Vi8604%'

 

 

새로운 메시지는 SQL Server 2017 CU12 SQL Server 2016 SP2 CU에도 적용되지만 기본적으로 활성화 되지 않는다. 추적플래그 460 활성화하여 세션ID 또는 서버 레벨에서 메시지 ID 8152 2628 바꿀 필요가 있다. 현재로서는 SQL Server 2019 CTP 2.0에서도 동일한 추적 플래그 460 사용할 있어야 한다. 향후 SQL Server 2019 릴리스에서는 기본적으로 메시지 2628 8152 대체한다.

오류메시지가 반환할 있는 잘린 문자열에 대한 최대 크기는 100 까지만 표시된다. 아래 스크립트는 123자의 문자를 VARCHAR(120) 삽입하는 스크립트이다.

CREATE TABLE myTable (myString VARCHAR(120));

GO

INSERT INTO myTable

VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.')

GO

 

스크립트를 실행하면 문자여링 120자에서 잘리지만 실제 표시되는 오류는 처음100 까지만 표시된다.

Msg 2628, Level 16, State 1, Line 30

String or binary data would be truncated in table 'AdventureWorks2016CTP3.dbo.myTable', column 'myString'. Truncated value: 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore '.

 

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/

 

 

2018-10-25 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, SQL 2019, TF 460, 문자 잘림 오류

+ Recent posts