SQL Server Failover Cluster 구성

 

l  Version : SQL Server 2019

 

SQL Server 고가용성 하나인 SQL Server Failover Cluster (장애조치 클러스터) 인스턴스를 구성하는 방법에 대해서 알아본다. SQL Server 장애조치 클러스터를 구성하기 위해서는 Windows Failover Cluster 먼저 구성되어 있어야 한다. 그리고 디스크 또한 공유 디스크를 사용이 필수이다. 이번 포스트에서는 Windows Failover Cluster 구성이 완료 되어 있다는 가정하에 SQL Server Failover Cluster 구성하는 방법에 대해서 설명한다.

 

이번 포스트에서 구성하려는 장애조치 클러스터의 구성은 아래 그림과 같다.  DB01 액티브 서버로 운영되고, DB02 패시브 서버로 운영되며 비상시 장애조치 되어 역할이 변경된다. SQL Server 운영에 필요한 설치 파일 사용자 데이터베이스 파일은 공유 디스크에 저장되어 운영 된다.

 

 

 

[Master 서버, 클러스터 설치]

DB1(Master) 서버에서 SQL Server 설치 파일을 실행한 다음 [설치]-[SQL Server 장애조치(Failover) 클러스터 새로 설치] 클릭 한다.

 

 

사용 조건 단계에서 라이선스 계약에 동의함을 선택하고 [다음] 클릭한다.

 

 

Microsoft 업데이트 단계에서는 업데이트를 체크하지 않고 [다음] 클릭한다. 업데이트를 클릭하게 되면 설치 과정에서 업데이트가 발생하여 설치 시간이 오래 걸릴 있으니, 업데이트는 모든 설치 이후에 별도로 진행 있도록 한다.

 

 

장애 조치(Failover) 클러스터 설치 규칙 단계에서는 클러스터 설치에 적합한 환경인지 등을 검사하게 된다. [다음] 클릭한다.

 

 

기능 선택 단계에서는 사용자에게 필요한 SQL Server 기능을 선택 한다. 설치할 기능을 선택하고 [다음] 클릭한다.

 

 

인스턴스 구성 단계에서는 SQL Server 클러스터에 사용할 네트워크 이름을 입력한다. 이름은 이후 다른 서버가 클러스터에 조인할 식별할 있는 이름이기에 의미 있는 이름으로 지정할 있도록 한다. 이름을 입력하였으면 [다음] 클릭한다.

 

 

클러스터 리소스 그룹 단계에서는 리소스 그룹에 포함할 인스턴스를 선택한다. 아래 그림에서는 기본 인스턴스로 생성하였기에 그룹 이름 또한 기본 인스턴스 이름으로 보여진다. 리소스 그룹을 선택하고 [다음] 클릭 한다.

 

클러스터 디스크 선택 단계에서는 SQL Server에서 사용할 공유 디스크를 선택한다. 단계에서 디스크 목록이 나타나지 않는다면 Windows Failover Cluster 구성할 공유 디스크에 대한 설정을 잘못된 것이므로 윈도우 클러스터부터 다시 확인 있도록 한다. 디스크  선택을 하고 [다음] 클릭한다.

 

클러스터 네트워크 구성 단계에서는 SQL Server Cluster 사용할 IP 입력한다. 여기에 입력한 IP 이후 사용자가 접속할 사용되는 Cluster IP이다. 클러스터 IP 접속을 해야 Failover 발생하였을 , 활성 SQL 서버로 자동으로 연결해 준다. 중복되지 않은 클러스터 IP입력을 하였다면 [다음] 클릭한다.

 

 

서버 구성 단계에서는 SQL Server 서비스에서 사용할 계정과 암호를 입력한다. 이때 도메인 계정을 사용할 있도록 한다. 계정과 이름을 입력하였으면 [다음] 클릭 한다.

 

 

데이터베이스 엔진 구성 단계에서는 SQL Server 접근 방법 SQL 관리에 필요한 sa 비밀번호를 입력한다.

 

 

데이터베이스 엔진 구성 단계에서 [데이터 디렉터리] 탭에서는 SQL Server 필요한 데이터가 위치할 디렉터리를 지정한다. 디렉터리 경로는 앞에서 추가한 클러스터 공유 디스크의 위치를 지정한다.

 

 

데이터베이스 엔진 구성 단계에서 [TempDB] 탭에서는 SQL Server TempDB 사용할 디렉터리 경로를 지정한다. 디스크는 로컬을 사용해도 되지만, 보통 클러스터 구성시 로컬 디스크는 많은 공간을 할당하지 않기 때문에 용량이 클러스터의 디스크로 지정하였다. 지금까지 구성이 완료되었으면 [다음] 클릭 한다.

 

 

설치 준비 단계에서는 지금까지 설정한 요소들을 정리해서 보여주며 [설치] 클릭하여 설치를 진행할 있도록 한다.

 

 

완료 단계에서는 정상적으로 설치가 완료된 것을 보여주며 [닫기] 클릭 한다.

 

 

설치가 완료된 다음, 앞에서 지정한 디렉터리 경로에 SQL Server 필요한 파일이 생성된 것을 확인할 있다.

 

 

[Passive서버, 클러스터 노드 추가]

지금까지 Master 서버에서 SQL 클러스터링에 대한 설치를 진행하였고, 이후 단계는 클러스터 노드에 추가할 SQL Server 설치한다. 이후 과정은 Passive 서버에서 진행하기 때문에 착오가 없도록 한다.

 

SQL Server 설치 파일을 실행하여 [설치] – [SQL Server 장애 조치(Failover) 클러스터에 노드 추가를 선택 한다.

 

 

이후 설치 과정은 이전과 동일한 부분이 있어 그림으로만 대체한다. 필요한 부분에서는 설명을 추가 한다.

 

 

 

앞에서 생성한 인스턴스 클러스터 그룹의 이름을 선택하면 클러스터 노드의 이름을 자동으로 불러와서 보여준다.

 

 

클러스터 IP또한 이미 클러스터 구성 입력한 정보를 그대로 보여준다.

 

 

인스턴스에서 사용할 계정과 암호를 입력한다. 도메인 계정으로 사용할 있도록 한다.

 

 

 

 

클러스터 노드에 추가가 완료된 다음 SQL Server 접속 테스트를 진행한다. 이때 노드의 로컬IP 아닌 SQL Server Cluster IP 입력하도록 한다. 로컬IP 접속할 경우 해당 노드의 인스턴스에 직접 로그인 하기 때문에 Failover 발생시 활성 서버로 자동으로 연결되지 않는다. 클러스터 IP 사용할 경우에는 활성 노드로 자동으로 연결해 준다.

 

 

서버 이름을 조회해 보면 로컬의 서버 이름이 아닌 클러스터 이름을 보여준다. 그리고 SQL Server 클러스터 상태를 조회해 보면 현재 구성되어 있는 모든 노드의 이름을 보여주며 어떤 노드가 활성 상태인지를 보여준다.

 

 

테스트를 위해 TestDB 생성해 보았는데, 클러스터를 구성할 지정했던 디렉터리 경로에 사용자 데이터베이스 파일이 생성되는 것을 확인할 있다.

 

 

SQL server 장애조치 클러스터를 구성하였을 , 하나의 서버에 장애가 나도 다른 서버가 역할을 대신할 있어 가용성을 높일 있다. 노드의 개수는 최대 4 까지 가능하다. 물론 가용성을 높이기 위한 기술로는 클러스터링 외에도 복제, 미러링, AlwaysOn 기술등이 있다. 사용자 환경을 고려하여 최적의 솔루션을 선택하여 가용성을 확보 있도록 한다.

 

 

2023-07-16/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, SQL Failover Cluster, SQL 장애조치, SQL 클러스터링, 장애조치, SQL 고가용성, 페일오버 클러스터

SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기

 

l  Version : SQL Server

 

SQL Server 데이터베이스에는 모든 트랜잭션과 트랜잭션에 의해 적용된 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있다. 트랜잭션 로그는 데이터베이스의 매우 중요한 요소로 시스템 오류가 발생한 경우 데이터베이스를 일관된 상태로 다시 전환하려면 로그가 필요하다.

l  트랜잭션 로그 아키텍처 : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

데이터베이스에 대량의 쓰기 작업이 발생하면 트랜잭션 로그에는 변경사항을 기록하기 위해 오버헤드가 발생하면서 로그 쓰기 작업(Write Log) 지연되는 경우가 발생한다. 로그 모니터링을 통하여 Write Log 지연이 발생할 경우 DELAYED_DURABILITY 옵션을 사용하여 해당 문제를 개선할 있다. 옵션을 사용할 경우 트랜잭션 로그의 커밋을 기다리지 않기 때문에 워크로드의 대기 시간이 줄어들어 성능 개선은 있지만 트랜잭션 데이터를 소실할 있기 때문에 주의해서 사용해야 한다. 이번 포스트에서는 DELAYED_DURABILITY 옵션에 대한 특징을 살펴보고, 어떠한 경우에 사용할 있는지 살펴본다.

 

SQL Server 트랜잭션 로그 파일에 데이터를 저장해야 트랜잭션 로그 파일이 저장된 디스크에 직접 데이터를 기록하지 않는다. 대신 모든 데이터는 인메모리 구조인 로그 캐시(로그 버퍼, 또는 로그 블록 이라고 불리기도 한다.) 직렬로 기록된다. 또한 SQL Server OS ACID 원칙을 준수해야 하므로 전체 로그 캐시를 디스크 하위 시스템에 저장하거나 필요한 경우 롤백 되는 트랜잭션 로그 파일로 플러시 한다. 로그 캐시의 크기는 512B ~ 64KB이다.

 

로그 캐시는 특정 조건에서 디스크로 플러시 된다.

l  트랜잭션이 커밋될

l  로그 캐시가 가득 차서 60KB 도달했을

l  Sys.sp_flush_log 실행될

l  CHECKPOINT 프로세스가 완료 되었을

 

SQL Server 로그 캐시가 트랜잭션 로그 파일로 플러시 되기 시작하는 순간 WRITELOG 대기 유형에 등록되고 로그 캐시가 메모리에서 디스크 드라이브의 파일로 데이터 플러시를 완료 때까지 해당 시간이 누적된다. 누적시간이 낮을수록 트랜잭션 로그파일의 쓰기 대기 시간이 낮아 진다.

 

SQL Server에서는 기본적으로 동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록된 후에만 커밋을 성공으로 보고하고 클라이언트에 컨트롤을 반환한다. 따라서 이러한 커밋이 느릴수록 클라이언트에서는 느린 응답을 받을 수밖에 없다. 동기 커밋은 아래와 같은 완전 내구성이 있는 환경의 경우 필수적으로 사용해야한다.

l  시스템에서 데이터 손실을 허용할 없는 경우

l  병목 현상의 원인이 트랜잭션 로그 쓰기 대기 시간이 아닌 경우

 

DELAYED_DURABILITY 옵션을 활성화 경우, 비동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록되기 전에 커밋 성공으로 클라이언트에 컨트롤을 반환하기 때문에 클라이언트에서는 빠른 응답을 얻을 있다. 또한 동시 트랜잭션의 로그 I/O 경합 가능성이 낮아지고, 청크 구성으로 디스크에 플러시하여 경합을 줄이고 처리 속도를 높일 있다. 비동기 커밋의 경우 아래와 같은 환경에서 사용할 있다.

l  약간의 데이터 손실을 허용하는 경우

l  트랜잭션 로그 쓰기 중에 병목 현상이 발생하는 경우

l  작업의 경합률이 높은 경우

 

DELAYED_DURABILITY 옵션은 데이터베이스 수준에서 트랜잭션 내구성 수준을 제어할 있다. 아래 스크립트는 DELAYED_DURABILITY 옵션을 데이터베이스 수준에서 변경한다.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

 

l  DISABLED : 기본값으로 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 상관없이 데이터베이스 커밋된 모든 트랜잭션이 완전 내구성을 가진다. 저장 프로시저를 변경하고 다시 컴파일할 필요가 없다. 지연된 내구성으로 인해 데이터가 위험에 노출되지 않는다.

l  ALLOWED : 트랜잭션의 내구성이 트랜잭션 수준에서 결정된다. 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 의해 결정된다.

l  FORCED : 데이터베이스에 커밋되는 모든 트랜잭션이 지연된 내구성을 가진다. 설정은 지연된 트랜잭션 내구성이 데이터베이스에 유용하고 어플리케이션 코드를 변경하지 않으려는 경우에 유용하다.

 

 

아래 스크립트는 커밋 수준을 설정한다.

DELAYED_DURABILITY = { OFF | ON }

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 비동기적이고 지연된 내구성을 갖는 경우를 제외하고 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 동기적으로 완전 내구성있는 경우를 제외하고 트랜잭션은 지연된 내구성을 가진다.

 

아래 스크립트는 저장 프로시저에 커밋 수준을 적용한 예시이다.

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER   
AS BEGIN ATOMIC WITH    
(   
    DELAYED_DURABILITY = ON,   
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,   
    LANGUAGE = N'English'   
)   
/* procedure body goes here */
END

 

 

지연된 트랜잭션 내구성을 강제로 적용할 있도록 COMMIT 구문으로도 확장할 있는데, 데이터베이스 수준에서 DELAYED_DURABILITY DISABLED 또는 FORCED 경우 COMMIT 옵션은 무시된다.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 CIMMIT 비동기 적으로 지연된 내구성을 갖는 경우를 제외하고 COMMIT 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = DISABLED 데이터베이스 옵션을 적용하여 COMMIT 동기적이고 완전 내구성 있는 경우를 제외하고 COMMIT 트랜잭션은 지연된 내구성을 가진다.

 

 

지연된 내구성을 사용할 경우 특정 상황에서 데이터를 손실 가능성이 있기 때문에 반드시 해당 비즈니스의 목적과 데이터베이스 성능 등을 고려하여 사용 여부를 결정할 있도록 한다.

 

[참고자료]

l  Control Transaction Durability : https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver16

l  Measure Delayed Durability impact in SQL Server 2016 and later : https://www.mssqltips.com/sqlservertip/6355/measure-delayed-durability-impact-in-sql-server-2016-and-later/

l  Get SQL Server Delayed Durability Advantages Without Configuration Changes : https://www.mssqltips.com/sqlservertip/6324/get-sql-server-delayed-durability-advantages-without-configuration-changes/

l  How to handle the SQL Server WRITELOG wait type : https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

l  Improve SQL Server transaction log performance with Delayed Durability : https://www.sqlshack.com/improve-sql-server-transaction-log-performance-with-delayed-durability/

l  The Transaction Log (SQL Server) : https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16

l  SQL Server Transaction Log Architecture and Management Guide : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

 

 

2022-10-30/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, DELAYED_DURABILITY, 트랜잭션 로그 비동기 커밋, WAL, Write Log

SQL Server 에서 AWS S3 직접 백업하기

 

l  Version : SQL Server 2022

 

SQL Server 2022부터는 데이터 플랫폼에 통합 오브젝트 스토리지를 도입하여 Azure Storage외에도 AWS S3 호환 오브젝트 스토리지도 사용할 있다. , SQL Server에서 AWS S3 직접 백업을 있게 되었다.

기존 SQL Server 2019 경우 Microsoft Azure 저장소만 가능 하였다. SQL Server 2022 부터 도입된 S3 REST API SQL Server에서 AWS S3 직접 백업, 복원을 있어, 백업 이동에 따른 프로세스 단축 대용량 백업을 진행할 경우에도 로컬 공간이 부족하여도 백업을 진행할 있게 되었다.

 

지원되는 백업 종류

l  전체 백업

l  차등 백업

l  트랜잭션 로그 백업

l  복사 전용 백업 (COPY ONLY)

 

지원되지 않는 백업 종류

l  스냅샷 백업 (FILE_SNAPSHOT)

l  미러 백업 (MIRROR)

 

AWS S3버킷으로 백업 복원을 수행 하기위해서는 아래 정보가 필요 하다.

l  S3 버킷이름

l  S3 URI

l  S3 URL

l  IAM 액세스 ID

l  IAM 액세스

 

AWS IAM(Idnetity Access Management) 정책은 사용자의 권한 또는 특정 서비스에 대해 수행할 있는 역할을 정의한다. SQL Server 2022에서 S3 버킷으로 직접 데이터베이스 백업을 수행하려면 ListBucket PutObject 권한이 필요하다.

l  ListBucket : ListBucket 액세스는 요청의 인증된 발신자가 소유한 모든 버킷 목록을 반환한다.

l  PutObject : PutObject 액세스를 통해 버킷에 객체를 작성하고 추가할 있다.

 

SQL Server에서 AWS S3 백업을 하기 위해 우선 자격증명을 만들어야 한다. 아래는 자격증명을 만드는 스크립트이다.

CREATE CREDENTIAL   [s3://<endpoint>:<port>/<bucket>]
WITH   
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

 

l  [s3://<endpoint>:<port>/<bucket>] : 자격 증명 이름에는 S3 버킷 이름이 포함되어야 한다. S3:// 시작하고 접두사 HTTPS:// 제거한 S3 버킷 URL 따른다.

l  IDENTITY : S3 Access Key 값으로, 자격 증명에서 S3 커넥터를 사용하고 있음을 나타낸다.

l  SECRET : 접근키 ID 비밀 키를 콜론으로 구분하여 입력한다. 콜론 기호는 구분자 역할을 한다.  

 

 

 

백업 명령은 기존에 사용하던 백업문과 거의 동일하다. 다만 백업 위치가 DISK 아닌 URL 사용한다.

BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak'

 

 

백업 완료 S3 확인해보면 백업파일이 생성된 것을 확인할 있다.

 

아래 스크립트는 백업하려는 데이터베이스를 여러 스트라이프된 백업 파일로 오브젝트 스토리지 엔드포인트를 사용하여 백업을 수행한다.

BACKUP DATABASE <db_name>
TO      URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    FORMAT -- overwrite
,       STATS               = 10
,       COMPRESSION;

 

 

 

아래 스크립트는 오브젝트 스토리지 엔드포인트 위치에서 데이터베이스 복원을 수행한다.

RESTORE DATABASE <db_name>
FROM    URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    REPLACE -- overwrite
,       STATS = 10;

 

 

데이터를 저장하려면 S3 호환 오브젝트 스토리지 공급자는 파트(Part) 불리는 여러 블록으로 파일을 분할해야 한다. 파일은 최대 10,000개의 파트로 분할 있으며, 파트의 크기는 5MB ~ 20MB이다. 범위는 매개변수 MAXTRANSFERSIZE 통해 T-SQL BACKUP 명령으로 제어할 있다. MAXTRANSFERSIZE 기본값은 10MB 이므로 파트의 기본 크기는 10MB이다.  , 단일 백업 파일의 크기는 MAXTRANSFERSIZE 기본값으로 설정되어 있을 경우 100,000MB이다. 백업 스트라이프가 100,000MB 초과할 경우 아래와 같은 오류 메시지를 반환한다.

Msg 3202, Level 16, State 1, Line 161
Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.)
Msg 3013, Level 16, State 1, Line 161
BACKUP DATABASE is terminating abnormally.

 

단일 백업 파일의 크기가 100,000MB 보다 파일을 백업 해야 경우 최대 64개의 URL 스트라이프 백업을 지원한다. , 가능한 최대 백업 파일 크기는 10,000part * MAXTRANSFERSIZE * URL이다. 압축 백업을 사용할 경우 백업 파일 사이즈가 현저히 많이 줄어들기 때문에 백업 압축 옵션을 적극 활용할 있도록 한다.

아래 예제는 MAXTRANSFERSIZE 사용하여 20MB으로 설정하고, 백업 압축 암호화한다.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>;
GO
 
CREATE CERTIFICATE AdventureWorks2019Cert
    WITH SUBJECT = 'AdventureWorks2019 Backup Certificate';
GO
-- Backup database
BACKUP DATABASE AdventureWorks2019
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2019_Encrypt.bak'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2019Cert)
GO

 

백업 복원 엔진에서 URL 길이는 259바이트로 제한되어 있기 때문에 s3:// 제외하고 사용자의 경로 길이(호스트 이름 + 개체키) 259 - 5 = 254글자까지 입력할 있다. 만약 URL 길이를 초과할 경우 아래와 같은 오류가 반환된다.

SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together'

 

S3 스토리지는 SQL 호스트와 S3 서버간의 시간 차이가 15분을 초과할 마다 연결을 거부하고 “InvalidSignatureException”오류를 SQL Server 보낸다. SQL Server에서는 아래와 같은 오류로 반환된다.

Msg 3201, Level 16, State 1, Line 28
Cannot open backup device '<path>'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 28
BACKUP DATABASE is terminating abnormally.

 

 

[참고자료]

l  SQL Server backup to URL for S3-compatible object storage : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16

l  SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting : https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage-best-practices-and-troubleshooting?view=sql-server-ver16

l  https://www.mssqltips.com/sqlservertip/7301/sql-server-2022-backup-restore-aws-s3-storage/

l  https://www.mssqltips.com/sqlservertip/7302/backup-sql-server-2022-database-aws-s3-storage/

 

 

 

2022-10-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, AWS S3 backup, S3 백업, 클라우드 백업, cloud backup

VM 환경의 SQL Server에서 할당된 CPU 모두 사용하지 못하는 현상

 

·       Version : SQL Server

 

Virtual Machine (VM) 성능이 향상됨에 따라 많은 시스템들이 Physical 서버에서 VM 머신으로 마이그레이션을 진행하고 있다. 이번 포스트는 Physical머신에서 VM으로 마이그레이션 SQL Server에서 할당된 CPU 모두 사용하지 못하는 성능 문제에 대해서 다룬다.

 

Host Server

OS

Windows Server 2019 Standard

CPU

2 socket (64 Core)

RAM

128GB

 

VM Server

OS

Windows Server 2019 Standard

SQL Server

SQL Server 2016 Standard

CPU

8 Core

RAM

32GB

 

아래 그림을 보면 가상머신에 할당된 CPU 8 Core 에서 4 Core 사용하는것을 확인할 있다.

 

아래 쿼리를 사용하여 실제 SQL Server 사용중인 CPU 확인할 있다. 역시 할당된 8개의 코어중에 4개만 사용하고 있음이 표시되었다.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers

 

 

원인을 확인결과 VM 머신의 세팅에 문제가 있다는 것을 확인하였다. 필자가 구성한 VM환경을 보면, 8 소켓에 8Core 설정되어 있는것을 확인할 있다. 뜻은 1소켓 1 코어가 8 할당되었다는 의미이다. SQL 공식 다큐먼트를 보면 Standard 경우 4소켓 또는 24코어까지만 지원된다고 되어 있다. 소켓의 개수가 8개로 세팅되어, 실제 4 소켓만 동작을 하기 때문에 4Core 동작을 하게 된것이다.

VM 환경을 변경하여, 1소켓 8 Core 설정을 변경한 정상적으로 모든 CPU 사용하는 것을 확인하였다.

 

VM 환경을 변경하는것은 시스템 운영에 문제가 발생할 있으므로 반드시 시스템 담당자와 상의해서 진행할 있도록 한다.

 

아래 스크립트는 현재 구성된 VM 환경에서 SQL Server 할당된 CPU 모두 사용가능한지 쉽게 확인한다.

----------------------------------------------------------------------------------------------------------------

-- CPU VISIABLE ONLINE CHECK

----------------------------------------------------------------------------------------------------------------

DECLARE @OnlineCpuCount int

DECLARE @LogicalCpuCount int

 

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

 

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',

   CASE

     WHEN @OnlineCpuCount < @LogicalCpuCount

     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'

     ELSE 'You are using all CPUs assigned to O/S. GOOD!'

   END as 'CPU Usage Desc'

----------------------------------------------------------------------------------------------------------------

GO

 

 

 

2020-03-03/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, VM, Virtual Machine, CPU 할당, 가상머신 SQL, SQL on VM, dm_os_schedulers, dm_os_sys_info

SQL Server 복원 성능 최적화

 

·       Version : SQL Server

 

SQL Server에서 백업 파일을 복원할때 빠르게 복원하기 위한 최적화 방법을 소개한다. 방법을 사용한다고 해서 무조건 빠르게 복원되지는 않으며, 사용할 있는 시스템 리소스에 따라 최적화된 옵션을 제공함으로써 빠르게 복원할 있게 유도 하는 것이다.

 

데이터베이스 백업 복원에 대한 통계를 확인하기 위해 추적 플래그 3213, 3605 설정한다.

DBCC TRACEON (3213, -1)

DBCC TRACEON (3605, -1)

 

데이터베이스를 복원하면, SQL 이벤트 로그에서 아래와 같은 내용을 확인할 있다.

 

기본 설정을 사용하면  최대 전송크기는 1024K 이고 버퍼수는 6인것을 확인할 있다. 이때 사용되는 버퍼 공간은 6MB이다.

버퍼공간 = 최대 전송크기 X 버퍼

 

여기서 주목해야 부분은 메모리 제한(Memory limit) 이다. 현재 필자의 메모리 제한은 4095 MB (사용자 마다 다름)이지만 사용되는 버퍼 공간은 6MB 이다. 따라서 버퍼 공간을 늘려 복원 속도를 높일 있다. 최대 전송 크기 버퍼수 변경은 데이터베이스 복원시 추가 매개변수로 사용할 있다.

아래 예시는 최대 메모리 제한까지 사용하기 위해서 MAXTRANSFERSIZE 4096K 설정하고, BUFFERCOUNT 1000으로 설정하였다.

RESTORE DATABASE [DB] FROM DISL = N'D:\DB\BACKUP\DB.BAK' WITH REPLACE, STATS = 5, MAXTRANSFERSIZE = 4194302, BUFFERCOUNT = 1000

 

추가 매개변수를 사용하여 데이터베이스를 복원할 경우, 복원의 속도는 빨라지겠지만 동일 서버에서 운영되는 다른 서비스에 영향을 미칠 수도 있다. 해당 옵션을 사용하기 전에 시스템의 리소스 가용능력, 사용량등을 확인할 있도록 한다.

중요한것은 실제 운영 환경에 반영하기전에 테스트 환경에서 먼저 검증을 있도록 한다.

 

[참고자료]

·       Optimizing Backup and Restore Performance in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN

 

 

2020-02-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, SQL Server Recovery Step, 데이터베이스 복원, Database Recovery Process, BUFFERCOUNT, MAXTRANSFERSIZE

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상

 

·       Version : SQL Server 2016 Enterprise Edition

 

SQL Server에서 트랜잭션 로그를 사용하여 데이터를 복원시, 평소와 다르게 매우 오래 걸리는 현상이 발생하였다. 처음에는 I/O 서브 시스템을 의심하고 물리장비 까지 교체하였으나, 증상을 동일하였다. 여러가지 가설을 세웠고, 원인 분석 결과, Slow query 트랜잭션 로그의 복원시간과 관련이 있다는 것을 발견할 있었다.

 

필자의 운영 환경은 10 마다 트랜잭션 로그 백업을 진행하고, 백업된 로그는 다른 서버에서 Read Only(STAND BY) 복원하여 부서에서 사용할 있도록 로그 쉬핑을 구성하였다. 10 마다 발생하는 로그의 양은 10MB~ 20MB 정도로 평상시 복원하는데 1 정도 소요되었다. 그런데 어느날 부터 복원시간이 증가하여 30분정도 걸렸다. 그러다 보니, 연관된 여러 ETL 시스템 리포트 시스템에 영향을 주게 되었었다.

아래 그래프는 트랜잭션로그 복원시 디스크의 Idle Time 캡처한 것으로 파란색이 평상시의 상태이고 빨간색과 노란색이 문제가 발생했던 때의 상태이다. 트랜잭션 로그 복원이 진행되는  동안 엄청난 I/O 발생하는 것을 확인할 있다.

 

트랜잭션 복원이 진행되는 동안 단계에 대한 작업시간을 확인하기 위해서 DBCC TRACE 명령을 사용하여 Inter log SQL Server이벤트 로그에 기록하도록 하였다.

DBCC TRACEON(3004, -1)

DBCC TRACEON(3605, -1)

DBCC TRACEON(3213, -1)

 

트레이스 적용 트랜잭션로그 복원을 진행하고, 아래와 같은 이벤트 로그를 확인할 있었다.

 

이벤트 로그를 확인해보면 redo, undo 관한 시간이 표시되어 있는데, Slow 쿼리가 발생할 경우 트랜잭션 커밋이나 롤백이 발생하지 않은 상태(더티페이지)에서 백업이 진행되고, 다음에 쿼리 실행이 완료되어 다름 트랜잭션 로그에 반영되었을때, 해당 쿼리의 결과에 따라, 데이터가 롤백되거나 커밋을 해야해서 (특히 롤백이 문제다) 데이터 페이지, 인덱스 페이지등에 반영하느라 디스크 I/O 오버헤드가 발생하고 전체적인 복원 시간이 느려졌다. 아래와 같은 단계로 진행된다.

 

1 단계 : 분석. 트랜잭션 로그의 마지막 체크 포인트에서 시작한다. 단계는 SQL Server 중지 더티 페이지 테이블 (DPT) 확인하고 구성한다. 활성 트랜잭션 테이블은 SQL Server 중지 커밋되지 않은 트랜잭션으로 구성된다.

 

2 단계 : 다시 실행. 단계는 데이터베이스를 SQL 서비스가 중지 시점의 상태로 되돌린다. 가장 오래된 커밋되지 않은 트랜잭션이 롤백의 시작점 이다. DPT 최소 로그 시퀀스 이름 ( 로그 레코드에 LSN으로 레이블이 지정됨) SQL Server 페이지에서 작업을 다시 실행해야하는 번째 시간이며 가장 오래된 열린 트랜잭션에서 다시 시작하여 기록 작업을 다시 실행해야 한다. 필요한 잠금 장치를 확보 한다.

 

3 단계 : 실행 취소. 여기에서 1 단계에서 식별 활성 트랜잭션 (SQL Server 중단 커밋되지 않은) 목록이 개별적으로 롤백된다. SQL Server 트랜잭션에 대한 트랜잭션 로그 항목 간의 링크를 따른다. SQL Server 중지 커밋되지 않은 트랜잭션은 모두 취소된다.

 

 

정리하면, Slow 쿼리 증가로 인해 활성 트랜잭션이 많아졌고, 트랜잭선 로그 백업이 진행될때 더티페이지가 증가함에 따라 해당 로그 파일로 복원시 롤백 커밋 작업이 진행되어 이때 I/O 오버헤드가 발생하여 전체적인 복원이 느려졌다.

 

[참고자료]

·       Understanding How Restore and Recovery of Backups Work in SQL Server  : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191455(v=sql.105)?redirectedfrom=MSDN

·       SQL Server Mysteries: The Case of the Not 100% RESTORE : https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-mysteries-the-case-of-the-not-100-restore

·       SQLskills SQL101: Why is restore slower than backup : https://www.sqlskills.com/blogs/paul/sqlskills-sql101-why-is-restore-slower-than-backup/

·       SQL Server Database Recovery Process Internals – database STARTUP Command : https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

 

2020-02-27/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, Log shipping, 트랜잭션 로그 복원, SQL Server Recovery Step, 데이터베이스 복원, 로그 전달, 로그쉬핑, Database Recovery Process

SQL Server 2019 temp table 사용한 워크로드에서 recompile 감소

 

·       Version : SQL Server 2019

 

SQL Server 2019에는 응용프로그램 코드에 필요한 변경을 최소화 하면서 성능을 향상시키는 가지 성능 최적화가 도입 되었다. 이번 포스트에서는 SQL Server 2019 성능 개선 사항 하나인 temp 테이블을 사용한 작업 부하에 대해 리컴파일 감소로 인한 성능 향상을 설명한다.

·       Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

 

개선 사항을 이해하기 위해 먼저 SQL Server 2017 이전의 동작을 살펴본다. DML (SELECT, INSERT, UPDATE, DELETE) 사용하여 임시 테이블을 참조할 임시 테이블이 외부 범위 배치에 의해 생성된 경우에는 실행 마다 DML 문을 다시 컴파일 힌다.

아래 스크립트는 실습를 사용하여 재현할 있다. OuterProc 프로시저에서는 아래와 같은 기능을 한다.

1. 임시테이블 만들기

2. InnerProc 저장 프로시저 호출

 

 

InnerProc 저장 프로시저의 경우 OuterProc에서 생성된 임시 테이블을 참조하는 개의 DML 문이 있다.

3. 임시 테이블에 행을 삽입

4. 임시 테이블에서 행을 리턴

 

 

우리는 임시 테이블을 DML문과 다른 범위로 만들었으며 기존 구현(SQL Server 2019 이전) 대해 임시 테이블 스키마가 실질적으로 변경되지 않음을 신뢰하지 않으므로 실행될 때마다 연관된 DML문이 추가 리컴파일 활동을 하여 CPU 사용률을 높이고 전체 워크로드 성능 처리량을 줄여 성능 저하로 이어질 있다.

 

SQL Server 2019 부터는 불필요한 리컴파일을 피하기 위해 추가 검사를 수행한다.

·       컴파일 타임에 임시 테이블을 생성하는데 사용된 외부 범위 모듈이 연속 실행에 사용된 것과 동일한지 확인한다.

·       초기 컴파일시 변경 DDL (Data Definition Language) 변경 사항을 추적하고 이를 연속 시행을 위한 DDL작업과 비교한다.

결과적으로 보증하지 않은 리컴파일 관련  CPU 오버헤드가 줄어든다.

 

아래 그림은 “OuterProc“ 저장 프로시저를 1,000 (in a loop) 실행하는 16개의 동시 스레드의 테스트 결과를 보여준다. Y축은 발생횟수를 나타내며 파란색 선은 Batch Requests/sec 나타내고 녹색 선은 SQL Re-Compilations/sec 나타낸다.

 

예제에서 기능이 활성화 되면 다음과 같은 결과가 나타난다.

·       Batch Requests/sec (파란색 , 번째 ) 표시되는 처리량 개선

·       전체 작업 시간 단축

·       SQL Re-Compilations/sec (녹색선) 표시되는 리컴파일 감소. ( 번째 테스트 시작시 약간의 증가가 발생하였음)

 

기능은 모든 데이터베이스 호환성 수준에서 SQL Server 2019에서 기본적으로 사용된다. 글을 쓰는 시점에서 기능은 데이터베이스 호환성 수준 150에서 Azure SQL Database에서도 사용할 있지만, 향후 다른 호환성 수준에서도 적용될 있다.

 

 

2019-09-24/ Sungwook Kang / http://sungwookkang.com

 

 

 

Azure SQL, SQL Server 2019, temp table, SQL Re-compilations, Batch Requests, Intelligent query processing in SQL databases

Azure SQL Managed Instance SQL Server 2016 Later에서 대기 통계 분석

 

·       Version : Azure SQL, SQL Server 2016 Later

 

대기 통계(Wait Statistics)는데이터베이스 엔진에서 무언가를 기다리는 쿼리를 식별하는데 도움이 되며 쿼리 지속시간이 이유를 분석할 있는 정보를 나타낸다. 이번 포스트에서는 워크로드가 대기하는 이유와 일부 리소스에서 대기중인 쿼리를 식별하는 방법에 대해서 살펴본다.

 

Azure SQL Managed Instance 사용하면 아래 DMV 사용하여 쿼리가 리소스를 대기하는 이유를 찾을 있다.

·       sys.dm_os_wait_stats : 인스턴스 레벨에서  대기 정보 반환

·       sys.query_store_wait_stats : 데이터베이스 레벨에서 대기중인 쿼리의 실행계획 반환

 

이러한 정보는 DMO/Query Store 사용하여 찾을 있다. 그러나 분석을 쉽게 하기 위해 무료 오픈소스인 QPI 라이브러리를 사용할 있다.

·       QPI  : https://github.com/JocaPC/qpi

 

QPI 라이브러리를 설치하려면 아래 링크에서 SQL Server 버전에 대한 SQL 스크립트를 다운로드 있다. Query Store 의존하기 때문에 SQL Server 2016 이상, Azure SQL 에서 가능하다.

·       QPI Installation : https://github.com/JocaPC/qpi#installation

 

sys.dm_os_wait_stats 인스턴스 시작 이후 또는 통계를 마지막으로 재설정 이후에 대기 통계를 수집하므로 대기 통계의 스냅샷을 작성하거나 최소한 재설정을 해야한다. 아래 스크립트를 실행하여 QPI에서대기 통계를 재설정 있다.

exec qpi.snapshot_wait_stats

 

절차는 인스턴스의 대기 통계를 재설정하고 관리형 인스턴스는 대기 통계를 수집을  시작한다. 워크로드가 실행되는 동안 qpi.wait_stats보기에서 대기 통계 정보를 읽을 있다. 결과를 보면 인스턴스의 기본 대기 통계는 INSTANCE_LOG_RATE_GOVERNON이며 Log Rate Governor 분류 된다. 대기 유형의 영향을 받는 쿼리를 찾으려면 실제 대기 유형 이름이 아닌 쿼리 저정소에서 Category 사용해야 하므로 Category 중요하다. 쿼리 저장소의 대기 통계는 대기 유형별로 기록되지 않는다.

 

 

대기가 발생한 Category 영향을 받는 상위 쿼리를 보려면 qpi.db_query_wait_stats 보기를 사용하여 Category 해당하는 대기 통계를 필터링 있다.

 

예제의 경우 관리형 인스턴스에서 로그 속도 제한에 도달하고 있으며, 원인은 인덱스 재구축으로 인한것일 있다. 데이터베이스가 여러개인 경우 데이터베이스마다 쿼리 저장소가 구성되므로 데이터베이스에서 쿼리를 실행해야한다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/05/analyzing-wait-statistics-on-managed-instance/

 

 

2019-09-23/ Sungwook Kang / http://sungwookkang.com

 

 

Azure SQL, SQL Server Managed Instance, Query Store, QPI, sys.dm_os_wait_stats, sys.query_store_wait_stats

SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인

 

·       Version : SQL Server 2019

 

SQL Server에서 통계정보는 옵티마이저가 실행 계획을 생성할 참고하는 중요한 지표이다. 통계 자동 업데이트가  true 설정된 경우, 데이터의 변경이 특정 임계치 이상되면 자동으로 통계 정보를 업데이트 한다.

·       SQL Server Statistics : http://sqlmvp.kr/140165557766

 

이때 통계 정보를 업데이트하면서 블럭킹이 발생하는데 이전까지는 블럭킹이 발생한것에 대해서 확인할 방법이 없었다. SQL Server 2019 부터는 이러한 문제를 해결하기 위해 새로운 진단 데이터가 도입되었다. 통계 업데이트시 블럭킹을 발생하는 것을 재현하기 위해 아래와 같은 시나리오를 만들었다.

·       자동 통계 업데이트를 트리거하는  SELECT 쿼리를 실행한다.

·       동기 통계 업데이트가 실행을 시작하고 통계가 생성될때 까지 쿼리가 대기한다. (기본적으로 차단됨)

·       동기 통계 업데이트 조작이 완료 까지 쿼리 컴파일 실행이 재개되지 않는다.

시간 동안 쿼리는 동기화 통계 업데이트 작업이 완료될 까지 대기하고 있으며, 문제를 확인하기 어려웠다. 대용량 테이블또는 사용량이 많은 시스템등 통계 업데이트에 시간이 오래 걸리는 경우 원인을 쉽게 확인할 있는 방법이 없다.

 

 

SQL Server 2019에서는 동기화 통계 업데이트로 인해 쿼리가 차단되면  sys.dm_exec_requests에서‘command’컬럼에 (STATMAN) 표시된다. 그리고 통계 업데이트 작업이 완료되면 초기 명령이름으로 돌아간다.

 

또한 새로운 WAIT_ON_SYNC_STATISTICS_REFRESH 대기 유형은 동기 통계 업데이트에서 집계된 대기 시간(블럭) 측정한다. 대기시간 누적은 sys.dm_os_wait_stats 동적 관리뷰에서 확인할 있다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/13/diagnostic-data-for-synchronous-statistics-update-blocking/

 

 

2019-09-20/ Sungwook Kang / http://sungwookkang.com

 

 

SQL Server2019, SQL Statistics, WAIT_ON_SYNC_STATISTICS_REFRESH, sys.dm_os_wait_stats, sys.dm_exec_requests

SQL Server 2019 Log Writer Workers

 

·       Version : SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019

 

SQL Server 2017 숨겨진 스케줄러에서 최대 4개의 Log Writer Worker 활용하여 트랜잭션 로그 처리 활동을 지원한다.

 

SQL Server 2019 버전부터는 하드웨어 성능에 따라 최대 Log Writer Worker 수가  최대 8개까지 증가한다.

;with kgroups AS

(SELECT kgroup_count = COUNT(DISTINCT processor_group)

 FROM sys.dm_os_nodes osn)

SELECT SQLServer_version = SERVERPROPERTY('ProductVersion'), sinfo.scheduler_count,

       sinfo.cpu_count, sinfo.softnuma_configuration_desc, sinfo.socket_count,

    sinfo.numa_node_count, kgroups.kgroup_count

FROM sys.dm_os_sys_info sinfo

CROSS JOIN kgroups;

 

SELECT req.session_id, req.command, sch.scheduler_id, sched_status = sch.[status],

       sch.cpu_id, sch.parent_node_id, osn.memory_node_id, osn.processor_group

FROM sys.dm_exec_requests req

JOIN sys.dm_os_schedulers sch ON req.scheduler_id = sch.scheduler_id

JOIN sys.dm_os_nodes osn ON sch.parent_node_id = osn.node_id

WHERE req.command = 'LOG WRITER';

 

 

 SQL Server 서비스 시작시 시스템 리소스 상태에 따라 Log Writer Worker수를 결정하며  아래 그림은 SQL Server 2019 2Core CP, 2GM RAM에서 실행 하였을때, Log Writer Worker 수를 SQL Server Error Log에서 확인한 것이다.

 

 

여러 Log Writer Worker 허용되지 않는 경우 단일 Log Writer Worker 사용한다. 그렇지 않으면 아래 공식을 사용하여 Log Writer Worker 수를 계산한다.

·       NUMA 노드 X 2

·       NUMA 노드에서 사용사능한 CPU 계산 (affinity mask 설정에 따라 CPU 카운트에 영향을 있음)

 

MAX_LOG_WRITERS 허용에 따라 4 또는 8개가 할당 된다.

 

[참고자료]

·       https://blogs.msdn.microsoft.com/bobsql/2019/02/11/sql-server-log-writer-workers/

·       http://sql-sasquatch.blogspot.com/2019/06/sql-server-2019-ctp-30-max-number-of.html

 

 

 

2019-09-19/ Sungwook Kang / http://sungwookkang.com

 

 

SQL Server2017, SQL Server 2019, Log Writer Worker, 로그 쓰기 워커, NUMA, OS NODE

SQL Server Login Timeout 디버깅

 

·       Version : SQL Server, SQL Server Linux

 

SQL Server 2017 SQL Linux에서 로그인시 랜덤하게 연결이 실패하는 경우가있다. 이번 포스트는 SQL Server Login Timeout 발생하는 원인을 분석하기 위한 디버깅 과정을 소개한다.

 

[Report Symptoms]

·       SQL 인증 AD 기반 로그인 모두에서 무작위 연결 실패

·       서버의 원격 클라이언트 또는 sqlcmd 실행시 디렉토리(/opt/mssql-tools/bin)에서 오류가 발생

·       장애 발생시 예측 없음

·       Non-yielding 스케줄러 보고서

·       SQL Server 2017 CU10 동작이 표시되지 않음

·       SQL Server 2017 CU12 동작을 나타냄

·       서버는 많은 CPU RAM 가진Superdome 상태

 

 

[Health Session Information]

문제를 디버깅하는 방법 하나는 /var/opt/mssql/log/system_health*.xel 파일에 있는 SQL Server 상태 세션을 추적하는 것이다. SSMS (SQL Server Management Studio)에서 XEvent 상태 세션을 보고 조정할 있다.

-          실시간 데이터 시청

-          마우스 오른쪽 ->세션을 조정

-          마우스 오른쪽 -> 스크립트 세션

상태 세션에서는 여러 이벤트 유형이 포함되며, 일부 이벤트 유형에서는 시스템 상태 세션에서 노이즈를 제거하기 위한 조건자가 포함된다.

sqlclr.clr_allocation_failure

sqlclr.clr_virtual_alloc_failure

sqlos.memory_broker_ring_buffer_recorded

sqlos.memory_node_oom_ring_buffer_recorded

sqlos.process_killed

sqlos.scheduler_monitor_deadlock_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded

sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded

sqlos.scheduler_monitor_system_health_ring_buffer_recorded

sqlos.wait_info

sqlos.wait_info_external

sqlserver.connectivity_ring_buffer_recorded

sqlserver.error_reported

sqlserver.security_error_ring_buffer_recorded

sqlserver.sp_server_diagnostics_component_result

sqlserver.sql_exit_invoked

sqlserver.xml_deadlock_report

 

Non-yielding, wait_info* 연결 이벤트는 문제에 필요한 정보를 제공한다. health record 보면 다음과 같은 패턴이 반복적으로 나타난다.

 

시스템 상태에 기록된 이벤트는 Non-yielding 시작, 로그인 타임아웃 종료 Non-yielding 종료를 표시한다.

 

 

[connectivity_ring_buffer_record]

connectivity_ring_buffer_record에서는 세부 정보가 표시된다.

 

연결시도가 실패하면 type=LoginTimers 레코드가 기록되어 로그인 활동에 대한 타이밍을 제공한다. total_login_time_ms = 23309 실패한 로그인 시도가 23.3초임을 표시한다. SSL 교환 시간은 낮았지만 login_trigger_and_resouce_governor_ms 값은 대부분의 시간을 소비했다. find_login_ms 이벤트는 다른 이용자였으며 login_trigger_and_resouce_governor_ms 하위이다. 아래 스크립트를 실행하여 버퍼 항목 관계를 통해서 LoginTimer 대해서 어떤 타이밍에 대한 부모 또는 자식 값인지 확인할 있다.

Select

       *

from sys.dm_os_ring_buffers

where ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

 

버퍼 xml 형식은 상위 하위 관계를 출력한다. LoginTimer  항목은 타이밍 정보를 표시한다. (전체 레코드에서 SSL, SSPI, 읽기, 쓰기 등에 대한 추가 정보가 포함됨)

<Record id="2" type="RING_BUFFER_CONNECTIVITY" time="591262106">

 

  <ConnectivityTraceRecord>

 

    <RecordType>LoginTimers</RecordType>

 

    <LoginTimersInMilliseconds>

 

      <TotalTime>23309</TotalTime>

 

      <EnqueueTime>0</EnqueueTime>

 

      <NetWritesTime>0</NetWritesTime>

 

      <NetReadsTime>0</NetReadsTime>

 

       <TriggerAndResGovTime>

 

        <TotalTime>23266</TotalTime>

 

        <FindLogin>23265</FindLogin>

 

        <LogonTriggers>0</LogonTriggers>

 

        <ExecClassifier>0</ExecClassifier>

 

        <SessionRecover>0</SessionRecover>

 

      </TriggerAndResGovTime>

 

    </LoginTimersInMilliseconds>

 

  </ConnectivityTraceRecord>

 

이름에서 있듯이 SQL Server 로그인 유효성 검사의 일부로 로그인 정보를 검색하고 필요한 경우 AD 연결한다. , 마스터에서 데이터베이스 페이지를 읽고 잠금을 획득하며 도메인 컨트롤러(DC/KDC) 호출한다.

 

 

[waint_info *]

다른 관찰은 시스템 상태 세션이 5초보다 wait_info* 이벤트를 포함하지 않는다는 것이다. 데이터베이스 읽기, 잠금 또는 선제적외부 호출을 5 이상 기다리는 경우 이벤트가 기록되었기 때문에 waint_info* 이벤트의 부족이 드러난다. 이는 데이터베이스 읽기, 잠금 또는 preemptive, 외부 호출에 문제가 발생하지 않을 것임을 의미한다.

 

 

[Network Trace]

네트워크 추적을 살펴보면 문제를 이해하는데 도움이 된다.

 

 

[Normal SQL Login, execute select @@VERSION and logout]

다음은 유요한 SQL Server 로그인의 예이다.

 

1.       ODBC CLIENT에서 LINUX SQL Server(SYN)로의 TCP 연결 설정

2.       TDS 사전 로그인 교환

3.       512 바이트 응답 (데이터베이스 컨텍스트 XXXX, 기본언어 XXXX, 테스트 크기 XXXX 사용)

4.       SQL Batch (@@VERSION 선택)

5.       FIN-TCP 연결종료 분리

 

 

[Failing Login]

다음은 실패한 SQL Server 로그인의 예이다.

1.       ODBC CLIENT에서 LINUX SQL Server(SYN) TCP 연결 설정

2.       TDS 사전 로그인 교환

3.       로그인 응답

4.       연결 끊김 상태를 유지하면서 다른 활동 없이 시간이 경과하기를 시작

5.       RST-클라이언트가 TCP연결을 재설정함(OS오류 : 10054)

가지 실패 변형이 있었지만 로그인 응답을 처리하는 가장 일반적인 대기가 발생.

 

 

[Network Layer Overloaded]

실패한 SQL Server 로그인과 달리 네트워크 계층이 오버로드 되어 SQL Server 대한 요청을 완료하지 않은 경우 네트워크 추적이 나타난다. 일반적으로 SYN 재전송이 시작된다.

 

 

[Attempting A Reproduction]

이제 패턴에 대한 아이디어를 얻었다.  문제를 재현하는 것은 문제를 추적하고 올바르게 해결되도록 하는데 도움이 된다. Ostress툴을 사용해서 재현할 있다.

"C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -Usa -Pxxxxxxx  -Q"--" -Stcp:10.193.17.114 -q -n64 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output

 

-          Use -U and -P for SQL authentication

 

-          Use -E for AD authentication

 

-          -q ignores query results

 

-          -S target server name forcing tcp protocol

 

-          -Q with a query of --  (comment only to do nothing)

 

-          -n controls the number of threads

 

-          -r controls the number of loops for each thread

 

-          -c the control file

 

-          -T146 disable encrypted TDS

 

-          -o output location

 

설정 파일에서 DisconnectPct=100.0 설정한다. 이를 통해 ostress 연결, 쿼리 실행,연결 끊기에 대해서 -n 스레드에 대해서 -r 반복하도록 지시한다. XEvent에서  로그인 로그 아웃 이벤트를 추적하면 연결 연결 끊기 활동을 있다. 다음은 테스트가 수행하는 로그인 로그아웃 비율을 확인하기 위한 간단한 쿼리이다.

drop table #before

go

drop table #after

go

 

select * into #before

from sys.dm_os_performance_counters where counter_name in ('Logins/sec', 'Logouts/sec')

 

waitfor delay '00:00:10'

 

select * into #after       from sys.dm_os_performance_counters where counter_name in ('Logins/sec', 'Logouts/sec')

 

select b.object_name, a.cntr_value - b.cntr_value as , (a.cntr_value - b.cntr_value) / 10.0 as [Rate/sec]

       from #before b

              inner join #after a on a.counter_name = b.counter_name

 

연결 경로를 연습하는 cmd 파일 스크립트로 시작했다.

start "ostress" "C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -E -Q"--" -Stcp:xxxxxxx -q -n32 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output

 

start "ostress" "C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -E -Q"--" -Stcp:xxxxxxx -q -n32 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output2

 

:top

 

       sqlcmd -E -Stcp:xxxxxxx -Q"select @@VERSION"

 

       ping -n 10 xxxxxxxx

 

goto top

 

 

[Windows TCP Settings]

2TB, 144CPU 시스템에서 스트레스 수준을 높이면서 TCP 문제가 발생했다. 동안 SQL Server에서 트래픽이 발생하지 않고 갑자기 실행된 스트레스가 발생했다. 활동이 중단되는 동안, local /opt/mssql-tools/bin/sqlcmd 연결이 계속 작동했다. Linux SQL Server시스템에서 로컬로 실행되는 아래 bash 스크립트를 사용하여 원격 연결이 실패한 동안 성공적인 연결을 확인했다.

while [ 1 -gt 0 ]

 

do

 

        echo

 

        echo $(date)

 

 

 

        ./sqlcmd -Usa -Pxxxxxxx -Q"select @@VERSION" -Stcp:.

 

        sleep 2

 

done

 

Windows 클라이언트 TCP 포트가 부족하여 중단 연결 동작이 발생했다.

netsh int ipv4 show dynamicport tcp

netsh int ipv6 show dynamicport tcp

 

netsh int ipv4 set dynamicport tcp start=10000 num=50000

netsh int ipv6 set dynamicport tcp start=10000 num=50000

 

이제 네트워크 포화 상태 (재전송이 있는 SYN) 보기 시작할 까지 높은 수준의 연결 연결 끊기 작업을 시작했지만 Non-yielding 패턴은 보이지 않았다.

 

[Disrupt The Domain Controller (Keytab)]

FindLogin 도메인 컨트롤러 호출을 수행한다는 것을 알면 다음에 도메인 컨트롤러 VM 일시 중지하여 연결시간 초과로 인해 Non-yielding 발생하여 FindLogin 많이 기록되도록 하였다.  KDC/DC 쿼리 동작을 나태나는 키탭 파일(잘못된 DC 대상) 항목을 추가할 있다. 그런 다음 FindLogin에서 Linux, AD 활동을 위해 SSSD 또는 LDAP 호출하기 전에 preemptive으로 전환되지 않는 가지 코드 경로를 찾았다. SQL Server preemptive으로 전환하지 않았기 때문에 시스템 상태 로그에 wait_type * 레코드가 없다.

 

[Non-Yielding Scheduler]

Non-Yielding 문제와 관련된 주요 행동이다. 아래 다이어그램은 발생한 문제를 강조하는데 도움이 된다.

 

 

소유한 작업자가 SQL Server 스케줄러를 끄지 않고 도메인 컨트롤러를 호출(preemptive)했다. 사전 로그인 시퀀스를 통해 SQL  인증 AD 인증 작업자가 일부 생성되어 있다. 소유 작업자가 yield일때 실행 가능한 목록은 다음에 실행할 작업자를 결정하는데 사용된다. 실행 가능 목록이 스케줄러의 최대 작업자 수에 도달하면 요청이 큐에 대기된다.

KDC/DC 쿼리가 5초이상 걸리고 Scheduler Monitor(NUMA노드당 하나) non-yielding condition (BEGIN) 보고한다. SchedulerMonitor 5초마다 진행률을 확인하여 통과 횟수를 증가 시킨다. 통과 횟수가 한계(12, 60) 도달하면 non-yielding 상황이 보고된다.

테스트의 경우 non-yielding 일반적으로 5~15 동안 지속되었으며 시스템 상태 세션에서 non-yielding BEGIN END 이벤트가 브라켓 연결시간 종료 이벤트와 함께 보고되었다. 그런 다음 다양한 로그인 시간 초과가 트리거 된다. 클라이언트가 연결을 감지하면 시간 초과에 도달하면 TCP 연결을 닫기 위해 FIN 전송된다. 일반적으로 오류 10054 SQL Server 버블링-연결이 닫힌다.

·       로그인이 사전 로그인을 처리하는 중이라면 버퍼 항목에 SSL 읽기 등의 시간과 연결 끊김이 표시될 있다.

·       로그인이 DC 호출에 멈춘 경우 일반적으로 찾기 고르인 시간이 누적된다.

·       로그인이 스케줄러에 대기된 경우 로그인 실패 이벤트에 대해 1 또는 0ms 경과 시간이 표시된다. 작업자가 작업을 받을수 있을 때까지 클라이언트는 이미 10054 보냈으며 SQL Server 로그인을 처리하려고 하면 연결이 닫히고 실패한것으로 감지된다.

 

[SQL Authentication Impact]

이제 AD 인증이 어떻게 SQL 인증, 로그인 시간 초과로 이어질 있는지 있다. SQL 인증 요청이 지연된 KDC/DC 쿼리와 동일한 스케줄러에 있는 경우 리퀘스트에 영향을 준다.

 

[Other Scheduler Impacted]

코드 경로는 많은 SQL 잠금에 관여하지 않으므로 KDC/DC 쿼리를 실행하는 정지된 사용자는 일반적으로 다른 스케줄러 해당 스케줄러와 관련된 로그인에 영향을 미치지 않는다. 다른 스케줄러는 가지 방식으로 KDC/DC쿼리의 영향을 받는다.

1.       다른 스케줄러가 AD로그인을 처리하려고 시도하고 KDC/DC 쿼리가 느리면 쿼리에 동일한 동작이 발생할 있다.

2.       non-yielding 감지되면 덤프가 호출된다. 덤프는 SQL Server 프로세스를 일시 중단한다. 덤프 시간이 로그인 시간 초과를 초과하면 클라이언트가 연결을 닫고 일반적으로 SQL Server 스케줄러에서 10054 TCP 오류가 발생한다.

 

[Recap]

버그는 도메인 컨트롤러를 쿼리하기 전에 FindLogin 코드가 preemptive되지 않아 지연 로그인 시간 초과를 초래한다. 수정된 경우 KDC/DC 쿼리에서 지연이 발생한 경우 개별 AD 로그인 시도에 여전히 로그인 시간 초과가 발생하지만 다른 로그인 쿼리는 이상 영향을 받지 않는다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2019/02/10/sql-mysteries-sql-server-login-timeouts-a-debugging-story/

 

 

 

 

2019-09-18/ Sungwook Kang / http://sungwookkang.com

 

 

SQL Server2017, XEvent, Login TimeOut, SQL Authentication Impact, FindLogin, Non-Yielding Scheduler, SQL Login, SQL TimeOut, SQL Logout

SQL Server Worker Thread 기본 계산

 

·       Version : SQL Server

 

SQL Server 2017부터는 소규모 환경을 고려하여 SQL Server 기본 worker thread  수가 약간 변경되었다. 소규모 환경에서 SQL Server 실행하는 경우 SQL Server worker thread 줄인다. X64 설치의 경우 sp_configure ‘max worker threads’ 값을 0으로 설정하면 SQL Server 아래 계산 공식을 사용한다.

Default

512

Small Environment

256

 

소규모 환경에서는 SQL Server 항상 worker thread  256 사용한다. 환경에서 실행하면 worker thread 512 기본값이며 CPU 수에 따라 조정된다.

CPU 4개보다 많으면 worker thread  수는512 까지 증가하며,  CPU 수에 따라32또는 16 증가하여 최대 512까지 증가한다. 만약 시스템에 64 이상의 CPU 있으면 추가 worker thread CPU 32 worker thread 증가한다.

 

 

이러한 설계는 CPU 메모리 리소스가 적은 소규모 환경 CPU 메모리를 사용하는 시스템을 고려한다. worker thread 보이는 스케줄러에만 적용된다. 숨겨진 스케줄러 DAC (Dedicated Admin Connection) 스케줄러는 계산에 영향을 받지 않는다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2019/02/10/sql-server-worker-thread-default-calculation/

 

 

 

2019-09-17/ Sungwook Kang / http://sungwookkang.com

 

 

SQL Server2017, worker thread

SQL Linux fsync 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?)

 

·       Version : SQL Server Linux

 

 

PostgreSQL에서 fsync() 오류처리는 안전하지 않으며 XFS에서 데이터 손실이  발생할 있다는 내용이 있다.

·       PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS : https://www.postgresql.org/message-id/flat/CAMsr%2BYE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG%2BvzHun1EqMxyQg%40mail.gmail.com#CAMsr+YE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG+vzHun1EqMxyQg@mail.gmail.com

 

이번 포스트는  SQL Server에서도 Linux 동일한 문제가 발생하는지 유효성을 검증하는 내용으로,  SQL Server 경우 O_DIRECT 사용하기 때문에 PostgreSQL 같은 문제가 발생하지 않는다.

·       Direct I/O : https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/5/html/global_file_system/s1-manage-direct-io

 

응용 프로그램이 버퍼된 쓰기를 수행하고 성공을 수신한다. (이는 안정된 미디어 파일 시스템 캐시에 데이터를 저장할 있음을 의미한다.) fsync/fdatasync 데이터가 안정적인 미디어에 저장되도록 하는데 사용된다. 안정적인 미디어 쓰기는 동기화 작업중에 발생하며 EIO 오류를 보고하는 여러가지 이유로 (디스크 공간부족, SAN 연결 끊김 ) 실패할 있다.

·       fsync/fdatasync : http://man7.org/linux/man-pages/man2/fdatasync.2.html

 

 

 

위의 PostgreSQL링크에 설명된 문제는 동기화에서 오류를 반환하지만 캐시된 페이지의 상태를 지울수 있다 것이다. 다음 동기화는 캐시된 쓰기가 안정적인 미디어로 플러시 되지 않지만 응용 프로그램에 알려졌다는 것을 의미하는 ESUCCESS 반환한다.

 

데이터베이스 응용프로그램이 백업 파일을 열어 파일 시스템 캐싱 (~_O_DIRECT) 허용한다고 가장헌다. SQL Server 작업을 수행하지 않으며  실제로 Linux SQL Server에서 작업을 수행할 없도록 했다.

1.       버퍼링된 I/O 사용하여 백업이 시작된다.

2.       백업이 진행되면서 파일 시스템 캐시에 쓰기가 발생한다.

3.       파일 시스템 캐시 쓰기가 발생하는 동안 동기화에 대한 외부 호출이 발생한다.

4.       백업에 속한 버퍼에서 동기화쓰기 오류가 발생한다. 데이터베이스 응용프로그램 외부의 응용 프로그램에서 동기화를 호출하여 데이터베이스 응용프로그램이 실패를 인식하지 못한다.

5.       백업이 끝나면 데이터베이스 응용 프로그램에서 fdatasync 실행하고 EIO 반호나하는 대신 ESUCCESS 반환된다.

6.       fdatasync 성공적으로 완료되면 미디어 백업이 안정적으로 강화되고 데이터베이스 응용 프로그램이 트랜잭션 로그의 비활성 부분을 자른다.

7.       데이터베이스에 이상 트랜잭션 조작 또는 복구를 수행 적절한 로그 레코드가 없고, 일단 유효하다고 생각된 백업파일이 유효하지 않는다.

 

문제는 SQL Server 데이터베이스, 로그 백업파일에 영향을 미치지 않는다. SQL Server 파일 시스템 캐시를 무시하기 위해 O_DIRECT 사용하여 이러한 파일 형식을 연다. Linux SQL Server 강제 플러시 모드에서 실행 중인 경우에도 파일은 O_Direct 열리므로 문제가 발생하지 않는다.

 

 

[참고자료]

·       SQL Server Linux: fsync and Buffered I/O : https://blogs.msdn.microsoft.com/bobsql/2018/12/18/sql-server-linux-fsync-and-buffered-i-o/

 

 

2019-09-16 / Sungwook Kang / http://sungwookkang.com

 

 

SQL Server, SQL Linux, File System, Windows, Linux, fsync, fdatasync, O_Direct

SQL Server SQL Linux에서 인스턴스 파일 초기화 차이점

 

·       Version : SQL Server, SQL Server Linux

 

SQL Server 로그 파일 또는 데이터 파일이 증가하거나 새로 작성될때, 인스턴트 파일 초기화 작업을 진행한다. 이번 포스트에서는 인스턴스 파일이 초기화 될때, 기본 파일 시스템 구현과 Windows Linux 간의 동작 차이를 알아본다.

·       Database File Initialization : https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

 

SQL Server 데이터 로그 파일을 만들거나 확장(증가)할때 아래 API 호출 한다.

·       CreateFile : 파일 작성 또는 열기

·       SetEndOfFile : 파일 크기를 설정하고 I/O 장치에서 공간을 확보

·       SetFileValidData : 유효한 데이터 크기 설정

 

파일이 로그 파일(LDF) 경우 SQL Server 알려진 패턴값을 할당된 공간에 쓴다. 데이터 파일 (MDF, NDF) 경우 SQL Server 인스턴스 파일 초기화 추적 플래그 1805 설정을 확인하여 할당된 공간에 패턴값을 쓸지 여부를 결정한다.

TF 1805 : 데이터 파일에 대한 인스턴스 파일 초기화를 비활성화 한다.

참고 : 스탬핑은 일반적으로 최적의 성능과 Windows Linux 파일 시스템 페이지 블록크기 정렬과 정렬을 위해 4MB 청크로 수행

 

[Windows]

Windows 파일 시스템(NTFS, RTFS)에는 파일을 즉시 초기화 하기 위한 개의 멤버키가 있다.

·       EOF : 파일 위치

·       VDL : 유효한 데이터 길이 위치

 

Empty File

파일이 처음 작성될  EOF VDL 모두 파일의 시작을 가리킨다.

·       EOF = 0

·       VLD = 0


     

 

SetEndOfFile

SetEndOfFile 파일을 확장하여 I/O 장치에서 공간을 확보하고 EOF 값을 조정한다. VLD 값은 변경되지 않은 상태로 유지된다.

·       EOF = 10G

·       VLD = 0

   

SetFileValidData

SetFileValidData VDL 이동하는데 사용된다. VDL 기록된 것으로 간주되는 경우(쓰기가 수행되지 않은 경우에도) VDL 오프셋 이전의 모든 데이터와 VDL 이전의 공간 읽기는 I/O 장치에서 오래된 데이터를 반환할  있다. VDL 이후의 데이터는 유효하지 않은 것으로 간주되며 읽기 요청에 대해 0 리턴된다.

·       EOF = 10GB

·       VDL = 1GB

참고 : 보안 고려사항과 관련된 내용은 위의Database File Initialization 문서를 참고한다.

 

Write beyond the current VDL (WriteFile*)

VDL 오프셋 이상으로 쓰기가 발생하면 WindowsVDL 이동하여 쓰기를 수용하고 이전 VDL 쓰기 요청 시작 사이의 오프셋에 0 쓴다.

·       EOF = 10GB

·       이전 VDL =1 GB

·       VDL = 5GB

 

Instant File Initialization (New File)

VDL 증분 변경 대신 SQL Server SetEndOfFile 빠른 할당 기능을 사용하고  동일한 오프셋으로 SetFileValidData 호출한다. VDL이전의 모든 데이터는 Windows 파일 시스템에 의해 쓰여진(유효한) 것으로 간주된다. 인스턴트 파일 초기화가 활성화  경우 Windows 0 쓰지 않으며 SQL Server 데이터 파일의 패턴을 스탬프 처리 하지 않는다. 내부 SQL Server 데이터베이스 할당 구조는 SQL Server 데이터 파일 할당  유효한 데이터 읽기 활동을 추적한다.

 

Instant File Initialization (Grow)

인스턴스 파일 초기화를 사용하여 파일을 확장하면  오프셋으로 SetEndOfFile  SetFileValidData 수행된다. Windows   오프셋과 이전 오프셋 사이의 데이터를 유요한 것으로 취급한다.

 

 

[Linux]

Fallocate(http://man7.org/linux/man-pages/man2/fallocate.2.html) 시스템 호출(ABI) 사용한 Linux 지원 파일 할당 Windows API호출은 아래와 같이 Linux ABI 호출에 매핑 된다.

·       CreateFile : Linux 사용

·       SetEndOfFile : Linux fallocate 사용

·       SetFileValidData : Linux Noop

 

Windows Linux 파일 시스템의 주요 차이점은 유효한 데이터 길이( VDL) 아닌 범위를 추적한다. Linux에서 범위에는 I/O 장치에 쓰여 졌는지 여부를 나타내는 플래그가 포함된다.

Empty File

파일이 처음 작성될   EOF= 0이고 포함 범위는 기록되지 않도록 (N)으로 설정된다. 쓰지 않은 범위의 읽기는 Linux에서 항상 0 반환한다. LinuxI/O 장치를 사용하지 않지만 단순히 쓰지 않는 범위로 추적된 공간에 대해 리턴 버퍼를 0으로 채운다.

 

힌트 : 익스텐트 크기  조정에 대해서는 Linux 파일 시스템 설명서를 확인한다. 기본 크기는 일반적으로 최적의 성능을 위해 SQL Server 페이지는8K  64K 범위 경계에  맞는 메모리 페이지 크기 경계(주로4K) 정렬된다.

 

SetEndOfFile

파일 크기 증가는 대체 호출로 발생한다. Linux I/O 장치에서 공간을 확보하고  EOF 추적 범위 메타 데이터를 설정하여 기록되지 않음을 나타낸다. Fallocate SetEndOfFile Windows 파일 시스템의 공간을 확보하는 것처럼 공간을 확보하여 대용량 파일을 빠르게 생성할  있다. 차이점은 SetFileValidData이다. Linux 실제 쓰기 없이 범위 추적을 쓰기 설정하는 기능을 제공하지 않는다.

 

 

성능 고려 사항 : 대상 파일 시스템에 대해 fallocate 지원되지 않으면 SQL Server ftruncate 사용한다. 이름과 달리 ftruncate ABI 파일을 늘리는데 사용될  있지만  프로비저닝된 조장이다.(공간은 메타데이터만 업데이트 되지 않는다.) ftruncate 필요한 경우 실제 공간을 확보하고 제공하기 위해 SQLPAL 파일에 0 쓴다. SQLPAL 프로세스에 대한 오류가 없고 읽기 동작이 없다.

 

Write

 번째 쓰기가 수행되면 범위에 대한 메타 데이터도 업데이트 된다. 쓰기  쓰기 되지 않은 데이터를 추적하기 위해 익스텐트를 분할하거나Linux 커널에 의해 확장된 쓰기는 디스크의 공간에 0 쓰므로 전체 익스텐트가 쓰기 된것으로 표시될  있다.

 

참고 : 대부분의 Linux 파일 시스템에서는  번의 쓰기 요청이 발생하지만 쓰기 크기  오프셋 정렬에 따라  커질수 있다. (데이터 파일 요청1개와 메타데이터 변경 요청 1)

 

 

Windows 에서 SetFileValidData 단일  메타 데이터 작업이다. VDL 설정되면 쓰기(순차 또는 임의) VDL == EOF 추가 메타 데이터 업데이트가 필요하지 않다. Linux에서 쓰기에는 데이터 쓰기 메타 데이터 쓰기가 필요한 익스텐트 업데이트가 필요하다. Linux 또는 Windows에서 가능한 빨리 파일을 쓰고 확장할 있다. 그러나 Linux에서 처음 쓰기를 수행하면 메타 데이터가 유지관리 된다.

·       데이터베이스에서 쓰기 속도가 중요한 경우 익스텐트 파일 초기화를 사용하고 번째 쓰기에 추가 오버헤드가 발생하도록 한다.

 

참고 : 대부분의 쓰기 작업은Checkpoint 또는 Lazy Write 같은 백그라운드 프로세스로 수행되므로 SQL Server에서 오버헤드를 숨기는 경우가 많다. 활성 SQL Server 세션에서 쓰기가 발생할 있으므로 대량 로드는 예외이다.

 

·        쓰기 속도를 늘릴수 있는 경우 -T1805 사용하면 데이터베이스가 쓰기 확장 중에 데이터파일 공간을 스탬핑 되도록 있다. 스탬핑은 청크로 최적화되어 있으며 번째 쓰기 데이터 메타 데이터 작업이 발생하는 쓰기 경로가 된다. 위치가 기록(스탬프) 되면 이상 추가 메타 데이터 쓰기가 필요하지 않다.

 

참고 : 파일 시스템이 fallocate 지원하지 않으면 SQLPAL 의해 파일에 0 기록된다. 로그 파일(LDF) 알려진 패턴(0으로 작성) 표시하며 SQLPAL 공간을 0으로 채울때 메타데이터가 이미 업데이트 되었으므로 데이터 파일에 대한 인스턴스 파일 초기화를 안전하게 유지할 있다.

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/12/10/sql-server-instant-file-initialization-setfilevaliddata-windows-vs-fallocate-linux/

 

 

2019-09-13 / Sungwook Kang / http://sungwookkang.com

 

 

SQL Server, SQL Linux, File System, Windows, Linux, SetFileValidData (Windows) vs fallocate (Linux), T1805, SetEndOfFile, SetFileValidData, VDL

 

BCP 실행시 동일 세션에서 여러개의  BULK INSERT 문으로 표시되는 이유

 

·       Version : SQL Server

 

SQL Server에서 BCP 명령을 사용하여 대량의 데이터를 로드할때, sys.dm_exec_requests 항목을 모니터링 해보면 command 항목에 BCP 대신 BULK INSERT 라고 표시되어 있다. 아래 표를 보면 동일한 세션에 BULK INSERT라고 여러개의 작업이 표시된 것을 확인할 있다.

cpu_time

total_elapsed_time

writes

session_id

start_time

command

1387

1396

32

51

2018-08-07 00:45:42.670

BULK INSERT

1930

1941

66

51

2018-08-07 00:46:02.087

BULK INSERT

632

638

32

51

2018-08-07 00:46:23.313

BULK INSERT

 

단일 BULK INSERT 대신 동일한 세션에 BULK INSERT 배치가 여러번 발생하는 이유는 무엇일까? BCP 명령을 사용할때, 옵션 -b (Batch Size) 지정하면서 배치크기에 도달할때까지 행이 스트링되고 커밋이 발생한다. 그리고 후속 행에 대해서 새로운 배치가 실행된다. 따라서 BCP 아닌 일련의 BULK INSERT 작업이 표시되는 것이다.

 

[참고자료]

·       https://blogs.msdn.microsoft.com/bobsql/2018/08/07/sql-mysteries-tracing-bcp-might-fool-you/

·       https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

 

2019-09-12 / Sungwook Kang / http://sungwookkang.com

 

 

SQL Server BCP, Bulk Load, BULK INSERT

삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 조치해야할 사항)

 

·       Version : SQL Server

 

도메인 [DOMAIN\group] 멤버 [DOMAIN\user] 있고 [DOMAIN\group] SQL Server 인스턴스에 대한 로그인 권한이 있는 그룹이 있다고 가정한다. “Drop Login [DOMAIN\group]” 실행하면 [DOMAIN\user] 사용자는 이상 SQL Server 액세스 없을 것으로 예상했다. 그러나 아래와 같은 결과가 나왔다.

·       [DOMAIN\user] 새로운 연결은 허용되지 않음

·       기존 연결된 사용자는 계속 작동함 (기존의 [DOMAIN\user] 연결에는 그룹 구성원 권한이 캐시되므로 해당 값을 새로 고칠때 까지 연결을 통해 쿼리를 계속 실행할 있다.)

 

“DROP LOGIN [DOMAIN\user]” 실행될 [DOMAIN\user] 로그인으로 추가되어있고 SQL Server 연결이 되어 있는 경우 다음과 같은 오류 메시지와 함께 명령이 실패 한다.

DROP LOGIN” command fails with error “Could not drop login 'DOMAIN\user' as the user is currently logged in.

 

 [DOMAIN\user] 연결이 해제되면  “DROP LOGIN [DOMAIN\user]” 성공하고 사용자는 이상 SQL Server 액세스할 없다.

[DOMAINO\group] 로그인으로 추가되고 “DROP LOGIN [DOMAINO\group]” 명령을 실행하면 [DOMAINO\user] 로그인 경우에도 “DROP LOGIN”명령이 성공한다. 그러나 [DOMAIN\ user] 여전히 로그인되어 있는지 확인하기 위해해서는 아래 스크립트를 실행하여 확인할 있다.

select login_name from sys.dm_exec_sessions where login_name = 'DOMAIN\user'

 

"DROP LOGIN <loginName>" 실행하면 SQL Server <loginName> 계정에 대한 활성 연결이 있는지 확인한다. 로그인에 대해 활성화 연결이 없으면 로그인이 제거되고 명령이 성공한다. 그러나 <loginName> AD 그룹 경우 SQL Server 그룹의 구성원 활성 로그인이 있는지 확인하지 않는다. [DOMAIN\ group] 삭제하면 [DOMAIN\user] 로그인되어 있고 [DOMAIN\ group] 구성원 경우에도 [DOMAIN\ group] 대한 활성 연결이 없기 때문에 명령이 성공한다. 실제로 [DOMAIN\ user] 권한있는 작업 ( : 로그인 생성) 수행하거나 연결을 재설정 ( : sp_reset_connection 로그 아웃 다시 로그인) 때까지 연결 상태를 유지한다. 이때 SQL Server [DOMAIN\ user] 권한을 확인하고 이상 유효한 로그인이 없는지 확인하고 연결을 끊는다. 이러한 연결을 강제로 재설정 있다. 그룹 로그인을 삭제 아래 스크립트를 실행한다.

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'DOMAIN\user'

 

스크립트를 실행하면 [DOMAIN\ user] 연결의 모든 세션 ID 보여준다. 이제 KILL <session_id>”를 실행하여 강제로 사용자 연결을 종료한다. [DOMAIN\user] 세션은 강제 종료된 상태이기 때문에 쿼리를 실행할 이상 유효한 로그인이 없는 상태여서 다시 연결을 해야한다. 이때 그룹이 삭제되어 있기 때문에 연결할 없게 된다.

연결 자체는 sp_reset_connection 호출하여 스레드가 로그 아웃했다가 SQL Server 다시 로그인하도록하여 권한 업데이트를 강제 수도 있다. 연결이 영구적이며 지속적으로 로그 아웃했다가 다시 로그인하여 권한 검사를 수행하지 않기 때문에 연결 풀링에 유용하다. 풀링 연결을 검색 sp_reset_connection 호출하면 로그인 권한이 취소 경우 연결이 실패된다.

 

그룹에 대한 작업 권한이 취소되면 구성원 사용자의 권한이 즉시 취소된다. 예를 들어 [DOMAIN\group] "foo"테이블에 대한 선택 권한이 있고 "REVOKE SELECT ON foo TO [DOMAIN\ group]" 실행 경우 [DOMAIN\user] 즉시 "foo" 대한 선택 권한을 잃게된다. 아래 스크립트를 사용하여  실습 가능하다.

# Login as an admin user to SQL Server and add [DOMAIN\group] as a login

#

CREATE LOGIN [DOMAIN\group] FROM WINDOWS

GO

 

# In another terminal, login to SQL Server as [DOMAIN\user] and run a query to show you have access

#

SELECT @@VERSION

GO

 

# Switch back to your admin terminal and drop the group login. It will succeed even though [DOMAIN\user] is connected.

#

DROP LOGIN [DOMAIN\group]

GO

 

# Switch back to your [DOMAIN\user] window and check you still have access

#

SELECT @@VERSION

GO

 

# Switch to the admin terminal and kill the connection

#

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = DOMAIN\user

GO

 

KILL <session_id from query above>

GO

 

# Switch to your [DOMAIN\user] window to see they no longer have access. This command should not return the version information.

#

SELECT @@VERSION

GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/08/03/sql-server-mysteries-the-case-of-the-dropped-ad-group-login/

 

 

2019-09-11 / Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server Login, SQL Server Account, Active Directory, AD, DROP LOGIN, CREATE LOGIN

QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance 로그쓰기 사용량 확인

 

·       Version : Azure SQL

 

Azure SQL Managed Instance Azure 클라우드에서 호스팅되는 완전히 관리되는 SQL Server 인스턴스이다. 관리형 인스턴스에는 최대 로그쓰기 처리량 성능에 일부 제한이 있어 워크로드 속도가 느려질 있다. 이번 포스트에서는 관리형 인스턴스에서 쓰기 로그 처리량 문제를 식별하는 방법에 대해서 살펴본다.

 

Azure SQL Managed Instance에는 최대 로그 쓰기 속도와 같은 가지 기본 제공 리소스 제한이 있다. 로그 쓰기 제한을 도입한 이유는 로그 백업이 들어오는 데이터를 포착할 있어야 하기 때문이다.

·       Overview Azure SQL Database managed instance resource limits : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-resource-limits#service-tier-characteristics

 

이번 포스트에서는 QPI 라이브러리를 사용하여 관리형 인스턴의 대기 통계를 쉽게 분석하고 있다.  QPI 라이브러리를 설치하려면 아래 링크로 이동하여 SQL Server 버전에 대한 SQL 스크립트를 다운로드 한다. QPI 라이브러리는 Query Store 보기에 의존하기 때문에 SQL Server 2016이상, Azure SQL 에서 사용이 가능하다.

·       Download QPI(Query Performance Insights) : https://github.com/JocaPC/qpi#installation

Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.

 

라이브러리를 사용하면 대기 통계의 스냅샷을 쉽게 작성하고 잠시 기다렸다가 대기 통계 값을 읽을 있다.

exec qpi.snapshot_wait_stats;

 

waitfor delay '00:00:03';

 

select *

from qpi.wait_stats

order by wait_time_s desc;

 

 

결과를 살펴보면, 관리 인스턴스의 태스크가 INSTANCE_LOG_RATE_GOVERNOR 대기 유형을 기다리고 있음을 있다. 스크립트를 주기적으로 실행하여 이러한 문제쿼리를 식별할 있다.  또한 qpi에서 제공하는 다양한 명령어를 사용하여 관리형 인스턴스의 IO 성능을 분석하여 병목 현상을 식별할 있다.

·       qpi.snapshot_file_stats 프로시저는 sys.dm_io_virtual_file_stats DMV 함수에서 I/O 통계 스냅샷을 작성한다. sys.dm_io_virtual_stats에는 누적 정보가 포함되어 있으며 최근 시간 간격으로 샘플을 계산해야 하므로 스냅샷을 작성해야 한다.

·       qpi.file_stats보기는 마지막 스냅샷 이후 파일 통계를 가져온다. qpi sys.dm_io_virtual_file_stats 데이터를 기반으로 하는 IOPS 처리량등 여러 계산이 포함된다.

 

아래 스크립트는 파일 유형(LOG/DATA)별로 분류된 인스턴스의 쓰기 처리량(MB/s) IOPS 요약한다.

exec qpi.snapshot_file_stats;

 

waitfor delay '00:00:01';

 

select

       type,

       write_mbps = sum(write_mbps),

       iops = sum(iops)

from qpi.file_stats

group by rollup (type);

 

 

결과를 살펴보면 현재 인스턴스에서는 47.5MB/s 로그파일 쓰기와 1226 IOPS 사용하고 있음을 있다. Azure 설명서에서 리소스 제한에 대한 설명을 보면  ~48MB/s 현재 사용되는 인스턴스의 리밋이며, 이러한 리밋으로 인해서 INSTNACE_LOG_RATE_GOVERNOR 대기 통계가 발생하는 것이다. 인스턴스의 최대 사용량 리밋으로 인해서 성능 저하가 발생하지 않도록QPI 스크립트를 주기적으로 실행하여, 인스턴스의 리밋과 QPI결과값을 비교하여,모니터링 있도록 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/identify-log-write-limits-on-azure-sql-managed-instance-using-qpi-library/

 

2019-09-10 / Sungwook Kang / http://sungwookkang.com

 

Azure SQL, QPI, Query Performance Insights, SQL Server 2016, Query Store

ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행

 

·       Version : SQL Server, Azure CosmosDB

 

Azure CosmosDB 기존의  SQL Server 같은 클래식 데이터베이스에서 CosmosDB 컬렉션을 조회할 있는 ODBC 드라이버를 제공한다. 이번 포스트에서는 Transaction-SQL 사용하여 SQL Server에서 CosmosDB 컬렉션을 쿼리하는 방법을 설명한다.

 

SQL Server에서 CosmosDB 쿼리하는 이유는 무엇일까? CosmosDB 문서 기타 비관계형 데이터 유형을 저장 검색하고, 필터링하고 정렬할 있는 SQL / API 제공한다. 그러나 경우에 따라 GROUP BY, HAVING, 분석 기능이 있는 복잡한 쿼리를 실행하거나 CosmosDB 비관계형 데이터를  SQL Server 테이블에 저장하는 데이터와 조인해야 하는 경우가 있다. 경우 CosmosDB 데이터를 쿼리하기 위해 Transaction-SQL 모든 기능을 활용할 있다.

 

[CosmosDB 설정]

먼저 CosmosDB계정을 생성하고 컬렉션에 실습할 데이터를 추가 한다. 데이터베이스 WWI Orders 라는 콜렉션을 생성하고, 개의 문서를 추가한다.

 

실습 데이터 등록이 완료 되었으면, SQL API 사용하여 컬렉션에 액세스한다.

 

[드라이버 설정]

SQL Server 설치된 컴퓨터에 CosmosDB ODBC 드라이버를 설치해야한다. 드라이버를 설치하면 DSN에서 ODBC 소스를 설정하고 연결을 테스트해야한다.

·       ODBC 드라이버 다운로드 : https://aka.ms/cosmos-odbc-64x64

 

 

[CosmosDB 쿼리]

모든 설정 후에 기존 SQL Server에서 OPENROWSET 함수를 사용하여 CosmosDB DSN 지정하여 CosmosDB 데이터를 쿼리 있다.

 

실습 데이터의 결과를 보면 CosmosDB에서 3개의 문서를 나타내는 3개의 행을 얻게 된다. 누락된 필드는 NULL 반한된다. 또한 아래 스크립트처럼 필터를 사용할 수도 있다.

SELECT a.*

FROM OPENROWSET('MSDASQL',

'DSN=cosmosdb1',

'select * from Orders where billto_Name = ''John Smith''') as a

 

실습데이터의 JSON 형식을 보면 shipTo 또는 billTo 같이 복잡한 JSON 객체가 모두 동일한 ROW 표시되고 있는 것을 있다. , 모든 필드가 <object name>_ <field name> 형식으로 반환된다는 것이다. 하위 오프젝트가 있는 것으로 예상되는 경우 사용자는 이를 알고 있어야 한다. 또한 필자의 경우 태그와 같은 배열 속성이 매핑되거나 반환되지 않는다.

 

[결론]

CosmosDB ODBC 드라이버를 사용하면 CosmosDB 데이터에 대해 Transact-SQL 쿼리를 실행할 있다. 이는 CosmosDB 저장된 원격 데이터에 대해서 데이터 분석이 필요한 경우 유용햘 있다. 경우 결과를 필터링 하고 필요한 필드만 선택한 다음 전체 Transact-SQL 언어를 사용하여 SQL Server에서 다양한 분석을 수행하는 조건자를 사용하여 CosmosDB 보낼 있다.

( 글을 쓰는 시점에는) CosmosDB드라이버는 Azure SQL 설치되어 있지 않으며 자체 드라이버를 추가할 없기 때문에 이는 Azure SQL 데이터베이스가 아닌 SQL Server에서만 가능하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/running-azure-cosmosdb-queries-from-sql-server-using-odbc-driver/

 

2019-09-09 / Sungwook Kang / http://sungwookkang.com

 

Azure CosmosDB, SQL Server에서 Azure CosmosDB 연결하기, CosmosDB ODBC

Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링

 

·       Version : SQL Server, Azure SQL Managed Instance

 

Azure SQL Managed Instance 사용할때, 선택한 인스턴스의 vCore 예약 스토리지에 따라 사전에 정의된 스토리지 공간이 있다. 이번 포스트에서는 Managed Instance 스토리지 공간을 모니터링하는 방법에 대해서 알아본다.

 

Managed Instance에서는 아래와 같은 제약이 있다.

·       인스턴스의 스토리지 제한은 Azure Portal에서 선택할 있다. 범용은 8TB, 비즈니스 크리티컬은 4TB 보다 없다.

·       (범용 전용) 로컬 SSD 디스크 스토리지의 제한 – Managed Instance 경우  tempdb 24GB X vCore 공간만큼 로컬 SSD 배치된다. 배치된 로컬 SSD 한계에 도달하면 tempdb에서 임시 객체를 만들수 없다.

 

스토리지 리밋에 도달하면 스토리지 공간/ vCore 수를 늘리거나 일부 리소스를 비워야 한다. 스토리지 변경은 오래 걸릴수 있기 때문에 스토리지 한계에 도달하기 전에 스토리지를 추가하는 것이 중요하다. sys.dm_os_volume_stats Managed Instance 스토리지를 포함하여 볼륨에 대한 사용 정보를 제공한다. 아래 스크립트를 사용하여 스토리지의 사용량 정보를 확인 있다.

SELECT

       volume_mount_point,

       used_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       total_gb = CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(8,1))

FROM sys.master_files AS f

       CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

GROUP BY volume_mount_point;

 

 

 

 

아래 그림에서 http:// 경우 원격 Azure Premium Disk 저장소를 사용하고 있는지 보여준다.

 

 

스크립트를 주기적으로 실행하여 available_gb 줄어드는 것을 확인하여 스토리지 공간이 부족할 경우 알림을 받을 있도록 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/08/monitor-storage-local-storage-usage-on-general-purpose-managed-instance/

 

 

2019-09-06 / Sungwook Kang / http://sungwookkang.com

 

Azure SQL Managed Instance, Azure Local Storage, SQL Azure, sys.dm_os_volume_stats, 로컬 스토리지

SQL Server Enterprise Edition 에서 CPU 40 Core 이상 사용하지 못하는 현상

 

·       Version : SQL Server

 

호스트의 서버의 CPU96 Core 장비에서 SQL Server 2016 Enterprise Edition 설치 사용하는데 CPU 40Core 이상을 사용하지 못하는 문제가 있었다. 처음에는 NUMA 불균형이 발생하지 않았는지 의심했었지만, 증상을 보면 0 코어부터 39 코어까지만 정확히 사용하는것을 확인할 있었다. 여러가지 원인을 조사 결과 해당 문제는 SQL Server 라이선스 업데이트로 해결 되었다. MS 라이선스 정책은 많이 어려운 편인데 (서비스를 제공하는 형태에 따라 다양한 라이선스가 있음, 라이선스의 자세한 내용은 여기서 다루지 않는다.) 일반적인 SQL Server Enterprise 에디션을 설치 할경우 40Core 이상 사용할 없다고 한다. 40Core 이상 사용하려면 Core 라이선스를 선택해서 설치해야 한다.

 

이미 SQL Server Enterprise Edition 설치되어 있는 경우는 어떻게 할까? 이런 경우 SQL Server 재설치는 필요없이 라이선스 업데이트가 가능하다. 우선 SQL Server Enterprise Core Edition 다운로드 받아서, 호스트 서버에서 마운트 한다. 그리고 Install과정을 진행한다. 설치 과정중 나타나는 라이선스 코드를 복사하여, 아래 스크립트에서 PID 부분에 복사한 라이선스 코드를 입력하고 실행한다.

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID=XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /IACCEPTSQLSERVERLICENSETERMS

 

라이선스 없데이트 과정은 수분의 시간이 필요하며, 과정중에 SQL Server 서비스가 중지되므로 반드시 점검때 하도록 한다.

 

 

2019-09-03 / Sungwook Kang / http://sungwookkang.com

 

SQL Server Enterprise Edition, SQL Server Core lincese, SQL 라이선스, SQL Server 40코어 이상 사용하기, SQL Server CPU Core

SQL Server Agent Job에서 sysploicy_purge_history  작업실패

 

·       Version : SQL Server

 

SQL Server 2008 이후로 SQL Server Agent Job에서는 syspolicy_purge_history라는 job 등록되어 있다. 해당 Job MSDB 쌓여 있는 히스토리를 정리하는 작업을 진행한다.  가끔 해당 Job 실행시 아래와 같은 오류가 발생할 있는데, 오류가 무엇인지 확인해보고 해결하는 방법에 대해서 살펴 본다.

 

Job 실패하였을때, 해당 로그를 살펴보면 아래와 같은 오류로그가 발생하였다.

Date                    7/29/2019 1:23:41 PM

Log                       Job History (syspolicy_purge_history)

 

Step ID                3

Server                

Job Name                         syspolicy_purge_history

Step Name                       Erase Phantom System Health Records.

Duration                           00:00:00

Sql Severity        0

Sql Message ID  0

Operator Emailed          

Operator Net sent         

Operator Paged

Retries Attempted          0

 

Message

Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.  '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

 

해당 Job 실행하는 명령을 직접 Powershell콘솔에서 실행하여도 아래와 같은 오류가 발생한다.

 

해당 오류는 명령이 참조하는 모듈의 PATH 설정이 누락되어 발생한 경우로, 시스템 변수에서 해당 변수값을 지정하면 해결이 가능하다. 모듈의 PATH 경로는 SQL 버전마다 조금씩 다르며 아래 경로를 참고할 있도록 한다.

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules

 

모듈이 위치한 경로를 확인하였으면,  [My Computer] – [right click] – [Properties]에서 [Advance] 탭을 선택하여 [Environmeont Variables] 클릭한다.  PSModulePath 이름으로 모듈이 위차한 PATH 등록(또는 수정)한다.

 

경로가 올바르게 수정되었으면, SQLPS 시작하여 정상적으로 PATH 설정이 완료되었는지 확인이 가능하다. 또는 SQL Agent에서 해당 Job 실행하여 정상적으로 실행이 되는지 확인할 있다.

 

 

[참고자료]

https://blog.sqlauthority.com/2016/03/08/sql-server-syspolicy_purge_history-job-failing-step-erase-phantom-system-health-records/

 

2019-08-07 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, Agent job, sysploicy_purge_history , Agent 히스토리 삭제, PSModulePath, Erase Phantom System Health Records

SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기

 

·       Version : SQL Server

 

SQL Server에서 파티션 테이블을 사용하기 위해서는 SQL Server Enterprise Edition 사용해야한다. Standard Edition에서도 파티션 테이블 처럼 사용하기 위해서는 새로운 테이블을 생성하고 sp_rename 함수를 사용하여 기존테이블과 새로운 테이블의 이름을 변경하는 방식으로 사용하였다.

-- Replace live with staging

BEGIN TRAN

DROP TABLE DataTable;

EXEC sp_rename DataTable_Staging, DataTable;

COMMIT

 

-- Swap live and staging

BEGIN TRAN

EXEC sp_rename DataTable, DataTable_Old;

EXEC sp_rename DataTable_Staging, DataTable;

EXEC sp_rename DataTable_Old, DataTable_Staging;

COMMIT

 

위와 같은 방법을 사용하여 메타 데이터 레벨에서 테이블 이름을 변경하면 스키마가 일치하더라도 이름이 바뀐 테이블이 지정된 이름의 메타 데이터와 연결되지 않는다. 시스템은 캐시된 ObjectID 사용하는데 스왑아웃을 수행하려면 캐시 데이터를 업데이트하는 추가 단계가 필요하다. 스위치 아웃으로 인해 ObjectID 관련 메타정보가 그대로 유지되면 이러한 추가단계가 불필요하다.

 

ALTER TABLE SWITCH TO 명령은 기존 테이블을 유지하면 데이터 세트를 스와핑힌다. 명령을 사용하기 위해서는 스키마 정보가 호환 가능해야한다. 컬럼이름, 컬럼 순서, 조약 조건등 특성이 동일해야한다.

-- Replace live with staging

BEGIN TRAN

TRUNCATE TABLE DataTable;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

COMMIT

 

-- Swap live and staging

/* Note: An extra table, DataTable_Old, is required to temporarily hold the data being replaced before it is moved into DataTable_Staging. The rename-based approach did not require this extra table. */

BEGIN TRAN

ALTER TABLE DataTable SWITCH TO DataTable_Old;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

ALTER TABLE DataTable_Old SWITCH TO DataTable_Staging;

COMMIT

 

 

 

 

 

[참고자료]

https://bengribaudo.com/blog/2016/11/15/3521/swapping-data-sets

 

 

2019-05-02 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, partition table, table swap, data set swap, alter table switch to, sp_rename, alter table, 테이블 변경, 테이블 이름 변경, 데이터 변경

SQL Server master 데이터베이스 정리

 

·       Version : SQL Server

 

SQL Server에서 master 데이터베이스의 역할은 시스템에 대한 모든 정보를 기록한다. 여기에는 로그인 계정, 끝점, 연결된 서버 시스템 구성설정과 같은 인스턴스 차원의 메타 데이터가 포함된다.

·       master database : https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

 

우리는 가끔 의도하지 않게 master 데이터베이스에 개체를 생성하는 경우가 있다. 대부분 개체를 생성할 USE 문을 생략했을 수도 있고 많은 작업창을 띄어 놓고 사용하다가 실수할 수도 있다. 이번 아티클에서는 master 데이터베이스를 정리하는 방법에 대해서 알아본다. master 데이터베이스는 시스템의 설정을 저장하고 있으므로 해당 작업시 주의가 필요 하다.

 

[master에서 삭제하지 않을 목록]

아래 표에 정으되어 있는 오브젝트 유형은 시스템과 관련있기 때문에 삭제 목록에서 제외한다.

ET

External table

IT

Internal table

PC

Assembly (CLR) stored procedure

PG

Plan guide

RF

Replication-filter-procedure

S

System table

SQ

Service queue

TA

Assembly (CLR) DML Trigger

X

Extended procedure

-

CLR User-Defined Data Type (UDDT)

 

사용자는 자신만의 모듈을 생성하여 시스템 객체로 표시할 수도 있다. 경우 시스템 저장프로시저와 구별 있는 확실한 방법이 없기 때문에 주관적으로 식별해야 한다.

 

[master 에서 삭제할 목록]

오브젝트를 삭제하기 위해서는 약간의 순서가 필요하다. 예를 들어 테이블을 삭제하려면 외부키나 뷰를 제거해야하며, 테이블 함수를 사용하는 테이블을 삭제 때까지 파티션 함수를 제거할 없다. 또한 순환 참조가 있을 있으므로 많은 경우 스크립트를 여러번 실행해야 수도 있다.

 

아래 표시된 유형은 객체를 삭제하면 자동으로 삭제되기 때문에 크게 걱정할 필요가 없다.

C

Check constraint

D

Default constraint

PK

Primary key constraint

TR

DML trigger

UQ

Unique constraint

 

나머지는 아래 순서대로 삭제해야한다.

1

F

Foreign key constraint

Need to be dropped before tables, as described below.

2

V

View

Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.

FN

SQL scalar function

IF

SQL inline table-valued function

TF

SQL table-valued-function

3

P

Stored procedure

Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).

4

U

Table (user-defined)

Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.

5

TT

Table type

Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.

Alias types

 

6

SO

Sequence object

Dropped after tables/modules because they can't be dropped if any table or module references them.

R

Rule (old-style, stand-alone)

D

Old-style CREATE DEFAULT

Partition functions

 

7

Partition schemes

 

Can't be dropped until after partition functions.

8

SN

Synonym

I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).

9

Schemas

 

I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.

10

Roles and Users

 

For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. 

(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) 

For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

 

[기본 접근]

순환 참조는 여러 개체가 서로를 참조하므로 개체를 삭제하는 올바른 순서를 결정하기가 복잡하다. 그래서 스크립트가 실패하더라도 다시 실행하여 스크립트가 성공할때 까지 반복해서 실행 있는 스크립트로 작성해야 한다. 예를 들어 개의  SCHEMABINDING 뷰를 사용하는 경우 개의 뷰를 모두 삭제해야 테이블이 삭제된다.   순서가 명확하다면 순서대로 하면 되지만 뷰가 여러개 이면서 서로 뷰를 참조하는 경우 순서를 예측하기 어렵기 때문에 삭제 명령을 반복해서 실행한다.

try { drop view 1; go; drop view 2; }

 

 

[삭제 스크립트 (순서대로 실행)]

Foreign keys

-- script to drop Foreign Keys

USE [master];

GO

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER TABLE ' + objectname + N'

    DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

  SELECT N''FK ' + fkname + N' failed. Run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT fkname = QUOTENAME(fk.[name]),

    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

  FROM sys.foreign_keys AS fk

  INNER JOIN sys.objects AS t

  ON fk.parent_object_id = t.[object_id]

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

) AS src;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

View and functions

-- script to drop views

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

  SELECT N''View ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

  FROM sys.views AS v

  INNER JOIN sys.schemas AS s

  ON v.[schema_id] = s.[schema_id]

  WHERE v.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Stored Procedures

-- script to drop procedures

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.[type] = 'P'

  AND o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Tables

-- script to drop user tables

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Table ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.tables AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Table Types

-- script to drop table types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_table_type = 1

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Alias Types

-- script to drop alias types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.user_type_id > 256

  AND o.is_table_type = 0

  AND o.is_assembly_type = 0  -- not a CLR UDT

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Sequences

-- script to drop sequences

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.sequences AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE RULE

-- script to drop old-style Rules

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Rule ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'R'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE DEFAULT

-- script to drop old-style Defaults

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Default ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'D'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition functions

-- script to drop partition functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_functions

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition schemes

-- script to drop partition schemes

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_schemes

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Synonyms

-- script to drop synonyms

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.synonyms AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Schemas

-- script to drop schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Schema ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name])

  FROM sys.schemas AS s

  WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to re-route default schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

 END TRY

 BEGIN CATCH

  SELECT N''User ' + p.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

  SELECT 1

  FROM sys.schemas AS s

  WHERE name = p.default_schema_name

);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Roles and Users

-- script to change ownership of roles to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to change ownership of schemas to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

 END TRY

 BEGIN CATCH

  SELECT N''Schema ' + name + N' failed - run the script again.'',

    ERROR_MESSAGE(); END CATCH

