-      Version : SQL Server 2012

SQL Server 데이터베이스 엔진은 자동으로 현재의 데이터베이스버퍼캐시에 있는 데이터를 디스크로 기록하는 CHECKPOINT 명령을 실행한다.

SQL Server 엔진은 성능상의 이유로 변경 내용이 있을 때마다메모리(버퍼캐시)에서 데이터베이스 페이지를 수정하며 이러한페이지를 디스크에 기록하지는 않는다. 대신 데이터베이스 엔진은 각 데이터베이스에서 정기적으로 CHECKPOINT를 실행 한다. CHECKPOINT는 현재 메모리내의 수정된 페이지(더티페이지)와 메모리의 트랜잭션 로그정보를 디스크에 쓰고 트랜잭션 로그에 대한 정보도 기록한다.

이 기능은 SQL Server의 예기치 않은 장애나 시스템 충돌로인하여 데이터베이스를 복구하는데 데이터의 안정성 및 시간을 절약할 수 있는 방법이 된다.

[CHECKPOINT]

: http://msdn.microsoft.com/ko-kr/library/ms189573.aspx

http://msdn.microsoft.com/ko-kr/library/ms188748.aspx

SQL Server 2012의 TARGET_RECOVERY_TIME옵션과 recovery interval 옵션의 상호 작용

SSMS를 실행하여 다음 쿼리를 실행하면 해당 데이터베이스의 TARGET_RECOVERY_TIME 값을 확인 할 수 있다.

SSMS의 UI로 확인도가능 하다. 다음과 같이 해당 데이터베이스에서 마우스 오른쪽을 클릭하여 [속성]을 선택 한다.

[데이터베이스 속성]창이나타나면 [옵션] 탭에서 [복구] – [대상 복구 시간(초)]에서확인 가능 하다.

스크립트를 이용하여 TARGET_RECOVERY_TIME 값을 변경하자. 실습에서는 120초 마다CHECKPOINT가 발생하도록 설정 하였다.

SSMS의 UI에서 변경도가능하다. 변경 방법은 위에서 확인한 [대상 복구 시간(초)]에서 변경하면 적용 된다.

[CHECKPOINT 제어의 장점]

-      특정 데이터베이스의 전체 복구 시간을 줄일 수 있다.

-      IO 비용을 고려하여 CHECKPOINT를 조절 할 수 있다.

[CHECKPOINT 제어의 단점]

-      매우 바쁜 OLTP 환경에서 성능 충돌이 발생할 수도 있다. 반드시 테스트 환경에서 검증하고 적용하자.

sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기

• Version : SQL Server 2008, 208R2, 2012

master 데이터베이스에 있는 sp_MSforeachtdb, sp_MSForeachtable 프로시저에 대해서 알아 보자. (필자도 업무용 쿼리를 분석하다 알게 되었다).

[sp_MSforeachdb]

sp_MSforeachdb 프로시저는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하며 이 이름을 참조하여 지정된 반복문을 수행 한다.

아래 스크립트는 인스턴스내의 모든 데이터베이스의 helpfile 정보이다.

아래 스크립트는 인스터턴스 내의 모든 데이터베이스 파일 정보를 반환 한다.

다음 예제는 테이블 변수를 사용하여 테이블 변수에 데이터를 삽입 한다.

다음 예제는 where 절에 use ? 를 사용한다.

[sp_MSforeachtable]

sp_MSforeachtable 프로시저는 현재 데이터베이스 내의 모든 테이블 이름을 반환하며 이 테이블이름을 참조하여 지정된 반복문을 수행 한다.

아래 스크립트는 해당 데이터베이스에 존재하는 모든 테이블의 row count를 테이블에 삽입하여 출력한다.

위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.

[참고자료]

• http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB#

• http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

• CREATE DATABASE

• ALTER ANY DATABASE

• VIEW ANY DEFINITION

Deadlock 발생시 원인과 해결법.

1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다.

DBCC traceon(1204,-1)DBCC Tracestatus(-1) -- 잘 실행되고 있는지 확인

2. Deadlock 발생되면 SQL의 에러로그에 로그가 남게 된다.

Deadlock encountered .... Printing deadlock informationWait-for graphNode:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2Grant List 1::Grant List 2::Owner:0x27c007e0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:84 ECID:0SPID: 84 ECID: 0 Statement Type: UPDATE Line #: 11Input Buf: RPC Event: dbo.Example_Stored_procRequested By: 0ResType:LockOwner Stype:'OR' Mode: IX SPID:78 ECID:0 Ec:(0x44AA55F0) Value:0x3affcd00 Cost:(0/0)Node:2 PAG: 9:1:18134 CleanCnt:2 Mode: SIU Flags: 0x2Grant List 1::Owner:0x28e6f060 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:78 ECID:0SPID: 78 ECID: 0 Statement Type: UPDATE Line #: 11Input Buf: RPC Event: dbo. Example_Stored_procGrant List 2::Requested By:ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost:(0/0)Victim Resource Owner:ResType:LockOwnerStype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost

3.  에러로그를 확인해 보면 'Example_stored_proc'  프로시저에서 update 시 exclusive lock  이 발생하고 있다는 것을 확인 할 수 있다.

4. sp_helptext 'Example_stored_proc'  를 실행하여 프로시저의 내용을 확인 한다.

5. 이와 관련있는 프로시저를 확인하고 deadlock 이 발생하는 page를 확인할 수 있다.

Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

6. 해당 페이지를 조사해 본다. , DBCC Page 사용

MSDN : http://support.microsoft.com/kb/83065

DBCC page(9,1,18061,0)

PAGE: (1:18061)---------------BUFFER:-------BUF @0x01665900---------------bpage = 0x1DF58000 bhash = 0x00000000 bpageno = (1:18061)bdbid = 9 breferences = 1 bstat = 0xbbspin = 0 bnext = 0x00000000PAGE HEADER:------------Page @0x1DF58000----------------m_pageId = (1:18061) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000m_objId = 1013578649 m_indexId = 0 m_prevPage = (0:0)m_nextPage = (0:0) pminlen = 52 m_slotCnt = 82m_freeCnt = 3075 m_freeData = 5009 m_reservedCnt = 0m_lsn = (2689:87968:2) m_xactReserved = 0 m_xdesId = (0:0)m_ghostRecCnt = 0 m_tornBits = 0.................

object_id를 알아 낼 수 있다.

7.  object 확인

Use DBNAMESelect object_name(OBJECT_ID)

8. 프로시저에서 해당 object를 확인하고  update 문을 찾아 그대로 update 테스트 해 본다.

이때, 해당 컬럼에 인덱스가 있는지 확인 한다. update 시 인덱스가 없으면 table scan 을 하게되고 exclusive lock 이 발생한다.

9. Key 되는 컬럼에 인덱스를 생성 해 준다.

10. 후에 다시 실행해봐서 deadlock 이 발생하는지 확인 한다.

• Version : SQL Server 2005, 2008, 2008R2, 2012

메모리 및 CPU와 관련된 성능 카운터에 대해서 알아본다.

[메모리]

Page life expectancy

[SQLServer:Buffer Manager] – [Page life expectancy]에 위치하며 페이지가 버퍼풀에 머무르는 시간을 나타낸다. 페이지가 버퍼풀에 오래 머무를수록 디스크를 액세스하는 빈도가 낮아지며 메모리에서 데이터를 읽기 때문에 성능상 이점이 있다. Page life expectancy 계속해서 낮게 나온다면 메모리를 추가할 것을 고려해야 한다. MS백서에서는 임계값을 300(5분)으로 안내하고 있지만 이는 2006년에 권장한 값으로 각자의 시스템에 따라 유동적인 임계값을 설정할 필요가 있다. 중요한것은 임계값이 아니라 유동적인 변화를 파악하는데 있다.

SQLServer:Memory Manager

[SQLServer:Memory Manager]에 위치하며 이 카테고리의 값은 SQL Server 프로세스 내에서 메모리가 어떻게 사용되는지를 나타낸다. 카운터에 표시되는 값 자체를 다른것과 비교 할 수는 없지만 어떠한 변화를 가지는지 동향을 살펴보고 어느 부분에서 압력을 받고 있는지 파악하여야 한다.

Memory Grants Pending

[SQLServer:Memory Manager] – [Memory Grants Pending]에 위치하며 작업 영역 메모리 부여를 대기 중은 현재 프로세스 수를 나타낸다. 메모리 부여 대기는 블록킹을 이해하기 위해 특히 중요한 부분으로 주의해서 보아야 한다. 특정 고부하 쿼리 및 연산이 큰 쿼리는 많은 메모리를 요구한다. 이때 Memory Grants Pending 이 발생하고 대기가 발생한다.

Memory grant queue waits

[SQLServer:Wait Statistics]에 위치하며 메모리 부여를 기다리는 프로세스에 대한 통계이다.

Private Bytes

[Process] – [Private Bytes]에 위치하며 프로세스가 할당하여 다른 프로세스와는 공유할 수 없는 메모리의 현재 크기(byte)를 나타낸다. 이 카운터의 값을 확인하여 SQL Server 외부 메모리 부족으로 인한 성능 저하의 원인인 다른 프로세스를 추적 할 수 있다.

[CPU]

Processor Object

[Processor]에 위치하며 객체들은 OS의 CPU 사용량을 나타낸다.

Page lookups/sec

[SQLServer:Buffer Manager] – [Page lookups/sec]에 위치하며 CPU 사용량을 분석할 때 중요한 카운터 이다. 이 카운터는 버퍼 풀에서 페이지를 찾기 위한 요청 수를 나타낸다. 이 카운터의 값이 증가할 때는 CPU의 사용량이 증가한다. 더 많은 페이지를 스캔하고 더 많은 데이터를 사용하기 때문에 더 많은 CPU를 사용한다. 일반적으로 Page lookup/sec이 큰 경우에는 테이블 스캔(인덱스 누락, 잘못된 설계)이 발생한 경우이다.

Processor Time

[Process] – [% Processor Time]에 위치하며 모든 프로세스 스레드가 프로세서를 사용하여 컴퓨터 명령을 실행하는데 경과된 시간의 백분율을 나타낸다. 일부 하드웨어 인터럽트 또는 트랩 상태를 처리하기 위해 실행되는 코드도 이 계산에 포함된다. 프로세서 시간이 크다면 SQL Server 성능 저하 원인을 CPU를 소비하는 다른 프로세스를 추적해 보아야 한다.

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

CPU 관련 SQL Server 대기 통계 해석

주식 중개인은 한 번 숫자에 의해 잘못 인도 되었기 때문에 잘못된 결정을 내렸다고 말했습니다. 실제로 호기심 많은 단어 선택; 마치 그가 스프레드 시트에서 악의적 인 숫자로 속인 무고한 방관자 인 것처럼 말입니다. 모든 종류의 분석에서, 숫자로 표현 된 데이터의 정확한 해석에 대한 책임은 분석가에게 있습니다.

이는 경제 분석, 의료 연구 데이터 또는 금융 시장 연구에서와 마찬가지로 SQL Server 성능 분석에서도 중요합니다. 이 글에서는 CPU 대기 통계를 올바르게 해석하여 실제로 문제를 나타내는 지 확인하고 그렇다면 어떤 조치를 취할 수 있는지 확인합니다.

SQL Server 대기 통계 모니터링 에 대한 이전 기사에서는 sys.dm_os_wait_stats 의 출력을 보았습니다 .  우리는 signal_wait_time_ms 가 대기 한 리소스가 사용 가능 해지면 스레드를 기다려야하는 밀리 초 수를 나타냅니다. 스레드를 기다리는 데 상당한 시간이 소요되는 경우 시스템 CPU 코어가 수요를 따라 가지 않고 성능 병목 상태라고 쉽게 상상할 수 있습니다. 반드시 그런 것은 아닙니다.

일부 저자들은 총 신호 대기 시간이 신호 대기에서 차지하는 총 대기 시간의 비율보다 덜 중요하다고 올바르게 제안했습니다. 이것은 그림의 절반에 불과합니다. 모든 대기에서 신호 대기 시간을 집계하면 성능 병목 현상을 나타내지 않는 높은 비율이 표시됩니다.

다음 쿼리는 CPU 관련 대기에 대해 블로그에서 가져 왔습니다.

SELECT CAST ( 100.0 * SUM ( signal_wait_time_ms ) / SUM ( wait_time_ms )

AS 숫자 ( 20 , 2 )) AS signal_cpu_waits

FROM sys . dm_os_wait_stats

이 쿼리를 실행하면 다음과 같은 결과가 표시 될 수 있습니다.

전체 신호 대기 백분율

출처에 따르면, 이것은 약간 높은 비율을 나타냅니다. 개별 신호 대기를 보면 어떻게되는지 봅시다.

SELECT *, CAST ( 100.0 * signal_wait_time_ms / NULLIF ( wait_time_ms , 0 )

AS DECIMAL ( 5 , 2 )) AS [신호 대기 백분율]

개별 대기에 대한 신호 대기 백분율

처음 세 행에는 신호 대기 시간이 포함되어있을뿐만 아니라 총 대기 시간과 정확히 같은 신호 대기 시간이 포함됩니다. 이것은 내가 "아무것도하지 않는다"라고 부르는 대기에 일반적인 행동입니다. 수행 할 작업이없는 대기에 대한 대기 시간이 종종 많기 때문에 위 쿼리에서 계산 된 비율에 대한 기여도는 불균형 적으로 큽니다. 실제로 누군가 CPU에 문제가 있다고 생각할 수 있습니다. 더 나쁜 것은 실제 CPU 문제가이 부풀려진 비율에 덜 큰 영향을 미쳐 눈에 띄지 않게 될 수 있습니다.

목록의 네 번째 대기 유형은 SOS_SCHEDULER_YIELD입니다.이 점을 곧 고려하겠습니다.

세 가지 SQL Server 대기 유형

신호 대기 시간과 전체 대기 시간 간의 전체 비율은 수행 할 작업이 모두 필터링 된 경우에만 유용합니다. 이를 수행하기위한 예제 쿼리는 이전 설치에서 제공되었으며 여기에서 다운로드 할 수 있습니다 .

SOS_SCHEDULER_YIELD

스레드 실행에 대한 사용자 모드 스케줄링 (UMS)에 대한 세부 사항을 논의 할 시간이나 공간이 현재 없습니다. 매혹적인 주제이기 때문에 너무 나쁩니다. UMS는 실행을 다른 스레드로 전환하는시기와 방법에 따라 응용 프로그램이 운영 체제보다 잘 알고 있다는 개념을 기반으로하므로 운영 체제에 필요한 비싼 커널 컨텍스트 스위치를 줄일 수 있습니다. UMS는 대기 스레드의 우선 순위를 조작하여 운영 체제가 항상 SQL Server가 원하는 스레드에 시간 조각을 제공하도록함으로써이를 수행했습니다. UMS는 SQL Server 7에 도입되었으며 몇 년 동안 SQL Server가이 프로그램을 사용하는 유일한 프로그램이었습니다. 그러나 Microsoft의 Windows 직원은이 아이디어가 좋은 아이디어라고 생각했습니다.

여기서 가장 중요한 사실은 SQL Server의 스레드 스케줄러가 항상 양자라고하는 4 밀리 초의 균일 한 타임 슬라이스를 처리한다는 것입니다. 따라서 가장 짧은 작업을 제외한 모든 스레드는 CPU를 포기하고 시스템이 사용 중이 아닌 경우 거의 즉시 CPU를 다시 확보합니다. 따라서 스레드 수율 및 다른 태스크가 스레드에 할당되는 것은 흔한 일이며 많은 수의 SOS_SCHEDULER_YIELD는 아무 것도 알려주지 않습니다.

장기 실행 쿼리는 종종 4 밀리 초 스레드 실행 한계에 도달하므로 높은 SOS_SCHEDULER_YIELD 대기 횟수 준수에 크게 기여합니다. 모든 것이 적절하게 최적화되는 한 오래 실행되는 쿼리를 갖는 데 아무런 문제가 없습니다. 일부 작업은 다른 작업보다 시간이 더 필요합니다.

위의 쿼리 결과에서 신호 대기 대 총 대기 비율은 매우 높지만 평균 대기 시간, 즉 총 대기 수를 대기 수로 나눈 값은 매우 낮습니다. CPU 가용성은 여기서 문제가 아니므로 비율에만 의존하는 또 다른 잠재적 위험을 보여줍니다.

SOS_SCHEDULER_YIELD 대기는 개별 도구의 단일 측정을 기반으로 많은 성능 조건을 진단 할 수없는 방법을 잘 보여줍니다. Windows 성능 모니터가 지속적으로 높은 CPU 활동 (80-90 %)을 보이고 있다고 상상해보십시오. 문제? 말할 수 없습니다. 해야 할 일이 많을 수 있으며 CPU는 이러한 작업을 질서 있고 효율적으로 수행하는 데 허비하고 있습니다. 그러나 많은 스레드가 준비되었지만 적시에 CPU 슬라이스를 얻지 못하는 상황에서 동일한 CPU 백분율이 표시 될 수 있습니다. 이와 같은 상황에서는 성능 모니터 (또는 원하는 경우 sys.dm_os_performance_counters)의 CPU 사용량이 높고 기본 단서가 될 sys.dm_os_wait_stats의 신호 대기 시간이 많을 수 있습니다.

스레드 풀

건강한 시스템에서는 THREADPOOL 대기를 거의 볼 수 없습니다. 스레드 풀에 실행 가능한 태스크에 할당 할 스레드가없는 경우 THREADPOOL 대기가 발생합니다. 구성된 최대 작업자 스레드 수가 워크로드에 작은 도구 인 경우 발생할 수 있습니다. 그러나 구성 값을 조정하기 전에 이것이 일반적인 조건인지 또는 예외적으로 매우 높은 사용량의 기간 동안 만 발생했는지 검사해야합니다. 스레드 유지 관리 비용이 발생하며 거의 발생하지 않는 조건부로 스레드 최대 값을 조정해서는 안됩니다.

CXPACKET

CXPACKET은 SQL Server가 병렬 실행 계획으로 쿼리에 대해 여러 스레드를 동기화하려고 할 때 발생할 수 있습니다. CXPACKET 대기에 대한 응답은 쿼리 자체에 따라 다릅니다. 쿼리가 추가 인덱스의 이점을 얻을 수 있습니까? 쿼리에 부적절한 데이터 형식과 같은 문제를 일으킬 수있는 결함이 있습니까? 쿼리가 정상으로 보이고 인덱스가 적합 해 보인다면 최대 병렬 처리 수준을 조정하여 CXPACKET 대기 시간을 줄일 수 있습니다.

결론

CPU 사용량과 관련된 대기 통계는 단일 문제의 직접적인 지표를 거의 제공하지 않습니다. 기껏해야 CPU 대기 통계는 문제 가능성에주의를 기울일 수 있지만, 관련된 쿼리 및 서버 작업량에 대한 추가 조사만으로 문제가 실제로 존재하는지 여부와 해당되는 경우 수행 할 조치를 결정할 수 있습니다.

1. NUMA

Microsoft SQL Server는 NUMA(Non-Uniform Memory Access)를 인식하며 특수한 구성 없이 NUMA 하드웨어에서 원활하게 작동한다. 클럭 속도와 프로세서 수가 증가할수록 이러한 추가 처리 능력을 사용하는 데 필요한 메모리 대기 시간을 줄이기가 더 어려워 진다. 이러한 문제를 피하기 위해 하드웨어 공급업체에서는 대용량의 L3 캐시를 제공하지만 이는 제한적인 해결책일 뿐이다. NUMA 아키텍처는 이 문제에 대한 포괄적인 해결책을 제공 한다. SQL Server는 응용 프로그램을 변경할 필요 없이 NUMA 기반 컴퓨터를 활용하도록 디자인 되었다. 이제 그 구성을 자세히 알아보고 현재 운영중인 서버에서 비효율적인 구성이 있으면 고쳐보길 바란다. 먼저 당부하고 싶은 것은 서버의 설정을 바꾸는 것은 대단히 위험한 행위이다. 구성 변경 시 충분한 검토와 테스트를 수행 한 후 적용하길 바란다.

1. NUMA(Non-Uniform Memory Access)

    

   SQL Server는 응용 프로그램을 변경할 필요 없이 NUMA 기반 컴퓨터를 활용하도록 디자인 되었다.

2. 작은 프로세서 집합에 사용되는 시스템 버스를 여러 개 구성하는 것이 일반적인 하드웨어 추세이다. CPU 개수가 증가하고 메모리 용량이 증가하는 추세에 대용량 서비스를 할 때 기존의 UMA 방식으로 메모리를 공유하여 사용하게 되면 메모리 대기 시간이 길어 진다. 이를 해결하기 위해 공유 메모리를 전용 메모리로 할당 함으로써 효율성을 높인다.
3. NUMA 와SQL Server버전

    

4. 소프트 NUMA 구성시 SQL Server 2000 SP4 이상 지원되며 SQL Server 2005 이상 사용 할 것을 권장 한다.
5. NUMA 아키텍처

   다른 NUMA 노드와 연결된 메모리보다 로컬 메모리를 액세스 하는 것이 훨씬 빠르다. NUMA 하드웨어에서는 일부 메모리 영역이 실제로 나머지 영역과 다른 버스에 있다. NUMA는 로컬 메모리와 외부 메모리를 사용하므로 다른 영역에 비해 일부 메모리 영역에 액세스 하는 시간이 오래 걸린다.

6. 각 프로세서 그룹에는 자체 메모리가 있으며 자체 I/O 채널이 있는 경우도 있다. 그러나 각 CPU는 일관된 방법으로 다른 그룹과 연결된 메모리에 액세스 한다. 각 그룹을 NUMA노드라 한다.

• 로컬 메모리 : 현재 스레드를 실행 중인 CPU와 같은 노드에 있는 메모리.

• 외부 메모리 : 현재 실행중인 노드에 속하지 않는 메모리 (원격 메모리 라고도 함)

• NUMA 비율 : 로컬 메모리 액세스 비용에 대한 외부 메모리 액세스 비용의 비율. (비율이 1이면 SMP(대칭 다중 처리). 비율이 높을수록 외부 메모리에 액세스 하는 비용이 증가.)

   

.

1. NUMA 와 SMP 차이점

   NUMA는 특정 메모리 버스의 CPU 개수를 제한하고 고속 연결로 여러 개의 노드를 연결하여 SMP의 병목 현상을 해결 한다.

2. NUMA 아키텍처는 SMP(Symmetric Multiprocessor) 아키텍처의 확장성 제한을 극복하기 위해 고안 되었다. SMP를 사용하면 모든 메모리 액세스가 같은 공유 메모리 버스에 게시 된다, CPU 수가 비교적 적을 때는 문제가 없지만 수 많은 CPU가 공유 메모리 버스 액세스를 위해 경쟁 할 때는 이 기능이 제대로 작동 하지 않는다.

1. NUMA 구성에 영향을 주는 설정

1. 하드웨어 NUMA는 컴퓨터 제조업체에서 제공.

2. 소프트 NUMA(SQL Server 메모리)는 레지스트리를 사용.

3. CPU 선호도는 affinity mask 옵션 사용.

4. NUMA 선호도에 대한 포트구성은 TCP/IP 포트 매핑 사용.

2. 하드웨어 NUMA

    

   

    

    

   SQL Server에서 사용 가능한 NUMA 개수 확인 DMV

3. 하드웨어 NUMA가 적용된 컴퓨터 에서는 여러 개의 시스템 버스가 있다. 각 프로세서 그룹에는 자체 메모리가 있으며 자체 I/O 채널이 있는 경우도 있지만 CPU는 일관된 방법으로 다른 그룹과 연결된 메모리에 액세스 한다. NUMA 노드 내의 CPU 수는 하드웨어 공급업체에 따라 다르다. 하드웨어 NUMA가 있는 경우 NUMA 대신 인터리브 메모리를 사용하도록 구성 할 수 있다. 그러면 Windows와 SQL Server에서는 NUMA로 인식 되지 않는다.

