SQL Server 테이블 메모리를 상주하게 만들고 감지하는 방법

SQL Server 데이터 테이블을 메모리에 유지하는 것은 SQL Server에서 제공하는 기능으로 일반 소규모 시스템의 개발 과정에는 거의 관여하지 않습니다. SQL Server 테이블의 모든 데이터를 메모리에 저장하여 인메모리 데이터베이스를 구현하고 실시간 성능을 향상시키는 방법을 보여주기 위해 관련 문서를 여기에 정리했습니다.

1, DBCC PINTABLE

이는

Microsoft SQL Server가 메모리에서 테이블 페이지를 플러시하지 않음을 의미합니다.

DBCC PINTABLE (database_id, table_id)

데이터베이스 ID를 확인하려면 DB_ID 함수를 사용하세요.

테이블 ID를 확인하려면 OBJECT_ID 함수를 사용하세요.

주석을 달면

테이블이 메모리로 읽혀지지 않습니다. 일반 Transact-SQL 문을 통해 테이블의 페이지를 캐시로 읽어오면 해당 페이지는 메모리 상주 페이지로 표시됩니다. SQL Server에 새 페이지를 읽을 공간이 필요한 경우 메모리 상주 페이지가 지워지지 않습니다. SQL Server는 여전히 페이지 업데이트를 기록하고 필요한 경우 업데이트된 페이지를 디스크에 다시 씁니다. 그러나 SQL Server는 DBCC UNPINTABLE 문을 사용하여 테이블이 비거주 상태가 될 때까지 캐시에 사용 가능한 페이지의 복사본을 보관합니다.

DBCC PINTABLE은 자주 참조되는 작은 테이블을 메모리에 보관하는 데 가장 적합합니다. 작은 테이블의 페이지를 한 번에 메모리로 읽으면 해당 데이터에 대한 향후 모든 참조를 디스크에서 읽을 필요가 없습니다.

참고 DBCC PINTABLE은 성능 향상을 제공할 수 있지만 주의해서 사용해야 합니다. 큰 테이블이 있는 경우 테이블은 처음에 시스템의 다른 테이블에 충분한 캐시를 남기지 않고 캐시의 많은 부분을 사용합니다. 호스팅된 테이블이 캐시보다 크면 테이블이 전체 캐시를 채웁니다. sysadmin 고정 서버 역할의 멤버는 SQL Server를 종료했다가 다시 시작한 다음 테이블을 비거주 테이블로 만들어야 합니다. 너무 많은 테이블을 호스팅하면 캐시보다 큰 테이블을 호스팅하는 것과 동일한 문제가 발생합니다.

예:

@db_id int, @tbl_id int 선언

DATABASE_NAME

Set @db_id = DB_ID('DATABASE_NAME')

Set @tbl_id = Object_ID('Department')

DBCC pintable(@db_id, @tbl_id)

은 Department 테이블을 메모리로 설정할 수 있습니다. 거주자 .

@db_id int, @tbl_id int 선언

DATABASE_NAME

Set @db_id = DB_ID('DATABASE_NAME')

Set @tbl_id = Object_ID('Department')

DBCC UNpintable(@db_id, @tbl_id)

은 Department 테이블을 메모리 상주로 설정 해제할 수 있습니다.

다음 SQL 명령을 사용하여 실행을 감지할 수 있습니다.

Select ObjectProperty(Object_ID('Department'),'TableIsPinned')

반환 결과가 1이면 테이블이 메모리에 상주하도록 설정되었음을 의미합니다. 메모리에 상주하도록 설정되어 있지 않습니다.

2, SP_TableOption

은

text, ntext 또는 image 열이 있는 테이블에서 행 기능을 켜는 데 사용할 수 있습니다.

sp_tableoption [ @TableNamePattern = ] '테이블'
, [ @OptionName = ] '옵션_이름'
, [ @OptionValue = ] 'value'

여기서 'option_name'의 사용법은 다음과 같습니다.

pintable - 비활성화되면(기본값) 테이블이 더 이상 RAM에 상주하지 않는 것으로 표시됩니다. 활성화되면 테이블이 RAM에 상주하지 않는 것으로 표시됩니다. (지정된 테이블은 메모리에 상주할 수 있습니다.)

또한 대량 로드 시 테이블 잠금, 행 삽입 잠금, 행의 텍스트 및 기타 선택적 값은 메모리에 상주하는 테이블을 포함하지 않습니다. 특정 용도에 대해서는 SQL Server Books를 쿼리할 수 있습니다. 온라인

값 사용법은 다음과 같습니다.

option_name이 활성화되거나(true, on 또는 1) 비활성화됩니다(false, off 또는 0).

예:

EXEC sp_tableoption 'Department','pintable', 'true'

는 Department라는 데이터 테이블을 메모리에 상주하게 합니다.

EXEC sp_tableoption 'Department','pintable', 'false'는

Department가 메모리에 상주하는 데이터 테이블을 취소합니다. .

다음 SQL 명령을 사용하여 실행을 감지할 수 있습니다.

Select ObjectProperty(Object_ID('Department'),'TableIsPinned')

반환 결과가 1이면 테이블이 메모리에 상주하도록 설정되었음을 의미합니다. 메모리에 상주하도록 설정되어 있지 않습니다.

3. 결론

데이터 테이블을 메모리 상주로 설정하면 테이블을 검색할 때까지 테이블이 실제로 메모리로 읽혀지지 않습니다. 따라서 다음 SQL 명령을 사용하여 데이터 테이블 Department를 메모리에 추가로 저장할 수 있습니다.

Select * From Department

또한 다음 SQL 명령을 사용하여 데이터베이스의 메모리에 상주하도록 설정된 모든 테이블을 쉽게 표시/검색할 수 있습니다.

SELECT * FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned' ) > 0