index rebuild increased Data File size

Dear Experts

Index maintenance on single Table(i.e value entry) increased the size of secondary data file(.ndf) .
Before maintenance my secondary data file size was around 535 GB and After rebuild of index on value entry it increased to around 589 GB ,thus DB size increased by 54 GB .But the only good thing happened was that the size of value Entry Table reduced from 129 GB to 122 GB i.e Table size reduction of 7 GB

I am using NAV5 .0 on SQL SERVER 2008 Enterprise

I used the following query for index maintenance

ALTER INDEX ALL ON
[DBNAME $Value Entry]
REBUILD;
GO

why was the unexpected growth in data file (.ndf) after index rebuild ?

As far I remember i used navision optimize functionality(i.e index rebuild & delete zero sift record) few days back but that gave me positive result as my DB Size was same but free space was increased after running optimize function.

so i want to know why there was huge increase in DB size (.ndf file) after index rebuild ??

and if data file increase is normal occurrence in index rebuild then why does navision optimize function behaves differently as it decreased DB size by increasing free space in DB and there was no increase in data file size.

As far I know optimize = index rebuild( index drop/ index recreate process ) + delete zero sift record

or does optimize function do some thing else that made DB size to reduce ?

As per my scenario optimization should be preferred over alter index rebuild but as far i know alter index - rebuild process cause less overhead than optimization .

Please correct me if my concepts are wrong .

Thanks in advance

Hi,

welcome to the Dynamics User Group! [<:o)]

Well, the idea of an ALTER INDEX REBUILD is to defragment indexes, thus usually this ends up in releasing some free pages, cleaning up the table.

The growth you describe is allmost exactly 10% - which rings a bell [8-|]

I guess there is/was an INDEX FILLFACTOR defined (Server Properties? Maintenence Plans?) - “Best Practice” is to have something between 5 to 10 percent free space for an index (if you cannot calculate it more precisely).

So prabably there’s a fillfactor of 90 (= 10% free space) applied to that table; thus, the REBUILD will restore thst FF which might explain this growth!

Cheers,

Jörg

Thanks Jorg

Its really nice to be here.I am following you Blogs that are really great. I am very eager to read Field Perfomace Guide .How it is possible to get it in India?

I have not used any MP for index maintenance.I mentioned the TSQL Query which I used to rebuild index ,and there I have not specified any Fill Factor.

Does it picks Deafult Fill factor from some where if not specified.If it is true then how can i see the default Fill Factor . Is there any way to set Deafult Fill Factor??

Here my concern is same operation(Index Rebuild ) executed by two means gives different result as follow :

  1. Navision Table Optimize : Doesnot increase Size of .ndf File but frees some extra space (so it can be treated as reduction in Database Size)

  2. TSQL Query (Alter index rebuild) : Huge increase in .ndf file (i.e 54 GB) that results in Huge increase of Database(i.e Around 45 GB (=54-7 ) despite of Table Size is reduced (Value Entry size is reduced by 7 GB )

AFAIK Table Optimize drops index and recreates them so creates extra overhead thus TSQL Query (Alter index rebuild) is prefferd over Navison Table Optimize.

But looking at my scenario it makes sense to prefer Navison Table Optimize over TSQL Query (Alter index rebuild) as it reduce Database size whereas TSQL Query (Alter index rebuild) increases DB Size.

why such different behaviour for same opereation ?

One more Question before Navsion 5.0 Sp1 version it makes sense to run Navison Optimize Function as it also deltes Zero Sift Records along with index rebuild but from Navsion 5.0 Sp1 onwards Is there any sense of running Navison Table Optimize Function if I am regularly using Tsql Query for index maintenance ?

Thanks

Pankaj

Thanks for your kind feedback. Regarding the “Field Guide” I’m afraid I don’t know who is distributing it in INdia, probably Amazon.com but the shipping fees might be horrible …

If you check the SQL Server Instance properties to have a look at the “Database Settings” you might find a “Default Index Fill-Factor”! Or you coould check the configuration with sp_configure:

exec sp_configure ‘show advanced options’, ‘1’
go
reconfigure
go
exec sp_configure
go

then look up the “fill factor (%)” setting.

The difference you experience with Table Optimizer (TO) (CREATE INDEX WITH DROP_EXISTING) and Maintenance Plan (MP) (ALTER INDEX REBUILD) is indeed strange. I guess the ALTER INDEX is using that default fillfactor, while the DROP/CREATE doesn’t …

I’d rather not use the TO today, as it resets all optimizations (if applied) and causes supe-impact to the system. Secondly, you cannot schedule it, and is has a huge blocking impact to concurrent business processes … I actually use my own tools for proper index maintenance, but with some SQL script you could easily establish sufficiebt index maintenance, while reducing the impact; pls check out this one about details.

Thanks for taking interest in my post.[:D]

checked the fillfactor and it is as follow:

name minimum maximum config_value run_value

fill factor (%) 0 100 0 0

so Fill Factor is not the possibility for strange behaviour of MP.

I would like to share some detail , Operation performed in Test Env. to enhance performance on Nav 5.0 running Database on SQL Server 2008 R2 . Main Pain area is performance & Disk Space as DB is huge 565 GB and

Can you please share your experience on :

  1. In normal scenario What should be the result of running MP(Alter index) when it is ran first time (say once in a year) for eg
    Does it increase/decrease .ndf file ?
    Does it increase/decrease Free space ?

  2. I technically upgraded Nav 5.0 to NAV 2009 R2 in Test Environment so beside taking care of index property wat else should be taken care of ??

  3. Do you see any problem if Tech upgrade is implemented in Live Environment ?

As Full upgrade is not possible for client at the moment so to solve performance issue i decided to technical upgrade as it would give direct advantage regarding

Huge amount of Disk Space would be freed as SIFT Table will be deleted.

Take advantage of several updates done by MS after release of NAV 5.0 to enhance NAV performance on SQL Server by upgrading binary executables.

Please share your views.

Thanks

Dear All,

Can u please guide me how to do SQL Indexing in NAV. My total number of customers 3 laks. while doing general Journal entry its getting time 3 Minutes to drilldown.

Thanks & Regards

Senthil Kumar

(letters2senthil@gmail.com)