'분류 전체보기' 카테고리의 글 목록 (3 Page)

분류 전체보기

SQL Server 버퍼 캐시에 대한 통찰력 2021.01.27
SQL 에이전트 작업 삭제 오류 ( 유지관리계획 삭제 오류 ) DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. Microsoft SQL Server, 오류: 547 2021.01.27
SQL Server 쿼리가 느려졌을 때 2021.01.20
SQL Server 백업/복원 예상 완료시간 확인하기 2021.01.20
DB Backup Script 2021.01.20
free SSMS add-in for effective database performance tracing 2021.01.20
이벤트 스케줄러 사용하기(event scheduler) 2021.01.06
MySQL 데이터 경로 변경하는 방법 2020.12.29
CentOS 7에 PostgreSQL 13을 설치하는 방법 2020.11.18 1
테이블 사이즈 2020.11.13

SQL Server 버퍼 캐시에 대한 통찰력

ㅎ2 2021. 1. 27. 10:54

2021. 1. 27. 10:54

SQL Server의 메모리 사용량에 대해 이야기 할 때 종종 버퍼 캐시를 언급합니다. 이것은 SQL Server 아키텍처의 중요한 부분이며 자주 액세스하는 데이터를 매우 빠르게 쿼리하는 기능을 담당합니다. 버퍼 캐시가 작동하는 방식을 알면 SQL Server에서 메모리를 올바르게 할당하고 데이터베이스가 데이터에 액세스하는 방식을 정확하게 측정하며 과도한 데이터를 캐시하는 코드의 비 효율성이 없는지 확인할 수 있습니다.

버퍼 캐시에는 무엇이 있습니까?

하드 디스크는 느립니다. 메모리가 빠릅니다. 이것은 컴퓨터로 작업하는 모든 사람에게 자연의 사실입니다. 고성능 메모리와 비교할 때 SSD조차도 느립니다. 소프트웨어가이 문제를 처리하는 방법은 느린 저장소의 데이터를 빠른 메모리에 쓰는 것입니다. 일단로드되면 즐겨 찾는 앱이 매우 빠르게 작동 할 수 있으며 새 데이터가 필요할 때만 디스크로 돌아 가면됩니다. 컴퓨팅에서의 이러한 사실은 SQL Server 아키텍처의 중요한 부분이기도합니다.

SQL Server 데이터베이스에서 데이터를 쓰거나 읽을 때마다 버퍼 관리자가 메모리로 복사합니다. 버퍼 캐시 (버퍼 풀이라고도 함)는 가능한 한 많은 데이터 페이지를 보유하기 위해 할당 된 메모리를 사용합니다. 버퍼 캐시가 가득 차면 최신 데이터를위한 공간을 만들기 위해 오래되고 덜 사용되는 데이터가 제거됩니다.

데이터는 버퍼 캐시 내의 8k 페이지에 저장되며 "깨끗한"또는 "더티"페이지라고 할 수 있습니다. 더티 페이지는 디스크에 마지막으로 기록 된 이후 변경된 페이지이며 해당 인덱스 또는 테이블 데이터에 대한 쓰기 작업의 결과입니다. 클린 페이지는 변경되지 않은 페이지이며 그 안의 데이터는 여전히 디스크에있는 것과 일치합니다. 검사 점은 충돌 또는 기타 불행한 서버 상황이 발생할 경우 알려진 양호한 복원 지점을 만들기 위해 더티 페이지를 디스크에 기록하는 SQL Server에 의해 백그라운드에서 자동으로 실행됩니다.

sys.dm_os_sys_info DMV 를 확인하여 SQL Server의 현재 메모리 사용 상태에 대한 개요를 볼 수 있습니다 .

SELECT

physical_memory_kb,

virtual_memory_kb,

committed_kb,

committed_target_kb

FROM sys.dm_os_sys_info;

이 쿼리의 결과는 내 서버의 메모리 사용량에 대해 알려줍니다.

열의 의미는 다음과 같습니다.
physical_memory_kb : 서버에 설치된 총 실제 메모리입니다.
virtual_memory_kb : SQL Server에서 사용할 수있는 총 가상 메모리 양입니다. 이상적으로는 가상 메모리 (디스크 또는 메모리가 아닌 곳에서 페이지 파일 사용)가 메모리보다 훨씬 느리기 때문에 이것을 자주 사용하고 싶지 않습니다.
Committed_kb : 데이터베이스 페이지에서 사용하기 위해 버퍼 캐시에서 현재 할당 한 메모리 양입니다.
Committed_target_kb: 이것은 버퍼 캐시가 "사용하고자하는"메모리 양입니다. 현재 사용중인 양 (committed_kb로 표시됨)이이 양보다 크면 버퍼 관리자가 메모리에서 이전 페이지를 제거하기 시작합니다. 현재 사용중인 양이 적 으면 버퍼 관리자가 데이터에 더 많은 메모리를 할당합니다.

메모리 사용은 SQL Server 성능에 매우 중요합니다. 일반적인 쿼리를 처리하는 데 사용할 수있는 메모리가 충분하지 않으면 디스크에서 데이터를 읽는 데 훨씬 더 많은 리소스를 소비하게되며 데이터를 버리고 나중에 다시 읽습니다.

버퍼 캐시 메트릭을 어떻게 사용할 수 있습니까?

동적 관리 뷰 sys.dm_os_buffer_descriptors를 사용하여 버퍼 캐시에 대한 정보에 액세스 할 수 있습니다.이 뷰 는 SQL Server에서 메모리에 저장된 데이터에 대해 알고 싶었지만 물어보기를 두려워했던 모든 것을 제공합니다. 이보기 내에서 버퍼 설명 자당 단일 행을 찾을 수 있으며, 이는 메모리의 각 페이지에 대한 정보를 고유하게 식별하고 제공합니다. 큰 데이터베이스가있는 서버에서는이 뷰를 쿼리하는 데 약간의 시간이 걸릴 수 있습니다.

쉽게 얻을 수있는 유용한 메트릭은 서버의 데이터베이스 별 버퍼 캐시 사용량 측정입니다.

SELECT

databases.name AS database_name,

COUNT(*) * 8 / 1024 AS mb_used

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.databases

ON databases.database_id = dm_os_buffer_descriptors.database_id

GROUP BY databases.name

ORDER BY COUNT(*) DESC;

이 쿼리는 메모리에있는 대부분의 페이지에서 가장 적은 페이지 순으로 버퍼 캐시의 각 데이터베이스에서 사용하는 메모리 양을 반환합니다.

내 로컬 서버가 지금 그다지 흥미롭지는 않지만 AdventureWorks2014에 대해 다양한 쿼리를 실행하면 위에서부터 쿼리를 다시 실행하여 버퍼 캐시에 미치는 영향을 확인할 수 있습니다.

