-- 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');

-----------구구단--------------

declare @a int, @dan int

set @dan = 2

while @dan < 10

begin

   set @a = 1

   while @a < 10

   begin

      print str(@dan) + ' x ' +str(@a) + ' = ' + str(@dan * @a)

      set @a = @a+1

   end

   print ''

   set @dan = @dan + 1

end 

set @a = 1

print '끝'

[참조] http://j.finfra.com/zbl/?document_srl=3545&mid=lecMsSqlLecdata&sort_index=readed_count&order_type=asc

의 내용을 약간 변경함

한 테이블이라면 

exec sp_spaceused '테이블명'

------------------------------------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[dba_SpaceUsed] 

    @SourceDB varchar ( 128 ) = null -- Optional database name

         -- If omitted, the current database is reported.

  , @SortBy char(1) = 'S' -- N for name, S for Size

           -- T for table name

/* Returns a table with the space used in all tables of the

*  database.  It's reported with the schema information unlike

*  the system procedure sp_spaceuse.

*

*  sp_spaceused is used to perform the calculations to ensure

*  that the numbers match what SQL Server would report.

*

*  Compatible with sQL Server 2000 and 2005

*

* Example:

exec dbo.dba_SpaceUsed null, 'N'

*

* © Copyright 2007 Andrew Novick http://www.NovickSoftware.com

* This software is provided as is without warrentee of any kind.

* You may use this procedure in any of your SQL Server databases

* including databases that you sell, so long as they contain 

* other unrelated database objects. You may not publish this 

* procedure either in print or electronically.

******************************************************************/

AS

SET NOCOUNT ON

DECLARE @sql nvarchar (4000)

IF @SourceDB IS NULL BEGIN

SET @SourceDB = DB_NAME() -- The current DB 

END

--------------------------------------------------------

-- Create and fill a list of the tables in the database.

CREATE TABLE #Tables ( [schema] sysname

                      , TabName sysname )

SELECT @sql = 'insert #tables ([schema], [TabName]) 

                  select TABLE_SCHEMA, TABLE_NAME 

         from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES

         where TABLE_TYPE = ''BASE TABLE'''

exec (@sql)

---------------------------------------------------------------

-- #TabSpaceTxt Holds the results of sp_spaceused. 

-- It Doesn't have Schema Info!

CREATE TABLE #TabSpaceTxt (

                         TabName sysname

                  , [Rows] varchar (11)

                  , Reserved varchar (18)

  , Data varchar (18)

                  , Index_Size varchar ( 18 )

                  , Unused varchar ( 18 )

                       )

---------------------------------------------------------------

-- The result table, with numeric results and Schema name.

CREATE TABLE #TabSpace ( [Schema] sysname

                       , TabName sysname

                  , [Rows] bigint

                  , ReservedMB numeric(18,3)

  , DataMB numeric(18,3)

                  , Index_SizeMB numeric(18,3)

                  , UnusedMB numeric(18,3)

                       )

DECLARE @Tab sysname -- table name

      , @Sch sysname -- owner,schema

DECLARE TableCursor CURSOR FOR

    SELECT [SCHEMA], TabNAME 

         FROM #tables

OPEN TableCursor;

FETCH TableCursor into @Sch, @Tab;

WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @sql = 'exec [' + @SourceDB 

  + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '

  + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';

Delete from #TabSpaceTxt; -- Stores 1 result at a time

EXEC (@sql);

    INSERT INTO #TabSpace

SELECT @Sch

    , [TabName]

         , convert(bigint, rows)

    , convert(numeric(18,3), convert(numeric(18,3), 

       left(reserved, len(reserved)-3)) / 1024.0) 

                ReservedMB

    , convert(numeric(18,3), convert(numeric(18,3), 

       left(data, len(data)-3)) / 1024.0) DataMB

    , convert(numeric(18,3), convert(numeric(18,3), 

       left(index_size, len(index_size)-3)) / 1024.0) 

                 Index_SizeMB

    , convert(numeric(18,3), convert(numeric(18,3), 

       left(unused, len([Unused])-3)) / 1024.0) 

                [UnusedMB]

        FROM #TabSpaceTxt;

FETCH TableCursor into @Sch, @Tab;

END;

CLOSE TableCursor;

DEALLOCATE TableCursor;

-----------------------------------------------------

-- Caller specifies sort, Default is size

IF @SortBy = 'N' -- Use Schema then Table Name

SELECT * FROM #TabSpace

  ORDER BY [Schema] asc, [TabName] asc

ELSE IF @SortBy = 'T'  -- Table name, then schema

SELECT * FROM #TabSpace

  ORDER BY [TabName] asc, [Schema] asc

ELSE  -- S, NULL, or whatever get's the default

SELECT * FROM #TabSpace

  ORDER BY ReservedMB desc

;

DROP TABLE #Tables

DROP TABLE #TabSpaceTxt

DROP TABLE #TabSpace

[참조]

http://sowhat.tistory.com/9

참고 사이트

http://msdn.microsoft.com/ko-kr/library/ms345146(SQL.90).aspx

-------------------------------------------------------------------------------------------------------------------------------

-- testDB 데이터베이스를 만든 후

USE testDB

go

-- 1) 파일그룹 생성

Alter database testDB add FILEGROUP fg1

Alter database testDB add FILEGROUP fg2

Alter database testDB add FILEGROUP fg3

Alter database testDB add FILEGROUP fg4

alter database testDB add file

(NAME = data1

 , filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test1.ndf'

 , size = 1mb

 , maxsize = 100mb

 , filegrowth=1mb) to filegroup fg1

alter database testDB add file

(NAME = data2

 , filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.ndf'

 , size = 1mb

 , maxsize = 100mb

 , filegrowth=1mb) to filegroup fg2

alter database testDB add file

(NAME = data3

 , filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test3.ndf'

 , size = 1mb

 , maxsize = 100mb

 , filegrowth=1mb) to filegroup fg3

alter database testDB add file

(NAME = data4

 , filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test4.ndf'

 , size = 1mb

 , maxsize = 100mb

 , filegrowth=1mb) to filegroup fg4

-- 2) PARTITION FUNCTION 생성

CREATE PARTITION FUNCTION CalenderPF(NVARCHAR(4))

AS RANGE LEFT FOR VALUES ('4월', '7월', '10월')

-- 3) PARTITION SCHEME 생성

CREATE PARTITION SCHEME CalenderPS

AS PARTITION CalenderPF

TO (fg1, fg2, fg3, fg4)

-- 4) 파티션 테이블 생성

CREATE TABLE TestCalender

(Seq INT,

Month NVARCHAR(4)) ON CalenderPS(Month)

INSERT INTO TestCalender VALUES (1, '2월')

INSERT INTO TestCalender VALUES (1, '4월')

INSERT INTO TestCalender VALUES (1, '6월')

INSERT INTO TestCalender VALUES (1, '8월')

INSERT INTO TestCalender VALUES (1, '12월')

SELECT Month, $partition.CalenderPF(Month) FROM TestCalender

SELECT * FROM TestCalender

WHERE $partition.CalenderPF(Month) = 2

[참조] http://wizcody.co.kr/43   <- 사이트의 내용을 바탕으로 테스트 해보았습니다.

/* 생성된 분할(파티션)테이블에서 추가적으로 파티션구성을 수정하는 내용 */

-- 1) 파일그룹 생성

Alter database testDB add FILEGROUP fg5

Alter database testDB add file

(NAME = data5

 , filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test5.ndf'

 , size = 1mb

 , maxsize = 100mb

 , filegrowth=1mb) to filegroup fg5

-- 2) PARTITION SCHEME 수정

ALTER PARTITION SCHEME CalenderPS

NEXT USED fg5;

-- 3) PARTITION FUNCTION 수정

ALTER PARTITION FUNCTION CalenderPF ()

SPLIT RANGE ('11월');

이렇게 하면 구성은 되지만 데이터상 '11월', '12월'이 내가 생각하는 결과값으로 보여지지 않는다. 그래서 '201201' 이렇게 연산가능한 데이터로 수정해서

다시 테스트 해야할 것 같다.

아래와 같이 다시 테스트 해 보았다.

-- 1. PARTITION FUNCTION

DROP PARTITION FUNCTION PF01; -- 삭제

CREATE PARTITION FUNCTION PF01(NVARCHAR(8))

AS RANGE LEFT FOR VALUES ('20120101', '20120301', '20120501', '20120701')

-- 2. PARTITION SCHEME(파티션 구성표)

DROP PARTITION SCHEME PS01; -- 삭제

CREATE PARTITION SCHEME PS01

AS PARTITION PF01

TO (fg1, fg2, fg3, fg4, fg5)

/* => 만약 PARTITION FUNCTION을 삭제 하고자 한다면 PARTITION SCHEME을 먼저 삭제하고 난 뒤 PARTITION FUNCTION 을 삭제해야함

그렇지 않으면 '하나 이상의 파티션 구성표에서 파티션 함수 'PF01'을(를) 사용하고 있습니다.' 와 같은 에러 출현 */

-- 3. 파티션 테이블 생성

CREATE TABLE dbo.PartionTable

(seq INT,

date NVARCHAR(8)) ON PS01(date)

-- 4. 테스트 데이터 입력

insert into dbo.PartionTable values (001, '20120101')

insert into dbo.PartionTable values (002, '20120102')

insert into dbo.PartionTable values (003, '20120103')

insert into dbo.PartionTable values (004, '20120105')

insert into dbo.PartionTable values (005, '20120203')

...

insert into dbo.PartionTable values (448, '20121009')

insert into dbo.PartionTable values (449, '20121009')

insert into dbo.PartionTable values (450, '20121009') -- 총 450건 입력

-- 5. 비교를 위해 파티션 테이블이 아닌 테스트용 테이블을 만든다.

create table PartionTable1

(

seq int,

date varchar(8)

)

그리고 똑같이 450 건 insert를 한다.

-- 6. 각각 INDEX를 생성한다.

create index ix01 on dbo.PartionTable(date)

create index ix01 on dbo.PartionTable1(date)

-- 7. 조회 테스트

SELECT * FROM dbo.PartionTable where date like '201202%' -- index를 탄다.

SELECT * FROM dbo.PartionTable1 where date like '201202%' -- Table Scan한다.