I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Renu Doda" <renu.doda@.newsgroup.nospam> wrote in message
news:%23kQjWN3oFHA.2976@.TK2MSFTNGP12.phx.gbl...
I have a DB which has tables for each month with about 9 million rows each.
Now after that we create a clustered index on the table with specific
required columns. The issue is that the primary datafile doesn't grow much
every month but the secondary file, which has only indexes is huge abt 70
GB. Every month as the previous month's data is not needed on a regular
basis, I tried dropping the clustered index in a hope that this might save
some space but of no use.
Any advice?|||This is a multi-part message in MIME format.
--060606090504010006000906
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto each
data page, which may mean your 70G may come down a little (if there was
empty space already, for example if the clustered indexes were built
with a fill factor less than 100%) but it may not.
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page split)
onto 2 new pages in the index, each being 50% full, so it can fit the
new/changed data in the correct location in the index), which will mean
increased I/O against those clustered indexes. But I'm assuming data
from past months won't change much so presumedly there won't be many
changes to make to the clustered keys.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:
>When you create a clustered index, the leaf pages are the data pages. Thus,
>if you create a clustered index and specify the filegroup, the data pages
>will go into that filegroup.
>
>
--060606090504010006000906
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, the fact of the matter is you have about 70G of data (ie. not
indexes - as Tom says a clustered index IS the data itself) and it has
to go somewhere.<br>
<br>
You may be able to squish it a little by rebuilding the clustered
index(es) in question and specifying a fill factor of 100% to ensure
you're not leaving any empty space on each data page (ie. leaf node of
the clustered index). That will cram as much data as possible onto
each data page, which may mean your 70G may come down a little (if
there was empty space already, for example if the clustered indexes
were built with a fill factor less than 100%) but it may not.<br>
<br>
A downside to 100% fill factors though is if there are changes to the
clustered keys or inserts, then you'll get quite a lot of page splits
because there's no free space on each data page to put the new/changed
key (so SQL Server has to split an existing page (known as a page
split) onto 2 new pages in the index, each being 50% full, so it can
fit the new/changed data in the correct location in the index), which
will mean increased I/O against those clustered indexes. But I'm
assuming data from past months won't change much so presumedly there
won't be many changes to make to the clustered keys.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tom Moreau wrote:
<blockquote cite="miduJA4xn3oFHA.3988@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">When you create a clustered index, the leaf pages are the data pages. Thus,
if you create a clustered index and specify the filegroup, the data pages
will go into that filegroup.
</pre>
</blockquote>
</body>
</html>
--060606090504010006000906--|||In addition to the other replies: if you drop the clustered index, the
data will remain in the filegroup of the clustered index. To move the
data to another filegroup you would have to recreate the index on the
desired filegroup (easiest with CREATE ... CLUSTERED INDEX ... WITH
DROP_EXISTING).
Gert-Jan
Renu Doda wrote:
> I have a DB which has tables for each month with about 9 million rows each.
> Now after that we create a clustered index on the table with specific
> required columns. The issue is that the primary datafile doesn't grow much
> every month but the secondary file, which has only indexes is huge abt 70
> GB. Every month as the previous month's data is not needed on a regular
> basis, I tried dropping the clustered index in a hope that this might save
> some space but of no use.
> Any advice?
