Data Science Lab

SQL Server 2016 Tempdb 경합(contention) 최적화

·         Version : SQL Server 2017, 2016, 2014

SQL Server에서 tempdb는 경합이 자주 발생하는 부분이므로 항상 모니터링의 주요 대상이다. SQL Server 2016에서는 최신 업데이트를 통해 tempdb file 및 trace flag 개선작업이 진행 되었다.

[Object Allocation Contention]

SQL Server에서  tempdb 구성시 일반적인 권장사항은 아래와 같다.

1.       여러 데이터 파일 생성 : CPU 코어당 최소 1개 이상으로  데이터 파일을 생성하며 최대8개의 파일로 구성한다.(필요에 따라 더 많이 생성할 수도 있다.) 모든 파일의 크기는 동일 해야한다.

2.       SQL Server 2014 또는 이전 버전의 경우 TF 1117 및 TF1118을 설정한다. SQL Serve 2016에서는 이 추적 플래그가 기본 디자인으로 적용되어 설정할 필요 없으며 TF 3427을 사용한다.

이러한 권장사항은 많은 임시테이블이 동시에 생성될 때 발생할 수 있는 개체 할당(object allocation) 병목 문제를 해결하는데 도움이 된다. PAGELATCH 대기는 2:X:1(PFS), 2:X:3(SGAM) 또는 2:X:<8088의 일부 배수> (PFS) 리소스에서 경합이 발생한 것으로 X는 파일 번호를 나타낸다. 이 페이지는 SQL Serve가 새 개체에 공간을 할당 해야할 때 사용되는 특수 페이지이다. 동일한 크기의 여러 파일을 보유함으로써 각각의 새로운 개체 할당은 라운드로빈 방식으로 다른파일과 다른  PFS페이지를 조회한다. SQL Server에서는 이러한 구성을 기본값으로 설정하는것이 일반적이다.

SQL Server는 여러 데이터 파일을 분할하여  완벽한PFS 경합을 해결하지 못하여 파일간 라운드로빈 및 파일내의 PFS 페이지간에 라운드로빈을 사용하여 모든 파일과 파일 자체에서 개체 할당을 분산시킬수 있도록 디자인을 수정하였고 아래 그림과 같이 동작한다.

이 그림을 살펴보면 처음부터 끝까지 파일을 채우지 않고 파일 전체에 걸쳐 오브젝트 할당하여  분산시키는 것을 볼 수 있다. 이렇게 분산 하였을때 단점은 파일 시작 부분으로 부터 데이터를 재배치 해야하므로 축소 작업이 오래 걸릴수 있다. 또한 축소 작업중 지속적으로 임시 테이블이 생성되는 경우 축소가 안될 수도 있다. 그래서 일반적으로 축소 작업을 권장하지 않는다.

이번 업데이트로 변경된 디자인으로 파일 수를 늘리면 PFS 경합에 도움될 뿐만 아니라 파일 크기를 늘리면 파일의 PFS 페이지 수가 늘어난다. 이러한 동작은 PFS 경합을 해결하기 위해 여러개의 파일이 필요한 필수 조건을 대체한다. 하지만 위에서 나열된 파일 수에 대한 권장사항은 여전히 유효하며 실제로 PFS 경합외에도 tempdb 저장장치에 대한 I/O 경합이 있는 경우 여러 파일을 사용하면 여러 파일에 걸쳐 I/O의 균형을 쉽게 맞출수 있다.

[Metadata Contention]

개체 할당 경합은 많은 버전의 SQL Server에서 흔히 발생했지만 예전에 비해 처리량이 많아지면서 임시 테이블 생성 빈도 또한 증가하면서 tempdb의 또 다른 유형인 메타데이터 충돌이 발생했다. 이 경합은 임시 테이블을 추적하는데 사용되는 tempdb의 시스템 개체에 대한 경합이다. SQL Server 2000부터 메타데이터 경합이 발생했기 때문에 SQL Server 2005부터 임시테이블 캐싱이  도입 되었다. 저장 프로시저에 의해 작성된 임시 테이블의 메타 데이터를 캐싱하므로 경합을 줄일 수 있었다. 캐시는 무제한 리소스가 아니므로 새 객체를 위한 공간을 만들기 위해 주기적으로 캐시를 정리해야한다. 캐시에서 항목을 제거할 때마다 메타데이터 테이블에서 해당 행을 삭제해야 한다. 이때 캐시 삭제에 대한 경합이 발생한다.  사실 이 문제는 그리 크지 않았지만 SQL Server 2016에서는 새로운 기능과 함께 메타데이터가 증가 했기 때문에 이 문제가 더욱 두드러졌다. 임시 테이블과 Always Encrypted와 같은 기능은 추가 메타데이터가 필요하다. 즉 메모리 부족이나 캐시 항목이 무효화되어 임시 테이블 캐시를 제거해야하는 경우 과거보다 훨씬 많은 메타데이터를 제거해야한다.

이 문제를 해결하기 위해 임시 테이블 캐시를 정리하는 방법에 3가지 주요 변경사항을 적용했다.

1.       프로세스를 동기식에서 비동기식으로 변경

저장 프로시저 내에서 임시 테이블이 변경될 때마다(예 : 인덱스 추가, 열 추가, 명시적 드롭 등) 재사용할 수 없다. 이전 동작은 프로시저가 끝날 때 테이블을 동기적으로 삭제하는 것이었다. 이제 테이블은 즉시 삭제되지 않고 삭제된 목록으로 이동되어 나중에 다른 스레드에 의해 삭제 된다.

2.       NUMA 노드당 한개의 Helper 스레드 사용 및 각 패스마다 제거되는 테이블 수 증가

이전는 삭제할 목록이 특정 크기에 도달하면 캐시에 항목을 추가해야하는 스레드가 먼저 삭제 목록에서 개체를 제거해야했다. 이제 NUMA 노드당 하나의 스레드만 helper 스레드로 처리되고 스레드는 한번에 1개가 아닌64개의 개체를 제거한다.

3.       메타데이터 검색시 래칭 전략을 최적화

페이지에서 레코드를 제거하려면 메모리에서 해당 페이지에 독점(exclusive)으로 액세스 해야한다. 이는 페이지 래치 또는 버퍼 래치, 특히 PGAELATCH_EX이다. 원래 프로세스는 독점(exclusive )래치를 획득하여 삭제할 메타데이터 행을 검색했다. 이제는 공유 래치를 사용하여 먼저 행이 존재하는지 확인한 다음 삭제해야하는 행을 있을때에만 베타적인 래치를 획득한다. 우리가 찾고 있는 대부분의 메타데이터는 임시 테이블에 존재하지 않으므로 래치를 독점적으로 변환하지 않아도 된다. 이로인해 전반적인 프로세스가 차단을 일으킬 가능성이 줄어든다.

그러면 이 메타데이터 경합이 발생하는지 어떻게 알 수 있을까? 언뜻 보면 tempdb와 비슷한 모양을 보일 수 있다. PAGELATCH는 다양한 tempdb 페이지 (2:X:Y, X는 파일 번호, Y는 페이지 번호) 를 기다린다. 차이점은 PFS및 SGAM페이지가 아니라 sysobjvalues및 syseobjvalues와 같은 시스템 객체에 속한 페이지이다. 아래 링크는 tempdb의 메타 데이터 경합을 해결하는데 도움된다.

·         Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016  : https://support.microsoft.com/en-us/help/4131193/performance-issues-occur-in-form-of-pagelatch-ex-and-pagelatch-sh-wait

·         FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017  : https://support.microsoft.com/en-us/help/4058174/heavy-tempdb-contention-occurs-in-sql-server-2016-or-2017

그외에 경합을 줄이는 몇 가지 모범사례가 있다.

1.       저장프로시저의 끝에 임시 테이블을 명시적으로 삭제하지 말것. 임시 테이블을 만든 세션이 종료되면 자동으로 정리된다.

2.       임시 테이블을 생성 후에 변경하지 말것

3.       임시 테이블을 truncate하지 말것

4.       인덱스 작성문을 SQL Server 2014에 도입된 새로운 인라인 인덱스 작성구문으로 이동

위 방법을 사용하면 저장 프로시저에서 만든 임시테이블을 캐싱 할 수 있다. 임시 테이블에서 변경되거나 명시적으로 삭제된 임시 테이블은 재사용할 수 없으므로 삭제 대상으로 표시된다. 삭제해야하는 임시 테이블이 많을 수록 위에 설명된 충돌 가능성이 커진다. 일부 환경에서는 테이블 변수가 임시 테이블보다 오버헤드가 적으며 더 적합할 수도 있다. 테이블의 행수가 적다면(rule of thumbs is < 100) 테이블 변수가 좋은 대안일 수 있다. 하지만 테이블 변수는 통계가 작성되지 않는다는 점에 유의해야한다. 테이블 변수에 대해 실행중인 쿼리가 카디널리티 변경에 민감한 경우 TF 2453을 활성화하여 테이블 변수에 대한 카디널리티 기반으로 리컴파일을 설정하는 것이 좋다.

[Auditing Overhead]

SQL Server 2016에서 tempdb에 많은 부하를 주는 변경사항으로는 C2 감사라도고 하는 CCC (Common Criteria Compliance)와 관련 있다. SQL Server는 CCC에서 트랜잭션 수준 감사를 허용하는 기능을 도입했다. 특히 임시테이블에서 많은 삽입과 업데이트를 하는 작업에서 약간의 오버헤드가 발생할 수 있다. 하지만 이 오버헤드는 CCC를 사용 여부에 관계없이 발생한다. SQL Server 2016 SP1 CU2에서는 TF 3427을 적용하여 오버헤드를 무시하도록 설정 할 수 있다. SQL Server 2017 CU4에서는 CCC가 비활성화 된 경우 자동적으로이 코드를 무시한다.

·         FIX: Workloads that utilize many frequent, short transactions in SQL Server 2016 and 2017 may consume more CPU than in SQL Server 2014 : https://support.microsoft.com/en-us/help/3216543/workloads-that-utilize-many-frequent-short-transactions-in-sql-server

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/

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

SQL Server, SQL 2017, tempdb, object allocation,  PGAELATCH, 템프디비, 페이지 래치, 페이지 할당,

XEvent를 사용하여 Auto tuning 작업 모니터링

·         Version : SQL Server 2017

SQL Server 2017 부터 Auto tuning 기능이 도입되어 사용자 쿼리에 대해 SQL plan change regression방식으로 쿼리의 플랜이 변경되었을때 변경 사항을 감지하고 더 나은 플랜을 사용하도록 자동 조정한다. SQL Server는 각 쿼리에 대해 마지막으로 성공한 플랜을 추적하고 기존의 플랜과 비교하여 마지막 실행 플랜이 더 좋다고 판단되는 경우 강제로 마지막 플랜으로  변경 한다. 이번 포스트에서는 XEvent를  활용하여 Auto tuning이 적용되어 강제로 플랜이 변경 되었을때 추적하는 방법에 대해서 알아본다.  

Automatic tuning process는 변경된 플랜을 감지하지만 마지막으로 실행된 플랜이 기존의 플랜과 비교하여 성능 차이가 충분히 높지 않으면 마지막으로 사용된 플랜을 강제로 적용하지 않는다. Auto tuning 프로스세가 현재의 플랜과 마지막에 실행된 플랜과 비교할때XEvent의 qds.automatic_tuning_plan_regression_detection_check_completed 이벤트가 실행되어 추적할 수 있다.

·         is_regression_detected : SQL Server가 플랜 변경을 감지 했음을 의미

·         is_regression_corrected : 플랜이 수정 되었음을 의미

·         option_id : 항상 0 (FORCE_LAST_GOOD_PLAN은 SQL Server2017의 첫번째 자동 튜닝 옵션임)

 XEvent의 또 다른 중요한 필드는 cpu_gain으로 권장되는 플랜이 기존의 플랜을 대신하게 될 경우 향상되는 CPU 예상비용이10.000.000 마이크로 세컨드( 10 CPU second)보다 작으면 새로운 플랜이 강제로 적용되지 않을 수 있다.

새로운 플랜이 강제로 적용되면 Auto tuning는 지속적으로 강제 적용된 플랜을 모니터링하고 성능이 저하되지 않는지 확인한다. 성능 저하가 발생하면 Query Optimized는 새로운 계획을 생성할지(recompile) 기존 계획을 유지할지 결정한다. SQL Server가 플랜 검증을 완료할 때마다 XEvent의  qds.automatic_tuning_plan_regression_verification_check_completed  이벤트가 실행된다.

·         is_regression_detected : SQL Server가 강제 플랜을 감지하면 1로 설정 됨

·         is_regression_corrected : SQL Server Query Optimized가 새로운 계획을 만들면 1로 설정됨

SQL Server는 강제로 새로 적용된 플랜이 기존 플랜보다 좋지 않은 경우 새로운 플랜은 적용되지 않으며 데이터베이스 엔진은 컴파일과정을 통해 새로운 플랜을 생성한다.  SQL 엔진에서 강제된 플랜이 기존의 플랜보다 우수하다고 확인되면 다시 컴파일 되기 전까지 강제된 플랜이 유지 된다.

[참고자료]

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

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/07/18/monitoring-automatic-tuning-actions-using-xevents/

2018-06-19 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MS SQL, SQL 2017, SQL Auto Tuning, Query Optimized, XEvent, Monitoring automatic tuning actions using XEvents

SQL Server 2017 소규모 시스템에서 향상된 리소스 사용

·         Version : SQL Server  2017 CU4

SQL Server는 소규모 컴퓨터에서 엔터프라이즈 서버까지 확장 할 수 있도록 설계된 데이터베이스이다. 일반적으로 데이터베이스는 많은 자원을 필요로 한다. 특히 CPU 및 메모리는 데이터베이스의 성능에 매우 중요한 요소이다. (물론 디스크 성능도 중요하다.) SQL Server 2017 CU4에서는 작은 메모리(2GB)에서도 SQL Server 성능이 향상될 수 있도록 개선 되었다.

·         Improvement: Improves the performance of SQL Server 2017 on smaller systems : https://support.microsoft.com/en-us/help/4078095/improves-performance-of-sql-server-2017-on-smaller-systems

[Default Max Worker Threads]

이전에는 기본 최대 작업자 스레드 계산에서 RAM을 고려하지 않는 디자인이었다. 기본 워커 수를 계산하는 공식은 아래와 같다.

SQL Server 2017 CU4에서 변경한 사항은 최대 작업자 스레드를 계산할때, RAM 사이즈를 고려하여, RAM 크기가 제한된 시스템에서는 X64의 카운트는 2로 나누어져 기본 최대 작업자 수를 낮춰 준다. (작업자당 2MB 이하).

·         Sp_configure에서 ‘max workers’ 설정은 시스템 값을 무시할때 사용

·         기본값128 이상으로 설정하는 것을 권장

·         Sys.dm_os_sys_inof는 활성 가능한 최대 작업자 설정 값을 출력

[CLR Garbage Collection]

CLR 가비지 수집은 클리이언트 모드, 서버 모드가 있다. (SQL Server 기본값은 서버 모드 이다.) 서버 모드 가비지 수집은 시스템 CPU 수를 기반으로 CLR힙에 대한 공간을 예약한다. SQLCLR이 시작되면 서버 모드에서는  가비지 콜렉션을 초기화 하기위해 대량의 메모리 공간을 할당한다. 이러한 대규모 할당은 운영체제가 스와핑 또는 기타 활동을 유발 시킬 수 있다. 클라이언트 모드 가비지 콜렉션은 서버모드의 병렬처리 및 CPU 활동을 줄여 메모리와 리소스를 덜 차지 한다. SQL Server 2017 CU4에서 변경한 사항은 제한된 소규모 시스템에서 클라이언트 가비지 수집 모드를 사용하여 서버 모드의 가비지 수집시 발생하는 추가 오버헤드를 제거하는 것이다.

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/03/18/sql-server-2017-improved-resource-usage-on-smaller-machines/

2018-06-12 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MS SQL,  max worker threads, CLR, garbage collection, SQL 2017, 가비지 콜렉션, 워커스레드, 작업자 스레드

SQL Server 2017에서 향상된 UDF 실행 계획

·      Version : SQL Server 202017 CU3

SQL Server 2017 CU3 이후 부터 UDF(User-Defined Function) 실행계획이 개선되어 사용자에게 좀 더 많은 정보를 보여주게 되었다. (필자가 글 을 쓰는 시점으로 최신 CU7까지 출시 되었다.) 이전에는 쿼리에 참조된 UDF를 사용할 때 쿼리 실행 시간의 상당 부분이 함수 내에서 발생하여도 쿼리 실행계획을 보고 쉽게 파악하기가 어려웠다. SQL Server 2017 CU3 이후 부터는UdfCpuTime과 UdfElaspsedTime의 두 가지 새로운 속성이 추가되어 사용자 정의 함수내에서 사용되는 리소스를 확인할 수 있게 되었다.

아래 스크립트는 테스트를 구성하기 위한 UDF 생성 스크립트이다. 테스트 쿼리를 실행하기 위해서는 Adventureworks 가 설치되어 있어야 한다.

아래 스크립트는 UDF를 사용한 데이터 조회 스크립트이다. 실행 계획을 살펴보면 UDF 관련 정보를 확인할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/

2018-06-04 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL 2017, MS SQL, UDF 실행계획, 실행계획, DBA, DB튜닝, 향샹된 실행 계획 보기, SQL Server 2017, Showplan enhancements for UDF

SQL Linux에서 Job Agent 설치

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서Job Agent를 설치하는 방법에 대해서 알아본다. SQL Server Job Agent는 반복적인 작업을 예약하여 실행 할 수 있는 서비스이다.

SQL Linux Job Agent를 설치하기에 앞서 update 명령을 사용하여 설치된 패키지를 최신버전으로 업데이트 할 수 있도록 한다.

패키지 동기화가 완료되면 아래 명령을 사용하여 SQL Server vNext 의 최신 버전을 설치한다.

설치가 완료되었으며 update 명령을 사용하여 다시 한번 최신 버전을 동기화 한다.

SQL Server 설치 및 update가 완료되었으면 아래 명령을 실행하여 SQL Job Agent 를 설치 한다.

설치가 완료되면 안내 구문에 따라 SQL Server서비스를 재시작 한다.

아래 명령은 서비스가 정상적으로 실행되고 있는지 상태를 나타낸다.

SQL Server 재시작이 완료되고 서비스가 정상적으로 실행 되었으면  SSMS를 실행하여 SQL Linux에 연결한다. SSMS에서 Job Agent가 실행중인 것을 확인할 수 있으며, 예약 작업을 등록하여 사용할 수 있다.

[참고자료]

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent

2017-08-08 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 Job Agent 설치, SQL Linux  Job Agent, SQL 2017, SQL vNext, install SQL job agent

SQL Linux에서 Windows SQL 백업 파일 복원

·         Version : ubuntu, SQL Server Linux

Windows SQL Server에서 생성한 백업 파일을 Linux SQL 에서 복원하는 방법에 대해서 알아본다. 기본적으로 Windows SQL Server에서 수행한 백업에서 Linux SQL Server에서 복원하는 방법은 Windows와 거의 유사하다. 다른점은 Windows와  Linux는 디렉터리 구조가 다르므로 RESTORE 명령과 함께 MOVE 옵션을 사용하여 디렉터리 경로를 입력하면 된다. (Windows에서도 물리적 위치를 변경할 경우 move 명령을 사용한다.- 결국 복원 방식은 동일함.)

편의상 실습은 SQL Linux에서 Adventurework2014.bak 샘플 데이터베이스를 다운로드 받아서 복원한다. (필자는 편의상 /var/tmp 디렉터리를 사용하였다.)백업 파일 정보를 확인하는 명령을 실행하여 현재 bak 파일에 포함되어 있는 경로를 확인한다. Windows 경로가 포함되어 있는 것을 확인 할 수 있다.

아래 백업 명령을 실행하여 데이터베이스를 복원한다. 이때 MOVE 옵션을 사용하여 디렉터리를 변경한다.

정상적으로 데이터 파일이 생성되고 DB 가 복원된 것을 확인할 수 있다.

2017-07-28 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 복원, SQL Linux 백업 파일 복원, SQL 2017, restore database

Linux에서 Network I/O 확인

·         Version : ubuntu

SQL Server Linux 버전 출시로 MS SQL DBA분들이 Linux에 대한 관심이 많이 높아졌다. 이번 팁에서는 Linux에서 SQL Server를 사용할때 알아두면 좋은 리눅스 팁을 공유한다. 이번 포스트는 Linux에서 Network I/O를 확인하는 방법이다.

[netstat]

네트워크 통계의 약자인 netstat 명령어는Windows 사용자에게도 잘 알려져있는 명령이다. 사용방법은 Windows와 거의 동일하지만 Linux에서는 이 명령을 사용하면 더 많은 정보를 얻을 수 있다.

-i 옵션은 네트워크 인터페이스를 나열한다. 이 명령을 사용하면 인터페이스 이름과 보내고 받은 패킷을 확인할 수 있다.

·         RX-OK / TX-OK : 정상적인 패킷 수

·         RX-ERR / TX-ERR : 체크섬 오류로 거부된 패킷 수

·         RX-DRP / TX-DRP : 전체 퍼버에서 누락된 패킷 수

·         RX-OVR / TX-OVR : 시스템이 바빠서 누락된 패킷 수

-ltu 옵션은 수신포트(l)를 TCP(t)와 UDP(u)로 표시한다. 또한 -p옵션을  (-ltup) 추가하면 수신 대기중인 프로그램이 표시 된다.

[slurm]

Slurm은 터미널 기반의 네트워크 모니터링 도구이다. 이 유틸리티 는 표준 배포와 함께 배포되지 않으므로 아래 명령을 사용하여 설치 해야 한다.

