SQL

[진행사항]

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행하는지를 문의주신 건으로 이해됩니다.

AG의 데이터 동기화는 Primary Replica에서 발생한 Transaction log를 Secondary Replica에서 Redo하는 단계로 진행됩니다.

참고로 SQL Server 2016 이후 Parallel redo thread(Instance당 최대 100개, Database당 최대 16개)를 지원합니다.

다만, 이 Parallel redo thread의 개수는 Primary replica에서 Transaction log를 발생시킨 쿼리의 DoP와 관계 없이, 현재 Secondary replica에서 Transaction log를 Redo하는 부하 상황에 따라 달라지게 됩니다.

: SQL Server 2016/2017: Availability group secondary replica redo model and performance - Microsoft Community Hub

이 때문에, Primary replica에서 병렬 Index Rebuild 수행 시 Secondary replica에서 병렬로 Redo를 수행할 가능성은 높으나,
Index Rebuild 상황을 Redo하는 Secondary Replica의 Redo Thread 개수는, 일반적으로 Primary replica의 Index Rebuild Thread 개수보다 적게 됩니다.

예를 들어 다음과 같이 테스트환경에서 약 10GB 크기의 테이블에 대해, MAXDOP 4의 병렬 Index Rebuild를 수행하여 Secondary Replica에서 Redo thread를 모니터링 하면,
Primary Replica는 DoP 4로 Index Rebuild를 수행하고 있으나, 같은 시점의 Secondary Replica는 3개의 Redo thread로 Transaction을 Redo 하고 있음을 확인할 수 있습니다.

Case	Result
Test Scenario - 약 10GB 크기의 테이블에 Maxdop=4로 Index Rebuild를 수행	EXEC sys.sp_spaceused 'dbo.tbl_Index_ParallelBuild' GO           ALTER INDEX UCL__tbl_Index_ParallelBuild__01 ON dbo.tbl_Index_ParallelBuild REBUILD WITH (MAXDOP = 4) GO
Primary Replica - 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해 74번 세션에서 수행중인 Index Rebuild 수행 상황을 모니터링 하면, 2개의 Consumer thread를 제외하고, 4개의 Thread로 Parallel Rebuild 수행중임을 확인할 수 있습니다.
Secondary Replica - 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해 AG의 Redo 수행 상황을 모니터링 하면, AGDB01 데이터베이스에 대해 3개의 Parallel redo thread가 Index rebuild를 수행중임을 확인할 수 있습니다.

1.     파티션 테이블 압축시 서비스 영향도

-> 온라인으로 가능한지, IO 발생 및 압축진행중인 파티션 사용시 lock 발생 등의 위험요소가 궁금합니다.

-     파티션별 압축의 경우에도 전체 인덱싱과 마찬가지로 온라인 인덱싱을 지원합니다.

Online Indexing의 경우, 인덱싱 작업중 대상 테이블/파티션에 대한 CRUD가 가능하나, 아래와 같이 단계에 따라 작업 및 잠금을 획득하며, 이 과정에서 여러가지 형태의 성능 경합과 잠금이 발생합니다.

다만 해당 환경의 성능 여유가 충분한 상황이라면, 일반적으로 이와 같은 성능 경합이 심각한 성능 영향을 주지는 않습니다.

이와 관련하여서는 다음의 문서를 통해 조금 더 세부적인 내용을 확인하실 수 있습니다.

: How Online Index Operations Work - SQL Server | Microsoft Learn

Phase	Source activity	Source locks	Architecture
Preparation : Short phase	-  System metadata preparation to create the new empty index structure.   -  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.   -  Concurrent user write operations on the source are blocked for a short period. No concurrent DDL operations are allowed except creating multiple nonclustered indexes.	-  S (Shared) on the table*   -  IS (Intent Shared)   -  INDEX_BUILD_INTERNAL_RESOURCE**
Build : Main phase	-  The data is scanned, sorted, merged, and inserted into the target in bulk load operations.   -  Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.	-  IS   -  INDEX_BUILD_INTERNAL_RESOURCE**
Final : Short phase	-  All uncommitted update transactions must complete before this phase starts.   -  Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed.   -  System metadata is updated to replace the source with the target.   -  The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.	-  INDEX_BUILD_INTERNAL_RESOURCE**   -  S on the table if creating a nonclustered index.*   -  SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

-     다음은 온라인 인덱싱 작업에 대한 일반적인 가이드라인입니다. Online Indexing 작업 중 발생할 수 있는 여러가지 이슈사항과 사전 고려사항을 정리하고 있으며,
Update 부하가 높은 환경에서 Online Indexing 중, 종종 발생하는 Deadlock(자동으로 해소)과 같은 증상도 해당 문서를 통해 확인할 수 있습니다.

: Guidelines for online index operations - SQL Server | Microsoft Learn

-> 온라인으로 절대 해서는 안되는 작업인지

-     온라인 수행에 문제는 없습니다. 다만 고객환경의 비즈니스로직에서의 성능 영향도를 파악하기 위해 고객사 내부적으로 충분한 테스트를 거쳐 검증하신 후 작업을 수행하실 것을 권장 드립니다.

-> 압축 진행중인 해당 파티션에만 영향을 주는 것인지

-     특정 파티션에 대해 압축을 수행하는 경우, 결과적으로는 해당 파티션에만 영향을 주는 것이 맞습니다.

하지만 압축을 진행하는 과정의 마지막 단계에서 Metadata를 업데이트 하며 Sch-M 잠금을 획득하므로, 동 시간대 대상 테이블에 대해 Sch-S/Sch-M을 요청하는 작업들과의 일시적인 성능 경합이 있을 수 있습니다.

2.     테이블/인덱스 압축시 내부 프로세스 및 모니터링 필수 요소

-> 압축대상 파티션을 바로 압축을 시도 하는지 혹은 별도의 데이터파일 여유 공간에 생성후 전환 (rebuild 처럼) 등등

-     인덱스의 파티션별 압축 작업 또한 마찬가지로 ALTER INDEX REBUILD 작업을 수행하며, 데이터파일 신규 공간을 할당 받아 Sort 및 인덱스를 생성한 후 기존의 개체를 대체하게 되므로 추가 공간을 사용하게 됩니다.

예를 들어 30GB 크기의 A인덱스의 1번 파티션을 압축하여 10GB로 압축이 수행된 경우, 작업 시작 시점에 Datafile 내 여유공간이 없었다면 데이터파일의 크기가 10GB 증가하게 됩니다.

-     Sort_in_tempdb 옵션을 사용하는 경우에도 새로운 공간에 신규 인덱스를 배치한 후 기존의 인덱스 개체를 제거하므로 추가 Disk 공간의 사용은 발생합니다.

-> 롤백이 발생하게 되나요?

-     Offline Indexing의 경우 작업을 중단하는 경우 해당 작업에 대한 Rollback이 발생하며, Online Indexing의 경우에도 작업 중단에 의해 메타데이터 업데이트를 위한 짧은 Rollback이 필요합니다.

-     작업 수행 중 다음의 내용을 모니터링 하실 수 있을 것으로 의견 드립니다.

①     Data / Log Disk 사용률 모니터링

②     Blocked Process 발생 여부 모니터링

③     Data File 사용률 모니터링

④     Log File 사용률 모니터링

-> 파티션 압축 작업을 진행하게 되면 로그 사용량의 변화는 어떻게 되는지 궁금합니다.

-     전체 인덱스를 한 번에 압축하는 경우와 한 개 파티션 씩 압축하는 경우 모두 거의 유사한 크기의 Transaction Log가 발생하는 것으로 확인됩니다.

##SQL Version :Microsoft SQL Server 2019 (RTM-CU17) (KB5016394) - 15.0.4249.2 (X64)

확인사항]

먼저 DTC실패(Error: 8509)기록된후 Rollback하는 과정에서 Dump가 생성으며, 해당 이슈는 이미 알려진 이슈로 CU21에서 FIX가 되었습니다.

해당 Dump는 in-memory Table사용과정에서 발생할수있으며, 고객사같은 경우는 DTC Transaction이 Rollback 되는 과정에서 해당 known issue로 인한 Dump가 생성었고 이로인한 가용성그룹이 Resolving상태로 빠지게됩니다.

이후 Leasetime out이 발생하나 FailureConditionLevel(2)로 Failover가 자동적으로 Triggered되지 않았습니다.

해당 이슈가 Failover를 Trigger하는 부차적인 이슈가 있으며, 이로인한 failover를 반복적으로 수행되나 해당 역할을 Online시키지 못함에 따라 반복적으로 dump로 인한 failover가 수행되었습니다.

이과정에서 large transaction rollback및 반복적인 failover시도로인한 DB연결이슈(983)가 발생한 것으로 확인하였습니다.

이후 고객사에서 LES Server재시작(2024-04-02 14:49)이후 정상적으로 'DATABASE_P03' Online되며 AG가 정상적으로 Online되었습니다.

[제안사항]

해당 이슈는 CU21에서 FIX되었으며, 최소 CU21로 patch하시는 것을 제안드립니다.

Cumulative update 21 for SQL Server 2019 (KB5025808) - SQL Server | Microsoft Learn

다만, 기존 known issue들에 의 한 잠재적인 위험요소가 있기에 가장 마지막 버젼인 CU25로 업그레이드 하시는 것을 권장드립니다.

Download SQL Server® 2019 for Microsoft® Windows Latest Cumulative Update from Official Microsoft Download Center

일반적으로 백업은 DML을 blocking 하지 않으나 백업이 진행중일 때는 database/file을 shrink 할 수 없으며,

Primary에서 Log shrink 를 진행해도 Secondary에도 적용이 되기 때문에 위와 같은 Lock wait이 발생하게 됩니다.

1. AG 설정 xml 파일 생성

아래 쿼리를 Primary, Secondary(Async relica)에서 각각 실행

생성되는 링크 클릭

각각의 xml output을 primary.xml , secondary.xml 로 저장하여 전달

추가 Extended event 수집

문제가 발생하는 시간대를 특정지을 수는 없다고 하셨지만, 지연이벤트가 집중되는 시간대가 확인되시는 경우 해당 시간대에 수집하여 주시기 바랍니다.

아래 수집 경로 및 파일명도 원하시는 대로 변경하실 수 있으며, 서버 사용량에 맞게 file size 및 rollover file 개수를 조정하시기 바랍니다.

보내드리는 스크립트는 500MB 파일이 4개 생성되는 구조입니다.

Secondary의 경우 지연 현상에 대해 확인이 필요한 Async 노드에서만 수집하시기 바랍니다.

분석에 필요한 event가 SQL Server 2014 SP2 / 2016 SP1 이후 추가되었기 때문에 이전 버전에서는 해당 이벤트 수집이 어려운 점, 참고 부탁드립니다.

KB3173156 - Update adds AlwaysOn extended events and performance counters in SQL Server 2014 or 2016 - Microsoft Support

Perfmon 수집

1) cmd를 관리자권한으로 실행한 뒤 아래 명령을 실행하면 성능 수집기가 생성됩니다.

• C:\sqlperflogs 경로에 파일이 생성되고 5초 간격으로 카운터를 수집하도록 설정되어 있습니다. 옵션을 고객사 환경에 맞게 적절하게 변경하셔도 관계 없습니다.
• M/d/yyyy 를 다음날로 설정하시면 다음날 00시부터 수집이 되며, 24시간(23시간 59분 59초) 간격으로 새로운 파일 생성이 됩니다.
• 만약 날짜 상관 없이 수집을 바로 시작하고 24시간 간격으로 수집을 원하실 경우 아래, 명령어를 변경하신 후 start 해 주시기 바랍니다.
• 로그를 수집한 후 파일 사이즈를 확인하고 문제가 재현되지 않을 경우 디스크 공간을 많이 차지하지 않도록 주기적으로 로그 파일을 수동 제거할 필요가 있습니다.

DEFAULT INSTANCE

NAMED INSTANCE
“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.
Ex) MSSQL$NAMEDINS à MSSQL$DB2

