테이블/인덱스 사이즈 조회

• Users 테이블의 용량 정보 조회

• sys.tables + sys.schemas + sys.dm_db_partition_stats 조합

사용자별(스키마별) 테이블 + 테이블 사이즈

• SQL Server에서는 Oracle과 달리 보통 User와 Schema를 구분해서 봅니다.
• 예를 들어:

USER      : APPUSER
SCHEMA    : dbo
TABLE     : ORDERS

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS DECIMAL(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS reserved_gb
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
--   AND s.name = 'APPUSER' -- APPUSER만 조회시 
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

결과는:

schema_name   table_name       row_count    reserved_mb   used_mb   unused_mb
------------  ---------------  -----------  ------------  --------  ----------
dbo           ORDER_HISTORY    125000000    18500.23      18120.10  380.13
dbo           ORDERS           35000000     6200.50       6100.25   100.25
hr            EMPLOYEE         2500000      850.75        820.12    30.63
sales         CUSTOMER         1200000      420.50        400.21    20.29

• DBA 용

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(18,2))
        AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS DECIMAL(18,2))
        AS used_mb,
    CAST(
        (SUM(ps.reserved_page_count) - SUM(ps.used_page_count))
        * 8.0 / 1024
        AS DECIMAL(18,2)
    ) AS unused_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
ORDER BY
    reserved_mb DESC;

인덱스 사이즈 조회

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    SUM(ps.row_count) AS row_count,
    SUM(ps.reserved_page_count) * 8.0 / 1024 AS reserved_mb,
    SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb
FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.indexes i
    ON t.object_id = i.object_id
JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id
   AND i.index_id = ps.index_id
WHERE t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name,
    i.name,
    i.type_desc
ORDER BY
    reserved_mb DESC;

• 인덱스 정보 조회

EXEC sp_helpindex Users;

SCHEMA   TABLE       INDEX                TYPE          SIZE
-------  ----------  -------------------  ------------  ------
dbo      ORDERS      PK_ORDERS            CLUSTERED     2500 MB
dbo      ORDERS      IX_ORDERS_CUSTOMER   NONCLUSTERED  1800 MB
dbo      ORDERS      IX_ORDERS_DATE       NONCLUSTERED  1200 MB
dbo      ORDERS      IX_ORDERS_STATUS     NONCLUSTERED   900 MB