DBREINDEX or INDEXDEFRAG of a few indexes/window

kriki.

Check this post.
http://dynamicsuser.net/blogs/nunomaia/ ... dexes.aspx

I don't know if it help you.

Here is a script that will look at all the indexes in the database and rebuild the indexes with a fragmentation greater than 30% and just reorganize the indexes where the fragmentation is less than or equal to 30. It is in the form of a stored procedure and requires no input parameters so you just fire it off with a SQL job. This is for SQL 2005 and I "borrowed" the majority of the code from BOL. This is essentially freeware so feel free to use it or modify it.

---

/**
This TSQL script for SQL Server 2005 will rebuild or reorganize indexes in the
Dynamics - NAV database based the fragmentation percentage. If the fragmentation
Is over 30% it will rebuild thew index if it is under 30% it will reorganize the
index.

This script is in the form a Stored Procedure "sp_index_defrag" to use this script
1. Run the script against the Dynmaics - NAV database to build the Stored Procedure

2. To run the process just open a query window and select the Dynamics - NAV database
and type "maint_index_defrag" in the query window and click [Execute]

3. When it is finished running just close the query window.

TIP: You could setup a SQL Job that runs this Stored Procedure at set intervals
Such as once a week or once a month.
**/


IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id('dbo.maint_index_defrag') and sysstat & 0xf = 4)
DROP PROCEDURE maint_index_defrag
go

Create Procedure maint_index_defrag


as

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);
DECLARE @percentage int

SET @percentage = 30 --You can change the precentage threshhold here

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;

SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > @percentage AND index_id > 0;

DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;


OPEN partitions;


FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

@FETCH_STATUS = 0
BEGIN;
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 < (@percentage)
BEGIN;
SELECT @command = 'ALTER INDEX ' + '' + ' ON ' + '' + '.' + '' + ' REORGANIZE';
--IF @partitioncount > 1
-- SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;

IF @frag >= (@percentage)
BEGIN;
SELECT @command = 'ALTER INDEX ' + '' + ' ON ' + '' + '.' + '' + ' REBUILD';
--IF @partitioncount > 1
-- SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;

CLOSE partitions;
DEALLOCATE partitions;

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO

---

@Kriki isn't it a case when they are using Sales Orders, but they are actually not doing B2B but retail, and using it as an ad-hoc retail Point-of-Sale thing?

I had a project like that, we were beginners and kind of clueless, the company was doing normal B2B but also had a Cash and Carry shop, so to win the deal we did not recommend Landsteinar just writing invoices to a generic customer and rewriting the name on each... made the DB big.

kriki wrote:

@Phenno: In this, I have a solution for the rebuild index. It is not perfect but is workable. Another (free) tool is using this or this this

Thank you, I already switched to Hallengren's script.