여기에 너무 미쳐 있지는 않았지만 임의 쿼리로 인해 AdventureWorks2014의 버퍼 캐시에있는 데이터 양이 27MB 증가했습니다. 이 쿼리는 버퍼 캐시에서 가장 많은 메모리 사용량을 차지하는 데이터베이스를 빠르게 확인할 수있는 유용한 방법입니다. 다중 테넌트 아키텍처 또는 리소스를 공유하는 많은 주요 데이터베이스가있는 서버에서 이는 주어진 시간에 성능이 좋지 않거나 메모리를 많이 차지하는 데이터베이스를 찾는 빠른 방법이 될 수 있습니다.

마찬가지로 전체 합계를 페이지 또는 바이트 수로 볼 수 있습니다.

SELECT

COUNT(*) AS buffer_cache_pages,

COUNT(*) * 8 / 1024 AS buffer_cache_used_MB

FROM sys.dm_os_buffer_descriptors;

그러면 버퍼 캐시에있는 페이지 수와 사용 된 메모리가 포함 된 단일 행이 반환됩니다.

페이지가 8KB이므로 8을 곱하여 KB를 얻은 다음 1024로 나누어 MB에 도달함으로써 페이지 수를 메가 바이트로 변환 할 수 있습니다.

우리는 이것을 더 세분화하고 특정 객체에서 버퍼 캐시가 어떻게 사용되는지 살펴볼 수 있습니다. 이것은 어떤 테이블이 메모리 호그인지 결정할 수 있기 때문에 메모리 사용량에 대한 더 많은 통찰력을 제공 할 수 있습니다. 또한 현재 메모리에있는 테이블의 백분율 또는 자주 사용하지 않는 (또는 사용하지 않는) 테이블과 같은 몇 가지 흥미로운 메트릭을 확인할 수 있습니다. 다음 쿼리는 테이블별로 버퍼 페이지 및 크기를 반환합니다.

SELECT

objects.name AS object_name,

objects.type_desc AS object_type_description,

COUNT(*) AS buffer_cache_pages,

COUNT(*) * 8 / 1024 AS buffer_cache_used_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY objects.name,

objects.type_desc

ORDER BY COUNT(*) DESC;

시스템 테이블은 제외되며 현재 데이터베이스에 대한 데이터 만 가져옵니다. 인덱스가 파생 된 테이블과 구별되는 항목이므로 인덱싱 된 뷰가 포함됩니다. sys.partitions 의 조인 에는 인덱스와 힙을 고려하기 위해 두 부분이 포함됩니다. 여기에 표시된 데이터에는 테이블의 모든 인덱스와 정의 된 것이없는 경우 힙이 포함됩니다.

이 결과의 일부는 다음과 같습니다 (AdventureWorks2014의 경우).

마찬가지로,이 데이터를 테이블이 아닌 인덱스로 분할하여 버퍼 캐시 사용에 대해 더욱 세분화 할 수 있습니다.

SELECT

indexes.name AS index_name,

objects.name AS object_name,

objects.type_desc AS object_type_description,

COUNT(*) AS buffer_cache_pages,

COUNT(*) * 8 / 1024 AS buffer_cache_used_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

INNER JOIN sys.indexes

ON objects.object_id = indexes.object_id

AND partitions.index_id = indexes.index_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY indexes.name,

objects.name,

objects.type_desc

ORDER BY COUNT(*) DESC;

이 쿼리는 테이블 / 뷰 이름 외에 sys.indexes에 하나의 추가 조인을 만들고 인덱스 이름에 그룹화 한다는 점을 제외하면 마지막 쿼리와 거의 동일 합니다. 결과는 버퍼 캐시가 사용되는 방법에 대한 더 자세한 정보를 제공하며 다양한 용도의 인덱스가 많은 테이블에서 유용 할 수 있습니다.

결과는 주어진 시간에 특정 인덱스에 대한 전체 사용 수준을 확인하려고 할 때 유용 할 수 있습니다. 또한 전체 크기와 비교하여 읽고있는 인덱스의 양을 측정 할 수 있습니다.

메모리에있는 각 테이블의 백분율을 수집하기 위해 해당 쿼리를 CTE에 넣고 메모리의 페이지와 각 테이블의 합계를 비교할 수 있습니다.

WITH CTE_BUFFER_CACHE AS (

SELECT

objects.name AS object_name,

objects.type_desc AS object_type_description,

objects.object_id,

COUNT(*) AS buffer_cache_pages,

COUNT(*) * 8 / 1024 AS buffer_cache_used_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY objects.name,

objects.type_desc,

objects.object_id)

SELECT

PARTITION_STATS.name,

CTE_BUFFER_CACHE.object_type_description,

CTE_BUFFER_CACHE.buffer_cache_pages,

CTE_BUFFER_CACHE.buffer_cache_used_MB,

PARTITION_STATS.total_number_of_used_pages,

PARTITION_STATS.total_number_of_used_pages * 8 / 1024 AS total_mb_used_by_object,