SELECT DISTINCT memory_node_id FROM sys.dm_os_memory_clerks

결과 값이 하나의 메모리 노드(노드 0)만 반환되면 하드웨어 NUMA가 없거나 해당 하드웨어가 인터리브로 구성되어 있다는 뜻 이다.

1. 하드웨어 NUMA 구성 무시
2. SQL Server는 하드웨어 NUMA에 대해 4개 이하의CPU가 있고 CPU가 하나뿐인 노드가 하나 이상 있는 경우 NUMA 구성을 무시 한다.
3. 소프트 NUMA

    

   

    

    

    

   

    

    

4. SQL Server에서는 CPU를 NUMA라는 노드로 그룹화 할 수 있다. 많은 CPU가 있고 하드웨어 NUMA가 없는 컴퓨터에서 I/O 및 지연기록 병목을 해결 할 수 있다. 4개의 물리적 NUMA 노드를 구성하면 I/O스레드와 지연 기록기 스레드가 각각 4개가 되므로 성능이 향상 된다. 하드웨어 NUMA와 결합하여 NUMA 그룹을 세분화 할 수 있다. SQL Server 스케줄러와 SQL Server 네트워크 인터페이스(SNI)만 소프트 NUMA를 인식 한다.

NODE_ID 64는 관리자 전용 DAC 이다.

1. 소프트 NUMA 구성 무시

   EX) 예를 들어 하드웨어에 8개의 CPU(0..7)가 있고 하드웨어 NUMA 노드가 두 개(0-3 및 4-7)이면 CPU(0,1)과 CPU(2,3)을 결합하여 소프트 NUMA를 만들 수 있다. CPU(1, 5)를 사용하여 소프트 NUMA를 만들 수는 없지만 CPU 선호도를 사용하여 SQL Server 인스턴스의 선호도를 여러 NUMA 노드의 CPU로 설정할 수 있다. SQL Server에 CPU 0-3이 사용되는 경우 I/O 스레드와 지연 기록기 스레드가 각각 하나씩 있다. SQL Server에 CPU 1, 2, 5, 6이 사용되는 경우 두 개의 NUMA 노드에 액세스하게 되며 I/O 스레드와 지연 기록기 스레드가 각각 두 개씩 있다.

2. 여러 하드웨어 NUMA 노드의 CPU를 포함하는 소프트 NUMA를 만들 수 없다. 하지만 CPU 선호도(TCP 매핑)를 사용하여 여러 하드웨어 NUMA 노드에 액세스가 가능하다.
3. 하드웨어 NUMA 와 소프트 NUMA의 메모리 공유 차이점

   예를 들면 SMP 컴퓨터에 8개의 CPU가 있으며 각각 두 개의 CPU를 가진 소프트 NUMA 노드를 4개 만들면 메모리 노드 하나에서 4개의 NUMA를 모두 처리 한다.

    

    

4. 하드웨어 NUMA는 CPU와 메모리가 하나의 세트로 할당되어 동작 하지만 소프트 NUMA의 경우에는 CPU 할당은 가능 하지만 메모리는 공유하여 사용한다.
5. NUMA 시나리오
6. NUMA 선도호에 대한 포트 없음.

하드웨어 NUMA 및 SQL Server의 단일 인스턴스가 있는 컴퓨터에서의 기본 상태. 모든 트랙픽은 단일 포트를 통해 들어오며 사용 가능한 모든 NUMA 노드에 라운드 로빈 방식으로 배포 된다. NUMA는 액세스 효율을 높이며 I/O 및 지연 기록기 스레드를 증가 시킨다. 설정된 연결은 해당 노드에 적용 되므로 NUMA 노드에서 자동으로 균형이 조정된다.

1. 우선 순위 응용 프로그램의 성능 향상을 위해 여러 개의 노드에 단일 포트 연결.

포트 하나의 선호도를 주 우선 순위 응용 프로그램에 사용할 여러 개의 하드웨어 NUMA 노드로 설정한다. 두 번째 포트의 선호도를 두 번째 부 응용 프로그램에 사용할 다른 하드웨어 NUMA 노드로 설정 한다. 두 응용 프로그램의 메모리 및 CPU 리소스는 불균형하게 고정되어 부 응용프로그램보다 3배 많은 로컬 메모리 및 CPU 리소스를 주 응용 프로그램에 제공한다. 이 기능은 우선 적용되는 연결에 추가 리소스를 할당하여 일종의 우선 순위 스레드 실행을 제공 할 수 있다.

1. 여러 개의 노드에 여러 개의 포트 연결.

둘 이상의 포트를 동일한 NUMA 노드로 매핑 할 수 있다. 이렇게 하면 각 포트에서 서로 다른 권한을 구성 할 수 있다. 예를 들어 해당 TCP의 끝점에서 사용 권한을 제어하여 포트를 통해 제공되는 액세스를 제한 할 수 있다. 하지만 두 포트는 똑같이 NUMA를 완벽하게 활용 할 수 있다.

1. SQL Server의 NUMA 설정

   각 NUMNA 노드(하드웨어 NUMA 또는 소프트 NUMA)에는 네트워크 I/O 처리에 사용되는 I/O 완료 포트가 연결되어 있어 여러 포트에 네트워크 I/O 처리를 분산하는 데 도움이 된다.

    

