dynamicsusers: NAV SQL Indexes

[Blog bot]

Источник: http://dynamicsuser.net/blogs/nunoma...l-indexes.aspx
==============

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/nunoma...l-indexes.aspx