filegroups / reindex

Hi,

We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.

any ideas?
Derk Jan

Does it mean that your db has 2 filegroup - one for the data and one
for the indexes.  And after you loaded the data in, they were
distributed equally.  But after the reindex, the proportion changed and
no longer half half?

Index has two kind - clustered and non-clustered.  Clustered is with
tables (data itself stored in an order of the index).  Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup.  The size of non-clustered index varies depends on what
columns you have indexed on.  The more columns you index, the bigger
the index.

After re-index, the size will change (unless no modification has been
made).  So what you observe is normal to me or I must miss sth from
your post.

Mel

configuration as as you described it.

In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.

tanx,
Dick

"MSLam" wrote:

> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes.  And after you loaded the data in, they were
> distributed equally.  But after the reindex, the proportion changed and
> no longer half half?
>
> Index has two kind - clustered and non-clustered.  Clustered is with
> tables (data itself stored in an order of the index).  Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup.  The size of non-clustered index varies depends on what
> columns you have indexed on.  The more columns you index, the bigger
> the index.
>
> After re-index, the size will change (unless no modification has been
> made).  So what you observe is normal to me or I must miss sth from
> your post.
>
> Mel
>
>

Dick,

When you said data was loaded?  Did you put index on before the data
was loaded?  Did you turn it on afterwards?  What tool did you use to
load the data in?

Mel

Hi Lam,

Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.

cheers,
Dick

"MSLam" wrote:

> Dick,
>
> When you said data was loaded?  Did you put index on before the data
> was loaded?  Did you turn it on afterwards?  What tool did you use to
> load the data in?
>
> Mel
>
>

The only thing I could think of is the fillfactor field.  The
fillfactor may have be changed during the rebuild?

fillfactor

Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.

I assume you use DBCC REINDEX.

Mel

Hi,

Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.

Dick

"MSLam" wrote:

> The only thing I could think of is the fillfactor field.  The
> fillfactor may have be changed during the rebuild?
>
> fillfactor
>
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
>
> I assume you use DBCC REINDEX.
>
> Mel
>
>