2. SQL Server는 Windows에 표시되는 하드웨어 NUMA 경계를 기반으로 CPU 그룹화에 매핑할 스케줄러를 그룹화 한다. 특정 하드웨어 NUMA 노드에서 실행되는 스레드가 메모리를 할당하는 경우 SQL Server의 메모리 관리자는 참조 효율을 위해 해당 NUMA 노드와 연결된 메모리에서 메모리 할당을 시도 한다. 마찬가지로 버퍼 풀 페이지도 하드웨어 NUMA 노드에 분산 된다. 스레드가 로컬에 할당된 버퍼 페이지의 메모리에 액세스하는 것이 외부 메모리를 액세스 하는 것보다 효율 적이다.
3. 소프트 NUMA 레지스트리 설정

   (SQL Server 2008 R2 화면 이다. SQL 2008의 경우에는 GROUP 값을 삭제 한다.)

    

   (MSDN : http://msdn.microsoft.com/ko-kr/library/ms345357.aspx)

    

   

    

    

4. 레지스트리에 다음과 같이 CPU affinity mask를 지정 한다.
5. NUMA 노드에 TCP/IP 매핑

    

   설정 변경 후 서비스를 반드시 재시작 하여야 한다.

    

   (MSDN : http://msdn.microsoft.com/ko-kr/library/ms345346.aspx)

    

   

    

    

    

   

    

    

   데이터베이스가 시작 될 때 데이터베이스 엔진에서 오류 로그에 노드 정보를 기록한다. 사용할 노드의 번호를 확인하려면 오류 DMV 뷰의 노드 정보를 확인한다..

6. NUMA(Non-Uniform Memory Access) 노드 선호도에 대한 TCP/IP 포트는 SQL Server 구성 관리자에서 서버 설정으로 구성된다. 한 개 또는 여러 개의 노드에 TCP/IP 주소와 포트를 설정하려면 포트 번호 뒤에서 괄호 안에 노드 확인 비트맵(선호도 마스크)을 추가한다. 십진수나 16진수 형식으로 노드를 지정할 수 있다.

sp_readerrorlog

select * from sys.dm_os_schedulers

1. 어플리케이션 사용
2. SQL Server NUMA 구성이 완료 되었으면 매핑된 포트로 접속하여 사용 한다.
1. 0 노드 구성 사용.

1. 0노드, 2노드 구성 사용.

1. 활용
2. 하나의 SQL Server에서 다수의 인스턴스 또는 쿼리가 수행되는 경우에 원활한 서비스를 위하여 물리적인 리소스(CPU) 분산이 필요 할 때 사용 할 수 있다.
3. 배채(Batch)작업 활용
4. CPU 부하는 작지만 자주 요청되는 쿼리(OLTP)와 자주 요청되지는 않지만 CPU 부하가 큰 쿼리(집계쿼리)가 있을 때 각각의 쿼리 타입마다 CPU 리소스를 할당하여 다른 타입의 쿼리가 같은 CPU 사용을 방지하여 성능을 보장한다.
5. 게임 서비스에서 활용

   이때 인기가 높은 게임 하나로 인하여 다른 게임서비스에 영향을 받을 때 각 인스턴스에 NUMA 노드를 할당 함으로써 나머지 다른 서비스의 영향을 최소화 할 수 있다.

    

6. 게임 퍼블리셔를 예로 들어 보자. 하이엔드급의 SQL Server 한 대에 여러 개의 인스턴스를 설치하여 게임 서비스를 한다고 가정 하자. 모든 자원을 동일하게 사용하는가? 인기가 높은 게임은 많은 자원을 사용 할 것이고 상대적으로 인기가 낮은 게임은 자원의 사용량이 작을 것이다.
7. 참고 자료.

• 김민석님 블로그 : http://sqlsql.tistory.com/137

• 송혁님 블로그 : http://hyoksong.tistory.com/42

• afinity mask 옵션 : http://msdn.microsoft.com/ko-kr/library/ms187104.aspx

• NUMA 버퍼 풀 증가 및 축소 : http://msdn.microsoft.com/ko-kr/library/ms345403.aspx

• http://frankdenneman.nl/2010/10/numa-hyperthreading-and-numa-preferht/

SQL 2005 부터 인덱스와 관련하여 개선된 기능 중 하나인 온라인 인덱스 작업에 대하여 알아 보겠습니다. 온라인 인덱스 작업은 1년 365일 운영되어야 하는 시스템을 관리하는 DBA들에게 획기 적인 기능입니다. 온라인 인덱스를 할수 있게 됨으로써 다운 타임을 감소할수 있게 되었으며 인덱스를 재구성할려고 할때도 소요 시간 때문에 많은 고민을 해야 하는 수고를 덜어 줄수 있게 되었습니다. Create Index , Alter Index , Drop index Alter Table 명령어에서 ONLINE 옵션을 사용할수 있습니다.

온라인 인덱스 구성 vs. 오프라인 인덱스 구성ONLINE 옵션은 이러한 인덱스 작업을 수행하는 동안에도 사용자가 테이블이나 클러스터형(Clustered) 인덱스와 관련된 비클러스터형(Nonclustered) 인덱스에 접근할수 있습니다. 오프라인으로 클러스터형 인덱스를 구성하거나 재구성하는 등의 DDL 작업을 하면 이는 해당 데이터와 관련 인덱스에 배타적 잠금을 보유하게 되고 이로 인해 다른 사용자가 데이터나 인덱스에 접근하지 못하도록 방해하게 된다.

Example:

CREATE INDEX idx_t ON t(c1, c2)

WITH (ONLINE = ON)

일반 인덱스 구성 vs. 병렬 인덱스 구성멀티 프로세서를 가진 SQL서버에서 인덱스 구문 또한 다른 쿼리를 실행할 때처럼, 스캔, 정렬, 그리고 구성 작업을 수행하는데 병렬 처리가 가능 해졌습니다. 병렬 처리 수는 최대 병렬 처리 수(sp_configure로 설정한), MAXDOP 인덱스 옵션, 현재 작업부하의 크기, 파티션되지 않은 경우, 첫 번째 키 칼럼의 데이터 분포등에 의해서 결정될 수 있다.

Example:

CREATE INDEX idx_t ON t(c1, c2)

WITH (MAXDOP = 2)

-- 인덱스 구성에 2개의 프로세서 사용

온라인 인덱스 가능한 SQL 버전Microsoft SQL Server 2005 Enterprise Edition

온라인 인덱스는 어떻게 동작 할까?

인덱스 DDL(데이터 정의 언어) 작업 중에 동시 사용자 작업이 가능하도록 하려면 온라인 인덱스 작업 중에 원본 및 기존 인덱스, 대상 및 임시 매핑 인덱스(클러스터형 인덱스의 경우) 로 구성됨으로써 온라인 인덱스 작업 수행을 할수 있도록 합니다.

원본 및 기존 인덱스원본은 원래 테이블이거나 클러스터형 인덱스 데이터입니다. 기존 인덱스는 원본 구조와 관련된 모든 비클러스터형 인덱스입니다. 예를 들어 온라인 인덱스 작업이 네 개의 관련 비클러스터형 인덱스가 있는 클러스터형 인덱스를 재구성하는 것이라면 원본은 기존 클러스터형 인덱스이고 기존 인덱스는 비클러스터형 인덱스입니다.기존 인덱스는 여러 사용자의 동시 선택, 삽입, 업데이트 및 삭제 작업에 사용됩니다. 여기에는 대량 삽입(지원되지만 권장하지 않음)과 트리거 및 참조 무결성 제약 조건에 의한 암시적 업데이트가 포함됩니다. 쿼리와 검색에 모든 기존 인덱스를 사용할 수 있습니다. 즉, 기존 인덱스를 쿼리 최적화 프로그램에서 선택할 수 있으며 필요한 경우 인덱스 힌트에 지정할 수 있습니다.

대상대상은 만들거나 다시 작성하는 새 인덱스(또는 힙)이거나 새 인덱스 집합입니다. SQL Server 데이터베이스 엔진에서는 인덱스 작업을 수행하는 동안 원본에 대한 사용자 삽입, 업데이트 및 삭제 작업을 대상에 적용합니다. 예를 들어 온라인 인덱스 작업이 클러스터형 인덱스를 다시 작성하는 것이라면 대상은 다시 작성되는 클러스터형 인덱스입니다. 데이터베이스 엔진에서는 클러스터형 인덱스를 다시 작성할 때 비클러스터형 인덱스를 다시 작성하지 않습니다.대상 인덱스는 인덱스 작업이 커밋될 때까지 SELECT 문을 처리하는 동안 검색되지 않습니다. 내부적으로 인덱스가 쓰기 전용으로 표시됩니다.

임시 매핑 인덱스클러스터형 인덱스를 만들거나 삭제하거나 다시 작성하는 온라인 인덱스 작업에는 임시 매핑 인덱스도 필요합니다. 이러한 임시 인덱스는 동시 트랜잭션이 기본 테이블의 행이 업데이트되거나 삭제될 때 작성되는 새 인덱스에서 삭제할 레코드를 결정하는 데 사용합니다. 이러한 비클러스터형 인덱스는 새 클러스터형 인덱스(또는 힙)와 같은 단계에서 만들어지므로 별도의 정렬 작업이 필요 없습니다. 동시 트랜잭션의 모든 삽입, 업데이트 및 삭제 작업에서도 임시 매핑 인덱스가 유지됩니다.

참고 사이트

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/08/index-build-strategy-in-sql-server-introduction-i.aspxhttp://technet.microsoft.com/ko-kr/library/ms191261.aspx

출처: https://gdbt.tistory.com/68 [Gravity DB Team]

머신 사양들이 좋아지면서 해당 리소스를 충분히 활용하기 위해 

그리고 mssql 의 성능 향상을 위해 tempdb 파일을 늘리는데.. 

여러 자료를 조합해 본 결과 tempdb 의 데이터 파일은 머신의 코어 수와 맞추면 된단다..

단 요즘은 코어가 하도 많아서... 8개 이상은 의미가 없다... 고 한다 

뭐 스트레스 테스트까지는 해보지 않아서 정확한건 모르겠지만.. 

선인들이 해보셨으면 나도 비슷하다 생각하고... 파일을 늘려놨는데...

파일 늘리기 전 상황을 캡쳐해놓은것이 없어 파일을 늘린것이 얼마나 효과가 있는지 잘 모르겠다.. 

그래서 역으로 테스트를 해보려 한다.. 

(tempdb datafile이 8개인경우 먼저 캡쳐 && 4개 줄여서 테스트 ㅋㅋ)

근데 웃긴건 tempdb datafile 이 만들때는 잘 만들어지는데 지워지지 않는다 ㅋㅋㅋㅋㅋ

그래서 잘~~~ 찾아보니.. datafile이 비어지지 않아서란다 ㅋㅋㅋ

자동으로 비우고 해주면 되지... 알파고도 나온마당에 뭘 또 에러 띄우고 그러시나.. 

암튼 지우기 전에 아래의 쿼리를 실행하고 돌리쟈.. 

==================================================================

USE [tempdb]

GO

DBCC SHRINKFILE ([데이터파일명], EMPTYFILE);

GO

use [master]

ALTER DATABASE [tempdb]  REMOVE FILE [데이터파일명]

GO

===================================================================

그럼 겁내 잘지워진다 ㅎㅎㅎㅎ

근데... 실제 사용자들이 사용하는 DB는 아래의 메세지와 함께 또.. 지워지지 않는다.. 

=========================================================================================

DBCC SHRINKFILE: 페이지 9:1320은(는) 작업 테이블 페이지이므로 이동할 수 없습니다.

메시지 2555, 수준 16, 상태 2, 줄 3

파일 "tempdb8"의 일부 내용을 다른 위치로 이동할 수 없어 emptyfile 작업을 완료할 수 없습니다.

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

==========================================================================================

이유인 즉슨.. 해당 데이터파일이 사용중이다..(내지는 다른데가 사용중이라 비울수 없다..)

그래서 찾아보니... (http://sqlserverzest.com/2012/10/07/msg-2555-level-16-state-1-line-1-error-dbcc-shrinkfile-with-emptyfile-option/) 

위와 같은 메세지가 날때는 

1. 외부 커넥션을 다 끊던지..

2. 서버를 재시작 하라고 하신다 ㅎㅎㅎㅎ

실운영 서버라 테스트는 못했지만.. 꼭 필요한 경우라면 진행 가능할듯 하다. ㅎ

그럼 오늘의 정리는 이만 ㅎ

페이지(Page)란?

• 데이터 파일을 구성하는 논리 단위

• SQL Server의 기본 데이터 저장 단위(8KB)

• 데이터를 쓸 때 행을 페이지에 기록됨

• 데이터를 읽을 때 페이지 내의 모든 행이 읽어짐

• 페이지 내의 행이 많을 수록 I/O 효율 증가

• 0 ~ n 사이의 순차적인 번호 -> 페이지 번호

데이터를 INSERT 하게 된다면 페이지에 들어가게 되고, 테이블에 쿼리를 날려 조회하는 것이 아니라 페이지에서 SELECT하는 것으로 생각하면 됩니다. 그러므로 페이지라는 것을 숙지해야합니다.

인덱스(Index) 는 왜 사용하고 장점은 무엇인가?

• 빠른 데이터를 검색하기 위해서입니다. 찾는 데이터를 가지고 있다면 직접 주거나, 없다면 어디 있는지 알려줍니다.

• 데이터의 중복을 방지할 수 있습니다. (Primary Key, Unique)

• 잠금을 최소화 시켜줍니다. (동시성을 높여줍니다.)

인덱스(Index) 의 단점은 무엇인가?

• 물리적인 공간을 차지하게 됩니다. (인덱스도 테이블처럼 데이터를 가지므로 물리적인 공간을 차지하게 됩니다.)

• 페이지를 가지고 있는 존재는 데이터와 인덱스 두가지입니다. (프로시져/뷰는 사이즈가 없습니다.)

• 인덱스에 대한 유지관리 부담이 존재합니다.

• 데이터가 극히 적다면은 얻는 효과보다 유지관리 부담이 더 클 수 있습니다.

테이블의 존재 형태는 아래 그림 처럼 두 가지로 나뉩니다. 힙(heap)과 클러스터 형 인덱스 입니다.

힙(Heap) 이란?

• 정렬의 기준이 없이 저장 된 테이블의 형태를 말합니다.

• 데이터 페이지 내의 행들 간에 순서가 없고, 페이들 간에도 순서가 없습니다.

• 클러스터 형 인덱스가 없는 테이블이라고 생각하시면 쉽습니다.

힙의 장점과 단점은 무엇인가?

• 장점은 INSERT 문이 좋아하는 테이블 형태입니다. 새로운 행을 기존 페이지의 빈 곳에 추가하면 되고, 빈 공간이 없으면 새로운 페이지에 추가하면 되기 때문입니다.

• 단점은 SELECT 문이 싫어하는 테이블 형태입니다. 데이터가 극히 적으면 상관없겠지만, 데이터가 많으면 전체적으로 모든 테이블을 스캔해야하기 때문에 데이터를 찾기가 어렵습니다.

위에서도 말했듯이, 원하는 데이터를 찾기 위해서는 Table Scan 을 해야 합니다. 전체적으로 페이지를 다 읽는겁니다. 전체적으로 모든 페이지를 다 읽는다고 하니, 당연히 좋지 않습니다.

클러스터 형 인덱스란?

• 특정 열(또는 열들)을 기준으로 데이터가 정렬되어 있습니다. (물리적 정렬이 아닌, 논리적 정렬)

• 테이블 당 하나의 클러스터 형 인덱스를 설정 가능합니다.

다시 한번 말하면, 테이블의 형태는 두가지로 나뉘게 됩니다.

정렬되어 있지 않은 힙과 특수한 Key 값으로 정렬되어진 클러스터 형 인덱스로 나누어집니다. 우리는 선택을 해야 합니다. 힙을 사용할지, 클러스터 형 인덱스를 선택할지. 힙이 무조건 좋지 않다라고 말할 수 없습니다. 상황마다 다르기 때문에 그 상황에 맞게 테이블을 설계하는 것이 개발자의 몫입니다.

클러스트 형의 데이터 찾기

이제부터 많은 용어가 나옵니다. 그러한 용어는 필수적으로 숙지해야합니다.

• Clustered Index Seek : Root 페이지부터 찾아가서, 아주 빠른 성능을 보여줍니다.

• Clustered Index Scan : 모든 데이터 페이지를 읽기 때문에 힙에서 사용했던 방식인 Table Scan과 다를바 없습니다.

힙 + 비 클러스터 형 인덱스

• 비 클러스터 형 인덱스에는 인덱스 키 열의 모든 데이터와 RID(행의 주소)를 가지고 있습니다.

• RID를 가지고 있는 이유는 RID는 거의 변하지 않고 크기가 크지 않기 때문에 한번에 찾아갈 수 있기 때문입니다.

• 999개 까지 만들 수 있습니다.

• Index Seek : NonClustered 가 생략되어졌고, Root 페이지를 이용하기 때문에 성능이 좋습니다.

• RID Lookup :  비 클러스터형 인덱스에 RID 값이 저장되어 있기 때문에, 힙을 검색할때에는 RID값을 이용해서 검색하기 때문에 RID Lookup이 됩니다.

클러스터 형 인덱스 + 비 클러스터 형 인덱스

• 비 클러스터 형 인덱스에는 클러스터 형 인덱스 키 열과 인덱스 키 열의 모든 데이터를 가지고 있습니다.

• 클러스터 형 인덱스의 키 열은 거의 변하지 않습니다. (힙의 RID는 변할 가능성이 높습니다.)

• 999개 까지 만들 수 있습니다.

• Key Lookup : 비 클러스터 형 인덱스가 가지지 못한 데이터를 찾아가는 과정입니다.

결론

아래 그림으로 한방에 정리됩니다. 테이블은 클러스터 형 인덱스가 잡혀있는 테이블과 아닌 테이블로 구성되어 진다는 것을 알 수 있습니다.

그리고 비 클러스터 형 인덱스가 결합되어지면, 힙에는 RID 값을 가지고 있고 클러스터 형 인덱스는 키 값을 가지고 있습니다. 비 클러스터 형 인덱스에서 힙으로 검색될 때에는 RID Lookup, 클러스터 형 인덱스에 검색 되어 질 때에는 Key Loopup 이라는 용어가 사용되어 집니다.

현업에서 RID Lookup 으로 검색되어지는 테이블이 있다면, 그 테이블이 왜 힙을 사용하고 있는지 원인 분석이 필요한 것으로 보여집니다.