mibuso.com

Microsoft Business Solutions online community
It is currently Sun May 26, 2013 12:14 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: unexpected result after index maintenance
PostPosted: Sat Jul 21, 2012 11:22 am 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Sun Jul 22, 2012 4:06 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Sun Jul 22, 2012 7:43 am 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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 ??


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Sun Jul 22, 2012 10:32 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 3849
Location: Olst
Country: Netherlands (nl)
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.

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Developer
Author of the book : Microsoft Dynamics NAV 2009 Application Design

MY BLOG : http://www.brummelds.com


Top
 Profile E-mail WWW  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Sun Jul 22, 2012 5:03 pm 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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 ](*,) ](*,)


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 4:35 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 8:26 am 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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 ?


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 1:25 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 2:05 pm 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 2:13 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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)

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 2:47 pm 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 3:33 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 3:39 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
Be interesting to see what the increase (if any) is if you were to do a second rebuild.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 3:43 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7931
Location: Howell, MI
Country: United States (us)
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 :mrgreen:

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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: unexpected result after index maintenance
PostPosted: Mon Jul 23, 2012 8:28 pm 
Offline

Joined: Sat Jul 21, 2012 10:01 am
Posts: 7
Country: India (in)
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.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: