SQL Server 메모리

1. SQL Server 메모리의 이해

 

데이터는 디스크에 저장되고 디스크에서 검색을 하는데 디스크 I/O 작업에 많은 리소스를

소비하게 되므로 SQL Server는 디스크 I/O 작업을 효율적으로 관리하기 위하여 버퍼풀의

메모리 구조를 이용한다.

 

1) SQL Server 메모리 동작 방식

데이터를 조회하게 되면 SQL Server는 버퍼풀에 데이터가 있는지 확인하고 없을 경우

디스크에서 버퍼풀로 데이터를 복사한 후 데이터를 사용한다.

버퍼풀에 데이터가 있으면 디스크에 액세스 하지 않고 버퍼풀의 데이터를 바로 사용한다.

데이터를 변경하게 되면 트랜잭션 로그에는 변경 내역이 즉시 반영되지만 실질적인

데이터 페이지에는 CHECKPOINT 시점의 최종적으로 수정된 데이터만 디스크에 쓰여져

디스크 I/O를 줄여준다.

SQL Server가 사용하는 메모리는 동적으로 관리된다.

처음 시작될 때 SQL Server는 가동에 필요한 최소한의 메모리 공간만 할당 받아서 사용하게 된다.

SQL Server가 운영되는 동안 메모리가 필요할 경우 시스템의 여유 메모리를 할당 받아 사용하고,

사용이 끝나 다른 곳에서 메모리를 요구할 경우 메모리를 반환한다.

 

 

 

2) 32비트 시스템 메모리 특징

32bit 응용 프로그램에서는 총 4GB의 프로세스 주소 공간을 할당할 수 있다.

이중 2GB는 OS의 커널 모드 주소 공간. 2GB는 사용자 모드 주소 공간으로 사용한다.

메모리 옵션을 사용하여 커널 모드 주소 공간을 1GB로 제한하고, 응용 프로그램에 더 할당하거나

AWE 옵션을 활용하여 사용자 모드 주소 공간을 확장하여 응용 프로그램이 더 많은 메모리를

용할 수 있도록 한다.

 

 

3) 메모리 설정을 해야 하는 이유

부하가 많은 시스템에서 I/O가 지속적으로 발생하면 SQL Server는 시스템의 메모리를 계속

요구하며 할당 받아 사용하게 되는데 이는 OS나 다른 응용 프로그램의 동작에 영향을 줄 수 있다. 

반대로 OS나 다른 응응 프로그램에서 많은 공간의 메모리를 사용하고 있다면

SQL Server가 할당 받아 사용할 메모리가 줄어들게 된다.

SQL Server의 버퍼풀 메모리가 부족할 경우 잦은 디스크 I/O를 발생시키고, 쿼리를

실행하는데 필요한 최소 메모리를 얻지 못하고 리소스를 대기하는 동안 오류가 발생한다.

 

 

 

2. 메모리 설정 옵션들

 

1) 부팅 옵션

3GB

32bit 환경에서 4GB 메모리를 사용 중이라면 boot.ini에 /3GB 옵션을 설정해서 운영체제에

1GB를 할당 하고, SQL Server에 3GB를 할당해서 사용할 수 있다.

그러나 16GB를 초과하는 메모리를 사용할 경우에는 가상메모리 주소 할당을 위해 커널 모드에

2GB 이상의 메모리가 필요하기 때문에 /3GB 옵션은 해제해야 한다.

 

PAE

32bit 운영체제에서 4GB이상의 물리메모리를 사용할 수 있도록 확장

AWE 설정 시 필수 사항

 

2) 시스템 옵션

LOCK PAGE IN MEMORY

시스템이 디스크의 가상 메모리로 데이터를 페이징 하지 않도록 방지.

OS에서 메모리 반환 요청을 하지 않는다. 

AWE +  LOCK PAGE IN MEMORY 설정을 하면 할당된 메모리를 해제하지 않기 때문에 OS에 메모리를

뺏기지 않는다. 그래서 max server memory를 지정해 주지 않으면 OS가 메모리 부족을 감지할 경우 

buffer pool 영역 이외의 영역이 non-buffer pool 영역에서 메모리를 빼앗기게 때문에 더 심각한 문제가 

발생할 수 있다. 그래서 max server memory  설정을 해야한다. 

32bit 환경에서 4GB 이상 메모리를 사용해야 하는 경우나 64bit 시스템을 사용할 경우 이 옵션을 반드시 설정 해야한다. 

 

LARGE SYSTEM CACHE

변경 된 데이터 페이지를 물리적인 메모리에 남겨두는 제한을 설정한다.

시스템 캐시를 사용하게 되면 서버의 성능은 향상되지만 애플리케이션에서

사용할 수 있는 가용메모리는 줄어들게 된다.

응용 프로그램이 좀 더 메모리를 사용할수 있게 해주는 옵션임.

http://sqlsql.tistory.com/82

 

3) SP_CONFIGURE 옵션

min/max server memory

max server memory 까지 메모리를 확보 후 min server memory 까지 메모리를 해제.

min/max server memory를 같게 하여 SQL Server간 메모리 할당 해제에 따른 리소스 최소화.

 

AWE

4GB를 초과하는 물리적인 메모리를 32bit 주소 공간에 동적으로 할당하는 역할

/PAE 옵션과 함께 사용