설치가 완료되었으면 모니터할 인터페이스 이름을 인수로 전달하여 명령어를 실행 한다. 예를 들어 enp0s3 인터페이스를 모니터링 할려면 다음과 같이 명령어를 실행 한다. (인터페이스 이름은 netstat -i 로 확인한다.)

위 그림에서 보듯이 네트워크 활동을 표시하는 그래프가 상단 패널에, 텍스트 정보는 하단 패널에 나타난다.

[speedometer]

기본 배포에 포함되어 있지 않으므로 아래 명령어를 설치를 해야한다.

아래 명령어는 enp0s3 인터페이스의 RX/TX를 모니터링하는 명령이다.

여러 인터페이스를 모니터링 하려면 각각의 인터페이스명을 나열 한다.

[ifstat]

기본 배포에 포함되어 있지 않으므로 아래 명령어를 설치를 해야한다.

명령을 실행하면 아래와 같이 네트워크 사용량이 지속적으로 나타난다.

[nethogs]

응용프로그래별로 네트워크 사용량을 확인할 때 편리 하다. 기본 배포에 포함되어 있지 않으므로 아래 명령어를 설치를 해야한다.

위 그림에서 볼수 있듯이 PID, 사용자, 프로그램 별로 네트워크 사용량을 확인 할 수 있다. 이 유틸리티는 root 권한으로 실행 해야 한다.

[참고자료]

https://www.mssqltips.com/sqlservertip/4846/linux-administration-for-sql-server-dbas-checking-network-io/

2017-07-18 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux disk, 리눅스에서 네트워크 사용량 확인, SQL 2017, check network usage on Linux, netstat, slurm, speedometer, ifstat, network I/O,

SQL Linux에서traceflag 활성화

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서traceflag 활성화 하는 방법에 대해서 알아본다. 기존 Windows 의 SQL Server에서 추적 플래그를 사용하려면 아래 두 가지 방법 중 하나를 사용할 수 있다.

·         DBCC Traceon (traceflag, -1) (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-transact-sql)

·         SQL Server Configuration manager

SQL Linux에서 추적플래그를 전역으로 활성 또는 비활성화 하려면 mssql-conf 라는 SQL Server 구성 관리자 유틸리티를 사용해야 한다.  우선 아래 명령어로 현재 설정되어 있는 모든 추적플래그에 대한 정보를 확인한다.

Linux에서 추적 플래그를 활성화 하기 위해 mssql-conf 유틸리티를 사용하여 추적 플래그를 활성화 한다. 설정이 완료되면 SQL Server 서비스를 재시작 한다.

서비스 재시작이 완료 되었으면 현재 설정되어 있는 traceflag 를 확인 한다. Traceflag 1222가 적용된 것을 확인할 수 있다.

여러개의 추적 플래그를 사용하려면 아래 스크립트처럼 추적 플래그를 나열한다.

추적 플래그를 중지하려면 off 명령을 사용한다.  중지하려는 추적플래그 파라메터를 함께 명시 한다. 설정이 완료 되면 SQL Server 서비스를 재시작 한다. 명시된 추적플래그가 중지된 것을 확인할 수 있다.

추적 플래그를 설정할 때는 운영중인 서버에 적용하기 전에 반드시 테스트 서버에 적용하여 사이드이펙트를 체크해야 한다.

[참고자료]

·         https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#a-idtraceflagsa-enabledisable-traceflags

·         https://www.mssqltips.com/sqlservertip/4762/how-to-enable-and-disable-sql-server-traceflags-globally-for-linux-instances/

·         https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-transact-sql

2017-07-14 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 추적플래그 설정, SQL Linux traceflag, SQL 2017, dbcc tracestatus, traceflag on, traceflag off, mssql-conf

SQL Linux에서 dump file 위치 변경

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서 dump file 위치 변경하는 방법에 대해서 알아본다. Dump file 에 대한 경로 변경은  /opt/mssql/bin 폴더에 있는 mssql-conf 스크립트 내용을 변경하여 dump file 위치를 변경할 수 있다. 기본적으로 덤프 디렉토리는 SQL Server를 설치할때 설정된 /var/opt/mssql/log 이다.  아래 명령어를 이용하여 해당 디렉토리에 대한 파일을 확인할 수 있다.

이번 실습에서는 /tmp/dump 라는 디렉토리를 생성하고 dump file의 경로를 변경한다. 우선 아래 스크립트를 실행하여 /tmp/dump 디렉토리를 생성한다.

 SQL Server가 생성한 디렉토리에 접근할 수 있도록 폴더의 소유자와 그룹을 변경한다.

아래 명령을 실행하여 dump file의 생성 위치를 변경한다. 변경 후 SQL Server 서비스를 재시작 해야  한다.

[참고자료]

·         https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#a-iddumpdira-change-the-default-dump-directory-location

·         https://www.mssqltips.com/sqlservertip/4768/how-to-change-the-default-dump-file-location-of-sql-server-running-on-linux-server/

2017-07-11 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 dump file 위치 변경, SQL Linux  dump file, SQL 2017, mssql-conf, Change SQL Server dump file location, filelocation.defaultdumpdir

SQL Linux에서 Port 변경

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서Port를  변경하는 방법에 대해서 알아본다. SQL Linux의 경우도 기본적으로 1433 포트를 사용하고 있다. 보안 또는 다른 이슈로 포트 번호를 변경해야 하는 이슈가 있을때  /opt/mssql/bin 폴더에 있는 mssql-conf 스크립트 내용을 변경하여 Port 를 변경할 수 있다.

SQL Linux에 접속하여 root 권한을 획득 한다. 많은 사람들이 이미 기본포트가 1433인것을 알고 있지만 실제 사용하는 포트를 확인하기 위해 netstat 명령을 사용하여 서버에서 실행중인 모든 연결을 확인한다.

기본적으로SQL Server 포트는 1433이며, 1434는 SQL Server Browser 서비스에 사용된다. 이번 실습에서는 SQL Server포트를 1435로 변경하도록 한다. 아래 스크립트를 실행하여 포트 번호를 변경한다.

포트 변경이 완료되면 SQL Server 재시작을 해야한다. SQL  서비스 재시작이 완료되었으면  다시한번 netstat –tulpn을 실행하여 SQL Server가 사용중인 포트를 확인한다.

SQL Server가 1435 포트를 사용하는것을 확인할 수 있다. 실제 1435 포트를 이용하여 SQL Server에 연결할 수 있는지 확인한다.

[참고자료]

·         https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#a-idtcpporta-change-the-tcp-port

·         https://www.mssqltips.com/sqlservertip/4736/how-to-change-sql-server-port-number-for-a-linux-server/

2017-07-10 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 port 변경, SQL Linux  port, SQL 2017, mssql-conf, Change SQL Server port number, network.tcpport

Linux에서 DISK 공간 확인

·         Version : ubuntu

SQL Server Linux 버전 출시로 MS SQL DBA분들이 Linux에 대한 관심이 많이 높아졌다. 이번 팁에서는 Linux에서 SQL Server를 사용할때 알아두면 좋은 리눅스 팁을 공유한다. 이번 포스트는 Linux에서 디스크 공간을 확인하는 방법이다.

Linux에서 DISK 공간을 확인하기 위한 가장 기본 적인 방법은 ls 명령을 사용하는 것이다.  ls  명령은 Windows의 dir /w를 실행할 때와 같이 파일 크기 정보 없이 아카이브 정보만 반환 한다. 옵션인 -l(소문자 L)을 함께 실행하면 파일 전체의 목록을 확인할 수 있다.

출력된 결과의 각 항목은 파일에 대한 정보를 표시 한다.

위 정보를 보면 파일 크기가 바이트 단위로 표시되어 큰 파일의 경우 가독성이 불편하다. h 인수 ls 명령과 함계 사용하면 메가바이트 단위로 공간을 표시 한다.

df명령은 사용된 디스크 공간과 파일 시스템의 마운트 지점을 표시 한다. df의 자세한 옵션은 df 메뉴얼(https://www.gnu.org/software/coreutils/manual/html_node/df-invocation.html)을 참고 한다.

아래 명령은 df의 다양한 인수를 추가하여 특정 디렉토리의 파일 크기를 확인한다.

du명령은 주어진 디렉토리 트리와 하위 폴더 및 파일 크기를 표시한다. 이 명령을 사용하기 위해서는 해당 디렉토리 및 파일에 대해 읽기 권한이 필요하다. 따라서 파일 크기만 알고 싶다면 ls –l 명령을 사용하는것이 좋다.

Sort 인자를 추가해서 파일크기에 대해서 정렬해서 볼 수 있다.

2017-06-20 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux disk, 리눅스에서 DISK  공간 확인, SQL 2017, check disk usage on Linux, ls, df, du, disk space

SQL Linux에서collation 변경

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서collation을  변경하는 방법에 대해서 알아본다. Collation은 문자열 정렬 및 비교 방법에 대한 규칙을 지정하는데 사용된다. Collation이 다르면 쿼리의 동작이나 달라지거나 조인을 사용할때 일부 문제가 발생할 수 있다.  자세한 내용은 아래 포스트를 참고 한다.

·         Collation에 따른 실행계획 변경과 성능 문제 : http://sqlmvp.kr/140199553043

·         Collation에 따른 ALTER DATABASE 실패 : http://sqlmvp.kr/140194406833

·         Collation에 따른 DMV 실행 오류 : http://sqlmvp.kr/140185365035

·         Collation 변경 (Windows SQL) : http://sqlmvp.kr/140211882558

Linux에서 SQL Server를 설치 할때 collation을 선택하는 옵션이 없으면 기본값을 사용하여 설치 된다. 아래 명령을 사용하여 현재 SQL Server 인스턴스 collation을 확인한다.

SQL Server collation이 SQL_Latin1_General_CP1_CI_AS 임을 알 수 있다. 이번 포스트에서는 Korean_Wansung_CI_AS로 변경하는 방버에 대해서 알아본다.

우선 가장 먼저 SQL Server를 중지한다. 아래 명령을 사용하면 SQL Server를 중지 할 수 있다.

mssql-conf를 사용하여 SQL Server collation을 변경한다. Set-collation 옵션이 데이터 정렬을 변경하는 명령어이다. 아래 명령을 실행하여 collation을Korean_Wansung_CI_AS로 변경한다.

위 명령을 실행하면 Collation 변경 입력 프롬프트가 나타나고 사용자에 따라 필요한 collation을 입력한다. 변경이 완료되면 완료되었다는 메시지가 표시 된다.

변경이 완료 되었으면 서버를 시작하고 정상적으로 collation 이 변경되었는지 확인한다.

현재 사용자의 시스템에서 지원 가능한 collation을 확인하는 방법은 아래 명령을 사용한다.

[참고자료]

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#collation

https://www.mssqltips.com/sqlservertip/4763/how-to-change-sql-server-collation-on-linux/

http://slavasql.blogspot.com/2017/03/SSOL-set-collation.html

2017-07-05 / 강성욱 / http://sqlmvp.kr

SQL Linux, Linux SQL, 리눅스에서 collation 변경, SQL Linux  collation, SQL 2017, mssql-conf

SQL Linux에서 데이터 및 로그 파일의 기본 디렉토리 변경

·         Version : ubuntu, SQL Server Linux

SQL Server Linux에서 데이터 파일 및 로그 파일의 기본 디렉토리 위치를 변경하는 방법에 대해서 알아본다. Linux SQL Server의 경우 msslq-conf  유틸리티의 스크립트에 의해 수행 된다.

아래 스크립트를 실행하여 데이터베이스의 현재 기본 위치의 파일을 표시 한다. 모든 데이터베이스 파일이 /var/opt/mssql/data 에 위치하고 있음을 알 수 있다.

임시로 새로운 데이터베이스를 생성하여 경로를 확인하여도 기본 폴더인 /var/opt/mssql/data 로 생성된 것을 확인 할 수 있다.

이제 데이터베이스 파일의 위치를 /var/opt/mssql/data에서  /opt/mssql/data  폴더로 바꾸어 새로운 데이터베이스를 만들때 마다 새로운 경로에 생성되록 한다. 우선 새로운 경로의 디렉토리를 생성한다.

/opt//mssql/data 디렉토리 생성이 완료 되었으면  SQL Server 가 이 디렉토리에 접근할 수 있도록 폴더의 소유자와 그룹을 변경한다.

이제 mssql-conf 를 사용하여 기본 데이터베이스 파일의 위치를 설정한다. 이 스크립트는 /opt/mssql/bin 디렉토리에 위치하고 있다. 아래 명령을 사용하여   해당 경로에  파일이 있는지 확인 할 수 있다.

아래 명령을 실행하여 기본 데이터베이스 파일 위치를 /opt/mssql/data 로 변경한다.

기본 디렉토리 변경이 완료 되었으면 반드시 서비스를 재시작해야 변경사항이 적용 된다.

변경된 내용을 확인하기 위해  sqlcmd 를 사용하여  SQL Server 에 연결한다. 이때 아래와 같은 문제가 발생한다.

이 문제는 tempdb 의 데이터와 로그 파일이 수동으로 새 위치(/opt/mssql/data)로 이동하여 생성되면서 발생한 것으로 아래 명령을 실행하여 두 파일을 모두 기존 위치로 이동한다.

Tempdb 데이터 및 로그 파일을 기존 위치로 이동 후sqlcmd 를