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

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

 

• Version : SQL Server 2014

 

 

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

 

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

• SQL Server 2014 버풀 확장 : http://sqlmvp.kr/140212332983

 

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

 

다음 스크립트는 버퍼 풀 확장 옵션을 설정하고 파일 이름과 크기를 지정한다. 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;

 

 

[참고자료]

• ALTER SERVER CONFIGURATION : http://msdn.microsoft.com/ko-kr/library/ee210585.aspx
• sys.dm_os_buffer_pool_extension_configuration :

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

• sys.dm_os_buffer_descriptors : http://msdn.microsoft.com/ko-kr/library/ms173442.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