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

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

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 같은 테이블은 인덱스 설계를 검토할 가치가 높습니다.