Data Science Lab

 SSMS – AlwaysOn Group Latency report

·         Version : SQL Server 2014 SP2 later, SSMS 17.4 later

SQL Server에서 AlwaysOn가용성에 대한 Latency 분석을 하려면 XEvent(확장이벤트)  및 Performance Counter(성능 모니터)를 사용하여 데이터를 캡처하고 분석하는 과정이 필요했다. 이러한 작업은 설정에 많은 시간이 소요되었을 뿐만 아니라 XEvent에 대한 광범위한 지식이 필요하였다.

SQL Server의 관리도구인 SQL Server Management Studio (SSMS) 17.4 버전 부터는AlwaysOn 가용성 그룹에 대한 Latency 대시보드 기능이 추가되었다.  이 대시보드에서는 데이터베이스 관리자가 가용성 그룹 및 가용성 복제본 및 데이터베이스의 현재 상태를 확인하는데 사용할 수 있다. 대시보드에는 1차 복제본과 2차 복제본 간의 대기 시간(시간 계산은 Commit LSN, Sent LSN 의 값을 사용하여 계산)에 대한 정보를 제공하고 있다. 이러한 대시보드 정보를 바탕으로 가용성 그룹의 기본 복제본과 보조 복제본간의 로그 전송 흐름에서 병목현상을 쉽고 빠르게 파악할 수 있다.

SSMS에서 AlwaysOn Latency 에 대한 데이터 수집 설정 및 보고서를 확인하는 방법에 대해서 살펴본다.

SSMS는 17.4 이상의 버전을 실행한다. SSMS에 대한 최신 버전 다운로드는 아래 링크를 참고 한다.

-          Download SSMS :  https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Always On High Availability 항목에서 [Availability Groups] – [사용자 가용성 그룹]에서 마우스 오른쪽을 클릭하여 [Show Dashboard]를 실행 한다.

대시보드가 실행되면 Collect Latency Data를 클릭 한다. 이 항목을 실행하면 데이터 수집을 위한 각종 기능이 백그라운드에서 진행되며 SQL Server Job 에이전트에 수집기 스케줄러가 등록된다.

데이터 수집 링크를 클릭하면 SSMS가 가용성의 모든 복제본에 연결되어 데이터 수집 작업을 시작한다. 이때 주 복제본 및 보조 복제본의 SQL Server Agent가 실행 중이어야 한다.

수집 기능 설정이 완료되면 아래와 같이 Job Agent에 데이터 수집기 항목이 등록된 것을 확인할 수 있다.

스케줄러를 확인해보면 다양한  XEvent가 등록된 것을 볼 수 있다. 등록된 XEvent를 실행하여 약 2분 정도 데이터를 수집한다. 수집된 데이터는 TempDB의 AGInfo 테이블에 저장된다.

데이터 수집이 완료되면 Latency에 대한 보고서를 확인한다. 이때 주 복제본 서버와 보조복제본 서버의 리포트를 따로 확인 한다.

Always On High Availability 그룹에서 마우스 오른쪽 버튼을 클릭하여 [Report] – [Standard Report] – [Primary Replica Latency] 를 실행한다.

주 복제본의 Latency 보고서에는 3개의 섹션이 있다. 첫 번째 섹션에서는 AG에 대한 복제본 정보를 제공한다.

두 번째 섹션에서는주 복제본의 Commit 및 복제 시간의 평균을 시각적으로 제공한다.

세번째 섹션은 커밋에 사용된 시간,  원격 복제 시간, 압축시간 및 로그 블록의 전송 지속시간에 대한 값을 제공한다.

보조복제본 보고서에는 보조복제본에서만 소요된 시간을 제공한다.

-          Local Flush – 로그 블록을 LDF파일에 기록하는데 소요된 평균 시간. 이 값은 Log_Flush_Start 및 Log_Flush_Complete_Extended 이벤트를 사용하여 계산된다.

-          Decompression - 1차로 로그블럭을 수신한뒤 압축해제에 소요된 시간. SQL Server 2016 이상에서는 비동기 복제본에 대한 로그 전송이 압축되는 동안 동기 보조복제본에 대한 로그 전송은 압축되지 않는다.

-          Receive – USC 계층에서 로그 블록을 수신하고 추가 처리를 위해 대기열에 대기하는데 소요되는 시간

-          Send – 확인 메지시를 SQL의 USC 계층으로 보내는데 소요된 시간. 여기에는 보조복제본에서 주복제본까지 소요된 시간이 포함되지 않는다.

가용성 그룹 대기시간 데이터 수집기능은sysadmin(Windows 인증 전용) 계정에서만 사용할 수 있으며 현재 사용자 계정은 모든 복제본의 인스턴스 관리자 계정이어야 한다.

데이터 수집 기능에 대한 제약사항은 아래와 같다.

1.       한번에 하나의 가용성 그룹에 대한 대기 시간 데이터를 수집하는데에만 사용할 수 있다.

2.       TempDB에 저장되는 데이터는 테이블이름이 하드코딩되어 하나의 컬렉션만 실행할 수 있다.

3.       TempDB에 저장되므로 SQL Server가 재시작되면 이전 기록된 데이터 정보가 손실된다.

4.       로그인 계정이 서버관리자 계정이어도 SQL 인증 사용자 또는 로그인에 대해서 수집 기능을 사용할 수 없다.

5.       Windows 인증만 지원하므로 도메인간 트러스트 없는 도메인간 배포에서는 작동하지 않는다.

6.       분산된 가용성 그룹에서는 이 기능이 작동하지 않는다.

7.       SQL Server 2014 SP2 이상에서 동작한다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/new-in-ssms-always-on-availability-group-latency-reports/

2018-01-19 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MS SQL, SSMS, Always On, 가용성 그룹, Latency Report, 복제 시간, commit time, High availability Group, AG,

SQL Server 백업 압축의 압축률 확인

·         Version : SQL Server 2008 later

SQL Server의 백업 압축은 SQL Server 2008부터 지원되며 압축된 백업은 동일한 데이터의 압축되지 않는 백업보다 작으므로 일반적으로 I/O에 대한 비용이 절약되며 백업속도가 크게 향상된다. 하지만 압축을 하기 위한 추가 연산으로 약간의 CPU 오버헤드가 발생한다. 하지만 시스템 중에 가장 느린 부분은 디스크 이므로 CPU의 오버헤드를 감안하여도 충분히 이점이 있는 작업이다.

백업 압축 알고리즘은 사전 할당 알고리즘을 사용하여 실제 백업이 완료 되기 전까지 파일의 크기를 알수 없다.

·         백업 압축과 추적플래그 3042 : http://sqlmvp.kr/220356963341

단, 인덱스 정보, ROW 수, 페이지 정보를 바탕으로 어느 정도 유추는 가능하다.

·         데이터 압축 상태에 대한 개체 크기 예상 : http://sqlmvp.kr/140189749347

실제 압축 백업이 완료되었을때 어느정도  압축률이 발생했는지 확인하기 위해서는 백업 기록 테이블에서 원래의 백업 사이즈와 압축된 백업사이즈를 계산하면 확인이 가능하다.

2018-01-03 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MSSQL, SQL backup, compress backup, SQL 백업, DB 백업, 압축 백업, 백업 파일 크기, 압축률

SQL Server 교착 상태 모니터링 (-T1222)

·         Version : SQL Server

SQL Server에서 추적 플래그 –T1222를 사용하면 오류 로그에  XML 스키카 형식(XSD) 를 따르지 않는 XML 형식으로 교착 상태 정보가 기록된다. XML형식에는 3가지 주요 섹션이 있다.

·         첫번째 섹션은 교착 상태의 희생자를 선언한다.

·         두번째 섹션은 교착 상태와 관련된 각 프로세스에 대해 설명 한다.

·         세번째 섹션은 리소스를 설명 한다.

·         교착 상태 모니터링 : http://sqlmvp.kr/140164120810

교착 상태를 모니터링 할 경우 오버헤드가 추가되어 잠금 모니터 속도가 느려지고 더 긴 블록킹 시나리오가 발생할 수도 있다.  그래서 이러한 오버헤드를 줄이기 위해 교착 상태를 모니터링하는 –T1222 추적 플래그는 백그라운드 작업을 총 5개의 작업으로 제한하여 교착 상태 출력 작업으로 인해 스케줄러가 넘치지 않도록 디자인 되었다.  즉 교착 상태의 활성화가 6번째 작업이  발생하면 잠금 모니터는 오류 로그에 교착 상태 정보를 기록한다.

[참고자료]

·         https://blogs.msdn.microsoft.com/bobsql/2017/05/23/how-it-works-sql-server-deadlock-trace-flag-1222-output/

·         http://sqlmvp.kr/140164120810

·         https://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/

2018-01-02 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MSSQL, SQL Lock, dead lock, Trace flag 1222, TF 1222, -T1222, 데드락, 교착 상태, 락 모니터링, 데드락 모니터링

SQL Operations Studio

-         Linux, MAC에서 사용할 수 있는 SQL DBMS 툴

·         Version : Preview

SQL Server를 관리하기 위한 Microsoft 툴은SQL Server Management Studio(SSMS)가 있다. 이 툴은 Windows 환경에서 실행되며 막강한 기능을 가지고 있다. 아마 SQL Server를 운영하는 대부분의 관리자는 타 DBMS를 운영하는 관리자와는 달리 서드파티의 툴을 사용하지 않고 대부분 SSMS를 사용하고 있을 것이다. 그만큼 SSMS의 기능은 엄청나다.

최근 Microsoft가 오픈소스 친화적인 정책을 지향하면서 다양한 툴들이 Linux 또는 Windows가 아닌 운영체제에서 실행될 수 있게 되었다. 그 중 대표적인 툴이 Visual Studio Code 일 것이다. 이처럼 Microsoft는 크로스 플랫폼 또는 멀티 플랫폼을 지원하는 다양한 서비스를 출시하고 있다.

2017년 Microsoft에서 SQL Server Linux 버전을 출시하였다. 기본적인 기능은 이미 Windows 버전의 SQL Server와 거의 동일하다. 일부 편의 기능만 아직 출시 되지 않았을 뿐, DBMS의 기능은 훌륭하다. 하지만 SSMS는 Windows 버전 밖에 지원되지 않아, SQL Server를 Linux에 설치함에도 불구하고 관리툴은 윈도우 환경에서 실행하여야 했다. 하지만 최근 Microsoft에서는 SQL Operations Studio라는 새로운 DB 관리 툴을 출시 하였으며, 윈도우가 아닌 다른 환경도 지원하게 되었다. 이번 포스트에서는 SQL Operations Studio에 대해서 살펴본다.

SQL Operations Studio는 아래 링크에서 다운로드 받을 수 있다. 포스팅 하는 이 시점에는 Preview 버전이 공개된 상태로 향후 어떠한 많은 기능이 추가될지 기대 된다.

·         Download and install Microsoft SQL Operations Studio (preview)

https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

다운로드 받은 파일을 압축 해제 한다. SQL Operations Studio는 설치형으로 실행되는 툴이 아닌 exe를 바로 실행하여 사용하는 툴이다. 그래서 따로 설치 과정이 필요 없다. Sqlops.exe를 실행 한다.

SQL Operations Studio를 실행한 첫 화면이다. VS Code와 비슷한 UI를 하고 있다.

서버 등록 정보를 생성하기 위해 왼쪽의 메뉴바에서 Server를 클릭하고 새로운 서버를 등록한다.

