-- sys.dm_db_index_physical_stats(Transact-SQL)

-- http://technet.microsoft.com/ko-kr/library/ms188917.aspx

-- 1.지정한 테이블에 대한 정보 반환

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'testDB');

SET @object_id = OBJECT_ID(N'testDB.dbo.t3');

IF @db_id IS NULL

BEGIN;

    PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

    PRINT N'Invalid object';

END;

ELSE

BEGIN;

    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

END;

GO

-- 2.모든 데이터베이스에 대한 정보 반환

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

GO

-- 3.스크립트에 sys.dm_db_index_physical_stats를 사용하여 인덱스를 다시 작성하거나 다시 구성

--   다음 예에서는 데이터베이스에서 평균 조각화가 10%를 넘는 모든 파티션을 자동으로 다시 구성하거나 다시 작성합니다. 이 쿼리를 실행하려면 VIEW DATABASE STATE 권한이 필요합니다

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130); 

DECLARE @objectname nvarchar(130); 

DECLARE @indexname nvarchar(130); 

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000); 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 

-- and convert object and index IDs to names.

SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

    BEGIN;

        FETCH NEXT

           FROM partitions

           INTO @objectid, @indexid, @partitionnum, @frag;

        IF @@FETCH_STATUS < 0 BREAK;

        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

        FROM sys.objects AS o

        JOIN sys.schemas as s ON s.schema_id = o.schema_id

        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)

        FROM sys.indexes

        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)

        FROM sys.partitions

        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

        IF @frag < 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

        IF @frag >= 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

        IF @partitioncount > 1

            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

-- 4. sys.dm_db_index_physical_stats를 사용하여 페이지 대 압축된 페이지 수 표시

SELECT o.name,

    ips.partition_number,

    ips.index_type_desc,

    ips.record_count, ips.avg_record_size_in_bytes,

    ips.min_record_size_in_bytes,

    ips.max_record_size_in_bytes,

    ips.page_count, ips.compressed_page_count

FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips

JOIN sys.objects o on o.object_id = ips.object_id

ORDER BY record_count DESC;

-- 5.SAMPLED 모드에서 sys.dm_db_index_physical_stats 사용

--   다음 예에서는 SAMPLED 모드가 DETAILED 모드 결과와 다른 대략적인 값을 어떻게 반환하는지를 보여 줍니다.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);

GO

BEGIN TRAN

DECLARE @idx int = 0;

WHILE @idx < 1000000

BEGIN

    INSERT INTO t3 (col1, col2) 

    VALUES (@idx, 

    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))

    SET @idx = @idx + 1

END

COMMIT;

GO

SELECT page_count, compressed_page_count, forwarded_record_count, * 

FROM sys.dm_db_index_physical_stats (db_id(), 

    object_id ('t3'), null, null, 'SAMPLED');

SELECT page_count, compressed_page_count, forwarded_record_count, * 

FROM sys.dm_db_index_physical_stats (db_id(), 

    object_id ('t3'), null, null, 'DETAILED');