Freakin X-files type unexplained bull crap...

People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB. I shrink the data
base. The database size is now back to 77MB. YIKES! Does the clustered index
take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold eve
rything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typ
ical MS crap?
Thanks.No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the f
act that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks|||Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks