dbatools. Часть 3. Обслуживание индексов

добавлено: 21 дек 11
понравилось:0
просмотров: 3698
комментов: 4

теги:

Автор: gds

Третью часть серии dbatools я бы хотел посвятить обслуживанию индексов. На мой взляд, это одна из наиболее важных тем, и достаточно интересная. Для тех, кто мало знаком с индексами и их структурой, рекомендую сперва почитать статью Индексы. Теоретические основы.
Много предложений в интернете и в msdb как обслудивать индексы и вот решил поделиться своими наработками. Не будем много описывать, а давайте лучше перейдет к рассмотрению скрипта.

+ Script
-- Создаем схему для DBA
--------------------------
if schema_id('dbatools') is null
exec sp_sqlexec 'create schema dbatools'
go

-- Табличка с системными параметрами
----------------------------------------
if OBJECT_ID(N'dbatools.sys_params',N'U') is not null
	drop table dbatools.sys_params;
go
CREATE TABLE dbatools.sys_params(
		[param] [varchar](512) NOT NULL,
		[value_char] [varchar](128),
		[value_int] int,
Constraint PK_sys_params Primary key (param)
	) 
GO

--Реализация Sequence 
----------------------
if OBJECT_ID(N'dbatools.sequence',N'U') is not null
	drop table dbatools.sequence
go
create table dbatools.sequence(
sequence_name sysname not null,
sequence_start bigint not null,
sequence_step bigint not null,
sequence_current_value bigint not null,
constraint PK_sequence_1 primary key (sequence_name)
)
go
grant select on dbatools.sequence to public;
go

if OBJECT_ID(N'dbatools.sp_add_sequence','P') is not null
	drop procedure dbatools.sp_add_sequence
go
create procedure dbatools.sp_add_sequence(
	@sequence_name sysname,
	@sequence_start bigint = 1,
	@sequence_step bigint = 1
)
as
begin
 insert into dbatools.sequence (sequence_name,sequence_start,sequence_step,sequence_current_value)
 values (@sequence_name,@sequence_start,@sequence_step,@sequence_start-@sequence_step);
end;
go

if OBJECT_ID(N'dbatools.sp_drop_sequence',N'P') is not null
	drop procedure dbatools.sp_drop_sequence
go
create procedure dbatools.sp_drop_sequence(
	@sequence_name sysname
)
as
begin
 delete from dbatools.sequence 
	where sequence_name = @sequence_name;
end;
go

---- процедура для получения следующего значения 2008/2008R2

-- Создание линка
if exists(select * from sys.servers where name = '_LOOPBACK_' and is_linked = 1)
 exec sp_dropserver @server = '_LOOPBACK_' 
 GO
 
declare @s nvarchar(128)
set @s = @@SERVERNAME
-- Непосредственно создание сервера
exec sp_addlinkedserver   
   @server='_LOOPBACK_', 
   @srvproduct='',
   @provider='SQLNCLI10', 
   @datasrc=@s
-- По умолчанию будет передаваться текущий контекст безопасности
exec sp_addlinkedsrvlogin @rmtsrvname=N'_LOOPBACK_',@useself=N'True'

-- Включение доступа к данным и возможности запуска процедур
exec sp_serveroption '_LOOPBACK_', 'DATA ACCESS', 'true'
exec sp_serveroption '_LOOPBACK_', 'RPC', 'true'
exec sp_serveroption '_LOOPBACK_', 'RPC Out', 'true'
-- Отключение передачи контекста текущей транзакции
exec sp_serveroption '_LOOPBACK_', 'remote proc transaction promotion', 'false'
GO

-- Внутренная процедура получения следующего значения из последовательности
if OBJECT_ID(N'dbatools.sp_next_value_sequence_internal',N'P') is not null
	drop procedure dbatools.sp_next_value_sequence_internal
go
create procedure [dbatools].[sp_next_value_sequence_internal](
	@sequence_name sysname,
	@new_value bigint output 	
	)
as
begin
update [dbatools].[sequence]
set @new_value = sequence_current_value = sequence_current_value + sequence_step 
						  where sequence_name = @sequence_name		  
end;
go

grant execute on dbatools.sp_next_value_sequence_internal to public;
go

-- Внешняя процедура получения следующего значения из последовательности
if OBJECT_ID(N'dbatools.sp_next_value_sequence',N'P') is not null
	drop procedure dbatools.sp_next_value_sequence
go

create procedure [dbatools].[sp_next_value_sequence](
	@sequence_name sysname,
	@new_value bigint output 	
	)
with execute as caller
as
declare @sql_run nvarchar(1000)		  
begin
 select @sql_run = N'[_LOOPBACK_].'+quotename(db_name())+N'.[dbatools].[sp_next_value_sequence_internal]'
 exec @sql_run @sequence_name = @sequence_name,@new_value = @new_value output
end;
go

grant execute on dbatools.sp_next_value_sequence to public;
go

---- процедура для получения следующего значения 2005 (старая версия)
/*
if OBJECT_ID(N'dbatools.sp_next_value_sequence',N'P') is not null
	drop procedure dbatools.sp_next_value_sequence
go

create procedure [dbatools].[sp_next_value_sequence](
	@sequence_name sysname,
	@new_value bigint output 	
	)
as
begin
update [dbatools].[sequence]
set @new_value = sequence_current_value = sequence_current_value + sequence_step 
						  where sequence_name = @sequence_name
end;
go

grant execute on dbatools.sp_next_value_sequence to public;
go
*/

-- таблица для отчетов по обслуживанию
---------------------------------------
if OBJECT_ID(N'dbatools.rep_errors',N'U') is not null
	drop table dbatools.rep_errors
go
if OBJECT_ID(N'dbatools.rep_service_index',N'U') is not null
	drop table dbatools.rep_service_index
go
create table dbatools.rep_service_index(
record_id bigint not null identity(1,1) unique,
table_name nvarchar(257)not null,
idx_name nvarchar(128) not null,
start_time datetime not null,
end_time datetime,
oper_type smallint,
oper_type_desc as case oper_type
when 0 then 'defrag'
when 1 then 'rebuild'
when 2 then 'update stat'
when 10 then 'smart defrag'
when 11 then 'smart rebuild'
end,
oper_num bigint not null default 0,
fragmentation_in_percent decimal(5,2) not null default 0.00,
page_count bigint not null default 0,
part_numb int not null  default 0,
[description] nvarchar(300) null
)
go
CREATE INDEX [ix_service_index_1] ON [dbatools].[rep_service_index] 
(
	[oper_num] ASC
	
) include ([start_time],[end_time])
GO
create index [ix_service_index_2] on [dbatools].[rep_service_index] 
(
[oper_type] ASC
)include ([oper_num])
GO	
create index [ix_service_index_3] on [dbatools].[rep_service_index](start_time desc)
include(table_name,idx_name,part_numb,fragmentation_in_percent,page_count)
go
exec dbatools.sp_add_sequence
	@sequence_name = N'service_index',
	@sequence_start = 1,
	@sequence_step =1
GO
-- errors (report)
create table dbatools.rep_errors(
record_id bigint not null,
error_message nvarchar(max)
)
go
alter table dbatools.rep_errors add constraint FK_Err_RecId foreign key(record_id)  references dbatools.rep_service_index(record_id) on delete cascade
go
grant select on dbatools.rep_errors to public
go

-- Exclude index
if OBJECT_ID(N'dbatools.rep_service_index_exclude',N'U') is not null
	drop table dbatools.rep_service_index_exclude
go
create table dbatools.rep_service_index_exclude(
obj_id int not null,
idx_id int not null,
comment varchar(128),
Primary key (obj_id,idx_id)
)
go
grant select on dbatools.rep_service_index to public
go
insert into dbatools.rep_service_index_exclude(obj_id,idx_id,comment)
select i.OBJECT_ID,i.index_id,
	 quotename(object_schema_name(i.object_id,db_id()))+'.'+quotename(object_name(i.object_id)) as comment 
from sys.indexes as i
where	  object_schema_name(i.object_id,db_id()) = 'dbatools'
and	  object_name(i.object_id)  in ('rep_service_index','rep_service_index_exclude','rep_errors')
go
if object_id(N'dbatools.v_indexes',N'V') is not null
	drop view dbatools.v_indexes;
go

-- Расширенное представление по индексам
-----------------------------------------
create view [dbatools].[v_indexes] 
as
select 	
		SCHEMA_ID(OBJECT_SCHEMA_NAME(ix.object_id,DB_ID())) as [schema_id],		
		OBJECT_SCHEMA_NAME(ix.object_id,DB_ID()) as [schema_name],
		ix.object_id as [object_id],
		OBJECT_NAME(ix.object_id) as [object_name],
		ix.index_id as [index_id],
		ix.Name as [index_name],
		ix.type,
		ix.type_desc,
		ix.is_unique,
		ix.data_space_id,
		case ds.type
			when 'FG' then ds.name
			when 'PS' then (select distinct name from sys.partition_schemes where data_space_id = ix.data_space_id)
		end as data_space,
		isnull((select fanout from sys.partition_functions prtfn
									join sys.partition_schemes as prtsch on prtfn.function_id = prtsch.function_id
									where prtsch.data_space_id =  ix.data_space_id),0) as part_count,
		ix.ignore_dup_key,
		ix.is_primary_key,
		ix.is_unique_constraint,
		ix.fill_factor,
		ix.is_padded,
		ix.is_disabled,
		ix.is_hypothetical,		
		ix.allow_row_locks,
		ix.allow_page_locks
from sys.indexes as ix
join sys.data_spaces as ds on ds.data_space_id = ix.data_space_id
GO
grant select on dbatools.v_indexes to public
go

-- Расширенное представление по индексам со статистикой использования индекса
--------------------
if object_id(N'dbatools.v_indexes_usage_stats',N'V') is not null
	drop view dbatools.v_indexes_usage_stats;
go
create view [dbatools].[v_indexes_usage_stats]
as
select 
		ix.schema_id,		
		ix.schema_name,
		ix.object_id,
		ix.object_name,
		ix.index_id,
		ix.index_name,
		ix.type,
		ix.type_desc,
		ix.is_unique,
		ix.data_space_id,
		ix.data_space,
		ix.part_count,
		ix.ignore_dup_key,
		ix.is_primary_key,
		ix.is_unique_constraint,
		ix.fill_factor,
		ix.is_padded,
		ix.is_disabled,
		ix.is_hypothetical,		
		ix.allow_row_locks,
		ix.allow_page_locks,
		iu.user_seeks,
		iu.user_scans,
		iu.user_lookups,
		iu.system_seeks,
		iu.system_scans,
		iu.system_lookups	
from dbatools.v_indexes as ix
join sys.dm_db_index_usage_stats as iu on iu.index_id = ix.index_id and iu.object_id = ix.object_id and iu.database_id = DB_ID()
GO
grant select on dbatools.v_indexes_usage_stats to public
go		

-- Представление о просмотре функций секционирования и параметров
------------------------------------------------------------------
if object_id(N'dbatools.v_partition_scheme_info',N'V') is not null
	drop view dbatools.v_partition_scheme_info;
go
create view dbatools.v_partition_scheme_info 
as
select 
		prtsch.data_space_id as [DATA_SPACE_ID],
		prtsch.name as [SCHEME_NAME],
		prtfn.name as [FUNC_NAME],
		prtfn.fanout as [FUNC_PART],
		TYPE_NAME(prtpar.user_type_id) as [TYPE],
		prtrange.boundary_id as [RANGE_BOUNDARY],
		prtrange.value as [RANGE_VALUE]
from sys.partition_schemes as prtsch
	join sys.partition_functions as prtfn on prtfn.function_id = prtsch.function_id
	join sys.partition_parameters as prtpar on prtpar.function_id = prtfn.function_id
	join sys.partition_range_values as prtrange on prtrange.function_id = prtfn.function_id	
go
grant select on dbatools.v_partition_scheme_info to public
go

-- Представление о просмотре информации по файлам данных 
-- и журналу транзакций (размер файла/ Занято / Свободно)
----------------------------------------------------------
if OBJECT_ID(N'dbatools.v_show_free_spacedata_all',N'V') is not null
	drop view dbatools.v_show_free_spacedata_all
go
create view dbatools.v_show_free_spacedata_all 
as
with spaceused as(
select
	a.FILEID,
	[FILE_SIZE_MB] = 
		convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
		convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = left(a.NAME,128),
	FILENAME = left(a.FILENAME,520),
    FILE_GROUP = fg.name,
	STATE_DESC = df.state_desc,

	[DEFAULT] = fg.is_default 
from
	sys.sysfiles a
left join sys.database_files df on df.file_id = a.fileid
left join sys.filegroups as fg on fg.data_space_id =  df.data_space_id
) 
select 
	FILEID, 
	FILE_SIZE_MB,
	SPACE_USED_MB,
	FREE_SPACE_MB,
	[NAME],
	[FILENAME],
	FILE_GROUP,
	[STATE_DESC],
	[DEFAULT]
 from spaceused
union all
select	
	NULL as FILEID,
	sum(FILE_SIZE_MB)as FILE_SIZE_MB, 
	sum(SPACE_USED_MB)as SPACE_USED_MB,
	sum(FREE_SPACE_MB)as FREE_SPACE_MB,
	NULL as [NAME],
	NULL as [FILENAME],
	NULL as [STATE_DESC],
	NULL as FILE_GROUP,
	NULL as [DEFAULT]
from spaceused
go
grant select on dbatools.v_show_free_spacedata_all to public
go

-- табличная функция о просмотре кластерных индексов,
-- которые рекомендуется (необходимо) перестроить
-------------------------------------------------------
if OBJECT_ID(N'dbatools.fn_cluster_index_for_smart_rebuild') IS NOT NULL
	drop function dbatools.fn_cluster_index_for_smart_rebuild;
GO
create function dbatools.fn_cluster_index_for_smart_rebuild(
@min_page_count bigint = 8,
@max_page_count bigint = 9223372036854775807,
@avg_per float = 10.00
)returns table 
as return
select 
	quotename(OBJECT_SCHEMA_NAME(st.object_id,st.database_id)) + '.' + quotename(object_name(st.object_id)) as obj_name,
	quotename(i.index_name) as idx_name,
	st.avg_fragmentation_in_percent,
	i.allow_page_locks,
	case i.part_count when 0 then 0 else st.partition_number end as part_number,
	st.page_count,
	i.[type]

from dbatools.v_indexes i 
join master.sys.dm_db_index_physical_stats(db_id(),null,null,null,'LIMITED') st on st.object_id = i.object_id and  st.index_id = i.index_id 
where 
not exists (select * from dbatools.rep_service_index_exclude where obj_id = i.object_id and idx_id = i.index_id)
and i.index_id = 1
and i.is_disabled = 0 
and st.page_count > @min_page_count
and st.page_count <= @max_page_count
and	st.avg_fragmentation_in_percent > @avg_per
GO
grant select on dbatools.fn_cluster_index_for_smart_rebuild to public
GO

-- табличная функция о просмотре не кластерных индексов,
-- которые рекомендуется (необходимо) перестроить
--------------------------------------------------------
if OBJECT_ID(N'dbatools.fn_index_for_smart_rebuild') IS NOT NULL
	drop function dbatools.fn_index_for_smart_rebuild;
GO
create function dbatools.fn_index_for_smart_rebuild(
@min_page_count bigint = 8,
@max_page_count bigint = 9223372036854775807,
@avg_per float = 10.00
)returns table 
as return
select 
	quotename(OBJECT_SCHEMA_NAME(st.object_id,st.database_id)) + '.' + quotename(object_name(st.object_id)) as obj_name,
	quotename(i.index_name) as idx_name,
	st.avg_fragmentation_in_percent,
	i.allow_page_locks,
	case i.part_count when 0 then 0 else st.partition_number end as part_number,
	st.page_count,
	i.[type]
from dbatools.v_indexes i 
join master.sys.dm_db_index_physical_stats(db_id(),null,null,null,'LIMITED') st on st.object_id = i.object_id and  st.index_id = i.index_id 
where 
not exists (select * from dbatools.rep_service_index_exclude where obj_id = i.object_id and idx_id = i.index_id)
and i.index_id > 1
and i.is_disabled = 0 
and st.page_count > @min_page_count
and st.page_count <= @max_page_count
and	st.avg_fragmentation_in_percent > @avg_per
GO
grant select on dbatools.fn_index_for_smart_rebuild to public
GO

-- процедура по перестроению кластерных индексов
----------------------------------------------------
if object_id('dbatools.sp_smart_cluster_reindex',N'P') is not null
	drop procedure dbatools.sp_smart_cluster_reindex;
go

create procedure dbatools.sp_smart_cluster_reindex	
(
@min_page_count bigint = 8,
@max_page_count bigint = 9223372036854775807,
@avg_per float = 10.00,
@is_online bit = 0,
@debug_info bit = 0
)
as
declare 
		@lp_object_name			nvarchar(514),
		@lp_index_name			sysname,
		@lp_allow_page_locks	bit,
		@lp_avg_defrag_percent	decimal(10,3),
		@run_sql				nvarchar(1024),
		@oper_num				bigint,
		@ident					bigint,
		@oper_type				smallint,
		@part_numb				int,
		@page_count				bigint,
		@idx_type				tinyint

declare c1 INSENSITIVE cursor for 

with ste (table_name,idx_name,part_numb,avg_frag_per,avg_page_count) as(
select 
	r.table_name,r.idx_name,r.part_numb, p.avg_frag_in_per,p.avg_page_count
from (select distinct table_name,idx_name,part_numb from dbatools.rep_service_index) r
cross apply(
	select cast(avg(t.frag_per) as decimal(5,2)) as avg_frag_in_per, avg(t.pagcount) as avg_page_count
	from (
		select 	top 3
			fragmentation_in_percent as frag_per, 
			page_count as pagcount			
		from dbatools.rep_service_index
		where table_name = r.table_name and idx_name = r.idx_name and part_numb = r.part_numb
		order by start_time desc
		)t
)p
)
select 
	s.obj_name,
	s.idx_name,
	s.avg_fragmentation_in_percent,
	s.allow_page_locks,
	s.part_number,
	s.page_count,
	s.[type]
from dbatools.fn_cluster_index_for_smart_rebuild(@min_page_count,@max_page_count,@avg_per) s
left join ste q on	q.table_name  = s.obj_name  
							and q.idx_name = s.idx_name 
							and q.part_numb  = s.part_number
where 							
	cast(abs(s.avg_fragmentation_in_percent - isnull(q.avg_frag_per,0.00)) as decimal(5,2)) > 1.00 --abs_frag_per
and	abs(s.page_count - isnull(q.avg_page_count,0)) > 1 -- abs_count_page	

open c1
FETCH NEXT FROM c1
	INTO @lp_object_name,@lp_index_name,@lp_avg_defrag_percent,@lp_allow_page_locks,@part_numb,@page_count,@idx_type

if @@rowcount > 0 	
exec dbatools.sp_next_value_sequence
@sequence_name = N'service_index',
@new_value =@oper_num  output
else begin
close c1
deallocate c1
return 0
end	

set nocount on

WHILE @@FETCH_STATUS = 0
BEGIN
if @lp_avg_defrag_percent < 30
begin
 if @lp_allow_page_locks = 1 begin
	set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REORGANIZE ';
	set @oper_type = 10;
 end else begin
	set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REBUILD ';	
	set @oper_type = 11;
 end
end else if @lp_avg_defrag_percent >= 30 begin
		set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REBUILD ';
		set @oper_type = 11;
end

if @part_numb > 0
	set @run_sql = @run_sql + ' PARTITION = '+CAST(@part_numb as nvarchar(12));

if SERVERPROPERTY('EngineEdition') = 3 and @idx_type = 1 and (@lp_avg_defrag_percent >= 30 or( @lp_avg_defrag_percent < 30 and @lp_allow_page_locks = 0)) and @is_online = 1
	set @run_sql = @run_sql + N' WITH (ONLINE = ON) '

if @debug_info = 1 begin
	print N'Index params :'
	print N'	Object: '+@lp_object_name
	print N'	Index: '+@lp_index_name
	print N'	Page count: '+ cast (@page_count as nvarchar)
	print N'	Partition: '+cast (@part_numb as nvarchar)
	print N'	Allow page lock: '+cast (@lp_allow_page_locks as nvarchar)
	print N'	Index type: '+cast (@idx_type as nvarchar)
	print N'	Fragmentation (%): '+cast (@lp_avg_defrag_percent as nvarchar)
	print N'Executing: '+ @run_sql
	end
begin try
		if @debug_info = 1
		print N'Add log info.'
	insert into dbatools.rep_service_index(table_name,idx_name,start_time,end_time,oper_num,oper_type,fragmentation_in_percent,page_count,part_numb) 
		values (@lp_object_name,@lp_index_name,getdate(),null,@oper_num,@oper_type,@lp_avg_defrag_percent,@page_count,@part_numb) 
		set @ident = Scope_identity()

	exec sp_executesql @run_sql
	if @debug_info = 1
		print N'Executing: Complite!'
	update dbatools.rep_service_index
		set end_time = getdate()
	where	record_id = @ident
		if @debug_info = 1
		print N'Update Info: Complite!'

end try
begin catch
if @debug_info = 1
		print N'Update Info: ERROR!'
update dbatools.rep_service_index
	set [description] = @run_sql
where	record_id = @ident
if @debug_info = 1
		print N'Add error log info.'
insert into dbatools.rep_errors(record_id,[error_message]) values (@ident,error_message())
if @debug_info = 1 
		print N'ERROR_MESSAGE: '+error_message()		
end catch

FETCH NEXT FROM c1
	INTO @lp_object_name,@lp_index_name,@lp_avg_defrag_percent,@lp_allow_page_locks,@part_numb,@page_count,@idx_type
END
CLOSE c1;
DEALLOCATE c1;
go

grant execute on dbatools.sp_smart_cluster_reindex to public;
GO

-- процедура по перестроению не кластерных индексов
----------------------------------------------------
if object_id('dbatools.sp_smart_reindex',N'P') is not null
	drop procedure dbatools.sp_smart_reindex;
go

create procedure dbatools.sp_smart_reindex	
(
@min_page_count bigint = 8,
@max_page_count bigint = 9223372036854775807,
@avg_per float = 10.00,
@is_online bit = 0,
@debug_info bit = 0
)
as
declare 
		@lp_object_name			nvarchar(514),
		@lp_index_name			sysname,
		@lp_allow_page_locks	bit,
		@lp_avg_defrag_percent	decimal(10,3),
		@run_sql				nvarchar(1024),
		@oper_num				bigint,
		@ident					bigint,
		@oper_type				smallint,
		@part_numb				int,
		@page_count				bigint,
		@idx_type				tinyint
		
declare c1 INSENSITIVE cursor for 

with ste (table_name,idx_name,part_numb,avg_frag_per,avg_page_count) as(
select 
	r.table_name,r.idx_name,r.part_numb, p.avg_frag_in_per,p.avg_page_count
from (select distinct table_name,idx_name,part_numb from dbatools.rep_service_index) r
cross apply(
	select cast(avg(t.frag_per) as decimal(5,2)) as avg_frag_in_per, avg(t.pagcount) as avg_page_count
	from (
		select 	top 3
			fragmentation_in_percent as frag_per, 
			page_count as pagcount			
		from dbatools.rep_service_index
		where table_name = r.table_name and idx_name = r.idx_name and part_numb = r.part_numb
		order by start_time desc
		)t
)p
)
select 
	s.obj_name,
	s.idx_name,
	s.avg_fragmentation_in_percent,
	s.allow_page_locks,
	s.part_number,
	s.page_count,
	s.[type]
from dbatools.fn_index_for_smart_rebuild(@min_page_count,@max_page_count,@avg_per) s
left join ste q on	q.table_name  = s.obj_name  
							and q.idx_name = s.idx_name 
							and q.part_numb  = s.part_number
where 							
	cast(abs(s.avg_fragmentation_in_percent - isnull(q.avg_frag_per,0.00)) as decimal(5,2)) > 1.00 --abs_frag_per
and	abs(s.page_count - isnull(q.avg_page_count,0)) > 1 -- abs_count_page	

open c1
FETCH NEXT FROM c1
	INTO @lp_object_name,@lp_index_name,@lp_avg_defrag_percent,@lp_allow_page_locks,@part_numb,@page_count,@idx_type

if @@rowcount > 0 	
exec dbatools.sp_next_value_sequence
@sequence_name = N'service_index',
@new_value =@oper_num  output
else begin
close c1
deallocate c1
return 0
end	

set nocount on

WHILE @@FETCH_STATUS = 0
BEGIN
if @lp_avg_defrag_percent < 30
begin
 if @lp_allow_page_locks = 1 begin
	set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REORGANIZE ';
	set @oper_type = 10;
 end else begin
	set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REBUILD ';	
	set @oper_type = 11;
 end
end else if @lp_avg_defrag_percent >= 30 begin
		set @run_sql = N'ALTER INDEX '+ @lp_index_name + N' ON '+@lp_object_name + N'  REBUILD ';
		set @oper_type = 11;
end

if @part_numb > 0
	set @run_sql = @run_sql + ' PARTITION = '+CAST(@part_numb as nvarchar(12));

if SERVERPROPERTY('EngineEdition') = 3 and @idx_type = 2 and (@lp_avg_defrag_percent >= 30 or( @lp_avg_defrag_percent < 30 and @lp_allow_page_locks = 0)) and @is_online = 1 
	set @run_sql = @run_sql + N' WITH (ONLINE = ON) '

