편집 요약 없음 |
|||
| (같은 사용자의 중간 판 하나는 보이지 않습니다) | |||
| 405번째 줄: | 405번째 줄: | ||
=== 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 === | === 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 === | ||
* SQL Server Partition Function이 RANGE LEFT 인지 RANGE RIGHT 인지 확인 | |||
*:경계값의 의미가 달라집니다. | |||
<source lang=sql> | |||
CREATE PARTITION FUNCTION PF_DATE (date) | |||
AS RANGE RIGHT | |||
FOR VALUES | |||
( | |||
'2024-01-01', | |||
'2025-01-01', | |||
'2026-01-01' | |||
); | |||
</source> | |||
의미: | |||
<source> | |||
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 | |||
</source> | |||
* 한방쿼리 | |||
<source lang=sql> | |||
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; | |||
</source> | |||
==== 파티셔닝 테이블 조회 ==== | ==== 파티셔닝 테이블 조회 ==== | ||
* 파티션된 테이블 목록 + 테이블 파티션 사이즈 | * 파티션된 테이블 목록 + 테이블 파티션 사이즈 | ||
2026년 9월 9일 (수) 22:28 기준 최신판
세그먼트 (테이블+인덱스+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를 보는 것과 상당히 비슷한 느낌입니다.