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
'SQL Server > SQL Server Tip' 카테고리의 다른 글
SQL Server 2014 스토리지 할당 및 메모리 옵티마이즈 테이블 관리 (0) | 2015.07.16 |
---|---|
SQL Server 2014 리소스 관리자 풀과 CPU 제어 (0) | 2015.07.16 |
SQL Server 2014 버풀 확장 (0) | 2015.07.16 |
SQL Server 2014 버전에서 지원하는 기능 (0) | 2015.07.16 |
SQL Server 2014 설치 (0) | 2015.07.16 |