SQL 인덱스 관리 (임시)
ALTER INDEX [TCNGT~0] ON [dsm].[TCNGT] REORGANIZE
go
UPDATE STATISTICS [dsm].[TCNGT] ([TCNGT~0]) WITH FULLSCAN
go
DSM reorg script
use DSM
go
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
declare @DB_ID int;
set @DB_ID = db_id();
SELECT
aa.[object_id] AS objectid,
aa.index_id AS indexid,
-- bb.[name] as strIndexName,
-- bb.[type_desc] as strIndexType,
aa.partition_number AS partitionnum,
aa.avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL , NULL, 'LIMITED') aa
--left join sys.indexes bb On aa.[object_id] = bb.[object_id] and aa.[index_id] = bb.[index_id]
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0
--order by objectid, indexid;
-- 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.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
DECLARE @intCnt INT;
SET @intCnt = 0
WHILE @@FETCH_STATUS = 0
BEGIN;
SET @intCnt = @intCnt + 1;
SELECT @objectname = o.name, @schemaname = 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 = 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;
IF @frag < 20.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname + '] ON [' + @schemaname + '].[' + @objectname + '] REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (varchar(5), @partitionnum);
set @command = @command + char(10) + 'go' + char(10) + char(10)
--EXEC (@command);
--PRINT convert(varchar(3), @intCnt) + '. ' + @command
PRINT @command;
--통계 업데이트
SET @command = 'UPDATE STATISTICS ['+ @schemaname + '].[' + @objectname + '] ([' + @indexname + ']) WITH FULLSCAN';
set @command = @command + char(10) + 'go' + char(10) + char(10);
--EXEC (@command);
--PRINT convert(varchar(3), @intCnt) + '. ' + @command
PRINT @command;
END;
IF @frag >= 20.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON [' + @schemaname + '].[' + @objectname + '] REBUILD';
IF @partitioncount > 1
begin
SELECT @command = @command + ' PARTITION=' + CONVERT (varchar(5), @partitionnum);
set @command = @command + ' with (maxdop=18)'
end
else
begin
set @command = @command + ' with (maxdop=18, PAD_INDEX = on, fillfactor=90, '
--if (@indexname = 'PK_RMMessage')
if (1=2)
set @command = @command + 'online=off)'
else
set @command = @command + 'online=on)'
end
set @command = @command + char(10) + 'go' + char(10) + char(10)
--EXEC (@command);
--PRINT convert(varchar(3), @intCnt) + '. ' + @command
PRINT @command;
END;
PRINT '--FRAGMENTATION : ' + CAST(@frag AS VARCHAR(10)) + '%' + CHAR(10);
--PRINT '' + @command + char(10) + 'go' + char(10) + char(10);
--PRINT @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do
이 글은 스프링노트에서 작성되었습니다.
'4 Jobs > SAP' 카테고리의 다른 글
Table Buffer/Generic Buffer (0) | 2010.08.24 |
---|---|
Starting SAPinst on the Remote Host (0) | 2010.08.24 |
SAP t-Code (0) | 2010.08.24 |
SAP Basis System: Initialization DB-Connect Failed, Return Code 004096 (0) | 2010.08.24 |
RFC Error : Win32 error 1326 (0) | 2010.08.24 |