접속하려는 SQL Server의 정보를 입력한다. 필자의 경우 로컬에 설치된 SQL Server의 접속 정보를 입력하였으며, 여러번의 테스트로 인해 최근 히스토리에 서버 내용이 표시됨을 알 수 있다.

서버 정보를 입력하고 처음 서버를 접속했을 때 화면이다. 기본적으로 현재 생성되어 있는 데이터베이스에 대한 정보를 표시해 주며, 생성되어 있는 데이터베이스의 디스크 사용량 및 백업 유무에 대한 관리적인 관점에서의 정보도 표시해서 보여준다. (일반적으로 DBA 들에겐 크게 도움이 되지 않는 정보이지만 DBA가 아닌 사람들에겐 유용한 정보일 듯하다. 하지만 해당 정보를 생성하기 위해 많은 쿼리를 호출하는 과정이 대용량 서비스 경우 살짝 걱정이 된다.)

쿼리를 호출하기 위해 쿼리창을 실행한다. 상단 메뉴에서 New Query 를 실행하여 쿼리 창을 실행 할 수 있지만 이렇게 실행하면 해당 쿼리 창을 다시 DB에 연결해야하는 번거로움(순수 에디트 창만 오픈)이 있다. 그래서 쿼리를 실행하려는 데이터베이스에서 마우스 오른쪽을 클릭하여 New Query를 선택한다. 그러면 해당 데이터베이스에 연결된 쿼리창이 실행 된다.

AdventureWorks 데이터베이스에 쿼리를 실행해 보았다. 기본적으로 SSMS와 동일하게 결과 집합이 표시된다. 몇 가지 흥미로운 점은 Visual Studio 처럼 해당 리소스에 대해서 직접 참조하거나 Peek 창으로 오버레이하여 참조되는 정보를 확인할 수 있다. 여기서 참조 되는 정보는 테이블 정보라 할 수 있다.

아래 그림은 Go to Define를 했을 때 새창으로 테이블 정보를 나타낸 모습이다.

실행 계획 버튼으로 쿼리 결과에 대한 실행 계획도 바로 확인 할 수 있다. 물론 SSMS 처럼 실행 중의 쿼리 실행 계획 등 아직은 많은 기능이 제공되지는 않지만 부족한 기능은 정식버전에서 많이 추가 될것이라 생각한다.

결과 집합 창의 오른쪽에 위치한 몇 가지 아이콘이 있는데, CSV, Excel, JSON 형식의 파일로 저장할 수 있는 기능이다. 아마도 많은 사용자들이 결과셋을 파일로 저장하는데 편의 기능을 보충하기 위해 퀵메뉴를 지원하는 듯 한 느낌이었다.

결과셋 오른쪽 아이콘 중에 마지막 아이콘은 Chart 아이콘인데, 결과에 대한 내용을 Chart로 보여준다. Chart는 몇 가지 형태로 제공되고 있었다. 하지만 어떤 내용을 기준으로 차트를 생성하는지는 정확히 알 수 없었다. 만약 간단한 카운트나 비율에 대한 결과 값을 조회 하였다면 차트 기능을 이용하여 굳이 엑셀로 내려 받아서 차틑를 그리지 않아도 확인 할 수 있을 듯 했다.

아직은 Preview 버전이어서 기존의 SSMS에 비하면 많은 기능이 지원되지 않지만 가벼우면서 기본기에 충실한 툴의 느낌이 들었다. 아직은 프로파일러 기능이나, 어드바이저 기능들이 제공되지 않지만 시간이 지나면서 많은 기능이 포함되리라 생각이 든다. 무엇보다 Windows 환경이 아닌 곳에서도 SQL Operations Studio를 설치해서 사용할 수 있으니 Linux  등 다양한 환경에서 SQL Server 생태계 확대가 기대된다.

[참고자료]

·         https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

·         https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

·         https://docs.microsoft.com/en-us/sql/sql-operations-studio/quickstart-sql-server

2017-12-04 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MSSQL, SQL 2016, SQL Server Management Studio, SSMS, SQL Operations Studio, SQL 관리 툴, DB 관리 툴, SQL Linux, SSMS Linux

SQL Server 2016 Large RAM 및 Checkpoint

·         Version : SQL Server 2016, 2014, 2012

SQL Server의 Checkpoint는 현재 데이터베이스의 buffer pool에 있는 커밋되지 않는 데이터 페이지를 디스크로 플러시하는 작업이다. 하드웨어 사양이 고급화 되면서 수 TB이상의  메모리를 사용하는 경우가 늘어나고 있는데,  많은 양의 메모리가 버퍼캐시로 할당되어 실행될 때 SQL Server에서 발생할 수 있는 DB 체크포인트와 관련한 내용에 대해서 알아본다.

이 포스트는 CSS SQL Server Engineers 내용을 정리한것으로 자세한 내용은 원문을 참고하길 바란다.

·         SQL Server : large RAM and DB Checkpointing : https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/

FlushCache라고 하는 Database Checkpoint의 ‘Legacy(레거시)’ 구현은 특정 데이터베이스의 체크포인트를 실행하기 위해 SQL Server의 전체 버퍼풀을 검사해야한다. 체크포인트의 지속 시간은 일반적으로 요청하는 더티페이지 수에 의해 결정되지만 대형 RAM을 사용하는 프로덕션 서버의 경우 RAM크리가 수 TB 값에 도달하면서 체크포인트 시간 자체가 큰 의미가 있게되었다.  스캔 지속시간은 버퍼풀의 크기와 직접 연관이 있으며 최근 관찰된 값은2TB 메모리의 경우 약 12초 정도였으며 시스템마다 다를 수 있다.

DPM(Data Protection Manager)이라는 새로운 간접 체크포인트 옵션은 더 이상 버퍼풀 검사에 의 존하지 않는다. SQL Server 2012에서 처음 도입된 Indirection checkpoint는 완전한 DPM을 사용할 수 있는 것은 아니었다.  SQL Server 2016에서는 데이터베이스 생성시 기본적으로 간접 체크포인트가 활성화되는 DPM 방식을 사용하여 대용량 메모리를 사용하더라도 지연이 발생하지 않는다.

DPM을 활성화 하기 위해서는 아래 요구사항이 충족되어야 한다.

required build

·         SQL Server 2012 : SP3 CU3 and later

·         SQL Server 2014 : RTM CU14 or SP1 CU7 and later

Indirect Checkpoint activated for the relevant Databases

·         ALTER DATABASE Dbname SET TARGET_RECOVERY_TIME = 60 SECONDS

·         Any non-zero value activates Indirect Checkpoint

Traceflag 3449 enabled

·         DBCC TRACEON (3449,-1)

·         (the traceflag activation/deactivation has immediate effect without service restart needed)

·         or more likely, startup parameter for the instance (-T).

FlushCache 통계를 에러로그에 기록하기 위해서는 특정 TF를 적용하여 사용할 수 있다. SQL Server 2012이상에서는 실제로 매우 느릴때 해당 메시지를 자동으로 생성하기도 한다. TF는 추가 로깅이기 때문에 다른 시스템에 영향을 미치지 않고 안전하다.

SQL Server 2012 또는 2014에서는 TF3504를 적용하였을때 아래와 같은 메시지가 기록된다. 기록된 로그를 살펴보면  FlushCache 가 수행되고 있음을 알수 있지만 실제로FlushCache가 안정적으로 진행되는지에 대해서는 알수가 없다. 또한 대형 RAM을 사용하는 서버의 경우 1ms가 몇 초 정도 지연될수도 있다. 0 bufs write를 나타낸 경우 주로 디스크 입출력 지연과 관계되어 있지 않기 때문에 buffer pool 스캔에 묶여 있다고 판단할 수 있다.

SQL Server 2014이상 사용자는 TF3502를 추가적으로 사용하여 FlushCache 또는 DPM 구현을 사용하는지 관계없이 Checkpoint Operation이 발생하는것을 반영한다. DPM 경로의 경우TF 3504만으로 보고된 것이 없기 때문에 TF3502를 함께 사용하면 유용하다.

SQL Server 2016에서는 TF3504 정보가 업그레이드되어 각 체크포인트에 대해 FlushCache 또는 DPM메시지를 생성하므로 TF3504하나로 모든 메시지를 얻을 수 있다.

대형 RAM을 사용하는 서버의 경우체크포인트 로그 정보를 활용하여 DPM을 활성여부를 판단하여 체크포인트에 대한 지연을 방지 할 수 있도록 한다.

[참고자료]

·         https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/

·         SQL Server2012 Checkpoint 제어 : http://sqlmvp.kr/140171578379

·         Checkpoint 추적하기 : http://sqlmvp.kr/140192370231

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

SQL Server, MSSQL, SQL 2016, Larger RAM Checkpoint, Checkpoint, DPM, Data Protection Manager, TF3504,  TF3502,

SQL Server 2016 JSON 형식을 일반  ROW 형식으로 반환하기

·         Version : SQL Server 2016

SQL Server 2016 부터 지원되는 JSON 형식은 쿼리 결과를 JSON 형식으로 반환 할 수 있으며 JSON 형식의 데이터를 일반 ROW로 데이터를 반환할 수도 있다. 이번 포스트에서는 JSON 형식의 데이터를 일반 ROW형식으로 반환하는 방법에 대해서 알아본다.

JSON 형식의 텍스트를 구문 분석하여 일반 ROW 형식으로 데이터를 반환하는 함수는 OPENJSON 이다. OPENJSON 함수를 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 데이터에 대해서 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있다.

OPEN JSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 받아서 하나 또는 여러 개의 행으로 변환한다. 기본적으로 OPENJSON 함수는 다음 데이터를 반환한다.

·         JSON 개체에서 이 함수는 첫 번쨰 수준에서 발견된 모든 키:값 쌍을 반환한다.

·         JSON 배열에서 이 함수는 모든 요소  및 해당 인덱스를 반환한다.

WITH 옵션을 사용하여 열을 명시적으로 지정할 수 있다.

[기본 구조를 사용하는 OPENJSON]

결과에 대해 명시적으로 스키마를 제공하지 않는다.

[명시적 구조를 사용하는 OPENJSON]

OPENJSON 함수의 WITH절을 함께 사용하여 결과의 스키마를 지정하여 WITH에 포함된 열만 반환한다.

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

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

SQL Server 2016, JSON format, OPENJSON

model Database 손상시 발생하는 영향 및 복구 방법

·         Version : SQL Server

SQL Server에서 사용되는 system database에는 master, model, msdb, temp 4가지가 있다. master 경우 SQL Server에 대한 속성 정보를 가지고 있기 때문에 중요성에 대해서 많은 사람들이 인지하고 있다. 이번 시간에는 model 데이터베이스의 중요성에 대해서 알아보고 model 데이터베이스가 손상되었을때 어떤 영향이 있는지, 그리고 복구 방법에 대해서 알아본다.

model 데이터베이스는 SQL Server 인스턴스에서 생성된 모든 사용자 데이터베이스의 템플릿으로 사용된다. 새로운 데이터베이스를 생성하면 데이터베이스 옵션을 포함하여 model 데이터베이스 전체 내용이 새로운 데이터베이스로 복사된다. tempdb 또한 SQL Server 가 재시작 될때 model 데이터베이스로부터 일부 속성을 상속 받아 생성된다. 일반적으로 사용자 데이터베이스의 손상은 빠르게 감지 되지만 (사용자 데이터베이스는 서비스에서 계속 사용중이므로) model 데이터베이스의 경우 시스템이 재시작되거나 새로운 데이터베이스가 만들어질때 까지 손상을 발견하기가 쉽지 않다.

만약 model 데이터베이스가 손상된 채로 시스템이 재시작 되면 어떻게 될까? 위에서 설명하였듯이 SQL Server가 재시작될때 temp를 새로 생성하게 되는데 이때 model 데이터베이스를 참조하는데 model 데이터베이스 가 손상되었으므로 tempdb 또한 생성이 실패된다. 파일 시스템에서 오류 로그를 확인해 보면 tempdb가 손상되어 SQL Server를 시작하지 못했다는 오류를 발견할 수 있는데, 근본적인 원인은 model 데이터베이스가 손상되어 발생한것으로 오류 로그로는 명확하게 원인을 파악하지 못할 수 도 있다.

model 데이터베이스 손상으로 SQL Server가 시작되지 않을 시에는 아래  두 플래그를 적용하여 SQL Server를 시작할 수 있다.

·         -T3608 :  마스터 데이터베이스만 복구, tedbmp 가 필요한 작업이 시작되면 model 이 복구되고 tempdb가 생성된다.

·         -T3609 : 모든 데이터베이스 복구, tempdb는 지우지 않음.

만약 위 방법으로 복구되지 않고, model 데이터베이스에 대한 백업본이 없다면 동일한 SQL Server 버전 및 데이터 정렬(collation)을 사용하는 다른 데이터베이스로 데이터 파일과 로그 파일을 복사하여 복원할 수 있다.

2017-09-13 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MS SQL, model, System database, suspect page, dbcc checkdb, system database curruption

In-Memory OLTP 사용시 메모리 할당량 초과 오류

·         Version : SQL Server 2014, 2016

SQL Server 에서 In-Memory OLTP를 사용하는 환경에서 아래와 같은 오류가 에러로그에 기록되어 있다면 현재 적용되어 있는 In-Memory 용량을 초과한 것이다.

오류 로그에 기록된 내용은 아래와 같다.

·         requested = 131200 : 요청된 바이트 수

·         available = 74641 : 사용할 수 있는 바이트 수

·         quota = 34359738368 : 현재 할당된 바이트 수 (Standard Edition : 32G)

·         operation = 1 : 메모리 최적화 테이블 변수를 생성할 수 있도록 메모리가 요청 됨

Ø  operation 0 : create memory optimized table

Ø  operation  1 : create memory optimized table variable

Ø  operation  2 : insert

Ø  operation  3 : update

위의 메시지 외에도 사용자 어플리케이션에서는 아래와 같은 메시지를 수신하는 경우도 있다.

메모리 최적화 테이블 변수 사용시 루프를 사용할때 위와 같은 메모리 부족 오류가 자주 발생 할 수 있으니 주의 할 수 있도록 한다.

·         메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) : http://sqlmvp.kr/220996905075

SQL Server 2016 In-Memory OLTP quotas.

[참고자료]

·         https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/in-memory-oltp-in-standard-and-express-editions-with-sql-server-2016-sp1/

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

SQL Server 2016, In memory OLTP, SQL Server, DBA, Memory optimize table, In Memory quota

SQL Server 2016 쿼리 실행에 대한 각 스레드(오퍼레이터) 성능 통계

·         Version : SQL Server 2014 SP2, 2016

SQL Server 2016에서 쿼리 성능 문제를 해결할 때 참고 할 수 있는 몇 가지 정보가 추가 되었다. 그 중 대표적인 것이 라이브 쿼리 통계 이다.

·         SQL Server 2016 라이브 쿼리 통계 : http://sqlmvp.kr/220453033961

하지만 아직도 많은 쿼리 성능 문제를 해결하기 위한 메트릭은 부족한 편이다. 이번에 소개하는 기능은 showplan XML을 활성화하고 쿼리를 실행하였을때 각 스레드(오퍼레이터)에서 실행한 결과를 확인할 수 있는 기능이다. 예를 들어 SQL Server2014까지는 쿼리 실행결과 행결과를 반환하지만 스캔 성능에 관해 자세한 정보를 제공하지는 않았다.

SQL Server 2016 부터는 실제 행 수를 포함하여 I/O read, CPU 시간 등의 내용이 스레드 별로 표시 된다.

퀴리가 병렬로 수행되는 경우 아래와 같이 각 스레드의 실행 내용이 표시된다.

또한 새로운 확장이벤트(query_thread_profiler)를 도입하여 각 노드 및 스레드의 성능에 대한 통찰력을 제공한다. 아래 스크립트는 이벤트를 생성하는 예제이다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

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

SQL SQL Server 2016, Live query stats, operator level performance stat, DBA, SQL Tuning, DB Tuning, xEvent, MS SQL

Multisubnet환경의 AG 그룹에서 링크드 서버 사용시 주의점

·         Version : SQL Server 2012, 2014, 2016

SQL Server 2012부터 도입된 가용성 그룹(AG)은 SQL Server에서 제공할 수 있는 최고의 가용성을 보장하지만 구성이 까다로울 수 있다. 특히 다중 서브넷 환경에서 장애 조치를 구성하는 경우 예상치 못한 문제를 많이 겪을 수 있다. 필자 또한 단일 서브넷에서의 AG 경험은 있었지만 다중 서브넷 환경에서 구성 및 운영은 처음이어서 (Cloud  VM으로  다중 서브넷(Multi Region구성)  다양한 이슈를 겪고 있다.(기술적 이슈는 아님)

이번 이슈는 Multisubnet  AG구성을  구성한 환경에서 링크드 서버를 구성할때, Native Client를 사용하였을 경우 Node 1에서 Node2로 Failover되었을때 Node2번으로 접속하지 못하는 경우가 발생하였다.  이 문제를 해결하기 위해서는 ODBC를 사용해서 링크드 서버를 구성해야 한다. 아래 구성받법을 참고해서 링크드 서버를 구성 할 수 있도록 한다.

ODBC 드라이버를 다운로드 받아 설치 한다. 이때 11버전 이상을 선택 한다. (SQL 2016의 경우ODBC 드라이버 버전이 13이다.)

·         Microsoft® ODBC Driver 13 for SQL Server® - Windows + Linux

https://www.microsoft.com/en-us/download/details.aspx?id=50420

AG에서 사용할 ODBC 연결을 생성한다. System DSN 탭에서 연결을 생성한다.

Datasource로 ODBC Dirver 13 for SQL Server를 선택한다.

ODBC에서 사용할 이름을 지정하고 Server의 경우 AG의 리스너 이름을 입력한다.

기술적으로는 로그인 비밀번호를 넣지 않아도 되지만 링크 서버에 사용할 로그인과 비밀번호를 사용하여 테스트 할 수 있다. 물론 계정 및 비밀번호는 저장되지 않는다.

마지막 단계에서Multisubnet failver 옵션을  체크한다. 이 기능이 Failover 되었을때 다른 노드로 연결을 시도한다.

ODBC 생성이 완료되었으면 SSMS에서 링크드를 구성한다. 공급자는 ODBC 드라이버를 선택한다. 그리고 Datasource부분에 ODBC에서 생성했던 ODBC 이름을 사용하여 링크드 서버를 구성한다.

[참고자료]

https://royalsql.com/2016/09/29/mayday-this-is-an-emergency-my-linked-server-is-using-odbc-to-connect-to-an-ag/

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

SQL Server 2016, MS SQL , SQL Server Always On, AG 구성, 멀티서브넷 이중화, Linked Server, 링크드 서버, ODBC 링크드 구성

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 옵션은 네트워크 인터페이스를 나열한다. 이 명령을 사용하면 인터페이스 이름과 보내고