unexpected result after index maintenance

pankaj-sharma

Dear Experts

On of my client is using NAV 5.0 and they are having Database on sql2008 of around 530 GB so there main pain area
is both Disk Space & Performance.As suggested I executed index maintenance on value Entry Table but the result was
shocking for me as it increased my Database size.I know that log File are increased during index maintenance job but
what cause to increase 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 increased the pain area of client ](*,) .But the only good thing happened was that
the size of value Entry Table reduced from 129 GB to 122 GB.
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 years 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 altering index/rebuild caused to increase DB size whereas index drop/ index recreate process (optimize)
decrease DB size by increasing free space in DB ?

Thanks in advance

DenSter

Rebuilding indexes on just one table once every few years is not enough. For starters, you need to rebuild ALL indexes AT LEAST every week. Check out this youtube clip to learn about maintenance on SQL Server.

pankaj-sharma

Hello Daniel

I know regular index maintenance is must.Here i am trying to put scenario with single Table. As you can see from my post my concern is :

why there is huge growth in size of data file (.ndf ) after re-indexing (using alter index/rebuild) ?
why DB size decreased after doing optimize operation from navision ??

Because optimize operation is also doing re indexing just the difference is that it is dropping index and then recreating ??

so why there is different impact on DB size with these two methods of re-indexing ??

Marije_Brummel

I think your results make perfect sense.

The index has to be created using new space before the old index can be dropped. Hence the increase.

The new index is smaller since it is less fragmented. Hence the decrease.

With current HDD being like 450GB per spindle and a 530GB Database needs at least 8 of these, size cannot be an issue.

pankaj-sharma

Dear Mark

Navison Optimize function decreased DB size (there is no change in Data file but free space increased so it can be treated as decrease in DB size) but at the same time "Alter index rebuild" that I executed on SQL Server results huge increase in DB size(Data File size increased by 54 GB).

As far as I know navision optimize function also does re-indexing, so I am surprised with different behavior of same task i.e index rebuild :-k

Looking at my scenario it makes sense that optimize function should be preferred over TSQL- "Alter index rebuild " as it doesnot increase size of data File but AFAIK optimize function is overhead as it drops index then recreates it while TSQL "Alter index rebuild " does not drop index so less overhead on SQL server ,but it made my Data File size too big and i am not able to find the reason ](*,) ](*,)

DenSter

pankaj-sharma wrote:

I know regular index maintenance is must

Are you sure? Because you just explained that you did an optimize "a few years back", so to me that means that there has been no maintenance.

pankaj-sharma wrote:

it made my Data File size too big

It will be whatever size it needs to be, there is no 'too big'. You just need to make sure that you have enough storage space. That means if the problem is that the file needs to be a size that is bigger than your current capacity, you need to increase your capacity.

pankaj-sharma

Hello Denster

Thanks for taking interest im my post.

I mean to say that I ran Navison Table Optimize Function few days back for some other Databse but didn't notice any growth in .ndf File size and it increased free space (thus it can be treatd as Decrease in Database Size) but this time when i executed TSQL query Alter index Rebuild (I mentioned complete query on my post) there was huge growth in .ndf file.

As you said there is need of space thus increased sixe of .ndf file ,for that I tested it by
running navision Table optimize function for some another big Table(i.e ItemLedgerEnty) in same Database and this time .ndf file Size was same (there is no increase) but free space also increased.So now why there is no increase in Size of .ndf file ?? :-k

so I am surprised that both operations perform index maintenance(index re-create) but with different behaviour as follows :

1. Navision Table Optimize : Doesnot increase Size of .ndf File but frees some extra space.

2. TSQL Query (Alter index rebuild) : Huge increase in .ndf file

why such different behaviour for same opereation ?

bbrown

You say the increase was around 10%. Are you saying that's huge? One thing that could cause this result is a change in fill-factor. That could cause the rebuilt indexes to take more room.

I'm with Daniel on this. The database is using more space becuase it needs to. Give it the space it needs and move on to other things. Don't create a problem where one does not exist.

pankaj-sharma

Hello bbrown

I didnt specify any Fill Factor and the default fillfactor is 100 thus it doesnot seem the cause of increase in the size of DB File.

Thanks

bbrown

The fillfactor change could have been made sometiem prior to your rebuild. Also, it could be at a more granular level. Anyways just throwing that out there as a possible cause.

But my question is, why is this a problem? is it causing a problem for the client? Or are you just trying to satisfy your curiousity? (OK if you are)

pankaj-sharma

Dear bbrown

There has been no change in Fill Factor as the DB was restored in Test Environment day before I ran MP(Alter index).

It could be problematic for client also as Running MP on single Table caused around 54 GB increase in DB then you can imagine wat will be DB Size if I ran it for all the Tables.

you can also consider it as curosity as same operation (Index Rebuild ) performed by two means have different behaviour :

Table optimization does not change size of Data file & increase free space whereas
MP caused huge icreasein Data file.


Thanks

bbrown

I work with a database that's roughly the same size. We do a nightly reorg/rebuild task and don't see the growth behavior you are describing. There may be small growth, but I can't say. I don't watch it that closely. But I would notice growth on the scale you describe.

It seems your database is not deallocating the old space after buildign the new index. A rebuild first builds the new index (allocating new space for it), then it deallocates the space occupied by the old index.

bbrown

Be interesting to see what the increase (if any) is if you were to do a second rebuild.

DenSter

There's a big difference between "a few years" and "a few days"

Turn on SQL Profiler and catch what happens when you optimize a table from NAV. You can see exactly what the query is and what the difference is when you run your own query.

I've had the database grow like that on rare occasions, and each time it was some sort of fill factor problem. The problem with SQL Server is that in setup it is called 'fill factor' and in the reindex maintenance plan it is called '% free space'. I usually set the fill factor to 85% and once I mistakenly set it up with 85% free space. That database grew a LOT

Also I would have created a test database and run a full reindexing long ago to see what happens. What you think might happen might actually not happen at all. If what you say is correct and your fill factor is 100% I don't see a big problem. That is of course unless you have some other advanced setup going on that you are not telling us about.

pankaj-sharma

hello bbrown
Let you know about the result of re-idexing .

Hello Denster
Let me try the profiler to find out wat exactly is happening inside .

Thankyou all for taking interest.