CAST((CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memory

FROM CTE_BUFFER_CACHE

INNER JOIN (

SELECT

objects.name,

objects.object_id,

SUM(used_page_count) AS total_number_of_used_pages

FROM sys.dm_db_partition_stats

INNER JOIN sys.objects

ON objects.object_id = dm_db_partition_stats.object_id

WHERE objects.is_ms_shipped = 0

GROUP BY objects.name, objects.object_id) PARTITION_STATS

ON PARTITION_STATS.object_id = CTE_BUFFER_CACHE.object_id

ORDER BY CAST(CTE_BUFFER_CACHE.buffer_cache_pages AS DECIMAL) / CAST(PARTITION_STATS.total_number_of_used_pages AS DECIMAL) DESC;

이 쿼리는 이전 데이터 세트를 sys.dm_db_partition_stats 에 대한 쿼리와 조인 하여 현재 버퍼 캐시에있는 것과 주어진 테이블에서 사용하는 총 공간을 비교합니다. 끝에있는 다양한 CAST 작업은 잘림을 방지하고 최종 결과를 읽기 쉬운 형식으로 만드는 데 도움이됩니다. 내 로컬 서버의 결과는 다음과 같습니다.

이 데이터는 데이터베이스에서 어떤 테이블이 핫스팟인지 알려줄 수 있으며, 애플리케이션 사용에 대한 지식을 바탕으로 메모리에 너무 많은 데이터가있는 테이블을 확인할 수 있습니다. 작은 테이블은 아마도 여기서 우리에게 그다지 중요하지 않을 것입니다. 예를 들어, 위 출력의 상위 4 개는 1 메가 바이트 미만이며이를 생략하려는 경우 특정 관심 크기보다 큰 테이블 만 반환하도록 결과를 필터링 할 수 있습니다.

반면에이 데이터는 SalesOrderDetail의 3/4가 버퍼 캐시에 있음을 알려줍니다 . 이것이 비정상적으로 보이면 쿼리 계획 캐시를 참조하여 *를 선택하는 테이블에 비효율적 인 쿼리가 있는지 또는 지나치게 많은 양의 데이터가 있는지 확인합니다. 버퍼 캐시와 계획 캐시의 메트릭을 결합하여 필요한 것보다 훨씬 더 많은 데이터를 가져 오는 잘못된 쿼리 또는 애플리케이션을 찾아내는 새로운 방법을 고안 할 수 있습니다.

이 쿼리는 사용 된 테이블의 백분율을 수집 한 방법과 유사하게 사용중인 인덱스의 백분율을 제공하도록 수정할 수 있습니다.

SELECT

indexes.name AS index_name,

objects.name AS object_name,

objects.type_desc AS object_type_description,

COUNT(*) AS buffer_cache_pages,

COUNT(*) * 8 / 1024 AS buffer_cache_used_MB,

SUM(allocation_units.used_pages) AS pages_in_index,

SUM(allocation_units.used_pages) * 8 /1024 AS total_index_size_MB,

CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) AS percent_of_pages_in_memory

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

INNER JOIN sys.indexes

ON objects.object_id = indexes.object_id

AND partitions.index_id = indexes.index_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY indexes.name,

objects.name,

objects.type_desc

ORDER BY CAST((CAST(COUNT(*) AS DECIMAL) / CAST(SUM(allocation_units.used_pages) AS DECIMAL) * 100) AS DECIMAL(5,2)) DESC;

sys.allocation_units 는 인덱스에 대한 일부 크기 정보를 제공 하므로 dm_db_partition_stats 에서 추가 CTE 및 데이터 세트가 필요하지 않습니다 . 다음은 인덱스 크기 (MB 및 페이지)와 사용 된 버퍼 캐시 공간 (MB 및 페이지)을 보여주는 결과 조각입니다.

작은 테이블 / 인덱스에 관심이 없다면 특정 크기 (MB 또는 페이지)보다 작은 인덱스로 필터링하기 위해 쿼리에 HAVING 절을 추가 할 수 있습니다. 이 데이터는 특정 인덱스에 대한 쿼리의 효율성에 대한 좋은보기를 제공하며 인덱스 정리, 인덱스 조정 또는 SQL Server의 메모리 사용량에 대한보다 세부적인 조정을 지원할 수 있습니다.

dm_os_buffer_descriptors 에서 흥미로운 열 은 free_space_in_bytes 열입니다. 이는 버퍼 캐시의 각 페이지가 얼마나 가득 차 있는지 알려주므로 잠재적 인 공간 낭비 또는 비 효율성을 나타내는 지표를 제공합니다. 서버의 각 데이터베이스에 대해 데이터가 아닌 여유 공간이 차지하는 페이지의 비율을 확인할 수 있습니다.

WITH CTE_BUFFER_CACHE AS

( SELECT

databases.name AS database_name,

COUNT(*) AS total_number_of_used_pages,

CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB,

CAST(CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / (1024 * 1024) AS DECIMAL(20,2)) AS buffer_cache_free_space_in_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.databases

ON databases.database_id = dm_os_buffer_descriptors.database_id

GROUP BY databases.name)

SELECT

CAST((buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0)) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_space

FROM CTE_BUFFER_CACHE

ORDER BY buffer_cache_free_space_in_MB / NULLIF(buffer_cache_total_MB, 0) DESC

그러면 해당 특정 데이터베이스의 버퍼 캐시에있는 모든 페이지에서 합산 된 데이터베이스 당 여유 공간의 집계를 보여주는 데이터베이스 당 행이 반환됩니다.

이것은 흥미롭지 만 아직 그다지 유용하지는 않습니다. 그들은 데이터베이스에 약간의 낭비 된 공간이있을 수 있지만 어떤 테이블이 범인인지에 대해서는별로 설명하지 않습니다. 이전에했던 것과 동일한 접근 방식을 취하고 주어진 데이터베이스에서 테이블 당 여유 공간을 반환 해 보겠습니다.

SELECT

objects.name AS object_name,

objects.type_desc AS object_type_description,

COUNT(*) AS buffer_cache_pages,

CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB,

CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024 AS buffer_cache_free_space_in_MB,

CAST((CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024) / (CAST(COUNT(*) * 8 AS DECIMAL) / 1024) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_space

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY objects.name,

objects.type_desc,

objects.object_id

HAVING COUNT(*) > 0

ORDER BY COUNT(*) DESC;

이렇게하면 버퍼 캐시에 최소한 하나의 페이지가있는 테이블 또는 인덱싱 된 뷰당 행이 반환되며 메모리에 가장 많은 페이지가있는 페이지부터 순서가 지정됩니다.

이전 예에서와 같이 작은 테이블은 버퍼 캐시 메모리 소비에 미치는 영향이 미미하므로 무시할 수 있습니다. 상위 4 개 테이블에는 사용 가능한 공간이 매우 적습니다 (각각 2 % 미만).

이것이 정확히 무엇을 의미합니까? 평균적으로 페이지 당 여유 공간이 많을수록 우리가 찾고있는 데이터를 반환하기 위해 더 많은 페이지를 읽어야합니다. 또한 데이터를 저장하려면 더 많은 페이지가 필요하므로 데이터를 유지하려면 메모리와 디스크에 더 많은 공간이 필요합니다. 낭비 된 공간은 또한 필요한 데이터를 가져 오기위한 더 많은 IO와이 데이터가 검색 될 때 필요한 것보다 오래 실행되는 쿼리를 의미합니다.

여유 공간 과잉의 가장 일반적인 원인은 행이 매우 넓은 테이블입니다. 페이지가 8k이기 때문에 행이 5k 인 경우 한 페이지에 단일 행을 넣을 수 없으며 항상 사용할 수없는 추가 ~ 3k 여유 공간이 있습니다. 임의 삽입 작업이 많은 테이블도 문제가 될 수 있습니다. 예를 들어, 키가 증가하지 않으면 데이터가 순서없이 기록 될 때 페이지 분할이 발생할 수 있습니다. GUID는 최악의 시나리오이지만 본질적으로 증가하지 않는 키는 어느 정도이 문제를 일으킬 수 있습니다.

인덱스가 시간이 지남에 따라 조각화됨에 따라 조각화는 버퍼 캐시의 내용을 볼 때 부분적으로 초과 여유 공간으로 간주됩니다. 이러한 문제의 대부분은 스마트 데이터베이스 설계와 합리적인 데이터베이스 유지 관리로 해결됩니다. 여기에서 이러한 주제에 대해 자세히 설명 할 수는 없지만 즐거움을 위해 이러한 주제에 대한 많은 기사와 프레젠테이션이 있습니다.

이 기사의 앞부분에서 더티 및 클린 페이지가 무엇인지, 그리고 데이터베이스 내에서 쓰기 작업과의 상관 관계에 대해 간략하게 설명했습니다. dm_os_buffer_descriptors 내 에서 is_modified 열을 사용하여 페이지가 깨끗한 지 여부를 확인할 수 있습니다 . 이것은 페이지가 쓰기 작업에 의해 수정되었지만 아직 디스크에 다시 쓰여지지 않았 음을 알려줍니다. 이 정보를 사용하여 주어진 데이터베이스에 대한 버퍼 캐시의 깨끗한 페이지와 더티 페이지를 계산할 수 있습니다.

SELECT

databases.name AS database_name,

COUNT(*) AS buffer_cache_total_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 1

ELSE 0

END) AS buffer_cache_dirty_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 0

ELSE 1

END) AS buffer_cache_clean_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 1

ELSE 0

END) * 8 / 1024 AS buffer_cache_dirty_page_MB,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 0

ELSE 1

END) * 8 / 1024 AS buffer_cache_clean_page_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.databases

ON dm_os_buffer_descriptors.database_id = databases.database_id

GROUP BY databases.name;

이 쿼리는 페이지 수와 데이터 크기 (MB)를 반환합니다.

내 서버는 현재 너무 많이 진행되지 않습니다. 큰 업데이트 문을 실행하면 더 많은 쓰기 작업이 진행될 때 볼 수있는 내용을 설명 할 수 있습니다. 다음 쿼리를 실행 해 보겠습니다.

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty

이것은 본질적으로 작동하지 않으며 SalesOrderDetail 테이블이 실제로 변경되지는 않지만 SQL Server는이 특정 열에 대한 테이블의 모든 행을 업데이트하는 문제를 겪습니다. 위에서 더티 / 클린 페이지 수를 실행하면 더 흥미로운 결과를 얻을 수 있습니다.

버퍼 캐시에있는 AdventureWorks2014 용 페이지의 약 2/3 가 더티 페이지입니다 . 또한 TempDB 에는 테이블에 대한 업데이트 / 삽입 / 삭제 트리거를 나타내는 상당한 활동이있어 많은 양의 추가 TSQL이 실행되었습니다. 트리거로 인해 AdventureWorks2014에 대해 상당히 많은 추가 읽기가 발생했으며 이러한 추가 작업을 처리하기 위해 TempDB의 작업 테이블 공간이 필요했습니다 .

이전과 마찬가지로 버퍼 캐시 사용에 대한보다 세분화 된 데이터를 수집하기 위해 테이블 또는 인덱스별로 구분할 수 있습니다.

SELECT

indexes.name AS index_name,

objects.name AS object_name,

objects.type_desc AS object_type_description,

COUNT(*) AS buffer_cache_total_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 1

ELSE 0

END) AS buffer_cache_dirty_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 0

ELSE 1

END) AS buffer_cache_clean_pages,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 1

ELSE 0

END) * 8 / 1024 AS buffer_cache_dirty_page_MB,

SUM(CASE WHEN dm_os_buffer_descriptors.is_modified = 1

THEN 0

ELSE 1

END) * 8 / 1024 AS buffer_cache_clean_page_MB

FROM sys.dm_os_buffer_descriptors

INNER JOIN sys.allocation_units

ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id

INNER JOIN sys.partitions

ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))

OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))

INNER JOIN sys.objects

ON partitions.object_id = objects.object_id

INNER JOIN sys.indexes

ON objects.object_id = indexes.object_id

AND partitions.index_id = indexes.index_id

WHERE allocation_units.type IN (1,2,3)

AND objects.is_ms_shipped = 0

AND dm_os_buffer_descriptors.database_id = DB_ID()

GROUP BY indexes.name,

objects.name,

objects.type_desc

ORDER BY COUNT(*) DESC;

결과는 인덱스 별 버퍼 캐시 사용량을 보여 주며 메모리의 페이지가 정리되었거나 더럽지 않은지 보여줍니다.

이 데이터는이 시점에서 주어진 인덱스에 대한 쓰기 활동에 대한 아이디어를 제공합니다. 며칠 또는 몇 주에 걸쳐 추적했다면 인덱스의 전체 쓰기 활동을 측정하고 추세를 파악할 수 있습니다. 이 연구는 데이터베이스에서 사용할 수있는 최상의 격리 수준을 이해하려는 경우 또는 항상 READ UNCOMMITTED로 실행되는 보고서가 원래 생각했던 것보다 더티 읽기에 더 취약 할 수있는 경우 유용 할 수 있습니다. 이 특정 경우에 더티 페이지는 모두 이전에 위에서 실행 한 업데이트 쿼리와 관련이 있으므로 다소 제한된 집합으로 구성됩니다.

DBCC DROPCLEANBUFFERS

쿼리를 테스트하고 실행 속도를 정확하게 측정하는 방법으로 자주 사용되는 DBCC 명령은 DBCC DROPCLEANBUFFERS 입니다. 실행되면 전체 데이터베이스 서버의 메모리에서 모든 클린 페이지가 제거되고 일반적으로 소량의 데이터 인 더티 페이지 만 남게됩니다.

DBCC DROPCLEANBUFFERS 는 일반적으로 비 프로덕션 환경에서만 실행해야하는 명령이며, 성능이나 부하 테스트가 수행되지 않는 경우에만 실행되어야합니다. 이 명령의 결과로 버퍼 캐시가 대부분 비어있게됩니다. 이 시점 이후에 실행되는 모든 쿼리는 물리적 읽기를 사용하여 데이터를 스토리지 시스템에서 캐시로 다시 가져와야합니다. 이전에 설정 한 것처럼 메모리보다 훨씬 느릴 수 있습니다.

내 로컬 서버에서이 명령을 실행 한 후 이전의 더티 / 클린 페이지 쿼리는 다음을 반환합니다.

그게 다야! 이전 경고를 반복합니다.이 명령을 DBCC FREEPROCCACHE 와 비슷하게 처리합니다.이 명령 은 수행중인 작업을 절대적으로 알지 못하는 경우 프로덕션 서버에서 실행해서는 안됩니다.

이는 메모리의 데이터 캐싱으로 인한 속도 / 효율성 변경없이 성능 테스트 환경에서 쿼리를 반복해서 실행할 수 있다는 점에서 유용한 개발 도구가 될 수 있습니다. 실행과 비즈니스 사이에 깨끗한 버퍼 데이터를 삭제하십시오. 하지만 프로덕션 환경에서는 항상 버퍼 캐시를 사용하고 필요한 경우가 아니면 스토리지 시스템에서 읽지 않기 때문에 잘못된 결과를 제공 할 수 있습니다. 깨끗한 버퍼를 삭제하면 실행 시간이 다른 경우보다 느리지 만 실행될 때마다 일관된 환경에서 쿼리를 테스트하는 방법을 제공 할 수 있습니다.

이러한 모든주의 사항을 이해하고 필요에 따라 쿼리 성능, 쿼리의 결과로 메모리로 읽은 페이지, 쓰기 문에 의해 생성 된 더티 페이지 등에 대한 통찰력을 얻기 위해 필요에 따라 자유롭게 사용하십시오.

페이지 수명

SQL Server의 메모리 성능에 대해 논의 할 때 누군가가 페이지 수명 (줄여서 PLE)에 대해 질문하기까지 몇 분이 걸리지 않을 것입니다. PLE는 평균적으로 페이지가 액세스되지 않고 메모리에 남아있는 시간 (초)을 측정 한 후 제거됩니다. 이는 중요한 데이터가 가능한 한 오랫동안 버퍼 캐시에 남아 있기를 바라기 때문에 더 높이고 자하는 지표입니다. PLE가 너무 낮아지면 데이터가 디스크에서 버퍼 캐시로 지속적으로 읽혀지고 (즉, 느리게) 캐시에서 제거되고, 가까운 장래에 디스크에서 다시 읽을 가능성이 높습니다. 이것은 느리고 실망스러운 SQL Server의 비결입니다!

서버의 현재 PLE를 보려면 다음 쿼리를 실행하면 성능 카운터 동적 관리보기에서 현재 값을 가져옵니다.

SELECT

FROM sys.dm_os_performance_counters

WHERE dm_os_performance_counters.object_name LIKE '%Buffer Manager%'

AND dm_os_performance_counters.counter_name = 'Page life expectancy';

결과는 다음과 같습니다.

cntr_value는 성능 카운터의 값이고 조용한 로컬 서버의 경우 210,275 초입니다. SQL Server에서 읽거나 쓰는 데이터가 매우 적기 때문에 버퍼 캐시에서 데이터를 제거해야 할 필요성이 적기 때문에 PLE가 엄청나게 높습니다. 더 많이 사용되는 프로덕션 서버에서 PLE는 거의 확실히 더 낮습니다.

서버에 NUMA (비 균일 메모리 액세스) 아키텍처가있는 경우 다음 쿼리로 수행 할 수있는 각 노드에 대해 PLE를 개별적으로 고려할 수 있습니다.

SELECT

FROM sys.dm_os_performance_counters

WHERE dm_os_performance_counters.object_name LIKE '%Buffer Node%'

AND dm_os_performance_counters.counter_name = 'Page life expectancy';

NUMA가없는 서버에서는 이러한 값이 동일합니다. NUMA 아키텍처를 사용하는 서버에서는 여러 PLE 행이 반환되며,이 행은 모두 버퍼 관리자 전체에 대해 제공된 합계에 합산됩니다. NUMA로 작업하는 경우 전체 합계가 허용되는 반면 하나의 노드에 병목 현상이 발생할 수 있으므로 합계 외에도 각 노드에서 PLE를 고려해야합니다.

지금 가장 분명한 질문은 "PLE의 좋은 가치는 무엇입니까?"입니다. 이 질문에 답하려면 서버에 얼마나 많은 메모리가 있는지, 그리고 쓰고 읽는 데이터의 예상 볼륨이 얼마인지 확인하기 위해 서버를 더 자세히 조사해야합니다. 300 초는 종종 PLE에 대한 좋은 값으로 던져 지지만 빠르고 쉬운 답변처럼 잘못된 것 같습니다.

PLE가 어떻게 생겼는지 고려하기 전에 그것이 의미하는 바에 대해 좀 더 고려해 봅시다. 256GB RAM이있는 서버를 가정 해 보겠습니다.이 중 192GB는 구성에서 SQL Server에 할당됩니다. dm_os_sys_info 뷰를 확인하고 현재 버퍼 캐시에 약 163GB가 커밋되어 있음을 알았습니다. 마지막으로 위의 성능 카운터를 확인한 결과이 서버의 PLE가 2000 초임을 확인했습니다.

이러한 메트릭을 기반으로 우리는 버퍼 캐시에 163GB의 메모리를 사용할 수 있으며 데이터는 약 2000 초 동안 존재합니다. 이것은 우리가 평균적으로 2000 초당 163GB를 읽고 있다는 것을 의미합니다. 이는 약 83MB / 초로 나옵니다. 이 숫자는 응용 프로그램이나 프로세스에서 SQL Server에 얼마나 많이 액세스하고 있는지를 명확하게 보여주기 때문에 매우 유용합니다. 좋은 PLE가 무엇인지 고려하기 전에 몇 가지 질문을해야합니다.

애플리케이션 / 서비스에서 예상하는 평균 데이터 트래픽은 얼마나됩니까?
백업, 인덱스 유지 관리, 보관, DBCC CheckDB 또는 기타 프로세스로 인해 PLE가 매우 낮아질 수있는 "특별한"시간이 있습니까?
지연이 문제입니까? 응용 프로그램의 성능을 저하시키는 측정 가능한 대기가 있습니까?
서버에 상당한 IO 대기가 있습니까?
가장 많은 데이터를 읽을 것으로 예상되는 쿼리는 무엇입니까?

즉, 씬 데이터를 아십시오! PLE 질문에 대한 유일한 정답은 PLE의 좋은 가치는 사용량 증가 및 급증을 고려할 수있는 충분한 여유 공간을 갖춘 최적의 서버 성능을 나타내는 것입니다. 예를 들어, 버퍼 캐시에 할당 된 163GB의 메모리, 2000 초의 평균 PLE, 83MB / 초의 외삽 처리량을 가진 이전 서버를 살펴 보겠습니다. 몇 가지 추가 연구 끝에 PLE가 1500 초 미만으로 떨어지면 성능이 저하되기 시작한다는 것을 발견했습니다. 이 시점에서 추가 실사를 수행하고 애플리케이션이 한 달에 1 % 씩 증가한다는 것을 알게되었습니다 (데이터 크기 및 처리량 측면에서). 결과적으로 6 개월 후에는 현재와 유사한 수준의 PLE를 유지하기 위해 SQL Server 전용 172GB RAM이 필요하다고 추정 할 수 있습니다. 시간이 흐르면서

이러한 계산은 용량 계획의 중요한 부분이며 모든 조직이 미래에 대비할 수 있도록합니다. 이를 통해 우리는 일이 참을 수 없을 정도로 느려질 때 단순히 서버에 RAM을 추가하는 것이 아니라 능동적으로 유지할 수 있습니다. 애플리케이션이 한 달에 1 % 씩 영원히 성장하는 경우는 거의 없습니다. 오히려 데이터 증가, 새로운 기능, 아키텍처 변경 및 인프라 변경의 혼합을 기반으로 성장합니다. 즉, 응용 프로그램이 한 달에 1 % 씩 증가 할 수 있지만 주요 소프트웨어 릴리스 이후에는 한 번만 증가하면 10 % 증가 할 수 있습니다.

결론

버퍼 캐시를 들여다 보는 것은 애플리케이션과 프로세스의 성능에 대해 자세히 알아볼 수있는 좋은 방법입니다. 이 정보를 사용하여 성능이 저조한 쿼리를 추적하고 예상보다 더 많은 메모리를 사용하는 개체를 식별하며 향후 서버 계획을 개선 할 수 있습니다. 이 지식은 누가 영향을 미치고 누가 영향을받을 수 있는지 측면에서 개발, 관리, 아키텍처 및 디자인에 걸쳐 있습니다. 결과적으로 서버의 메모리 관리를 효과적으로 관리하면 SQL Server를 사용하는 모든 사람의 경험이 향상되는 동시에 생활이 더 쉬워집니다.

우리는 버퍼 캐시에 대한 유용한 정보를 반환 할 수 있지만 관련된 뷰 및 TSQL에 대해 반드시 자세히 설명하지는 않는 몇 가지 스크립트에 뛰어 들었습니다. 향후 기사에서는 우리가 더 자세히 사용한 시스템 뷰 중 일부를 다시 살펴보고 페이지 데이터와이 정보를 큰 이익을 위해이 정보를 사용하는 몇 가지 추가 방법을 살펴볼 것입니다. 나는 이것이 한꺼번에 흥미롭고 끔찍할 것이라고 기대하므로 다른 방법은 없을 것입니다.

'Database > SQL Server' 카테고리의 다른 글

Iterators in the Query Execution Plan (0)	2021.02.02
SORT WARNING & HASH WARNING (0)	2021.02.02
SQL 에이전트 작업 삭제 오류 ( 유지관리계획 삭제 오류 ) DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. Microsoft SQL Server, 오류: 547 (0)	2021.01.27
SQL Server 쿼리가 느려졌을 때 (0)	2021.01.20
SQL Server 백업/복원 예상 완료시간 확인하기 (0)	2021.01.20

SQL 에이전트 작업 삭제 오류 ( 유지관리계획 삭제 오류 ) DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. Microsoft SQL Server, 오류: 547

ㅎ2 2021. 1. 27. 09:30

2021. 1. 27. 09:30

MSSQL 을 사용하다 유지관리계획의 작업을 삭제를 하려고 하면 아래와 같은 에러 메시지가 나온다.

DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. 데이터베이스 “msdb”, 테이블 “dbo.sysmaintplan_subplans”, column ‘job_id’에서 충돌이 발생했습니다.
문이 종료되었습니다. (Microsoft SQL Server, 오류: 547)

도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

아래 이미지와 같이 작업에서 DAY_BACKUP 작업관리를 삭제하려면 에레메시지가 발생된다.
원인은 sysmainplan_plans 테이블의 ID값과 해당 작업의 로그가 쌓이는 sysmaintplan_log의 ID값이 서로 다르기 때문에 발생한다.
그럼 이제 이럴경우 삭제하는 방법을 간단하게 알아보자.

1.먼저 삭제할 작업의 목록을 선택 후 작업 스크립팅 -> DROP -> 새쿼리 편집기 창을 클릭한다.

2. 쿼리 실행창이 나타나며 job_ip가 확인된다 이 job_id를 복사를 한다.

3. 새쿼리 창을 뛰어서 아래와 같이 명령어를 입력한다

1 2	use master select * from msdb.dbo.sysmaintplan_subplans where job_id='19c04eaf-2ec4-47fe-816c-0ba0d23874d88'

명령어를 입력하면 plan_id값이 출력된다 이제 이 값을 복사하자

4. 위에서 확인된 plan_id값을 복사하였으며 아래와 같이 delete 쿼리문으로 삭제를 해준다

use master

delete from msdb.dbo.sysmaintplan_log where plan_id='8759DB1-A425-47F8-94C3-0B0DCF177277'

delete from msdb.dbo.sysmaintplan_subplans where plan_id='8759DB1-A425-47F8-94C3-0B0DCF177277'

5.이제 삭제에서 에러가 났던 부분을 삭제를 해보자 정상적으로 삭제가 된 것을 확인 할 수 있다.

모두 정상적으로 삭제가 완료된 상태

'Database > SQL Server' 카테고리의 다른 글

SORT WARNING & HASH WARNING (0)	2021.02.02
SQL Server 버퍼 캐시에 대한 통찰력 (0)	2021.01.27
SQL Server 쿼리가 느려졌을 때 (0)	2021.01.20
SQL Server 백업/복원 예상 완료시간 확인하기 (0)	2021.01.20
DB Backup Script (0)	2021.01.20

SQL Server 쿼리가 느려졌을 때

ㅎ2 2021. 1. 20. 15:31

2021. 1. 20. 15:31

운영환경에서 데이터가 증가하면서 혹은 통계업데이트에 의해 실행계획이 변경되어 쿼리가 갑자기 이전보다 확 느려질 수 있다.

아래와 같은 방법으로 실행계획을 확인할 수 있다.

1) 문제의 쿼리 찾기

--spid 찾기

select hostname, hostprocess, spid
from master..sysprocesses
where hostname = '호스트네임'

SELECT client_net_address, session_id
FROM sys.dm_exec_connections
WHERE client_net_address = 'IP'

--실행중인 쿼리 확인
SELECT
sqltext.TEXT,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.blocking_session_id,
req.percent_complete,
req.plan_handle -- 실행계획 확인할 때 사용
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where req.session_id = SPID

2) 실행계획 확인하기

SELECT * FROM sys.dm_exec_query_plan (req.plan_handle);

3) 예상 실행계획 삭제

DBCC FREEPROCCACHE (0x06000700A9F6B63640212E7B280000000000000000000000)

4) 캐시에 실행계획이 삭제되었는지 확인

SELECT TOP 10 UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

5) 문제의 쿼리 재수행

위 방법으로도 해결이 되지 않는다면 실행계획을 분석하여 쿼리 실행에 부하요소를 주는 근본적인 원인을 찾아야 한다.

'Database > SQL Server' 카테고리의 다른 글

SQL Server 버퍼 캐시에 대한 통찰력 (0)	2021.01.27
SQL 에이전트 작업 삭제 오류 ( 유지관리계획 삭제 오류 ) DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. Microsoft SQL Server, 오류: 547 (0)	2021.01.27
SQL Server 백업/복원 예상 완료시간 확인하기 (0)	2021.01.20
DB Backup Script (0)	2021.01.20
free SSMS add-in for effective database performance tracing (0)	2021.01.20

SQL Server 백업/복원 예상 완료시간 확인하기

ㅎ2 2021. 1. 20. 15:30

2021. 1. 20. 15:30

SELECT
session_id,
start_time,
status,
command,
percent_complete,
estimated_completion_time,
estimated_completion_time /60/1000 as estimate_completion_minutes,
--(select convert(varchar(5),getdate(),8)),
DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time
FROM sys.dm_exec_requests where command = 'BACKUP DATABASE' OR command = 'RESTORE DATABASE'

'Database > SQL Server' 카테고리의 다른 글

SQL 에이전트 작업 삭제 오류 ( 유지관리계획 삭제 오류 ) DELETE 문이 REFERENCE 제약 조건 “FK_subplan_job_id”과(와) 충돌했습니다. Microsoft SQL Server, 오류: 547 (0)	2021.01.27
SQL Server 쿼리가 느려졌을 때 (0)	2021.01.20
DB Backup Script (0)	2021.01.20
free SSMS add-in for effective database performance tracing (0)	2021.01.20
Tempdb 최적화 – tempdb 데이터파일 제거하기 (0)	2020.10.27

DB Backup Script

ㅎ2 2021. 1. 20. 08:55

2021. 1. 20. 08:55

-- Backup Script

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.TRN'

BACKUP LOG @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

'Database > SQL Server' 카테고리의 다른 글

SQL Server 쿼리가 느려졌을 때 (0)	2021.01.20
SQL Server 백업/복원 예상 완료시간 확인하기 (0)	2021.01.20
free SSMS add-in for effective database performance tracing (0)	2021.01.20
Tempdb 최적화 – tempdb 데이터파일 제거하기 (0)	2020.10.27
SQL Server 임시 테이블 : 방법 레시피 (0)	2020.10.23

