Maintenance Plan - Rebuild Index Task and Update Statistics

nav_student
Member Posts: 175
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
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
0
Comments
-
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.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
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..............0 -
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'
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool0 -
This can also be useful: http://www.mibuso.com/forum/viewtopic.php?f=48&t=42904Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
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/0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions