adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Сбор статистики по заполнению фрагментации индекса на отдельных таблицах

Первым делом создадим табличку куда будет собираться статистика.

 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())
   
       

После этого создадим задание которое будет наполнять табличьку

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ax_molniya');
SET @object_id = OBJECT_ID(N'ax_molniya.[dbo].[SALESLINE]');

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;

 
   insert INTO kmosk.dbo.reindeks_SALESLINE
   ([database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count])
     
   SELECT 
   [database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count]
      
    FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')
    END;
    
    
 --INSERT INTO    kmosk.dbo.test2 (DATA)
  --VALUES (GETDATE());


Задание будет наполнять табличку в 8 утра и 8 вечера

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>