-- Database Mirroring 항목 추가

DEFAULT INSTANCE

NAMED INSTANCE

“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.

Ex) MSSQL$NAMEDINS à MSSQL$DB2

2) 성능 수집을 중지하려면 아래 명령어를 입력합니다.

Logman.exe stop SQLPerfLog

파라미터 변경이 필요하신 경우 아래 링크 참고 부탁 드립니다. 

logman create counter | Microsoft Learn

위 logman 명령은 perfmon (성능 모니터)에서 로그 수집기의 중지 조건이나 스케줄을 GUI로도 편집하실 수 있습니다.

(optional) lsn 정보 수집

아래 내용을 수집 기간 동안 분 단위로 수집하여 주시면 분석에 도움이 될 수 있습니다.

다만 서버에 부하가 갈 수 있을 경우 수집하지 않으셔도 됩니다.

• 읽기 전용 보조 복제본의 장기 실행 활성 트랜잭션 ---- > 읽기 가능한 보조 복제본  에 장기 실행 트랜잭션이 있는 경우 동일한 데이터에 액세스할 때 보조 복제본의 로컬 다시 실행 스레드가 차단 될 수 있습니다 . 

• 높은 네트워크 대기 시간 / 낮은 네트워크 처리량   ----> 1차와 발급된 2차 간의 네트워크 전송이 느리거나 불안정한 경우 로그 전송 속도가 느려지고 2차로 전송되는 데이터의 양에 영향을 미칩니다(문서 https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn135335(v=sql.110)?redirectedfrom=MSDN )  

• 흐름 제어로 인해 높은 로그 전송 큐 크기 및 낮은 로그 전송 속도 ---- >   SQL AG의 성능이 특정 SQL AG 데이터베이스 또는 SQL AG 복제본 수준에서 흐름 제어 게이트를 트리거하면 로그 데이터 전송이 일시적으로 종료되고 제한됩니다. 초등부터 중등까지. 흐름 제어는 일반적으로 서버 과부하 또는 느린 네트워크로 인해 발생할 수 있습니다. AG에서 흐름 제어 게이트에 진입하는 것을 지속적으로 관찰한 경우 흐름 제어를 유발하는 환경 조건을 해결해야 합니다. 아래 성능 모니터 카운터를 확인하여 SQL AG 데이터베이스 또는 SQL AG 복제본이 흐름 제어 게이트를 트리거했는지 확인할 수 있습니다. 

• 차단된 REDO 스레드   ---- 보조 복제본에 다른 문제나 성능 병목 현상이 있는 경우 REDO 스레드가 차단되어 데이터 동기화 대기 시간이 발생할 수 있습니다. (문서 https://techcommunity.microsoft.com/t5/sql-server-blog/alwayson-minimizing-blocking-of-redo-thread-when-running/ba-p/383963 )

• 공유 REDO 대상   ----- 이 문제는 보조 복제본이 여러 개 있는 경우에만 발생합니다. 하나의 보조 복제본이 다시 실행 프로세스를 완료하는 데 매우 느린 경우 다시 실행 프로세스를 통해 허용되는 최대 LSN에서 다른 보조 복제본이 더 이상 진행되지 않도록 제한합니다. 추적 플래그 9559를 사용하여 문제를 완화할 수 있습니다.  (문서 https://learn.microsoft.com/en-us/archive/blogs/alwaysonpro/recovery-on-secondary-lagging-shared-redo-target )

• 보조 복제본의 병렬 다시 실행 스레드 부족  --- 보조 복제본에 병렬 다시 실행 스레드가 부족하면 TF3478을 사용하여 총 CPU 수에 따라 병렬 다시 실행 스레드의 최대 수가 증가하도록 허용할 수 있습니다. 기본적으로 SQL Server 인스턴스는 보조 복제본에 대한 병렬 다시 실행을 위해 최대 100개의 스레드를 사용합니다. 각 데이터베이스는 총 CPU 코어 수의 최대 절반을 사용하지만 데이터베이스당 스레드는 16개를 초과할 수 없습니다. 단일 인스턴스에 필요한 총 스레드 수가 100을 초과하면 SQL Server는 나머지 모든 데이터베이스에 대해 단일 다시 실행 스레드를 사용합니다. 

• 디스크 또는 I/O 하위 시스템 대기 시간 --> 보조 복제본의 디스크 또는 I/O 하위 시스템에 심각한 병목 현상이 있는 경우 다시 실행 속도가 영향을 받으며 보조 복제본에서는 매우 작습니다. 보조 복제본을 읽을 수 없고 SQL 데이터베이스 파일 스토리지에 대해 기본 및 이 SQL AG의 사용 전용 하드웨어와 유사한 하드웨어가 있는 경우 기본 복제본에 도달하기 전에 보조 복제본에서 이를 볼 가능성이 적습니다. 그러나 보조 복제본에 읽기 전용 워크로드의 양이 많은 경우 IO 하위 시스템에 추가 IO 오버헤드가 발생할 수 있습니다. 빈번한 데이터베이스 백업 및 VM 스냅샷은 추가 I/O 오버
• 헤드의 또 다른 잠재적인 원인이 될 수 있습니다. 

https://techcommunity.microsoft.com/t5/sql-server-support-blog/common-causes-and-troubleshooting-solutions-for-sql-ag-data/ba-p/2963083

가용성 복제본 모니터링

sys.availability_replicas

각 SQL Server Always On 가용성 그룹에 대한 기존 가용성 복제본에 대한 정보를 얻습니다. 예를 들어 내 랩 환경에는 [demoag] 및 [SQLAGDemo]라는 두 개의 가용성 그룹이 있습니다. 각 가용성 그룹에는 2개의 복제본이 있으므로 출력에 4개의 행이 표시됩니다.

이 DMV 출력에 유용한 열은 다음과 같습니다.

• endpoint_url: SQL Server Always On 가용성 그룹은 기본 및 보조 복제본 통신에 데이터베이스 미러링 엔드포인트를 사용합니다. SQL Server는 가용성 그룹을 정의하면 엔드포인트를 자동으로 구성합니다. 이 열에서 엔드포인트 URL을 얻습니다.
• availability_mode 및 availability_mode_desc: AG가 동기 또는 비동기 모드로 구성되는지 여부를 지정합니다.
  • 0: 비동기 커밋
  • 1: 동기 커밋
  • 4: 구성 전용: 이 모드에서는 기본 복제본이 보조 복제본으로 데이터를 보내지 않습니다. AG 구성 메타데이터만 동기화합니다.
• failover_mode 및 failover_mode_desc: 가용성 복제본의 장애 조치 모드는 사용하는 가용성 모드에 따라 다릅니다.
  • 동기식 모드: 자동 및 수동 장애 조치
  • 비동기식 모드: 수동 장애 조치
  이 DMV에서 장애 조치 모드에 대해 다음 값을 얻습니다.
  
  • 0: 자동 장애 복구
  • 1: 수동 장애 조치
• secondary_role_allow_connections: 읽기 전용 워크로드에 보조 복제본을 사용할 수 있습니다. 이 열에 대해 다음 값을 볼 수 있습니다.
  • 0: 연결 없음
  • 1: 읽기 전용
  • 2: 읽기 전용 액세스를 위한 모든 연결
• create_date: AG 복제본의 생성된 데이터입니다.
• backup_priority: 가용성 그룹 구성에서 복제본의 백업 우선 순위를 구성할 수 있습니다. 기본적으로 각 복제본에는 50개의 백업 우선 순위가 있습니다. 0에서 100까지의 값을 사용할 수 있습니다.

• 자세한 내용은 Always On 가용성 그룹의 백업 이해 문서를 참조하세요.

• read_only_routing_url: 가용성 그룹에 대한 읽기 라우팅 URL을 구성한 경우 이 열에서 해당 URL을 가져옵니다. 제 경우에는 구성하지 않았습니다. 따라서 NULL 값을 보여줍니다.

• 읽기 전용 라우팅 에 대한 자세한 내용은 SQL Server 2016에서 가용성 그룹에 대한 읽기 전용 라우팅을 구성하는 방법 문서를 참조하십시오.

• seeding_mode: 직접 시드의 경우 자동 값을 표시하고 그렇지 않으면 수동 시드를 얻습니다.
• read_write_routing_url: SQL Server 2019에서는 모든 읽기-쓰기 연결에 대해 보조 복제본에서 주 복제본으로 연결을 리디렉션할 수 있습니다. 사용자가 보조 복제본에도 연결하는 경우 내부 연결이 기본 복제본으로 다시 라우팅되기 때문에 오류 메시지가 표시되지 않습니다.

• 읽기-쓰기 URL을 구성하지 않았습니다. 따라서 출력에 NULL 값을 제공합니다.

자세한 내용은 Microsoft 문서 를 참조하십시오.

sys.dm_hadr_availability_replica_cluster_nodess

WSFC에서 AG의 가용성 복제본에 대한 정보를 제공합니다. 복제본 상태에 관계없이 가용성 복제본에 대한 정보를 얻습니다.

출력에서 복제본 서버와 인스턴스 이름을 볼 수 있습니다.

sys.dm_hadr_availability_replica_states

로컬 복제본, 원격 복제본 및 해당 동기화 상태에 대한 세부 정보를 얻는 데 유용한 DMV입니다. 다음은 이 DMV의 중요한 열입니다.

• role 및 role_desc: 이 열에서 SQL Server Always On 가용성 그룹의 복제본 역할을 얻습니다.
  • 0= 해결 중 - 해결 중 상태에서 Windows Server 장애 조치 클러스터는 장애 조치 중간 또는 실패 상태일 수 있습니다. WSFC에 쿼럼이 없으면 AG도 상태 확인에 표시됩니다.
  • 1: 기본(AG 기본 복제본)
  • 2: 보조( AG 보조 복제본)
• Operational_state 및 Operational_state_desc : 이 열에서 복제본의 현재 작동 상태를 가져옵니다. 이 열에 다음 값을 가질 수 있습니다.
  • 0- 보류 중인 장애 조치
  • 1- 보류 중:
  • 2- 온라인
  • 3- 오프라인
  • 4-실패
  • 5- No Quorum으로 인한 실패
  
  
  • 기본: 온라인, 보류 중, 실패
  • 보조: 온라인, 실패 및 NULL
  • 해결 중: 오프라인, Pending_failover, 실패 및 쿼럼 없음으로 실패
• recovery_health 및 recovery_health_desc: 이 DMV에 대한 ONLINE_IN_PROGRESS 및 ONLINE 값을 가져옵니다. DMV sys.dm_hadr_database_replica_states의 database_state 열에 대한 롤업입니다.
• synchronization_health 및 synchronization_health_desc: 동기 또는 비동기 커밋에서 모든 가용성 그룹 데이터베이스의 동기화 상태를 가져옵니다. 이러한 열에 가능한 값은 다음과 같습니다.
  • 0: 건강하지 않음
  • 1: 부분적으로 건강함
  • 2: 건강한

가용성 데이터베이스 모니터링

이 범주에서는 SQL Server Always On 가용성 그룹의 가용성 데이터베이스와 관련된 동적 관리 보기를 살펴봅니다.

sys.dm_hadr_auto_page_repair

SQL Server Always On 가용성 그룹은 자동 페이지 복구 기능을 제공합니다. 손상된 페이지의 경우 아래 이미지와 같이 다른 복제본에서 페이지를 수신합니다.

• 자세한 내용은 SQL Server Always On 가용성 그룹의 자동 페이지 복구 문서를 참조하세요.

sys.dm_hadr_database_replica_states

기본 및 보조 복제본 동기화, 로그 시퀀스 번호, 데이터베이스 상태, suspend\resume에 대한 정보를 얻는 데에도 유용한 DMV입니다. AG 대시보드에서 기본, 보조 복제본 상태에 대한 정보를 볼 수 있습니다. 대시보드는 이 DMV에서 정보를 가져와 그래픽 형식으로 표시합니다.

기본 복제본과 보조 복제본 모두에서 AG 대시보드를 실행할 수 있습니다. 기본 복제본의 AG 대시보드에는 모든 복제본에 대한 정보가 표시됩니다. 그러나 보조 복제본에서 시작하면 연결된 보조 복제본에 대한 정보만 가져옵니다.

아래 이미지에서 AG 대시보드와 DMV 출력을 볼 수 있습니다.

• 기본 복제본의 AG 대시보드:

• 

• 보조 복제본의 AG 대시보드:
• 

• database_id: AG 데이터베이스의 데이터베이스 ID입니다. db_name() 함수를 사용하여 데이터베이스 이름을 얻을 수 있습니다.
• is_primary_replica: 기본 복제본에 대해 값 1을 반환하고 보조 복제본에 대해 0을 반환합니다.
• synchronization_state 및 synchronization_state_desc: AG 데이터베이스의 데이터 이동 상태를 나타냅니다.
  • 0: 동기화 안 함 - 주 복제본이 해당 보조 복제본으로 트랜잭션을 보내지 않으면 상태가 동기화 안 됨으로 표시됩니다. 연결 문제, 일시 중단된 데이터 이동, 장애 조치 때문일 수 있습니다.
  • 1: 동기화 중 – 비동기 데이터 커밋에서 AG 데이터베이스의 상태를 동기화 중으로 가져옵니다.
  • 2: 동기화됨: 기본 복제본과 보조 복제본이 동기 데이터 커밋을 위해 동기화된 경우 동기화됨 값을 얻습니다.
  • 3: 되돌리기: 보조 데이터베이스가 복구의 Redo 단계에 있음을 보여줍니다.
  • 4: 초기화 중: 보조 데이터베이스 복구의 실행 취소 단계를 나타냅니다.
• database_state_desc: 이 열에서 sys.databases와 유사한 데이터베이스의 상태를 얻습니다. 다음 값을 가질 수 있습니다.
  • 온라인
  • 복원 중
  • 복구 중
  • 복구 대기 중
  • Suspect
  • Emergency
  • 오프라인
  • NULL
• is_suspended: 기본 복제본과 연결된 보조 복제본 간에 데이터베이스 이동이 일시 중단되면 플래그를 1로 변경합니다.
  • 0: 재개
  • 1: 일시 중단됨
• Suspend_reason : 여기에서 데이터 이동을 중지한 이유를 업데이트합니다. 사용자가 시작했거나 강제 장애 조치 시나리오로 인한 것일 수 있습니다.
  • 0: 사용자가 시작한 정지 활동(SUSPEND_FROM_USER)
  • 1: 강제 장애 조치로 인해(SUSPEND_FROM_PARTNER)
  • 2: Redo: Redo 단계의 오류로 인해(SUSPEND_FROM_REDO)
  • 3: 캡처: 기본 복제본 로그 캡처 프로세스의 오류로 인해.
  • 4:적용: 로그 작성 오류로 인해(SUSPEND_FROM_APPLY)
  • 5: 재시작: 데이터베이스 재시작으로 인해(SUSPEND_FROM_RESTART)
  • 6:Undo: Undo 단계의 오류로 인해(SUSPEND_FROM_Undo)
  • 7: 재검증: 로그 변경의 불일치로 인해(SUSPEND_FROM_REVALIDATION)
  • 8: 오류: 보조 복제본 LNS 지점의 오류로 인해(SUSPEND_FROM_XRF_UPDATE)
• secondary_lag_seconds: 이 열을 사용하여 주 복제본과 보조 복제본 간의 지연을 확인할 수 있습니다.
• DMV를 사용하여 동기화 지연을 계산하는 가용성 그룹 동기화 지연 측정 문서를 참조할 수 있습니다.

이 외에도 AG 대시보드와 유사한 기본 복제본과 보조 복제본 간의 데이터 전송 각 단계에 대한 LSN 정보를 제공합니다. 복제본 간의 동기화 문제를 해결하는 데 유용합니다.

SQL Server Always On 가용성 그룹을 모니터링하기 위한 충분한 정보를 얻기 위해 여러 DMV를 결합할 수 있습니다.

아래 쿼리는 다음 DMV를 조인합니다.

• sys.dm_hadr_database_replica_states,
• sys.availability_databases_cluster
• sys.availability_groups
• sys.availability_groups
• sys.availability_replicas

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'

BEGIN
	PRINT ' Go To the next step.'
END

ELSE
	BEGIN
		RAISERROR ('Do not go to the next step.', 16, 1)
	END

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

https://sqltouch.blogspot.com/2014/03/free-real-time-sql-server-performance.html

Download:.............. http://bit.ly/2NTn6Zi

 

Agreement:

Figure: SQL Performance Monitor

A challenge:
Retrieving and visualizing the SQL Server performance data is always a challenge and a tedious task for SQL Server database professionals. Utilizing the Windows PerfMon application is the easiest way to perform this task as well as querying “sys.dm_os_performance_counters” and some other DMVs brings a lot of useful information.

Starting from SQL Server 2005, Microsoft has introduced DMV to query various internal metadata directly to explore various health status data. Although collecting and analyzing SQL Server performance data in a regular basis provides trending ability, monitoring real-time performance data is critical to understand an ongoing performance condition that is occurring.

We are all familiar with built-in “SQL Server Activity Monitor” and obviously it is a good starting point to troubleshoot some SQL Server issues. However, the capacity of this tool is limited as it does not provide other performance metrics which are important to understand the server health status. To extend this idea especially during a performance condition, I have attempted to develop a “SQL Performance Monitor” desktop app by including some other interesting metrics which I believe might be helpful to troubleshoot or understand a problem.

This tool collects more than 50+ performance data directly from SQL Server in real-time and shows data in the chart continuously. Also, it does not require any installation and configuration.

Data collection:
SQL Scripts used in my tool are excerpted from SSMS and some are collected from various forums which are freely available. My understanding is that all the scripts that I have used are reliable however if any are not working, please let me know and I will attempt to fix the issue.

How does it work?
1.           Has the ability to monitor only a single SQL instance at a time and can be used against all editions of SQL Server from 2005 to SQL 2014. 

3.           Performance data will be saved automatically as they are collected in a SQLite database (sqlmonitor.db3).
4.           All saved performance data can be queried, and then can be exported as a CSV format. As “sqlmonitor.db3” is not protected therefore it can be opened with any SQLite tool.

Limitations:
1.           It has no notification system, such as email, alert, popup.
2.           It is a desktop 32-bit application, cannot run as a service.
3.           Chart colors have no special meaning.

Known Limitations:
(a)       SQL 2005 – in the “Server Info” tab the “Available Memory” will be zero.
(b)       CPU utilization has been calculated from “Resource Pool” and @@CPU_BUSY. Due to the internal limitation of SQL Server, and feature limitation of Standard and Express editions, CPU value may show zero on the chart. In Enterprise edition, CPU utilization will not be zero.

How to run:

Connect to a database server:
The tool bar of “SQL Performance Monitor”

Figure#1: Tool bar of SQL Activity Monitor

First time connection:
To connect a SQL Server instance, click the “SQL Server to Monitor” button. Supply the required information and then click “Try Connect” in the connection dialog box. Once connected, close the connection dialog box or choose another server to connect to.

All charts will be populated for an hour with blank data once a connection is made. It continues to collect and display data based on the duration configured on the tool bar. All collected data will be saved in a SQLite database (sqlmonitor.db) for later review and analysis.

Using a saved connection:
A successful connection can be saved for later use. Once the tool successfully connects to a database server, click the “save connection” button to save the connection string. An encoded text file will be created in the same folder with the “.txt” extension where the “SQLMonitor.exe” resides.

From the bottom list box of the “SQL Server Connection” (figure#2) dialog box, double click a previously saved file to connect to a SQL Server instance.

Couple of Screenshots from “SQL Performance Monitor”

Figure#2: SQL Server Connection dialog

Figure#3B: Viewing all sessions

Historical data:
In the history tab, put “SQL Instance Name” and “date” to query historical data. Click any column header to view data in the chart. All data and charts can be saved.

Figure#4: Historical data browse

Figure#5: Summarizing historical data