DBREINDEX or INDEXDEFRAG of a few indexes/window

kriki

I have a quite big database (150GB) that is running 24/24 7/7 with each day 2 windows of about 2 hours in which people are working little.
So doing a complete indexrebuild during the weekend is not possible. The only option is the rebuild/defrag indexes is during those windows. Problem is:I can't do it on all tables because it takes too much time.
So I was thinking about creating several SQL scripts and each of those is doing some tables/indexes. Problem is that I have a few hundreds of tables and if I want to move an index from 1 window to another, I have to change those scripts manually. Quite a time costing and error-prone procedure.

So I was hoping there is some tool that shows me all existing indexes and give me the possibility to add them to a script and once I have done that, generate the scripts to be run.
I have launched some searches on the forum but I didn't find any mentioning of the existence of such a tool.
Does someone know of such a tool or have a better idea how to do this?

Marije_Brummel

Hi Alain,

I would like to invite you to have a look at our tools at www.sqlperform.com.

It does a reindex but smart. With analysing the need. It also clears the zero sift records and updates the statistics. Very usefull.

I have installed it at approx. 10 customers in 3 months and no one takes more than 2 hours. Even big ones.

Good luck.

kriki

Mark Brummel wrote:

Even big ones.

And what do you consider a "big one"?

BTW : once I am ready with tuning the DB for SQL, I am sure the size will go under 100GB.

Marije_Brummel

It is not the size of the database that is important but the number of changes in hot tables.

I've done a warehouse customer with 140 concurrent users for example.

kriki

Mark Brummel wrote:

It is not the size of the database that is important but the number of changes in hot tables.

How about 12000 sales order lines a day? 24/24 7/7?

Marije_Brummel

Wow, that is a lot. :shock:

I guess we would need to implement our reindex function per table. This is not implemented yet but is on the roadmap.

kriki

And for when is that planned?

nunomaia

kriki.

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

I don't know if it help you.

kriki

nunomaia wrote:

kriki.

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

I don't know if it help you.

I definitely will study it. I think this could solve my problem without me having to create the statements for the tables.

Nobody

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

I found another stored procedure on a SQL forum to defrag or rebuild indexes. I also put it here because it can be usefull.
http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx#3688568135596798454

kriki

[Topic moved from Navision forum to Navision Tips & Tricks forum]

Phenno

I'm aware that this is a years old thread but I came a cross to it while searching for more information on this script which can be used to maintain indexes.

I found this script on blogs.msdn.com site and were using it for a while for some older NAV installations (SQL2005). Script says that it will reorganize all indexes fragmented less than @percentage and rebuild all indexes fragmented more than @percentage. But, while collecting indexes for rebuild, it filters-out all indexes fragmented more than @percentage.

In that case reorganize of some index will never occur?

Miklos_Hollender

@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

@Miklos: I saw them using NAV and they had at least 3-4 persons receiving phone calls for ordering things. And they create sales orders for it that later were shipped.

@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

Phenno

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.