DBREINDEX or INDEXDEFRAG of a few indexes/window

krikikriki Member, Moderator Posts: 9,094
edited 2015-05-20 in NAV Tips & Tricks
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?
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Comments

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • krikikriki Member, Moderator Posts: 9,094
    Even big ones.
    And what do you consider a "big one"? :wink:

    BTW : once I am ready with tuning the DB for SQL, I am sure the size will go under 100GB.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • krikikriki Member, Moderator Posts: 9,094
    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?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • krikikriki Member, Moderator Posts: 9,094
    And for when is that planned?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • nunomaianunomaia Member Posts: 1,153
    kriki.

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

    I don't know if it help you.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • krikikriki Member, Moderator Posts: 9,094
    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. :D
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • NobodyNobody Member Posts: 93
    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


  • krikikriki Member, Moderator Posts: 9,094
    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
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from Navision forum to Navision Tips & Tricks forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • PhennoPhenno Member Posts: 630
    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_HollenderMiklos_Hollender Member Posts: 1,598
    @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.
  • krikikriki Member, Moderator Posts: 9,094
    @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
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • PhennoPhenno Member Posts: 630
    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.
Sign In or Register to comment.