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.This is a multi-part message in MIME format.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
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.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 =3D 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
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:746=F0D84-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=3Dtrue. 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.
--=_NextPart_000_0095_01C3F0B0.98CB5A80--|||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 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, yo
need 1.2X the size of the table's data in free space available. Thi
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once th
clustered index has been created, it frees up that space again. Thus, you
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
-
To
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDB
SQL Server MV
Columnist, SQL Server Professiona
Toronto, ON Canad
www.pinnaclepublishing.com/sq
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in messag
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. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB. I shrink the database. The database size is now back t
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. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB
I run sp_spaceused @.updateusage=true. The database size is now 135MB and th
space available is 74MB. Want more? I shrink the database and lo behol
everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
typical MS crap
Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
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
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:841=8A72E-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 =3D 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 http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql ="Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com... &nb=sp; 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=3Dtrue. 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
--=_NextPart_000_0104_01C3F0B4.E5AA0410--|||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|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Don Peterson"
--=_NextPart_000_0027_01C3F0C9.D41032F0--
No comments:
Post a Comment