SQL SERVER (Microsoft SQL Server)
설치 방법
DB생성 및 접속
세그먼트 (테이블+인덱스+LOB) 사이즈 조회
- 스키마별 테이블에 대해 데이터 + 인덱스 + LOB + Row Overflow를 한 번에 집계합니다.
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
테이블/인덱스 사이즈 조회
통계정보 갱신
실행 계획
- SSMS에서 쿼리 → 실제 실행 계획 포함 켜기. (Ctrl + M)
SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT * FROM dbo.EMP WHERE EMPNO = 1000;
- 실행하면 결과 창 외에 실행 계획(Execution Plan) 탭이 나타납니다.
SELECT
└─ Hash Match
├─ Index Scan
└─ Index Scan
- Index Seek => oracle의 index range/unique scan
- Actual Plan 에서 실제 row 확인하기
- SQL Server에서도 Cardinality Estimation 문제가 있는지 보는 것이 중요.
Estimated Number of Rows = 10 Actual Number of Rows = 1,500,000
- SSMS 실행계획에서 우클릭하여: Save Execution Plan As... > .sqlplan파일로 저장
- oracle DBMS_XPLAN 과 비슷
<ShowPlanXML ...>
...
</ShowPlanXML>
I/O를 더 자세하게 확인
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
O.AMOUNT
FROM dbo.ORDERS O
WHERE O.CUSTOMER_ID = 100
AND O.ORDER_DATE >= '2026-01-01';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Table 'ORDERS'. Scan count 1, logical reads 23500, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 850 ms, elapsed time = 920 ms.
SET STATISTICS IO에서 특히 중요한 항목
Table 'ORDERS'. Scan count 1, logical reads 125000, physical reads 0, read-ahead reads 120000, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Scan count
- 테이블/인덱스에 대한 Scan 횟수입니다.
- Nested Loop와 같이 반복 접근이 발생하면 눈여겨봐야 합니다.
- logical reads
- -메모리에 있는 데이터 페이지를 읽은 횟수입니다.
- physical reads
- 디스크에서 실제 페이지를 읽은 횟수입니다.
- SQL Server는 데이터가 버퍼 캐시에 올라가 있을 경우:
- physical reads = 0 이어도 logical reads = 1000000 일 수 있습니다.
실제 튜닝 템플릿
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 대상 SQL
SELECT
O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
O.AMOUNT
FROM dbo.ORDERS O
WHERE O.CUSTOMER_ID = 100
AND O.ORDER_DATE >= '2026-01-01';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Oracle 과 비교표
| Oracle | SQL Server |
|---|---|
| A-Rows | Actual Rows |
| E-Rows | Estimated Rows |
| Buffers | logical reads |
| Physical Reads | physical reads |
| Execution Time | CPU / elapsed |
| TABLE ACCESS FULL | Table Scan |
| INDEX RANGE SCAN | Index Seek |
| INDEX FULL SCAN | Index Scan |
| HASH JOIN | Hash Match |
| NESTED LOOPS | Nested Loops |
| SORT | Sort |
Query Store
실행 횟수 CPU Time Duration Logical Reads Physical Reads Memory Plan 변경
SELECT TOP 20
qt.query_sql_text,
rs.execution_count,
rs.avg_duration,
rs.avg_cpu_time,
rs.avg_logical_io_reads
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
ORDER BY rs.avg_logical_io_reads DESC;