검색 여닫기
메뉴 여닫기
34
0
9
1.8천
DB스터디
개인 메뉴 토글
로그인하지 않음
만약 지금 편집한다면 당신의 IP 주소가 공개될 수 있습니다.

SQL Server: 두 판 사이의 차이

DB스터디
다른 명령
2번째 줄: 2번째 줄:
== [[설치 방법]] ==
== [[설치 방법]] ==
== [[DB생성 및 접속]] ==
== [[DB생성 및 접속]] ==
== [[테이블/인덱스 생성]] ==
== [[세그먼트 (테이블+인덱스+LOB) 사이즈 조회 ]] ==
 
* 스키마별 테이블에 대해 데이터 + 인덱스 + LOB + Row Overflow를 한 번에 집계합니다.
 
=== 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>
 
== [[테이블/인덱스 사이즈 조회 ]] ==
== [[테이블/인덱스 사이즈 조회 ]] ==
== [[통계정보 갱신]] ==
== [[통계정보 갱신]] ==

2026년 9월 9일 (수) 22:08 판

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 과 비교표
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;

힌트 사용 방법

튜닝 대상 SQL 찾기

원본 주소 "https://dbstudy.co.kr/w/index.php?title=SQL_Server&oldid=1794"
마지막 수정
이 문서는 2026년 9월 9일 (수) 22:08에 마지막으로 편집되었습니다.
저작권
별도로 명시하지 않은 경우, 내용은 크리에이티브 커먼즈 저작자표시에 따라 사용할 수 있습니다.