nunomaia: NAV SQL Indexes

[Blog bot]

In some companies it’s almost impossible to make a full index rebuild, because they have only a few hours of maintenance windows


You can adapt this script to rebuild a specific number of indexes during each day, during a specific hours. If you have several companies in a database you can even adapt it to rebuild each company at a specific hour of day.


This scripts only runs in SQL Server 2005.
This script isn’t complete you have to adapt it to your current company and define fragmentation values.


Create a table to store indexes and values.


-- TABLE OF INDEXES



CREATETABLE IndexFragList
(

DatabaseId INT,
IndexId INT,
ObjectID INT,
IndexName CHAR(255),
AvgFrag DECIMAL,
TableName VARCHAR(255)

)


-- ############ SCRIPT #############################



DECLARE @Maxfrag DECIMAL
DECLARE @Minfrag DECIMAL
DECLARE @DatabaseID INT
DECLARE @IndexId INT


DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFrag DECIMAL
DECLARE @ObjectID INT
DECLARE @TableName VARCHAR(6000)
DECLARE @Command VARCHAR(255)


DECLARE @Count INT


-- Decide on the maximum fragmentation to allow


SELECT @Maxfrag = 30.0
SELECT @Minfrag = 0.05


SET @COUNT =(SELECTCOUNT(*)FROM IndexFragList)
PRINT'COUNT.....'+CONVERT(VARCHAR(10), @COUNT)


IF @COUNT = 0
BEGIN
-- FILL INDEXES
INSERTINTO IndexFragList(DatabaseId, IndexId, ObjectID, IndexName, AvgFrag, TableName)


SELECT
a.database_id, a.index_id, a.object_id, b.name, avg_fragmentation_in_percent avg_frag, t.name
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.Product'),NULL,NULL,NULL)AS a
JOINsys.indexesAS b ON a.object_id= b.object_idAND a.index_id = b.index_id
JOINsys.tablesas t ON a.object_id= t.object_id
AND avg_fragmentation_in_percent > @Minfrag
END


-- CUSTOMIZE THIS
-- IF CAN FILTER BY COMPANY, ETC
DECLARE indexes CURSORFOR
SELECT DatabaseID, IndexId, ObjectID, IndexName, IndexId, AvgFrag, TableName
FROM IndexFragList ORDER BY AvgFrag DESC
OPEN indexes
FETCHNEXTFROM indexes INTO
@DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName
WHILE@@FETCH_STATUS= 0
BEGIN
SET @Command='ALTER index ['+RTRIM(@IndexName)+'] ON ['+ @TableName +'] '
-- REBUILD INDEX


IF @AvgFrag > @Maxfrag BEGIN
SET @Command=@Command +' REBUILD'
EXEC(@Command)
END





-- REORGANIZE INDEX
ELSEIF(@AvgFrag > @Minfrag)AND(@AvgFrag > @Maxfrag)BEGIN
SET @Command=@Command +' REORGANIZE'
EXEC(@Command)
END





DELETEFROM IndexFragList
WHERECURRENTOF indexes

-- CUSTOMIZE THIS
-- YOU CAN BREAK BY HOURS, NUMBER OF INDEXES, ETC
BREAK;





FETCHNEXTFROM indexes INTO
@DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName
END


CLOSE indexes
DEALLOCATE indexes






Подробнее... http://dynamicsuser.net/blogs/nunomaia/arc...ql-indexes.aspx