|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
with igs as ( select * from sys.dm_db_missing_index_group_stats ) , igd as ( select *, isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col from sys.dm_db_missing_index_details |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @SQL NVARCHAR(MAX) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT ' PRINT ''Перестройка [' + i.name + N'] начато '' + Cast(GETDATE() as varchar); ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON) PRINT ''Перестройка [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) ' ELSE 'REORGANIZE' END + ';' |
Первым делом создадим табличку куда будет собираться статистика.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE kmosk.dbo.reindeks_SALESLINE ( [database_id] VARCHAR(max) ,[object_id] VARCHAR(max) ,[index_id] VARCHAR(max) ,[partition_number] VARCHAR(max) ,[index_type_desc] VARCHAR(max) ,[alloc_unit_type_desc] VARCHAR(max) ,[index_depth] VARCHAR(max) ,[index_level] VARCHAR(max) ,[avg_fragmentation_in_percent] VARCHAR(max) ,[fragment_count] VARCHAR(max) ,[avg_fragment_size_in_pages] VARCHAR(max) ,[page_count] VARCHAR(max) ,[avg_page_space_used_in_percent] VARCHAR(max) ,[record_count] VARCHAR(max) ,[ghost_record_count] VARCHAR(max) ,[version_ghost_record_count] VARCHAR(max) ,[min_record_size_in_bytes] VARCHAR(max) ,[max_record_size_in_bytes] VARCHAR(max) ,[avg_record_size_in_bytes] VARCHAR(max) ,[forwarded_record_count] VARCHAR(max) , vreme datetime NOT NULL DEFAULT GETDATE()) |
После этого создадим задание которое будет наполнять табличьку