Index file grown bigger than data file day by day

Hi all,
I have a serious problem:
I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all index
after a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem with my database.
I have some questions:
1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?
Thanks in advanced
TN"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
Here's how to show the allocation of objects to file groups.
SELECT
OBJECT_NAME(id) TABLE_NAME,
CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
ORDER BY TABLE_NAME, INDEX_NAME
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
The distinction between data and index data is shaky at best. It's common
practice in Sql Server to use clustered indexes on many tables. Any table
with a clustered index has nothing but "index data". Queries will use
multiple indexes as often as they will use an index and "table data".
David|||When laying out tables accross filegroups, it's really important to
understand the difference between clustered and non-clustered indexes. When
a clustered index exists on a table, it actually IS the table so if you
intended to seperate row storage from index storage, you probably meant to
store the clustered indexes on the primary filegroup and the non-clustered
indexes on the index filegroup.
Regards,
Greg Linwood
SQL Server MVP
"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
> Thanks in advanced
> TN|||Hi David,
Thanks for your advice.
I do not know which is better between : index and data are stored in the
same file (data file ) or index and data are stored in separately 2 file
(index file and data file).
Please tell me what should I change to reduce index file growth.
Thanks
TN
"David Browne" wrote:
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> > Hi all,
> >
> > I have a serious problem:
> >
> > I created 1 database with 2 file group : 1 primary and 1 index.
> > - Primary file group includes 1 data file (*.mdf): store all tables
> > - Index file group includes 1 index file (*.ndf): store all index
> >
> > after a short time using, data file is 2GB but index file is 12 GB.
> > I do not know what problem with my database.
> > I have some questions:
> >
> > 1/ How so I reduce size of index file ?
> > 2/ How to know what is stored in index file?
> Here's how to show the allocation of objects to file groups.
> SELECT
> OBJECT_NAME(id) TABLE_NAME,
> CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
> FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
> dpages PAGES
> FROM sysindexes
> ORDER BY TABLE_NAME, INDEX_NAME
>
> > 3/ How to trace all impact to index file?
> > 4/ How to limit size growing of index file?
> The distinction between data and index data is shaky at best. It's common
> practice in Sql Server to use clustered indexes on many tables. Any table
> with a clustered index has nothing but "index data". Queries will use
> multiple indexes as often as they will use an index and "table data".
> David
>
>|||"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
Watch your *clustered* indexes.
You've likely created your clustered indexes on your index file
group. If you did, your data is on the index file group and not
on your data file group.
You'll want to put all of your indexes *except* your clustered
indexes on the index file group. Your *clustered* indexes should
be created on the data (primary) filegroup.|||Hi Sgt. Sausage,
Most of indexes in my database are non-cluster indexes and created on Index
filegroup.
Please help me more.
Thanks
TN
"Sgt. Sausage" wrote:
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> > Hi all,
> >
> > I have a serious problem:
> >
> > I created 1 database with 2 file group : 1 primary and 1 index.
> > - Primary file group includes 1 data file (*.mdf): store all tables
> > - Index file group includes 1 index file (*.ndf): store all index
> >
> > after a short time using, data file is 2GB but index file is 12 GB.
> > I do not know what problem with my database.
> > I have some questions:
> >
> > 1/ How so I reduce size of index file ?
> > 2/ How to know what is stored in index file?
> > 3/ How to trace all impact to index file?
> > 4/ How to limit size growing of index file?
> Watch your *clustered* indexes.
> You've likely created your clustered indexes on your index file
> group. If you did, your data is on the index file group and not
> on your data file group.
> You'll want to put all of your indexes *except* your clustered
> indexes on the index file group. Your *clustered* indexes should
> be created on the data (primary) filegroup.
>
>|||first thing, what is your physical disk setup? if you are on raid 5 or
raid 0+1 then having seperate file groups does nothing for performance.
if you are on raid 1 and have 2 seperate mirrored drive pairs, then
seperate file groups would help. filegroup seperation is mainly used
for backup and recovery purposes or if you are in a non-raid setup. if
you are on a raid setup, i would move everything back to one filegroup
and be done with it.
as stated above, you have to make sure that all of your clustered
indexes are on your primary filegroup, otherwise the data will be in
the wrong filegroup. indexes have what is called a leaf level...in a
clusterd index, that level is the actual data.....so, when you tell
sql server to create an index on a file group, if it happens to be a
clustered index, it will move the table over as well. i am not sure of
you index file group name so you will have to edit this, but this is
the code from above slightly modified:
SELECT
OBJECT_NAME(id) as TABLE_NAME,
NAME as INDEX_NAME,
FILEGROUP_NAME(groupid) as FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
WHERE FILEGROUP_NAME(groupid) = '<name_of_index_filegroup>'
AND indid in (0,1)
ORDER BY TABLE_NAME, INDEX_NAME
this will let you know if you have any clustered indexes on the wrong
filegroup.
i cannot tell you why your databases is growing so rapidly without
knowing more detail:
remember, indexes are for select statements. if you have a heavy dml
(updates and inserts) then your indexes are going to hurt your system.
hth,
hans nelsen