확장된 메모리는 버퍼풀로만 매핑해서 활용

 

4) Trace flag 옵션

845

Lock Page in Memory를 사용할 수 있도록 하는 옵션

SQL Server 구성관리자 -> SQL Server 서비스 -> SQL Server 속성 -> 고급 -> 시작 매개 변수 마지막에 ;-T845 추가 (맞나?..)

 

836

Max server memory로 지정된 공간까지 버퍼풀 메모리를 확보

AWE모드에서 작동

 

834

버퍼풀에 할당된 메모리가 Windows 큰 페이지 할당을 사용하도록 한다.

 

5) 시스템 별 메모리 옵션 설정하기

32bit SQL Server & 32bit Windows Server

/3GB 옵션으로 OS 1GB, 응용 프로그램 3GB 할당

만약 4GB를 초과하는 메모리를 사용한다면 /PAE 옵션을 통해 4GB 메모리 주소 공간을

최대 64GB까지 확장하고, AWE 옵션으로 SQL Server가 확장된 주소 공간을 버퍼풀

영역으로 사용할 수 있도록 설정.

Lock Page in Memory 설정으로 메모리가 페이징 되는 것을 방지.

max server memory 옵션 설정

16GB를 초과하는 메모리를 사용할 경우 3GB 옵션을 제거

 

64bit SQL Server & 64bit Windows Server

Lock Page in Memory / Max server memory 만 설정

3GB , PAE , AWE 옵션 필요 없음.

 

32bit SQL Server & 64bit Windows Server(WOW 모드)

OS가 64bit 이기 때문에 내부적인 메모리의 동작 방식은 64bit 시스템을 따라간다.

그래서 PAE 옵션은 필요하지 않다. 하지만 SQL Server는 32bit 시스템 이므로 AWE 모드를

활성화 한다. Lock Page in Memory / max server memory 옵션도 함께 설정한다.

 

 

 

3. MIN / MAX 메모리 설정하기

 

데이터 캐시를 위한 버퍼풀 영역의 크기만을 의미

SQL Server 전용 머신이라 할지라도 버퍼풀 이외 개체의 메모리 사용량을 예측하고 적절한

크기를 설정한다. 왜냐하면 SQL Server가 메모리를 확보한 후 해제하지 않아 OS나 다른

애플리케이션에서 메모리 부족으로 문제가 발생 하거나 반대로 SQL Server가 메모리를

확보하지 못해 느린 응답 속도를 낼 수 있다.

 

 

1) 메모리 크기 별 권장 메모리 설정

메모리 크기별 OS 필요 메모리
물리적 메모리	OS 필요 메모리	물리적 메모리	OS 필요 메모리
4GB 미만	512MB ~ 1GB	32 ~ 128GB	2 ~ 4GB
4~32GB	1 ~ 2GB	128GB 초과	4GB 초과
SQL Server 전용 서버에서의 최대 메모리
물리적 메모리	최대 메모리(MB)	물리적 메모리	최대 메모리(MB)
2GB	1500	4GB	3200
6GB	4800	8GB	6400
12GB	10000	16GB	13500
24GB	21500	32GB	29000
48GB	44000	64GB	60000
72GB	68000	96GB	92000
128GB	124000

 

 

2) 메모리 크기와 사용량 분석

-- 시스템메모리상태보기

select

        total_physical_memory_kb          -- 물리적메모리크기

        , available_physical_memory_kb    -- 사용가능한메모리크기

        , total_page_file_kb              -- 전체페이지파일

        , available_page_file_kb          -- 사용가능한페이지파일

        , system_memory_state_desc        -- 시스템메모리상태

from sys . dm_os_sys_memory

-- 버퍼풀상태보기

select

        physical_memory_in_bytes           -- 물리적메모리크기

        , virtual_memory_in_bytes          -- 가상메모리크기

        , bpool_committed                  -- 현재버퍼풀에할당된메모리크기

        , bpool_commit_target              -- 버퍼풀이최대로할당할수있는크기

        , bpool_visible                    -- AWE 메모리를매칭하고있는물리적주소공간

from sys . dm_os_sys_info

 

-- 데이터베이스별버퍼풀사용공간조회

select

        db_name (database_id ) as [database_name]

        , count(*) as [buffer_count]

        , cast( count (*)as bigint)* 8 / 1024.0 as[buffer_size(MB)]

from sys . dm_os_buffer_descriptors

where database_id <> 32767

group by db_name( database_id ),database_id

order by buffer_count desc

-- 개체별버퍼풀사용공간조회

select

        object_name (p . [object_id]) as [object_name]

        , p. index_id

        , count(*) as [buffer_count]

        , cast( count (*)as bigint)* 8 /1024.0 as [buffer size(MB)]

from sys . allocation_units as a

inner join sys. dm_os_buffer_descriptors as b

on a . allocation_unit_id= b .allocation_unit_id

inner join sys. partitions as p

on a . container_id= p .hobt_id

where b . database_id= db_id ()

and p . [object_id]> 100

group by p. [object_id] ,p . index_id

order by buffer_count desc

 

-- 버퍼풀이외의공간예측하기

select

       TYPE

       ,SUM(multi_pages_kb) as a

from sys.dm_os_memory_clerks

where multi_pages_kb != 0

 

group by type

츨처: http://ksewookk.blog.me/100178516895

 

[출처] SQL Server 메모리|작성자 dudu