'

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to remove members from roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

  PARSENAME(CONVERT(nvarchar(128),

  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

 THEN

  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

   + QUOTENAME(m.name) + N';'

 ELSE

  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

    + ''', @membername = N''' + m.name + N''';'

 END + N'

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP ROLE ' + QUOTENAME(r.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop users

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP USER ' + QUOTENAME(u.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''User ' + u.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

 

지금까지  master 데이터베이스를 정리하는 작업 순서, 스크립트 생성에 대해서 알아보았다. 객체를 삭제하는 명령은 자동화 있지만 모든것은 완벽히 분석할 수는 없다. (사용자 모듈을 시스템 모듈로 생성한 경우 ) 스크립트를 사용하더라도 생성된 스크립트를 반드시 DBA 검토를 해야한다. master 데이터베이스를 정리하는것은 위험한 작업이므로 반드시 많은 테스트와 검증이 필요하다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4530/cleaning-up-the-sql-server-master-database/

 

 

2019-04-18 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, mssql, DBA, master database, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions, sys.partition_schemes, sys.procedures, sys.schemas, sys.sequences,

SQL Server 프로토콜과 SQLCMD 사용한 연결

 

·       Version : SQL Server

 

SQL Server 연결하는 것은 이미 많은 문서를 통해서 알려져 있으며 GUI 제공하는 툴들이 많아서 어렵지 않게 느껴질 수가 있다. 하지만 상황에 따라 복잡해 있다. 만약 TCP 포트에서 수신대기를 하지 않는 SQL Server 인스턴스에는 어떻게 연결할까? 호스트 컴퓨터가 서버  DNS 확인할 없는 경우 어떻게  SQL Server 연결할까? 명명된 인스턴스는 무엇일까? 이번 포트스에서 SQLCMD 사용하여 다양한 프로토콜의 의미와 사용법을 알아 본다.

 

SQL Server 관리하는 다양한 툴들이 있다. 특히SSMS(SQL Server Management Studio) Microsoft에서 공식으로 제공하는 SQL Server관리툴이며GUI 제공한다. 그리고 SQLCMD라는 CLI 있다. SQLCMD 경우 명령줄로 이루어져 있다. SQL Server Linux 지원하면서 우리는 GUI 뿐만아니라 환경에도 익숙해져야 하기 때문에 SQLCMD 대해 다루어 본다.

 

SSMS 사용하여 SQL Server 인스턴스에 연결할  특정 프로토콜을 사용하기 위해서 Connection Properties 탭에서 프로토콜을 선택할 있다.

 

대부분 SQL Server 연결할 , 프로토콜을 지정하지 않으면 TCP/IP 사용한다고 생각할 있다. 하지만 프로토콜을 지정하지 않으면 TCP/IP 사용한다는 보장을 없다. SSMS SQLCMD에는 SQL Server 구성관리자의 프로토콜에 따라 순서가 결정된다.  아래 그림은 SQL Server 구성관리자의 프로토콜 구성이다. 구성관리자에는 32bit, 64bit  가지 클라이언트 구성이 있다. SSIS 사용하는 경우 32bit 커넥터를 사용할 있기 때문에 중요하다. 패키자가 64bit 버전에서 실행되고 64bit 설정을 하지 않을 경우 패키지가 실패한다.

 

[SQLCMD 사용한 SQL Server TCP/IP 연결]

TCP/IP 프로토콜을 사용하여 SQL Server 인스턴스에 연결할 , 인스턴스가 기본 포트가 아닌 경우 서버 IP 주소, 또는 호스트 이름과 포트가 필요하다. 아래 스크립트는 TCP/IP 사용하여 연결하는 일반적인 구문이다.

sqlcmd -S tcp:<computer name>,<port number>

 

IP 주소를 입력하면 TCP/IP 프로토콜을 사용할 것이라는 보장을 없다. TCP/IP 보장하기 위해서 tcp:라는 접두어를 사용하여 연결한다. 예를 들어 Windows 인증을 사용하는 SQL Server 기본 인스턴스를 사용하여 SQL-A라는 서버에 TCP/IP 프로토콜을 사용하여 연결하려는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A

sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd

 

IP 사용할 경우 아래 구문을 이용한다.

sqlcmd –S tcp:10.10.10.10

sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd

 

명명된 인스턴스를  사용하는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A\TEST

sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd

 

포트를 지정해야 경우 콤마(,) 구분하며 포트를 지정해야 한다. 예를 들어 기본 인스턴스가 1433 포트를 사용하고 TEST 인스턴스가 51613포트를 사용하는 경우 연결 문자열을 아래와 같다.

sqlcmd –S tcp:SQL-A,1433

sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:10.10.10.10,1433

sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:SQL-A,51613

sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:10.10.10.10,51613

sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd

 

 

[SQLCMD SQL Server 명명된 파이트 연결]

명명된 파이프는 서버와 클라이언트 간의 통신을 위해 명명된, 단방향, 또는 이중 파이프이다. 내부적으로 명명된 파이프의 모든 인스턴스는 동일한 파이프 이름을 갖지만 메시지 기반 통신과 클라이언트 가장을 허용하는 자체 버퍼를 유지한다. 명명된 파이프는 프로세스간 통신(IPC) 의존한다. 명명된 파이프를 사용하여 로컬 인스턴스에 연결하면 해당 파이프가 커널 모드에서 로컬 프로시저 호출(LPC) 실행된다는 점에 유의한다. 일반적으로 프로토콜은 원격 SQL Server 인스턴스에 연결할때 TCP/IP 사용하는것이 바람직하기 때문에 사용되지 않는다. 반면 로컬 인스턴스에 연결할 공유 메모리는 종종 선택되어 사용된다. 아래는 기본 인스턴스의 명명된 파이트 연결구문이다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query

 

예를 들어 서버 MYSERVER 기본 인스턴스에 대한 명명된 파이프는 다음과 같다.

\\ MYSERVER \ pipe \ sql \ query

 

명명된 인스턴스의 구문은 다음과 같다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query

 

예를 들어 서버 SQL-A에서  SQL Server 인스턴스 TEST 명명된 파이프를 사용하려는 경우 구문은 아래와 같다.

\\SQL-A\pipe\MSSQL$TEST\sql\query

 

명명된 파이프 프로토콜을 사용하여 SQL Server 인스턴스에 연결하는 것은 TCP/IP 사용하는것과 크게 다르지 않다. 연결문자열에np: 접두어를 사용한다. 아래 구문은 Windows SQL Server 인증을 사용하여 SQL Server 기본 인스턴스와 명명된 인스턴스에 연결한다.

sqlcmd –S np:\\SQL-A\pipe\sql\query

sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd

 

 

[SQLCMD 사용한  SQL Server 공유 메모리 연결]

프로토콜은 클라이언트 연결이 로컬 서버에서 실행될 때만 사용될 있다. 기본적으로 커널 모드에서 실행되는 로컬 프로시저 호출(LPC)이다. MDAC 2.8 이하를 사용하는 경우 공유 메모리 프로토콜을 사용할 없다. 경우 sqlcmd 자동으로 명명된 파이프로 전환한다. 공유 메모리를 사용하는 접두사는 lpc: 이다.

sqlcmd –S lpc:SQL-A

sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd

sqlcmd –S lpc:SQL-A\TEST

sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5133/different-ways-to-connect-to-sql-server-using-sqlcmd/

 

 

2019-04-17 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, sqlcmd, named pipe, sql server protocol, sql connection, mssql

SQL Server Edition 다운그레이드 확인사항

 

·       Version : SQL Server

 

SQL Server Enterprise Edition 에서 SQL Server Standard Edition으로 다운 그레이드 확인해야 가지 사항에 대해서 알아본다. SQL Server Enterprise Edition SQL Server Standard Edition으로 다운그레이드 일부 구성이 기본값으로 다시 설정된다.

 

[SQL Server 오류 로그 ]

SQL Server 오류 로그 파일의 수가 기본 6개로 재설정된다. 설정을 확인하고 필요한 수로 설정한다. SSMS GUI 사용할 수도 있으며 T-SQL 코드를 사용할 있다.

 

USE [master]

GO

 

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99

GO

 

 

[SQL Agent 메일 프로필]

SQL Server Agent 속성의 메일 프로필이 비활성 된다. 메일 프로필 사용 올바른 메일 프로필이 사용되는지 확인 한다. SSMS에서 GUI 사용하거나T-SQL 코드를 사용할 있다.

 

USE [msdb]

GO

 

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,

      @databasemail_profile=N'DBServerAlerts_Profile', -- replace with your Agent's profile

      @use_databasemail=1

GO  

 

[토큰 교체 설정]

“Replace tokens for all jobs responses to alert” 옵션을 사용하는 경우 다시 활성화 해야 한다.    그림(SQL Agent 메일 프로필 그림) 처럼 SSMS GUI 사용하거나 T-SQL 코드를 사용할 있다.

USE [msdb]

GO

 

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1

GO

 

[기타 잠재적 문제]

위의 설정은 레지스트리를 통해 구성되는 설정이며 시스템 데이터베이스는 저장되지 않는다.

·       SQL Server 2014 레지스트리 위치는 아래와 같다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

 

·       SQL Server 2017 레지스트리 위치는 아래와 같다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\SQLServerAgent.

 

레지스트리 아래 저장되는SQL Server 에이전트는 다음과 같다.

·       AlertFailSafeEmailAddress

·       AlertFailSafeNetSendAddress

·       AlertFailSafeOperator

·       AlertFailSafePagerAddress

·       AlertNotificationMethod

·       DatabaseMailProfile

·       ErrorLogFile

·       ErrorLoggingLevel

·       IdleCPUDuration

·       IdleCPUPercent

·       JobHistoryMaxRows

·       JobHistoryMaxRowsPerJob

·       JobShutdownTimeout

·       MonitorAutoStart

·       RestartSQLServer

·       UseDatabaseMail

 

SQL Server Edition 다운그레이드 사항을 확인하여 시스템 운영에 참고 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4698/sql-server-edition-postdowngrade-steps/

 

2019-04-11 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, Edition post-down grade, Job Agent, Mail profile, Error log number, Token replacement setting, SQL registry key, MS SQL, SQL Edition

AlwaysOn 구성환경에서 Server Role 체크 Job Agent 실행 중지하기

 

·       Version : SQL Server

 

SQL Server에서 AlwaysOn 구성하였을때, Failover 대비하여 Secondary 서버에서도 Primary서버와 동일하게 계정, Job Agent 등이 구성되어 있어야 한다. 그런데 Secondary 경우 DB 동기화 되고 있는 대기 서버이기 때문에 Job Agent Primary 동일하게 설정하면 Secondary에서 Job 실행 Job Fail 발생한다. 또한 Secondary에서 일부 Job 경우 실행이 되지 말아야 것들이 있다. 아래 스크립트는 AlwaysOn role 확인하여 서버가 Primary 때만 Job Agent 수행되도록 한다. 각각의 Job Agent 번째 단계에 추가하여 Primary Role 아닐때 Job 수행을 중지하도록 한다.

DECLARE @SERVER_ROLE nvarchar(50)

 

SELECT

    @SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

    INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1

 

IF @SERVER_ROLE <> 'PRIMARY'

       EXEC msdb.dbo.sp_stop_job N'Job Name' ;

 

Secondary에서 Job 실행되었을때, Role 확인하는 부분이 있어 Job 중지된것을 확인할 있다.

 

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

 

2019-04-10 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, AlwaysOn,  Job Agent, sys.dm_hadr_availability_replica_states, AG Role check

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

 

·         Version : SQL Server

 

SQL Server에서 인덱스 재구성 통계 업데이트 작업을 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

 

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

·         Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE 실행하고 UPDATE 구문 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

 

 

세션2에서 아래 스크립트를 실행한다.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

 

sp_who2 실행하고 프로세스가 모두 실행중인지 확인한다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

59

RUNNABLE

.

AdventureWorks2014

UPDATE STATISTIC

 

 

세션3에서 아래 스크립트를 실행한다.

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

 

sp_who2 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 있다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

58

SUSPENDED

59

dventureWorks2014

SELECT

59

RUNNABLE

57

AdventureWorks2014

UPDATE STATISTIC

 

지금까지는 매우 간단한 시나리오였으며 SELECT UPDATE STATISTICS 의해 차단되고 UPDATE STATISTICS INDEX REORG(DBCC) 의해 차단되었음을 확인할 있다. 실제 서비스에서 sp_who2 실행하면 블로킹 체인의 SPID 한번에 확인하기 어려울 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID 확인할 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

SET NOCOUNT ON

GO

 

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

       SPID,

       BLOCKED,

       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

       BATCH

   FROM #T R

   WHERE (BLOCKED = 0 OR BLOCKED = SPID)

   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

   UNION ALL

   SELECT

      R.SPID,

      R.BLOCKED,

      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

      R.BATCH

   FROM #T AS R

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

   )

SELECT

   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

 

DROP TABLE #T

GO

 

 

아래 출력을 보면 ALTER INDEX 체인의 머리 부분에 있고 UPDATE STATISTICS 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 있다.

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

 

아래 스크립트를 사용하면 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 있다. WHERE절에 SPID 수정해서 사용한다.

SELECT

  tl.request_session_id as spid,tl.resource_type,

  tl.resource_subtype,

  CASE

     WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

     ELSE ''

  END AS object,

  tl.resource_description,

  request_mode,

  request_type,

  request_status,

  wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

 

 

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 있다. ALTER INDEX 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 까지 SELECT 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 있다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

 

 

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA

SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

 

·         Version : SQL Server 2017 later, Azure SQL Database

 

SQL Server 2017 Azure SQL Database 런타임 환경에 최적화된 쿼리 처리 개선 기능을 도입했다. 이러한 개선 사항에는 Batch mode Adaptive Joins, Batch mode memory grant feedback, Interleaved execution for multi-statement table valued functions 포함된다.

·         Batch mode Adaptive Joins : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-adaptive-joins

·         Batch mode memory grant feedback  : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback

·         Interleaved execution for multi-statement table valued functions : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#interleaved-execution-for-multi-statement-table-valued-functions

 

SQL Server 2019에서는 Intelligent query processing(QP) 제품군에서 가지 새로운 기능으로 쿼리 처리 기능을 더욱 확장하고 있다.

·         Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

 

이번 포스트에서는 SQL Server 2019 CTP 2.1에서 사용할 있는QP 기능 하나인 Scalar T-SQL UDF  inline 대해서 살펴본다.

 

T-SQL UDF SQL 쿼리에서 코드 재사용 모듈화를 구현한다. 복잡한 비즈니스 규칙과 같은 일부 계산은 명령형 UDF 형식으로 표현하기가 쉽다. UDF 복잡한 SQL 조회를 작성하는 전문 지식 없이 복잡한 논리를 해결하는데 도움이 된다. 하지만 이러한 장점에도 불구하고 열악한 성능문제 때문에 최대한 사용을 자제하거나 금지한다.

Scala UDF inline 기능의 목표는 UDF 실행의 주요 병목인 Scala UDF 호출하는 쿼리의 성능을 향상 시키는 것이다.  Scala UDF 느렸던 이유는 Scala UDF 도입되었을 , 변수 할당, IF-ELSE 분기, 루프 등과 같은 친숙한 구문을 사용하여 사용자가 비즈니스 로직을 표현하였다. 아래 스크립트는 @ckey값이 주어지면 스칼라 UDF 사용하여 고객이 지정한 모든 주문의 가격을 계산한 다음 IF-ELSE 논리를 사용하여 가격을 기준으로 카테고리 값을 반환한다.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)

RETURNS CHAR(10) AS

BEGIN

       DECLARE @total_price DECIMAL(18,2);

       DECLARE @category CHAR(10);

     

       SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  

       IF @total_price < 500000

              SET @category = 'REGULAR';

       ELSE IF @total_price < 1000000

              SET @category = 'GOLD';

       ELSE

              SET @category = 'PLATINUM';

       RETURN @category;

END

 

UDF 여러 조회에서 사용할 있으며 임계 값을 갱신하거나 카테고리를 추가해야하는 경우 UDF에서만 변경해야한다. 아래 스크립트는 위에서 만든 UDF 호출하는 간단한 쿼리이다.

-- Q1:

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

 

SQL Server 2017(호환성 수준 140 이전 버전) 버전에서는 쿼리의 실행 계획은 아래와 같다.


SQL Server CUSTOMER 테이블의 모든 튜플에 대해 UDF 호출하고 결과를 출력한다. 실행계획  매우 비효율적이다. 이러한 쿼리는 다음과 같은 이유로 성능에 좋지 않다.

·         반복 호출 : UDF 대상 튜플 반복적으로 호출된다. 이로 인해 함수 호출로 인한 반복된 컨텍스트 전환의 추가 비용이 발생한다. 특히 본문에서 SQL 조회를 실행하는 UDF 심각한 영향을 준다.

·         비용 증가 : 최적화 중에는 관계 연산자만 비용이 청구되고 스칼라 연산자는 계산되지 않는다. Scala UDF 도입되기 전에 다른 스칼라 연산자는 일반적으로 저렴했으며 비용이 필요하지 않았다. 스칼라 작업에 추가된 작은 CPU 비용으로 충분했다.

·         Interpreted 실행 : UDF 명령문 별로 실행되고 명령문의 일괄 처리로 평가 된다. 명령문 자체는 컴파일 되고 컴파일된 계획은 캐시 된다. 캐싱 전략은 재컴파일이 발생할때 까지 시간을 절약하지만 문은 독립적으로 실행된다. 교차 명령문 최적화는 수행되지 않는다.

·         Serial 실행 : SQL Server UDF 호출하는 쿼리에서 쿼리 병렬 처리를 사용하지 않는다.

 

새로운 Scalar UDF inline 기능으로 변경된 사항은 스칼라 UDF 표현식이나 서브쿼리로 변환되어 UDF 연산자의 호출 조회에서 대체된다. 그러면 이러한 표현식과 서브쿼리가 최적화 된다. 결과적으로 쿼리 계획에는 이상 사용자 정의 함수 연산자가 없지만 또는 인라인 TVF 같은 계획에서 효과가 관찰된다. 이를 이해하기 위해서 먼저 아래 예제를 살펴 본다.

-- Q2 (Query with no UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

쿼리는 광고 항목의 할인 가격 합계를 계산하고 출하 날짜 배송 우선 순위별로 그룹화 하여 결과를 표시한다. 표현식 L_EXTENDEDPRICE * (1 - L_DISCOUNT) 해당 광고 항목의 할인 가격에 대한 수식이다. 할인된 가격을 계산해야하는 곳이면 어디서든 사용할 있도록 계산 함수를 만드는 것이 합리적이다.

-- Scalar UDF to encapsulate the computation of discounted price

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))

RETURNS DECIMAL (12,2) AS

BEGIN

       RETURN @price * (1 - @discount);

END

 

이제 다음과 같이 쿼리 Q2 수정하여 UDF 사용할 있다.

-- Q3 (Query with UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

아래 표는 3개의 쿼리를 실행한 결과이다. 앞에서 설명한 이유 때문에 Q3 Q2 비해 성능이 좋지 않다. 이제 Scala UDF inline 통해 SQL Server 스칼라 표현식을 쿼리로 직접 대체하여 UDF 성능의 한계를 극복한다.

[2CPU (12 core), 96GB RAM, SSD, TPC-H 10GB CCI 데이터 사용]

Query

Q2(UDF 없음)

인라인 업이 Q3

인라인이 포함된 Q3

Execution Time

1.6 second

29 minute 11 second

1.6 second

 

수있듯이 인라인 없이 Q3 Q2 비해 속도가 매우 느리다. 그러나 Scala UDF inline 사용하면 Q3 성능은 거의 오버헤드 없이 거의 Q2 동일하다. 쿼리성능을 저하시키지 않으면서 UDF 모든 이점을 누릴 있다. 또한 쿼리나 UDF 수정이 없었음을 확인할 있다. Scala UDF inline 통해 SQL Server 다중문 UDF 인라인 있다. 위에서 주어진 dbo.customer_category 함수와 Q1함수를 살펴보고 이것이 어떻게 동작하는지 살펴보자. 쿼리 Q1 경우 UDF 인라인된 쿼리 계획은 아래와 같다.


위의 계획에서 얻은 가지 주요 관찰 내용은 아래와 같다.

1.       SQL Server CUSTOMER ORDERS 사이의 암시적 조인을 유추했으며 Join 연산자를 통해 이를 명시적으로 만들었다.

2.       SQL Server ORDERS 대한 암시적 GROUP BY O_CUSTKEY 유추했으며 이를 구현하기 위해 Index Spool Stream Aggregate 사용했다.

3.       SQL Server 모든 연산자에서 병렬처리를 사용한다.

 

UDF 논리 복잡성에 따라 결과 쿼리 계획이 커지고 복잡해 수도 있다. 있듯이 UDF 내부의 작업은 이제 이상 블랙 박스가 아니므로 쿼리 최적화 프로그램은 비용을 절감하고 이러한 작업을 최적화 있다. 또한 UDF 이상 계획에 없으므로 반복 UDF 호출은 함수 호출 오버헤드를 회피하는 계획으로 대체된다.

 

Scala UDF inline 장점은 성능 오버헤드에 대한 걱정없이 사용자가 Scala UDF 사용할 있다.  이로써 사용자는 모듈화되고 재사용가능한 응용프로그램을 구축 있다. UDF 사용한 쿼리에 대해 집합 지향, 병렬 계획을 수행할 있을뿐만 아니라 기능은 다른 장점이 있다. Scala UDF 이상 interpreted 되지 않으므로 ( : 명령문마다 실행됨) 데드코드 제거, 상수 폴딩 상수 전파와 같은