if @debug_info = 1 begin
	print N'Index params :'
	print N'	Object: '+@lp_object_name
	print N'	Index: '+@lp_index_name
	print N'	Page count: '+ cast (@page_count as nvarchar)
	print N'	Partition: '+cast (@part_numb as nvarchar)
	print N'	Allow page lock: '+cast (@lp_allow_page_locks as nvarchar)
	print N'	Index type: '+cast (@idx_type as nvarchar)
	print N'	Fragmentation (%): '+cast (@lp_avg_defrag_percent as nvarchar)
	print N'Executing: '+ @run_sql
	end
	
begin try
	if @debug_info = 1
		print N'Add log info.'
		
	insert into dbatools.rep_service_index(table_name,idx_name,start_time,end_time,oper_num,oper_type,fragmentation_in_percent,page_count,part_numb) 
		values (@lp_object_name,@lp_index_name,getdate(),null,@oper_num,@oper_type,@lp_avg_defrag_percent,@page_count,@part_numb) 
		set @ident = Scope_identity()

	exec sp_executesql @run_sql
if @debug_info = 1
		print N'Executing: Complite!'
	
	update dbatools.rep_service_index
		set end_time = getdate()
	where	record_id = @ident
	if @debug_info = 1
		print N'Update Info: Complite!'
end try
begin catch
if @debug_info = 1
		print N'Update Info: ERROR!'
update dbatools.rep_service_index
	set [description] = @run_sql
where	record_id = @ident
if @debug_info = 1
		print N'Add error log info.'
insert into dbatools.rep_errors(record_id,[error_message]) values (@ident,error_message())
if @debug_info = 1 
		print N'ERROR_MESSAGE: '+error_message()		
end catch
FETCH NEXT FROM c1
	INTO @lp_object_name,@lp_index_name,@lp_avg_defrag_percent,@lp_allow_page_locks,@part_numb,@page_count,@idx_type
END
CLOSE c1;
DEALLOCATE c1;
go


grant execute on dbatools.sp_smart_reindex to public;
GO

-- Представление о просмотре времени затраченному на
-- обслуживание индекса
-------------------------------------------------------------
if OBJECT_ID(N'dbatools.v_date_diff_last_smart_reindex',N'V') is not null
	drop view dbatools.v_date_diff_last_smart_reindex;
go
create view dbatools.v_date_diff_last_smart_reindex 
as
select 
	oper_num,	
	min(start_time) as date_min,
	max(end_time) as date_max,
	(max(end_time)-min(start_time)) as date_diff
	from dbatools.rep_service_index
where oper_type  in (10,11)
group by oper_num
go
grant select on dbatools.v_date_diff_last_smart_reindex to public
go

-- Процедура по очистки устаревших записей 
-- в таблице dbatools.rep_service_index
-------------------------------------------
if OBJECT_ID(N'dbatools.sp_clear_expired_smart_reindex',N'P') is not null
	drop procedure dbatools.sp_clear_expired_smart_reindex;
go
create procedure [dbatools].[sp_clear_expired_smart_reindex]
as
begin
set nocount on
declare @days int
declare @TmpSmartIndex table (record_id bigint);
select @days = value_int from dbatools.sys_params where [param] = N'ExpiredDays_smart_reindex'
insert into @TmpSmartIndex
select record_id 
from dbatools.rep_service_index
where oper_num in (
select distinct oper_num from dbatools.rep_service_index
where start_time < dateadd(dd,@days,getdate()) and oper_type in (10,11)
)
if exists(select * from @TmpSmartIndex)
begin
	begin tran
	begin try		
		delete from dbatools.rep_service_index where record_id in (select record_id from @TmpSmartIndex)
	commit tran
	end try
	begin catch
		rollback tran
	 DECLARE @ErrorMessage NVARCHAR(4000);
	 DECLARE @ErrorSeverity INT;
	 DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
	end catch
end
end
GO
if not exists (select * from  dbatools.sys_params where [param] = N'ExpiredDays_smart_reindex')
	insert into dbatools.sys_params([Param],value_int)  values (N'ExpiredDays_smart_reindex',-90);
GO

Многие объекты вам наверное уже известны из моих предыдущих постов серии dbatools. Давай те остановимся на новых.
1. Таблицы dbatools.rep_service_index и dbatools.rep_errors. служат для регистрации информации по индексу. В таблицу dbatools.rep_service_index вносятся такие данные как имя таблицы, имя индекса, время начала перестроения, время завершения перестроения, тип операции, номер группы операций, фрагментация до начала перестроения, количество страниц в индексе, номер секции (в случае если индекс - секционирован). В поле description записывается sql комманда выполняемая над индексом (только при ошибки). В таблицу dbatools.rep_errors записывается текст ошибки.
2. В таблицу dbatools.rep_service_index_exclude мы будем вносить те индексы, которые хотим исключить из обслуживания.
3. dbatools.fn_cluster_index_for_smart_rebuild. Эта табличная функция используется для получения информации по кластерным индексам. Входными параметрами являются минимальное и максимальное количество страниц в индексе и процент фрагментации индекса, ниже которого индекс просто не будет отображаться.
Например нам надо получить список индексов для перестроения, но где количество страниц не выше 90 000 и стетень фрагментации выше 40%
select * from dbatools.fn_cluster_index_for_smart_rebuild(8,90000,40.00)
GO

Если у ваc присутствуют большие индексы в десятки и даже сотни гигабайт, вы их просто игнорируете. Например, у меня на одной из таблиц индекс в 70 Гигов и количество страниц в индексе там выше 2 500 000. Нижний порог выбран не случайно. Во первых фрагментировать индексы где количество страниц меньше 8 в большенстве случае нет необходимости. И во вторых 8 упорядоченных страниц это один экстент.
4. dbatools.fn_index_for_smart_rebuild тоже самое что и п.3, но для некластерных индексов. Дело в том что перестроение кластерных индексов занимает больше времени чем для некластерных. Во первых при перестроении страницы в файле выстраиваются физически последовательно. Во вторых кластерный индекс содержит все данные строки, а не кластерный лишь перечисленные. Хотя и тут можно поспорить, например, создав покрывающий индекс на таблицу.
5. функции dbatools.sp_smart_cluster_reindex и dbatools.sp_smart_reindex - используя информацию из функций dbatools.fn_cluster_index_for_smart_rebuild и dbatools.fn_index_for_smart_rebuild, перестраивают индекс по рекомендациям Microsoft, Использование функции sys.dm_db_index_physical_stats в сценарии для перестроения или реорганизации индексов.
6. Представление dbatools.v_date_diff_last_smart_reindex позволяет простатривать обслуживание набора индексов в пределах пакета операции (oper_type). Оно показывает время начала обслуживания, время окончания обслуживания Время затраченное на обслуживание.
7. dbatools.sp_clear_expired_smart_reindex процедура для очистки устаревших данных. Дата устаревания (ExpiredDate) записывается в таблицу dbatools.sys_params. По умолчанию это значение 90 дней.

По объектам вроде бы все. Теперь хочу разместить несколько запросов уоторые я использую.

Показывает список индексов подверженных обслуживанию при дефалтовых параметрах.
-- Show index need rebuild
select * from dbatools.fn_cluster_index_for_smart_rebuild(8,9223372036854775807,10.00)
GO
select * from dbatools.fn_index_for_smart_rebuild(8,9223372036854775807,10.00)
GO


Просмотр списка исключений по обслуживанию индексов.
-- show exclude index
select * from dbatools.rep_service_index_exclude
GO


собственно функии по обслуживанию индексов.
-- procedure rebuild index
exec dbatools.sp_smart_cluster_reindex --@avg_per = 50.00
GO
exec dbatools.sp_smart_reindex  --@debug_info = 1
GO
exec dbatools.sp_smart_reindex  --@max_page_count = 100000
GO


отчет по обслуживанию индексов
-- Show information with rebuild index
select 
	table_name,
	idx_name,
	start_time,
	end_time,
	(end_time-start_time) as diff_date,
	oper_type_desc,
	oper_num,
	fragmentation_in_percent,
	page_count,
	part_numb
from dbatools.rep_service_index
order by start_time desc,table_name,idx_name
GO


Тема будет периодически обновляться в ходе обновления объектов и появления новых. обо всех изменениях будет информированно в комментах.

Ссылки на связанные темы:
1. dbatools. Часть 1. Общеиспользуемые объекты
2. dbatools. Часть 2. Реализация последовательности (sequence)

Комментарии


  • изменены процедуры dbatools.sp_smart_cluster_reindex и dbatools.sp_smart_reindex. Добавлен анализ изменения индекса.

  • Msg 207, Level 16, State 1, Procedure sp_smart_cluster_reindex, Line 49
    Invalid column name 'type'.

  • 2zindur, спасибо. поправил.

  • gds, напиши плиз мне на igortsk@mail.ru
    Есть предложение совместить твои наработки с моими =)



Необходимо войти на сайт, чтобы оставлять комментарии