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

Dbstudy: /* 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 */

2026-09-09T13:28:21Z

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

@@ 405번째 줄: / 405번째 줄: @@
 === 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 ===
 * 한방쿼리
 <source lang=sql>
 WITH P AS

Dbstudy: /* 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 */

2026-09-09T13:24:44Z

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

@@ 405번째 줄: / 405번째 줄: @@
 === 스키마별 파티셔닝 테이블 + 파티션 + 사이즈 ===
 ==== 파티셔닝 테이블 조회 ====
 * 파티션된 테이블 목록 + 테이블 파티션 사이즈

2026년 9월 9일 (수) 13:23에 Dbstudy님의 편집

2026-09-09T13:23:29Z

차이 보기

2026년 9월 9일 (수) 13:17에 Dbstudy님의 편집

2026-09-09T13:17:18Z

← 이전 판		2026년 9월 9일 (수) 22:17 판
1번째 줄:		1번째 줄:
	== 세그먼트 (테이블+인덱스+LOB) 사이즈 조회 ==		== 세그먼트 (테이블+인덱스+LOB) 사이즈 조회 ==
			* SQL Server DB 용량 분석에서는 reserved와 used를 구분
			** reserved_mb 는 SQL Server가 할당해 놓은 공간이고,
			** used_mb 는 실제 사용 중인 공간입니다.

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

2026년 9월 9일 (수) 13:15에 Dbstudy님의 편집

2026-09-09T13:15:50Z

← 이전 판		2026년 9월 9일 (수) 22:15 판
322번째 줄:		322번째 줄:
	dbo MAIL 18000		dbo MAIL 18000
	</source>		</source>


			=== 인덱스가 너무 큰 테이블 찾기 ===
			* 테이블보다 인덱스가 더 큰 경우가 자주 있습니다.
			* 예:
			<source lang=sql>
			TABLE DATA 1 GB
			INDEX 8 GB
			</source>

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

			table_used_mb 와 index_used_mb

			를 비교하면 됩니다.

			* 직접적으로 찾으려면:
			<source lang=sql>
			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;
			</source>
			*예:
			<source>
			TABLE TABLE_MB INDEX_MB INDEX/TABLE
			---------- --------- --------- ------------
			ORDERS 1000 4500 4.50
			CUSTOMER 800 1800 2.25
			PRODUCT 1200 1500 1.25
			</source>
			ORDERS 같은 테이블은 인덱스 설계를 검토할 가치가 높습니다.

Dbstudy: 새 문서: == 세그먼트 (테이블+인덱스+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 ELS...

2026-09-09T13:13:01Z

새 문서: == 세그먼트 (테이블+인덱스+LOB) 사이즈 조회 == === DBA_SEGMENTS 스타일의 테이블 용량 요약 === <source lang=sql> 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 ELS...

새 문서

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

=== DBA_SEGMENTS 스타일의 테이블 용량 요약 ===
<source lang=sql>
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;
</source>
*결과는
<source>
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
</source>
<source>
TABLE 300 MB
INDEX 150 MB
LOB 850 MB
ROW_OVERFLOW 10 MB
-------------------
TOTAL 1311 MB
</source>

=== DBA용 ===
* 타입까지 표시

<source lang=sql>
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;
</source>

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

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

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

특히 다음과 같은 컬럼이 있으면:
<source lang=sql>
varchar(max)
nvarchar(max)
varbinary(max)
xml

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

* LOB 용량 기준으로 Top 20 :
<source lang=sql>
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;

</source>
*예를 들어:
<source lang=sql>
schema table lob_reserved_mb
------ ----------- ---------------
app DOCUMENT 85000
app ATTACHMENT 62000
dbo MAIL 18000
</source>