free SSMS add-in for effective database performance tracing

ㅎ2 2021. 1. 20. 08:54

2021. 1. 20. 08:54

'Database > SQL Server' 카테고리의 다른 글

SQL Server 백업/복원 예상 완료시간 확인하기 (0)	2021.01.20
DB Backup Script (0)	2021.01.20
Tempdb 최적화 – tempdb 데이터파일 제거하기 (0)	2020.10.27
SQL Server 임시 테이블 : 방법 레시피 (0)	2020.10.23
SQL Server 2016 - Temporal Table (0)	2020.10.23

이벤트 스케줄러 사용하기(event scheduler)

ㅎ2 2021. 1. 6. 13:56

2021. 1. 6. 13:56

1. event scheduler의 상태 확인
SHOW VARIABLES LIKE 'event%';

2. event scheduler ON/OFF
SET GLOBAL event_scheduler = ON ;
SET GLOBAL event_scheduler = OFF ;

3. event scheduler 확인 Query
SELECT * FROM information_schema.events;

4. event scheduler 생성하기

(MONTH : 월 / HOUR : 시간 / DAY : 일)등으로 Scheduler 설정이 가능합니다.
수행할 작업은 DO 아래행에 작성하시면 되며, 저는 Procedure로 작성해 봤습니다.
일반 쿼리문을 사용해도 됩니다. (select * from ....)

5. event scheduler 수정하기

6. event scheduler 삭제하기

'Database > MySql' 카테고리의 다른 글

PostgreSQL(13.1) FDW(2.3) oci.h: No such file or directory (0)	2021.02.01
MySQL 데이터 경로 변경하는 방법 (0)	2020.12.29
MySql RPM (0)	2020.08.28

MySQL 데이터 경로 변경하는 방법

ㅎ2 2020. 12. 29. 10:26

2020. 12. 29. 10:26

Mysql datadir 경로 확인 및 Mysql service 중지

mysql 접속 하여 아래와 같은 명령어로 datadir 경로를 확인 하고 서비스를 중단 한다.

mysql> select @@datadir;

+-----------------+

| @@datadir |

+-----------------+

| /var/lib/mysql/ |

+-----------------+

1 row in set (0.00 sec)

systemctl stop mysql

새로운 Mysql datadir 생성 및 경로 복사 하기

아래와 같은 명령어로 새로운 Mysql datadir 생성 후 Mysql datadir 경로 복사 한다. 권한 또한 부여 한다.

mkdir /data/

rsync -av /var/lib/mysql /data/

chown -R mysql:mysql /data/mysql

my.cnf 파일 수정

/etc/my.cnf을 아래와 같이 수정 한다.

vi /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

[client]

socket=/data/mysql/mysql.sock

#symbolic-links=0

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

SELinux 보안 context에 추가 및 서비스 시작

아래와 같은 명령어로 SELinux 보안 context에 적용을 시키고 서비스를 시작한다.

semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"

restorecon -R /data/mysql

systemctl start mysql

변경된 datadir 확인 하기

mysql접속 하여 datadir 경로가 바뀌었음을 확인 할 수 있다.

mysql> select @@datadir;

+--------------+

| @@datadir |

+--------------+

| /data/mysql/ |

+--------------+

1 row in set (0.00 sec)

database가 생성 될 때마다 /data/mysql/ 아래로 database가 생성이 된다.

'Database > MySql' 카테고리의 다른 글

PostgreSQL(13.1) FDW(2.3) oci.h: No such file or directory (0)	2021.02.01
이벤트 스케줄러 사용하기(event scheduler) (0)	2021.01.06
MySql RPM (0)	2020.08.28

CentOS 7에 PostgreSQL 13을 설치하는 방법

ㅎ2 2020. 11. 18. 14:08

2020. 11. 18. 14:08

PostgreSQL은 POSTGRES 4.2를 기반으로하는 객체 관계형 데이터베이스 관리 시스템입니다. PostgreSQL 13은 일반 대중 및 프로덕션 용도로 출시되었습니다. PostgreSQL은 모든 주요 Linux 운영 체제에서 실행되며 2001 년부터 ACID를 준수하고 있습니다. 또한 널리 사용되는 PostGIS 지리 공간 데이터베이스 확장기 와 같은 강력한 애드온이 있습니다 . 이 가이드는 CentOS 7에 PostgreSQL 13을 설치하는 과정을 안내합니다.

PostgreSQL 개발 팀은 각각 RHEL 및 Debian 기반 시스템에 대해 각각 온라인 YUM 및 APT 저장소에 사전 빌드 된 패키지를 제공합니다. 지원되는 배포판 중에는 CentOS, Fedora, Scientific Linux, Oracle Linux 및 Red Hat Enterprise Linux를 포함하는 모든 Red Hat 제품군이 있습니다. 이제 CentOS 7에서 PostgreSQL 13 설치를 시작할 수 있습니다.

CentOS 7에 PostgreSQL 13을 설치하는 방법

아래 단계를 사용하여 CentOS 7 Linux에 PostgreSQL 13을 설치합니다. 최근 CentOS / RHEL 8 시스템 에 PostgreSQL 13 설치 에 대한 별도의 기사를 작성했습니다 .

1 단계 : CentOS 7에 PostgreSQL Yum 리포지토리 추가

PostgreSQL Yum 리포지토리는 일반 시스템 및 패치 관리와 통합되며 PostgreSQL의 지원 기간 동안 지원되는 모든 PostgreSQL 버전에 대한 자동 업데이트를 제공합니다. 다음 명령을 실행하여 CentOS 7 Linux 시스템에 PostgreSQL 13 리포지토리를 추가합니다.

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

저장소를 추가 한 후 시스템 재부팅을 수행하는 것이 좋습니다.

sudo yum -y update

sudo reboot

2 단계 : CentOS 7에 PostgreSQL 13 설치

리포지토리를 성공적으로 추가 한 후 CentOS 7에 PostgreSQL 13 설치를 진행할 수 있습니다.

활성화 된 저장소 목록을 확인합니다.

$ sudo yum repolist

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

 * base: mirror.alpha-labs.net

 * extras: artfiles.org

 * updates: mirror.plustech.de

repo id               repo name                                           status

base/7/x86_64         CentOS-7 - Base                                     10,070

extras/7/x86_64       CentOS-7 - Extras                                      413

pgdg-common/7/x86_64  PostgreSQL common RPMs for RHEL/CentOS 7 - x86_64      360

pgdg10/7/x86_64       PostgreSQL 10 for RHEL/CentOS 7 - x86_64               789

pgdg11/7/x86_64       PostgreSQL 11 for RHEL/CentOS 7 - x86_64               838

pgdg12/7/x86_64       PostgreSQL 12 for RHEL/CentOS 7 - x86_64               414

pgdg13/7/x86_64       PostgreSQL 13 for RHEL/CentOS 7 - x86_64               140

pgdg95/7/x86_64       PostgreSQL 9.5 for RHEL/CentOS 7 - x86_64              698

pgdg96/7/x86_64       PostgreSQL 9.6 for RHEL/CentOS 7 - x86_64              759

updates/7/x86_64      CentOS-7 - Updates                                   1,134

repolist: 15,615

저장소에서 PostgreSQL 13 패키지를 사용할 수 있는지 확인하십시오.

$ sudo yum search postgresql13

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

 * base: mirror.alpha-labs.net

 * extras: artfiles.org

 * updates: mirror.plustech.de

========================== N/S matched: postgresql13 ===========================

postgresql13.x86_64 : PostgreSQL client programs and libraries

postgresql13-contrib.x86_64 : Contributed source and binaries distributed with

                            : PostgreSQL

postgresql13-devel.x86_64 : PostgreSQL development header files and libraries

postgresql13-docs.x86_64 : Extra documentation for PostgreSQL

postgresql13-libs.x86_64 : The shared libraries required for any PostgreSQL

                         : clients

postgresql13-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL

postgresql13-plperl.x86_64 : The Perl procedural language for PostgreSQL

postgresql13-plpython3.x86_64 : The Python3 procedural language for PostgreSQL

postgresql13-pltcl.x86_64 : The Tcl procedural language for PostgreSQL

postgresql13-server.x86_64 : The programs needed to create and run a PostgreSQL

                           : server

postgresql13-test.x86_64 : The test suite distributed with PostgreSQL

 

Name and summary matches only, use "search all" for everything.

이제 CentOS 7에 PostgreSQL 13 패키지를 설치합니다.

sudo yum -y install postgresql13 postgresql13-server

설치 프로그램이 요청하면 GPG 키를 가져옵니다.

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                                                             7.0 MB/s |  15 MB  00:00:02

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Importing GPG key 0x442DF0F8:

 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"

 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8

 Package    : pgdg-redhat-repo-42.0-11.noarch (@/pgdg-redhat-repo-latest.noarch)

 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Is this ok [y/N]: y

3 단계 : 데이터베이스 서비스 초기화 및 시작

CentOS 7에 PostgreSQL 13을 새로 설치 한 후에는 초기화가 필요합니다.

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

Initializing database ... OK

서비스 상태를 확인하여 실행 중인지 확인하십시오.

$ systemctl status postgresql-13

● postgresql-13.service - PostgreSQL 13 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2020-07-09 23:35:30 CEST; 37s ago

     Docs: https://www.postgresql.org/docs/13/static/

  Process: 1860 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 1865 (postmaster)

    Tasks: 8 (limit: 12210)

   Memory: 17.3M

   CGroup: /system.slice/postgresql-13.service

           ├─1865 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/

           ├─1867 postgres: logger

           ├─1869 postgres: checkpointer

           ├─1870 postgres: background writer

           ├─1871 postgres: walwriter

           ├─1872 postgres: autovacuum launcher

           ├─1873 postgres: stats collector

           └─1874 postgres: logical replication launcher

 

Jul 09 23:35:30 centos-01.computingforgeeks.com systemd[1]: Starting PostgreSQL 13 database server...

Jul 09 23:35:30 centos-01.computingforgeeks.com postmaster[1865]: 2020-07-09 23:35:30.180 CEST [1865] LOG:  redirecting log output to logging collector process

Jul 09 23:35:30 centos-01.computingforgeeks.com postmaster[1865]: 2020-07-09 23:35:30.180 CEST [1865] HINT:  Future log output will appear in directory "log".

Jul 09 23:35:30 centos-01.computingforgeeks.com systemd[1]: Started PostgreSQL 13 database server.

시스템이 리봇 될 때 서비스가 시작되도록합니다.

$ sudo systemctl enable postgresql-13

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service.

4 단계 : PostgreSQL 관리자 암호 설정

PostgreSQL 관리자 암호를 업데이트합니다.

$ sudo su-postgres

] $ psql -c " 'StrongDBPassword'암호로 사용자 postgres 변경" ALTER ROLE

5 단계 : 원격 데이터베이스 연결 활성화 (선택 사항)

파일을 편집하고 /var/lib/pgsql/13/data/postgresql.conf Listen 주소를 서버 IP 주소로 설정하거나 모든 인터페이스에 대해 " * "를 설정합니다.

$ sudo vi /var/lib/pgsql/13/data/postgresql.conf

# 59 행

listen_addresses = '172.20.11.11'

원격 연결을 허용하도록 PostgreSQL 설정

$ sudo vi /var/lib/pgsql/13/data/pg_hba.conf

# 어디서든 수락 (권장하지 않음)

host all 모두 0.0.0.0/0 md5



# 신뢰할 수있는 서브넷에서 수락 ( 권장 설정 )

host all 모두 172.20.11.0/ 24 md5

변경 사항을 저장 한 후 데이터베이스 서비스를 다시 시작합니다.

sudo systemctl restart postgresql-13

사용자 이름과 선택적으로 데이터베이스 이름을 제공하면서 psql 명령을 사용하여 연결을 테스트합니다.

$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>

6 단계 : pgAdmin 4 웹 인터페이스 설치

pgAdmin은 PostgreSQL 데이터베이스 서버를 관리하기위한 오픈 소스 도구입니다. 아래 가이드를 사용하여 CentOS 7 컴퓨터에 pgAdmin4를 설치하고 사용하십시오.

CentOS 7에 pgAdmin 4를 설치하는 방법

Install pgAdmin 4 on CentOS 7 & Fedora 33/32/31/30 | ComputingForGeeks

pgAdmin is the leading Open Source feature-rich PostgreSQL administration and development platform that runs on Linux, Unix, Mac OS X, and

computingforgeeks.com

CentOS 7 서버 또는 Workstation에 PostgreSQL 13 설치를 완료했습니다. 자세한 내용은 PostgreSQL 관리 설명서를 참조하십시오 .

PostgreSQL: Documentation

Documentation This section contains current and archived manuals for PostgreSQL users. You can read the release notes, and view a listing of books written about PostgreSQL.

www.postgresql.org

'ETC' 카테고리의 다른 글

클라우드 환경에서 데이터를 활용하면 어떤 장점이 있을까? (0)	2020.11.11
SP_ERRORLOG (0)	2020.11.02
클러스터 그룹 변경 (0)	2020.08.28
Net Framework install 오류 코드 : (0x00000057), "매개 변수가 잘못되었습니다." (0)	2020.08.28
Visual Studio Community (0)	2020.08.28

테이블 사이즈

ㅎ2 2020. 11. 13. 10:50

2020. 11. 13. 10:50

SELECT s.name + '.' + t.Name AS [Table Name], part.rows AS [Total Rows In Table - Modified],

CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))

AS [Table's Total Space In GB]

FROM

SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id

AND idx.Index_id = part.Index_id

INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC

'Database > Query' 카테고리의 다른 글

INDEX 상세 정보 조회 (0)	2020.11.02
프로시저 파리미터 (0)	2020.10.20
DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22

PREV 이전 1 2 3 4 5 6 ···12 NEXT 다음