SQL Server에서 T-SQL을 사용하여 테이블의 디스크 공간 사용량 확인하는 방법

데이터베이스를 관리할 때 각 테이블이 소비하고 있는 디스크 공간을 이해하는 것은 최적화 및 용량 계획에 매우 중요합니다. 많은 사용자들이 SQL Server Management Studio(SSMS) 그래픽 인터페이스를 통해 테이블 속성을 확인하는 데 익숙할 수 있지만, 같은 작업을 T-SQL을 사용하여 수행하는 방법을 아는 것은 특히 자동화 및 고급 데이터베이스 관리 작업에 매우 유용할 수 있습니다.

이번 블로그 포스트에서는 T-SQL을 통해 SQL Server에서 테이블이 차지하는 디스크 공간을 확인하는 간단한 방법을 살펴보겠습니다. 이 접근 방식은 효율성을 높이는 것뿐만 아니라 SQL 스크립팅에 대한 깊이 있는 이해를 제공할 수 있습니다.

솔루션 개요

우리는 임시 테이블과 sp_spaceused 시스템 저장 프로시저를 사용하여 데이터베이스의 다양한 테이블에 대한 정보를 수집할 것입니다. 우리가 달성할 사항은 다음과 같습니다:

  1. 크기 정보를 저장할 임시 테이블 생성.
  2. 모든 테이블에서 크기 데이터를 수집하기 위해 sp_spaceused 명령어 실행.
  3. 수집된 데이터를 읽기 쉬운 메트릭으로 파싱.
  4. 예약된 크기로 정렬된 출력을 표시.

시작해보겠습니다!

1단계: 임시 테이블 생성

첫 번째 단계는 크기 세부 정보를 저장하는 데 도움이 되는 두 개의 임시 테이블을 정의하는 것입니다. SQL Server에서 임시 테이블은 # 기호로 시작합니다.

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)   

설명:

  • #tmpSizeChar: 크기를 단위가 포함된 문자 형식으로 유지하는 결과를 보관합니다(예: MB, KB).
  • #tmpSizeInt: 정수 형식으로 결과를 저장하여 계산을 쉽게 합니다.

2단계: 테이블에서 크기 데이터 수집

우리는 sp_msforeachtable 저장 프로시저를 사용하여 데이터베이스의 모든 테이블에서 sp_spaceused 명령어를 실행합니다. 이 명령어는 각 테이블의 공간 사용에 대한 상세 정보를 제공합니다.

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

설명:

  • NOCOUNT ON: T-SQL 문이 영향을 미친 행의 수를 나타내는 메시지가 반환되는 것을 방지하여 여러 문이 포함된 스크립트의 성능을 향상시킵니다.

3단계: 수집된 데이터 파싱

데이터를 수집한 후에는 문자 데이터를 분석하기 위해 정수 값으로 변환해야 합니다.

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

설명:

  • 이 단계에서는 예약된 크기의 숫자 부분을 추출하고 신뢰할 수 있는 계산을 위해 정수로 변환합니다. 본질적으로 크기 문자열에서 문자(KB, MB 등)를 제거합니다.

4단계: 결과 표시

마지막으로, 임시 정수 크기 테이블에서 요약된 결과를 검색하고 표시합니다.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

설명:

  • 이 SQL 명령어는 #tmpSizeInt의 모든 레코드를 검색하고 reserved_size_KB 열에 따라 정렬하여 가장 많은 디스크 공간을 사용하는 테이블을 확인할 수 있게 합니다.

결론

위의 단계를 따르면 SQL Server에서 T-SQL로 테이블이 사용하는 디스크 공간을 확인할 수 있는 체계적인 방법을 갖게 됩니다. 이 방법은 데이터베이스 관리자가 최적의 성능을 유지하고 SQL Server 환경에서 리소스 사용량을 추적하려고 하는 개발자에게 특히 유용할 수 있습니다.

이제 GUI 클릭 대신 강력한 명령어 기반 접근 방식으로 데이터베이스 관리 기술을 기본적으로 향상시킬 수 있습니다!