situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing.
The table was about 1.1 GB in size.
I then created a primary key /clustered index on an
integer value on the new table.
The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it.
I then ran an INSERT INTO statement to copy the origianl
fact table.
The table was 1.1 GB in size and contained the clustered
index.
Why would creating a clustered index increase a table's
size when inserting into a table with an existing
clustered index had no significant size difference?
Doesn't the clustered index actually contain the data
pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBAThis is a multi-part message in MIME format.
--=_NextPart_000_01F7_01C376C4.E95D4FA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
When you create or rebuild a clustered index, SQL Server requires 1.2 X =the size of the table in free space in order to do the sort.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jonathan Lacefield" <Jonathan.lacefield@.solutionbuilders.com> wrote in =message news:002c01c376e5$98532500$a301280a@.phx.gbl...
I have been doing testing on tables sizes and I ran into a situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing. The table was about 1.1 GB in size.
I then created a primary key /clustered index on an integer value on the new table. The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it. I then ran an INSERT INTO statement to copy the origianl fact table.
The table was 1.1 GB in size and contained the clustered index.
Why would creating a clustered index increase a table's size when inserting into a table with an existing clustered index had no significant size difference? Doesn't the clustered index actually contain the data pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBA
--=_NextPart_000_01F7_01C376C4.E95D4FA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
When you create or rebuild a clustered =index, SQL Server requires 1.2 X the size of the table in free space in order to do =the sort.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jonathan Lacefield"
--=_NextPart_000_01F7_01C376C4.E95D4FA0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0025_01C37769.7486AB40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tibor, I don't believe that the space is released after the rebuild. =Let's say you had a single-file filegroups with only one 100MB, =non-indexed table in it. If you created a clustered index on it, I'd =expect the file to be about 220MB when completed. This happens to me =when I rebuild large tables. I see it in my file growth and the free =space available in the file.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:%23G$gqX4dDHA.2804@.TK2MSFTNGP11.phx.gbl...
... but that should be released after the rebuild is done. The question =is, I guess, how Jonathan checked the space used (file or table level)? =If at table level, how was that done (sysindexes was up-to-date or not)? =Also, assuming that able level and correct info, what did DBCC =SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uw9AgZudDHA.3464@.TK2MSFTNGP11.phx.gbl...
When you create or rebuild a clustered index, SQL Server requires 1.2 =X the size of the table in free space in order to do the sort.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jonathan Lacefield" <Jonathan.lacefield@.solutionbuilders.com> wrote =in message news:002c01c376e5$98532500$a301280a@.phx.gbl...
I have been doing testing on tables sizes and I ran into a situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing. The table was about 1.1 GB in size.
I then created a primary key /clustered index on an integer value on the new table. The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it. I then ran an INSERT INTO statement to copy the origianl fact table.
The table was 1.1 GB in size and contained the clustered index.
Why would creating a clustered index increase a table's size when inserting into a table with an existing clustered index had no significant size difference? Doesn't the clustered index actually contain the data pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBA
--=_NextPart_000_0025_01C37769.7486AB40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Tibor, I don't believe that the space =is released after the rebuild. Let's say you had a single-file filegroups with =only one 100MB, non-indexed table in it. If you created a clustered =index on it, I'd expect the file to be about 220MB when completed. This =happens to me when I rebuild large tables. I see it in my file growth and the =free space available in the file.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi"
... but that should be released after =the rebuild is done. The question is, I guess, how Jonathan checked the space used =(file or table level)? If at table level, how was that done (sysindexes was =up-to-date or not)? Also, assuming that able level and correct info, what did DBCC =SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
When you create or rebuild a =clustered index, SQL Server requires 1.2 X the size of the table in free space in order =to do the sort.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jonathan Lacefield"
--=_NextPart_000_0025_01C37769.7486AB40--|||This is a multi-part message in MIME format.
--=_NextPart_000_024D_01C3779C.3FF57400
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom,
I was referring to the space *in* the database file (the "table size"). =Like a copy operation of a file. After you copied the file, the sectors =that the "old" file occupied are available for re-use. I agree that the =database file does not shrink in size after the create/rebuild.
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eiYE$r4dDHA.560@.tk2msftngp13.phx.gbl...
Tibor, I don't believe that the space is released after the rebuild. =Let's say you had a single-file filegroups with only one 100MB, =non-indexed table in it. If you created a clustered index on it, I'd =expect the file to be about 220MB when completed. This happens to me =when I rebuild large tables. I see it in my file growth and the free =space available in the file.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:%23G$gqX4dDHA.2804@.TK2MSFTNGP11.phx.gbl...
... but that should be released after the rebuild is done. The =question is, I guess, how Jonathan checked the space used (file or table =level)? If at table level, how was that done (sysindexes was up-to-date =or not)? Also, assuming that able level and correct info, what did DBCC =SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uw9AgZudDHA.3464@.TK2MSFTNGP11.phx.gbl...
When you create or rebuild a clustered index, SQL Server requires =1.2 X the size of the table in free space in order to do the sort.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jonathan Lacefield" <Jonathan.lacefield@.solutionbuilders.com> wrote =in message news:002c01c376e5$98532500$a301280a@.phx.gbl...
I have been doing testing on tables sizes and I ran into a situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing. The table was about 1.1 GB in size.
I then created a primary key /clustered index on an integer value on the new table. The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it. I then ran an INSERT INTO statement to copy the origianl fact table.
The table was 1.1 GB in size and contained the clustered index.
Why would creating a clustered index increase a table's size when inserting into a table with an existing clustered index had no significant size difference? Doesn't the clustered index actually contain the data pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBA
--=_NextPart_000_024D_01C3779C.3FF57400
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Tom,
I was referring to the space *in* the =database file (the "table size"). Like a copy operation of a file. After you copied =the file, the sectors that the "old" file occupied are available for re-use. I =agree that the database file does not shrink in size after the =create/rebuild.
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
Tibor, I don't believe that the =space is released after the rebuild. Let's say you had a single-file =filegroups with only one 100MB, non-indexed table in it. If you created a =clustered index on it, I'd expect the file to be about 220MB when =completed. This happens to me when I rebuild large tables. I see it in my file =growth and the free space available in the file.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi"
... but that should be released after =the rebuild is done. The question is, I guess, how Jonathan checked the space used =(file or table level)? If at table level, how was that done (sysindexes was up-to-date or not)? Also, assuming that able level and correct info, =what did DBCC SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
When you create or rebuild a =clustered index, SQL Server requires 1.2 X the size of the table in free space in =order to do the sort.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jonathan Lacefield"
--=_NextPart_000_024D_01C3779C.3FF57400--|||This is a multi-part message in MIME format.
--=_NextPart_000_0023_01C37776.32723E60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
OK, perhaps he should run DBCC UPDATEUSAGE (or sp_spaceused MyTable, =true) and see if that gives him the right numbers.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:O2YRAu4dDHA.568@.TK2MSFTNGP11.phx.gbl...
Tom,
I was referring to the space *in* the database file (the "table size"). =Like a copy operation of a file. After you copied the file, the sectors =that the "old" file occupied are available for re-use. I agree that the =database file does not shrink in size after the create/rebuild.
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eiYE$r4dDHA.560@.tk2msftngp13.phx.gbl...
Tibor, I don't believe that the space is released after the rebuild. =Let's say you had a single-file filegroups with only one 100MB, =non-indexed table in it. If you created a clustered index on it, I'd =expect the file to be about 220MB when completed. This happens to me =when I rebuild large tables. I see it in my file growth and the free =space available in the file.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:%23G$gqX4dDHA.2804@.TK2MSFTNGP11.phx.gbl...
... but that should be released after the rebuild is done. The =question is, I guess, how Jonathan checked the space used (file or table =level)? If at table level, how was that done (sysindexes was up-to-date =or not)? Also, assuming that able level and correct info, what did DBCC =SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uw9AgZudDHA.3464@.TK2MSFTNGP11.phx.gbl...
When you create or rebuild a clustered index, SQL Server requires =1.2 X the size of the table in free space in order to do the sort.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jonathan Lacefield" <Jonathan.lacefield@.solutionbuilders.com> wrote =in message news:002c01c376e5$98532500$a301280a@.phx.gbl...
I have been doing testing on tables sizes and I ran into a situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing. The table was about 1.1 GB in size.
I then created a primary key /clustered index on an integer value on the new table. The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it. I then ran an INSERT INTO statement to copy the origianl fact table.
The table was 1.1 GB in size and contained the clustered index.
Why would creating a clustered index increase a table's size when inserting into a table with an existing clustered index had no significant size difference? Doesn't the clustered index actually contain the data pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBA
--=_NextPart_000_0023_01C37776.32723E60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, perhaps he should run DBCC =UPDATEUSAGE (or sp_spaceused MyTable, true) and see if that gives him the right numbers.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tibor Karaszi"
Tom,
I was referring to the space *in* the =database file (the "table size"). Like a copy operation of a file. After you copied =the file, the sectors that the "old" file occupied are available for re-use. I =agree that the database file does not shrink in size after the =create/rebuild.
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
Tibor, I don't believe that the =space is released after the rebuild. Let's say you had a single-file =filegroups with only one 100MB, non-indexed table in it. If you created a =clustered index on it, I'd expect the file to be about 220MB when =completed. This happens to me when I rebuild large tables. I see it in my file =growth and the free space available in the file.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi"
... but that should be released after =the rebuild is done. The question is, I guess, how Jonathan checked the space used =(file or table level)? If at table level, how was that done (sysindexes was up-to-date or not)? Also, assuming that able level and correct info, =what did DBCC SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
When you create or rebuild a =clustered index, SQL Server requires 1.2 X the size of the table in free space in =order to do the sort.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jonathan Lacefield"
--=_NextPart_000_0023_01C37776.32723E60--|||This is a multi-part message in MIME format.
--=_NextPart_000_0009_01C377AA.4FD990F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yep!
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OWw3te5dDHA.3680@.TK2MSFTNGP11.phx.gbl...
OK, perhaps he should run DBCC UPDATEUSAGE (or sp_spaceused MyTable, =true) and see if that gives him the right numbers.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:O2YRAu4dDHA.568@.TK2MSFTNGP11.phx.gbl...
Tom,
I was referring to the space *in* the database file (the "table =size"). Like a copy operation of a file. After you copied the file, the =sectors that the "old" file occupied are available for re-use. I agree =that the database file does not shrink in size after the create/rebuild.
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eiYE$r4dDHA.560@.tk2msftngp13.phx.gbl...
Tibor, I don't believe that the space is released after the rebuild. = Let's say you had a single-file filegroups with only one 100MB, =non-indexed table in it. If you created a clustered index on it, I'd =expect the file to be about 220MB when completed. This happens to me =when I rebuild large tables. I see it in my file growth and the free =space available in the file.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:%23G$gqX4dDHA.2804@.TK2MSFTNGP11.phx.gbl...
... but that should be released after the rebuild is done. The =question is, I guess, how Jonathan checked the space used (file or table =level)? If at table level, how was that done (sysindexes was up-to-date =or not)? Also, assuming that able level and correct info, what did DBCC =SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uw9AgZudDHA.3464@.TK2MSFTNGP11.phx.gbl...
When you create or rebuild a clustered index, SQL Server requires =1.2 X the size of the table in free space in order to do the sort.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jonathan Lacefield" <Jonathan.lacefield@.solutionbuilders.com> =wrote in message news:002c01c376e5$98532500$a301280a@.phx.gbl...
I have been doing testing on tables sizes and I ran into a situation that I do not understand.
My default fill factor is set to 95%.
I used a SELECT INTO to copy a fact table for testing. The table was about 1.1 GB in size.
I then created a primary key /clustered index on an integer value on the new table. The table grew to about 1.9 GB in size.
I then truncated this table but left the index on it. I then ran an INSERT INTO statement to copy the origianl fact table.
The table was 1.1 GB in size and contained the clustered index.
Why would creating a clustered index increase a table's size when inserting into a table with an existing clustered index had no significant size difference? Doesn't the clustered index actually contain the data pages and therefore shouldn't affect the size of the table?
Thanks,
Jonathan Lacefield
MCDBA
--=_NextPart_000_0009_01C377AA.4FD990F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep!
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
OK, perhaps he should run DBCC =UPDATEUSAGE (or sp_spaceused MyTable, true) and see if that gives him the right numbers.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tibor Karaszi"
Tom,
I was referring to the space *in* the =database file (the "table size"). Like a copy operation of a file. After you =copied the file, the sectors that the "old" file occupied are available for =re-use. I agree that the database file does not shrink in size after the create/rebuild.
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
Tibor, I don't believe that the =space is released after the rebuild. Let's say you had a single-file =filegroups with only one 100MB, non-indexed table in it. If you created a = clustered index on it, I'd expect the file to be about 220MB when completed. This happens to me when I rebuild large =tables. I see it in my file growth and the free space available in the =file.
-- Tom
----Thomas =A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi"
... but that should be released =after the rebuild is done. The question is, I guess, how Jonathan checked the =space used (file or table level)? If at table level, how was that done =(sysindexes was up-to-date or not)? Also, assuming that able level and correct =info, what did DBCC SHOWCONTIG say (avg page density)?
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Tom Moreau"
When you create or rebuild a =clustered index, SQL Server requires 1.2 X the size of the table in free =space in order to do the sort.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jonathan Lacefield"
--=_NextPart_000_0009_01C377AA.4FD990F0--|||It will take me a day or two to get the Stats on the
table. I used this table for testing and then dropped it
when I had completed the testing. I appreciate tour help
and info.
THanks
>--Original Message--
>Yep!
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message news:OWw3te5dDHA.3680@.TK2MSFTNGP11.phx.gbl...
> OK, perhaps he should run DBCC UPDATEUSAGE (or
sp_spaceused MyTable, true) and see if that gives him the
right numbers.
> --
> Tom
> ---
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message
news:O2YRAu4dDHA.568@.TK2MSFTNGP11.phx.gbl...
> Tom,
> I was referring to the space *in* the database file
(the "table size"). Like a copy operation of a file. After
you copied the file, the sectors that the "old" file
occupied are available for re-use. I agree that the
database file does not shrink in size after the
create/rebuild.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message news:eiYE$r4dDHA.560@.tk2msftngp13.phx.gbl...
> Tibor, I don't believe that the space is released
after the rebuild. Let's say you had a single-file
filegroups with only one 100MB, non-indexed table in it.
If you created a clustered index on it, I'd expect the
file to be about 220MB when completed. This happens to me
when I rebuild large tables. I see it in my file growth
and the free space available in the file.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> .
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%
23G$gqX4dDHA.2804@.TK2MSFTNGP11.phx.gbl...
> ... but that should be released after the rebuild is
done. The question is, I guess, how Jonathan checked the
space used (file or table level)? If at table level, how
was that done (sysindexes was up-to-date or not)? Also,
assuming that able level and correct info, what did DBCC
SHOWCONTIG say (avg page density)?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in
message news:uw9AgZudDHA.3464@.TK2MSFTNGP11.phx.gbl...
> When you create or rebuild a clustered index, SQL
Server requires 1.2 X the size of the table in free space
in order to do the sort.
> --
> Tom
> ----
--
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Jonathan Lacefield"
<Jonathan.lacefield@.solutionbuilders.com> wrote in message
news:002c01c376e5$98532500$a301280a@.phx.gbl...
> I have been doing testing on tables sizes and I ran
into a
> situation that I do not understand.
> My default fill factor is set to 95%.
> I used a SELECT INTO to copy a fact table for
testing.
> The table was about 1.1 GB in size.
> I then created a primary key /clustered index on an
> integer value on the new table.
> The table grew to about 1.9 GB in size.
> I then truncated this table but left the index on
it.
> I then ran an INSERT INTO statement to copy the
origianl
> fact table.
> The table was 1.1 GB in size and contained the
clustered
> index.
> Why would creating a clustered index increase a
table's
> size when inserting into a table with an existing
> clustered index had no significant size
difference?
> Doesn't the clustered index actually contain the
data
> pages and therefore shouldn't affect the size of
the table?
> Thanks,
> Jonathan Lacefield
> MCDBA
No comments:
Post a Comment