Maintenance Plan - Rebuild Index Task and Update Statistics

nav_student · 2010-07-29

Hi guys!

I want to configure this 2 tasks on my Maitenance Plan.

What configuration should i use on both tasks to improve Dynamics NAV performance?

Best Regards

kriki · 2010-07-29

If you do a rebuild index, you don't need to do a update statistics task anymore. The rebuild index does the update with 100% data sample. Better than this is not possible.

For a rebuild index, I generally use
"change free space per page percentage to 10%" (= 90% fillfactor).

It is not perfect, but I think it is the overall best value.

nav_student · 2010-07-29

Thanks kriki.

I won the SQL Performance Guide book and i saw the maintenance plan the following procedure

1.........
2 Rebuild Index Task
3. Update Statistics
3..............

stryk · 2010-07-30

If you could do a little TSQL programming, you could implement even better maintenance than with those MP tasks:

For the "Index Defragmentation" you could use the script provided with the "Books Online" related to the DMV "sys.dm_db_index_physical_stats" Example D.:

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
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, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

You just need to add the FILLFACTOR clause and maybe the MAXDOP clause, etc.. This script determines which indexes need to be defragmented and which method to use, thus it finds the optimal balance between result & impact on the system (regarding runtime, blocking, etc.). Should be executed weekly.

Regarding the statistics, check out this one:
http://dynamicsuser.net/blogs/stryk/archive/2008/08/01/index-statistics.aspx

Finally you should run daily:

(context of your NAV db)

exec sp_updatestats
go
exec sp_createstats 'indexonly'

kriki · 2010-07-31

This can also be useful: http://www.mibuso.com/forum/viewtopic.php?f=48&t=42904

NaviDeveloper_NL · 2010-10-19

I used these three solutions:

1. Michelle Ufford - http://sqlfool.com/2010/04/index-defrag-script-v4-0/
2. Franz Robeller, Principal Consultant Microsoft Germany http://cid-ad321dd74941ab0c.skydrive.li ... echNet.zip
3. Ola Hallengren - http://ola.hallengren.com/

Howdy, Stranger!

Quick Links

Categories

Maintenance Plan - Rebuild Index Task and Update Statistics

Comments