#title 인덱스 유지보수(재생성 or 조각모음)
[[TableOfContents]]

==== 2008 r2 도움말 ====
{{{
set nocount on;
set statistics io off
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 @command;
    end;

-- close and deallocate the cursor.
close partitions;
deallocate partitions;

-- drop the temporary table.
drop table #work_to_do;
go
}}}

==== 2008 ====
http://www.mssqltips.com/tip.asp?tip=1367
{{{
DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases   
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command 
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
           EXEC (@cmd) 
       END
       ELSE
       BEGIN
          -- SQL 2000 command 
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor 
}}}

==== 2005 ====
{{{
--인덱스 재생성 및 조각모음 대상
--인덱스 조각화 상태 15%이상
--페이지수 100 이상
-- 2005버전꺼..

set nocount on
set statistics io off
declare 
	@tablename varchar(255)
,	@schemaname varchar(255)
,	@fill_factor varchar(3);

set @fill_factor = '0';

if @fill_factor not between 1 and 100
	set @fill_factor = '100';

if @@version like 'Microsoft SQL Server 2005%' 
begin
	--2005버전
	declare tables cursor for
		SELECT 
			TABLE_SCHEMA
		,	TABLE_NAME
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_TYPE = 'BASE TABLE'

	create table #fraglist2 (
		database_id smallint NULL
	,	object_id int NULL
	,	index_id int NULL
	,	partition_number int NULL
	,	index_type_desc nvarchar(60) NULL
	,	alloc_unit_type_desc nvarchar(60) NULL
	,	index_depth tinyint NULL
	,	index_level tinyint NULL
	,	avg_fragmentation_in_percent float NULL
	,	fragment_count bigint NULL
	,	avg_fragment_size_in_pages float NULL
	,	page_count bigint NULL
	,	avg_page_space_used_in_percent float NULL
	,	record_count bigint NULL
	,	ghost_record_count bigint NULL
	,	version_ghost_record_count bigint NULL
	,	min_record_size_in_bytes int NULL
	,	max_record_size_in_bytes int NULL
	,	avg_record_size_in_bytes float NULL
	,	forwarded_record_count bigint NULL
	) 

	create table #rs2 (
		schema_name varchar(255)
	,	database_id smallint NULL
	,	object_id int NULL
	,	index_id int NULL
	,	partition_number int NULL
	,	index_type_desc nvarchar(60) NULL
	,	alloc_unit_type_desc nvarchar(60) NULL
	,	index_depth tinyint NULL
	,	index_level tinyint NULL
	,	avg_fragmentation_in_percent float NULL
	,	fragment_count bigint NULL
	,	avg_fragment_size_in_pages float NULL
	,	page_count bigint NULL
	,	avg_page_space_used_in_percent float NULL
	,	record_count bigint NULL
	,	ghost_record_count bigint NULL
	,	version_ghost_record_count bigint NULL
	,	min_record_size_in_bytes int NULL
	,	max_record_size_in_bytes int NULL
	,	avg_record_size_in_bytes float NULL
	,	forwarded_record_count bigint NULL
	)

	open tables
	fetch next from tables into @schemaname, @tablename

	while @@fetch_status = 0
	begin
		insert into #fraglist2 
		select *
		from sys.dm_db_index_physical_stats(db_id(), object_id(@schemaname + '.' + @tablename), null, null, null)

		insert into #rs2
		select @schemaname, * from #fraglist2
		
		truncate table #fraglist2
		fetch next from tables into @schemaname, @tablename
	end

	select
		테이블명
	,	인덱스명
	,	[논리 검색 조각화 상태]
	,	[검색한 페이지]
	,	case when 인덱스명 <> '' then '' else '--' end + [인덱스재생성 스크립트] [인덱스재생성 스크립트]
	,	case when 인덱스명 <> '' then '' else '--' end + [인덱스조각모음 스크립트] [인덱스조각모음 스크립트]
	from (
		select 
			schema_name + '.' + object_name(object_id) 테이블명
		,	(select name from sys.indexes where object_id = a.object_id and index_id = a.index_id) 인덱스명
		,	page_count [검색한 페이지]
		,	cast(avg_fragmentation_in_percent as numeric(18,2)) [논리 검색 조각화 상태]
		,	case when avg_fragmentation_in_percent > 15 and page_count > 100 then 'ALTER INDEX ' + b.index_name + ' ON ' + schema_name + '.' + object_name(object_id) + ' REBUILD WITH (FILLFACTOR = ' + @fill_factor + ');' else '' end [인덱스재생성 스크립트]
		,	case when avg_fragmentation_in_percent > 15 and page_count > 100 then 'ALTER INDEX ' + b.index_name + '  ON ' + schema_name + '.' + object_name(object_id) + ' REORGANIZE;' else '' end [인덱스조각모음 스크립트]
		from #rs2 a outer apply(
			select name index_name
			from sys.indexes where object_id = a.object_id and index_id = a.index_id
			) b
		where object_name(object_id) not like 'sys%'
		and schema_name <> 'sys'
		and alloc_unit_type_desc = 'IN_ROW_DATA'
	) t
	order by 1

	drop table #fraglist2
	drop table #rs2

	close tables
	deallocate tables
end else
	select @@version dbms_version, '2005버전이 아님' descriptions
go
}}}