세그먼트 (테이블+인덱스+LOB) 사이즈 조회

세그먼트 (테이블+인덱스+LOB) 사이즈 조회

• SQL Server DB 용량 분석에서는 reserved와 used를 구분
  • reserved_mb 는 SQL Server가 할당해 놓은 공간이고,
  • used_mb 는 실제 사용 중인 공간입니다.

DBA_SEGMENTS 스타일의 테이블 용량 요약

SET NOCOUNT ON;

WITH T AS
(
    SELECT
        ps.object_id,

        /* TABLE DATA
           Heap 또는 Clustered Index
           index_id = 0 : Heap
           index_id = 1 : Clustered Index
        */
        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                THEN ps.reserved_page_count
                ELSE 0
            END
        ) AS table_reserved_pages,

        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                THEN ps.used_page_count
                ELSE 0
            END
        ) AS table_used_pages,

        /* NONCLUSTERED INDEX */
        SUM(
            CASE
                WHEN ps.index_id > 1
                THEN ps.reserved_page_count
                ELSE 0
            END
        ) AS index_reserved_pages,

        SUM(
            CASE
                WHEN ps.index_id > 1
                THEN ps.used_page_count
                ELSE 0
            END
        ) AS index_used_pages,

        /* LOB */
        SUM(ps.lob_reserved_page_count) AS lob_reserved_pages,
        SUM(ps.lob_used_page_count)     AS lob_used_pages,

        /* ROW OVERFLOW */
        SUM(ps.row_overflow_reserved_page_count)
            AS row_overflow_reserved_pages,

        SUM(ps.row_overflow_used_page_count)
            AS row_overflow_used_pages,

        /* ROW COUNT */
        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                THEN ps.row_count
                ELSE 0
            END
        ) AS row_count

    FROM sys.dm_db_partition_stats ps
    GROUP BY
        ps.object_id
)
SELECT
    sch.name AS schema_name,
    tbl.name AS table_name,

    T.row_count,

    /* TABLE */
    CAST(T.table_reserved_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS table_reserved_mb,

    CAST(T.table_used_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS table_used_mb,

    /* INDEX */
    CAST(T.index_reserved_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS index_reserved_mb,

    CAST(T.index_used_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS index_used_mb,

    /* LOB */
    CAST(T.lob_reserved_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS lob_reserved_mb,

    CAST(T.lob_used_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS lob_used_mb,

    /* ROW OVERFLOW */
    CAST(T.row_overflow_reserved_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS row_overflow_reserved_mb,

    CAST(T.row_overflow_used_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS row_overflow_used_mb,

    /* TOTAL RESERVED */
    CAST(
          (
              T.table_reserved_pages
            + T.index_reserved_pages
            + T.lob_reserved_pages
            + T.row_overflow_reserved_pages
          ) * 8.0 / 1024
         AS DECIMAL(18,2)
    ) AS total_reserved_mb,

    /* TOTAL USED */
    CAST(
          (
              T.table_used_pages
            + T.index_used_pages
            + T.lob_used_pages
            + T.row_overflow_used_pages
          ) * 8.0 / 1024
         AS DECIMAL(18,2)
    ) AS total_used_mb,

    /* GB */
    CAST(
          (
              T.table_reserved_pages
            + T.index_reserved_pages
            + T.lob_reserved_pages
            + T.row_overflow_reserved_pages
          ) * 8.0 / 1024 / 1024
         AS DECIMAL(18,2)
    ) AS total_reserved_gb

FROM T
JOIN sys.tables tbl
    ON T.object_id = tbl.object_id
JOIN sys.schemas sch
    ON tbl.schema_id = sch.schema_id

WHERE tbl.is_ms_shipped = 0

ORDER BY
    total_reserved_mb DESC;

• 결과는

schema_name table_name      row_count    table_mb  index_mb  lob_mb  overflow_mb  total_mb
----------- --------------- ------------ --------- --------- ------- ------------ ---------
dbo         ORDERS          35,000,000   4200.50   1850.20   0.00    0.00         6050.70
dbo         CUSTOMER        8,500,000     850.20    620.30   0.00    0.00         1470.50
app         DOCUMENT        1,200,000     300.10    150.50   850.20  10.30        1311.10
app         LOG_DATA        50,000,000    980.20    300.50   0.00    450.80       1731.50

TABLE       300 MB
INDEX       150 MB
LOB         850 MB
ROW_OVERFLOW 10 MB
-------------------
TOTAL      1311 MB

DBA용

• 타입까지 표시

WITH S AS
(
    SELECT
        ps.object_id,
        ps.index_id,

        SUM(ps.reserved_page_count) AS reserved_pages,
        SUM(ps.used_page_count)     AS used_pages,

        SUM(ps.lob_reserved_page_count)
            AS lob_reserved_pages,

        SUM(ps.lob_used_page_count)
            AS lob_used_pages,

        SUM(ps.row_overflow_reserved_page_count)
            AS row_overflow_reserved_pages,

        SUM(ps.row_overflow_used_page_count)
            AS row_overflow_used_pages,

        SUM(ps.row_count) AS row_count

    FROM sys.dm_db_partition_stats ps
    GROUP BY
        ps.object_id,
        ps.index_id
)
SELECT
    sch.name AS schema_name,
    tbl.name AS table_name,

    CASE
        WHEN S.index_id = 0
            THEN 'TABLE_HEAP'

        WHEN S.index_id = 1
            THEN 'TABLE_CLUSTERED'

        ELSE 'INDEX'
    END AS segment_type,

    CASE
        WHEN S.index_id = 0
            THEN '(HEAP)'

        WHEN S.index_id = 1
            THEN ix.name

        ELSE ix.name
    END AS segment_name,

    S.row_count,

    CAST(
        S.reserved_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS reserved_mb,

    CAST(
        S.used_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS used_mb,

    CAST(
        S.reserved_pages * 8.0 / 1024 / 1024
        AS DECIMAL(18,2)
    ) AS reserved_gb,

    CAST(
        S.lob_reserved_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS lob_reserved_mb,

    CAST(
        S.row_overflow_reserved_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS row_overflow_reserved_mb

FROM S
JOIN sys.tables tbl
    ON S.object_id = tbl.object_id
JOIN sys.schemas sch
    ON tbl.schema_id = sch.schema_id
LEFT JOIN sys.indexes ix
    ON S.object_id = ix.object_id
   AND S.index_id = ix.index_id

WHERE tbl.is_ms_shipped = 0

ORDER BY
    reserved_mb DESC;

• 중요한 점은 LOB는 일반 Index ID와 별도의 allocation unit으로 저장될 수 있기 때문에, 위의 lob_reserved_mb 컬럼으로 별도로 확인하는 방식이 유용

LOB가 많은 테이블을 찾는 쿼리

SQL Server에서는 Oracle의 CLOB, BLOB 등에 해당하는 데이터가 있는 테이블을 찾는 일이 꽤 중요합니다.

특히 다음과 같은 컬럼이 있으면:

varchar(max)
nvarchar(max)
varbinary(max)
xml

LOB/LOB-like storage가 커질 수 있습니다.

• LOB 용량 기준으로 Top 20 :

SELECT TOP 20
    s.name AS schema_name,
    t.name AS table_name,

    SUM(ps.lob_reserved_page_count) * 8.0 / 1024
        AS lob_reserved_mb,

    SUM(ps.lob_used_page_count) * 8.0 / 1024
        AS lob_used_mb

FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.dm_db_partition_stats ps
    ON t.object_id = ps.object_id

WHERE t.is_ms_shipped = 0

GROUP BY
    s.name,
    t.name

HAVING
    SUM(ps.lob_reserved_page_count) > 0

ORDER BY
    lob_reserved_mb DESC;

• 예를 들어:

schema  table        lob_reserved_mb
------  -----------  ---------------
app     DOCUMENT     85000
app     ATTACHMENT   62000
dbo     MAIL         18000

인덱스가 너무 큰 테이블 찾기

• 테이블보다 인덱스가 더 큰 경우가 자주 있습니다.
• 예:

TABLE DATA       1 GB
INDEX           8 GB

이라면 인덱스 설계를 한번 검토해볼 필요가 있습니다.

table_used_mb 와 index_used_mb

를 비교하면 됩니다.

• 직접적으로 찾으려면:

WITH X AS
(
    SELECT
        ps.object_id,

        SUM(
            CASE
                WHEN ps.index_id IN (0,1)
                THEN ps.used_page_count
                ELSE 0
            END
        ) AS table_pages,

        SUM(
            CASE
                WHEN ps.index_id > 1
                THEN ps.used_page_count
                ELSE 0
            END
        ) AS index_pages

    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,

    CAST(X.table_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS table_used_mb,

    CAST(X.index_pages * 8.0 / 1024
         AS DECIMAL(18,2)) AS index_used_mb,

    CAST(
        X.index_pages * 1.0
        / NULLIF(X.table_pages, 0)
        AS DECIMAL(18,2)
    ) AS index_table_ratio

FROM X
JOIN sys.tables t
    ON X.object_id = t.object_id
JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE t.is_ms_shipped = 0

ORDER BY
    index_table_ratio DESC;

• 예:

TABLE       TABLE_MB   INDEX_MB   INDEX/TABLE
----------  ---------  ---------  ------------
ORDERS      1000       4500       4.50
CUSTOMER     800       1800       2.25
PRODUCT     1200       1500       1.25

ORDERS 같은 테이블은 인덱스 설계를 검토할 가치가 높습니다.

스키마별 파티셔닝 테이블 + 파티션 + 사이즈

• SQL Server Partition Function이 RANGE LEFT 인지 RANGE RIGHT 인지 확인

  경계값의 의미가 달라집니다.

CREATE PARTITION FUNCTION PF_DATE (date)
AS RANGE RIGHT
FOR VALUES
(
    '2024-01-01',
    '2025-01-01',
    '2026-01-01'
);

의미:

Partition 1 : <  2024-01-01
Partition 2 : >= 2024-01-01 AND < 2025-01-01
Partition 3 : >= 2025-01-01 AND < 2026-01-01
Partition 4 : >= 2026-01-01

• 한방쿼리

WITH P AS
(
    SELECT
        p.object_id,
        p.index_id,
        p.partition_number,

        SUM(dps.row_count) AS row_count,

        SUM(dps.reserved_page_count) AS reserved_pages,
        SUM(dps.used_page_count) AS used_pages,
        SUM(dps.lob_reserved_page_count) AS lob_reserved_pages,
        SUM(dps.lob_used_page_count) AS lob_used_pages

    FROM sys.partitions p

    JOIN sys.dm_db_partition_stats dps
        ON p.object_id = dps.object_id
       AND p.index_id = dps.index_id
       AND p.partition_number = dps.partition_number

    GROUP BY
        p.object_id,
        p.index_id,
        p.partition_number
)
SELECT
    sch.name AS schema_name,
    t.name AS table_name,

    CASE
        WHEN i.index_id = 0 THEN '(HEAP)'
        ELSE i.name
    END AS index_name,

    CASE
        WHEN i.index_id = 0 THEN 'HEAP'
        ELSE i.type_desc
    END AS index_type,

    ps.name AS partition_scheme,
    pf.name AS partition_function,

    P.partition_number,

    /* Boundary */
    prv.value AS boundary_value,

    P.row_count,

    CAST(
        P.reserved_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS reserved_mb,

    CAST(
        P.used_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS used_mb,

    CAST(
        P.lob_reserved_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS lob_reserved_mb,

    CAST(
        P.lob_used_pages * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS lob_used_mb

FROM P

JOIN sys.tables t
    ON P.object_id = t.object_id

JOIN sys.schemas sch
    ON t.schema_id = sch.schema_id

JOIN sys.indexes i
    ON P.object_id = i.object_id
   AND P.index_id = i.index_id

JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf
    ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values prv
    ON pf.function_id = prv.function_id
   AND P.partition_number = prv.boundary_id

WHERE
    t.is_ms_shipped = 0

ORDER BY
    sch.name,
    t.name,
    i.index_id,
    P.partition_number;

파티셔닝 테이블 조회

• 파티션된 테이블 목록 + 테이블 파티션 사이즈
• 아래 쿼리는 클러스터드 인덱스 또는 Heap 기준으로 테이블 데이터 파티션을 보여줍니다.

SELECT
    sch.name AS schema_name,
    t.name AS table_name,

    ps.name AS partition_scheme,
    pf.name AS partition_function,

    p.partition_number,

    CAST(
        SUM(dps.row_count)
        AS BIGINT
    ) AS row_count,

    CAST(
        SUM(dps.reserved_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS reserved_mb,

    CAST(
        SUM(dps.used_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS used_mb,

    CAST(
        SUM(dps.lob_reserved_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS lob_reserved_mb

FROM sys.tables t
JOIN sys.schemas sch
    ON t.schema_id = sch.schema_id

JOIN sys.indexes i
    ON t.object_id = i.object_id
   AND i.index_id IN (0, 1)
JOIN sys.partitions p
    ON i.object_id = p.object_id
   AND i.index_id = p.index_id
JOIN sys.dm_db_partition_stats dps
    ON p.object_id = dps.object_id
   AND p.index_id = dps.index_id
   AND p.partition_number = dps.partition_number

JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf
    ON ps.function_id = pf.function_id

WHERE
    t.is_ms_shipped = 0

GROUP BY
    sch.name,
    t.name,
    ps.name,
    pf.name,
    p.partition_number

ORDER BY
    sch.name,
    t.name,
    p.partition_number;

여기서:

i.index_id IN (0,1)

이 부분이 중요합니다.

0 = Heap
1 = Clustered Index

즉 테이블 데이터를 대표하는 저장 구조만 가져옵니다.

==== 파티셔닝 인덱스 목록 ====

* 파티션 인덱스 목록 + 사이즈

<source lang=sql>
SELECT
    sch.name AS schema_name,
    t.name AS table_name,

    i.name AS index_name,
    i.index_id,
    i.type_desc AS index_type,

    ps.name AS partition_scheme,
    pf.name AS partition_function,

    p.partition_number,

    CAST(
        SUM(dps.row_count)
        AS BIGINT
    ) AS row_count,

    CAST(
        SUM(dps.reserved_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS reserved_mb,

    CAST(
        SUM(dps.used_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS used_mb,

    CAST(
        SUM(dps.lob_reserved_page_count) * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS lob_reserved_mb

FROM sys.tables t

JOIN sys.schemas sch
    ON t.schema_id = sch.schema_id

JOIN sys.indexes i
    ON t.object_id = i.object_id

JOIN sys.partitions p
    ON i.object_id = p.object_id
   AND i.index_id = p.index_id

JOIN sys.dm_db_partition_stats dps
    ON p.object_id = dps.object_id
   AND p.index_id = dps.index_id
   AND p.partition_number = dps.partition_number

JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf
    ON ps.function_id = pf.function_id

WHERE
    t.is_ms_shipped = 0
    AND i.index_id > 0

GROUP BY
    sch.name,
    t.name,
    i.name,
    i.index_id,
    i.type_desc,
    ps.name,
    pf.name,
    p.partition_number

ORDER BY
    sch.name,
    t.name,
    i.name,
    p.partition_number;

결과:

SCHEMA TABLE   INDEX_NAME              INDEX_TYPE       PARTITION  ROW_COUNT    MB
------ ------- ----------------------  ---------------  ---------  ----------  -------
APP    SALES   PK_SALES               CLUSTERED               1    2,000,000   490
APP    SALES   PK_SALES               CLUSTERED               2    3,000,000   710
APP    SALES   PK_SALES               CLUSTERED               3    3,500,000   820

APP    SALES   IX_SALES_CUSTOMER      NONCLUSTERED            1    2,000,000   120
APP    SALES   IX_SALES_CUSTOMER      NONCLUSTERED            2    3,000,000   190
APP    SALES   IX_SALES_CUSTOMER      NONCLUSTERED            3    3,500,000   210

이 형태가 Oracle DBA_IND_PARTITIONS를 보는 것과 상당히 비슷한 느낌입니다.