SQL Server/SQL Server Tip

SQL Server 2014 버풀 설정 및 모니터링

SungWookKang 2015. 7. 16. 08:35
반응형

SQL Server 2014 버풀 설정 및 모니터링

 

  • Version : SQL Server 2014

 

 

SQL Server 2014에서 도입된 버퍼 풀 확장은 I/O 처리량을 향상 시키기 위해 비휘발성 RAM(SSD) 을 데이터베이스 엔진에서 사용 할 수 있도록 하는 기능이다.

 

버퍼풀에 대한 설명은 다음 포스트를 참고 한다.

 

이번 포스트는 버퍼 풀 확장을 설정하는 방법에 대해서 알아본다.

 

다음 스크립트는 버퍼 풀 확장 옵션을 설정하고 파일 이름과 크기를 지정한다. BPE 파일이 생성된 것을 확인 할 수 있다.

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'c:\SSDCACHE\Example.BPE', SIZE = 3GB);

 

 

 

다음 스크립트는 버퍼 풀 확장 파일의 크기를 수정한다. 매개 변수를 수정하려면 먼저 버퍼 풀 확장 옵션을 해제해야 한다. Max server memory를 12GB로 설정하고 버퍼 풀 확장을 3GB에서 GB로 확장 하였다.

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF;

GO

EXEC sp_configure 'max server memory (MB)', 12000;

GO

RECONFIGURE;

GO

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'C:\SSDCACHE\Example.BPE', SIZE = 5 GB);

GO

 

 

 

버퍼 풀에 대한 정보는 DMV를 통해서 확인 할 수 있다. Sys.dm_os_buffer_pool_extension_configuration은 각 버퍼 풀 확장 파일에 대해 하나의 행을 반환한다.

 

다음 스크립트는 버퍼 풀 확장 정보를 반환한다.

SELECT path, file_id, state, state_description, current_size_in_kb

FROM sys.dm_os_buffer_pool_extension_configuration;

 

 

다음 스크립트는 버퍼 풀 확장 파일에서 캐시된 페이지 수를 반환한다.

SELECT COUNT(*) AS cached_pages_count

FROM sys.dm_os_buffer_descriptors

WHERE is_in_bpool_extension <> 0

 

 

 

다음 스크립트는 데이터베이스에 사용되고 있는페이지를 반환한다. 사용 가능한 페이지나 빼앗긴 페이지 또는 읽을 때 오류가 있던 페이지에 대한 정보는 반환되지 않는다.

SELECT COUNT(*)AS cached_pages_count

,CASE database_id

WHEN 32767 THEN 'ResourceDb'

ELSE db_name(database_id)

END AS database_name

FROM sys.dm_os_buffer_descriptors

GROUP BY DB_NAME(database_id) ,database_id

ORDER BY cached_pages_count DESC;

 

 

 

다음 스크립트는 현재 데이터베이스의 각 개체에 대해 로드된 페이지 수를 반환한다.

SELECT COUNT(*)AS cached_pages_count

,name ,index_id

FROM sys.dm_os_buffer_descriptors AS bd

INNER JOIN

(

SELECT object_name(object_id) AS name

,index_id ,allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.hobt_id

AND (au.type = 1 OR au.type = 3)

UNION ALL

SELECT object_name(object_id) AS name

,index_id, allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.partition_id

AND au.type = 2

) AS obj

ON bd.allocation_unit_id = obj.allocation_unit_id

WHERE database_id = DB_ID()

GROUP BY name, index_id

ORDER BY cached_pages_count DESC;

 

 

[참고자료]

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

 

 

2014-05-16 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, sqlserver 2014, sql 2014, sql 버퍼 풀 확장, 버퍼 풀 설정, 버퍼 풀 수정, Sys.dm_os_buffer_pool_extension_configuration, sys.dm_os_buffer_descriptors

 

반응형