Normally, clustering is setup as a 1-to-1 relationship between active and
passive nodes, at least that is my understanding. We have a situation where
we are being asked to setup a 2 active node, 1 passive node environment.
Here's the question - if one of the active nodes fails, it fails over to the
passive node. Now what happens if the 2nd active node fails? Will the other
active node (originally the passive node) handle the failover or will it try
to failover to the first active node that failed or will the whole thing just
grind to a screeching halt? I know we can setup a 2 active/ 2passive node
setup, but we wanted to research the 2-to-1 possibility as well.
Thanks!
Message posted via http://www.droptable.com
Hi
You don't need a 1:1 relationship between nodes.
And forget the terms active / passive, it is totally out of date and does
not describe SQL Server 2000/2005 clustering.
You have an instance of SQL Server running on an node.
If you have 2 nodes, it could run on any of those nodes at any one time. If
you had 3 nodes, it could run on any of those nodes, at any one time.
If you have 2 nodes, one sits around waiting for a failover. If you have 3
nodes, 2 sit around waiting for a failover.
If you have more than one SQL Server instance running on the cluster, then
the instances can run on any of the nodes.
The instances could be all running on the same node, or dispersed over the
various nodes.
Quite often, people run with 3 nodes and 2 instances or 4 nodes with 3
instances. There is nothing stopping you running 3 instances on 3 nodes, 4
instances on 4 nodes, or any combination of up to 16 instances over 4 nodes.
You just need to make sure that you have enough resources, that in worst
case, one node could run all instances.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael G via droptable.com" <forum@.droptable.com> wrote in message
news:541CE6C54F90F@.droptable.com...
> Normally, clustering is setup as a 1-to-1 relationship between active and
> passive nodes, at least that is my understanding. We have a situation
> where
> we are being asked to setup a 2 active node, 1 passive node environment.
> Here's the question - if one of the active nodes fails, it fails over to
> the
> passive node. Now what happens if the 2nd active node fails? Will the
> other
> active node (originally the passive node) handle the failover or will it
> try
> to failover to the first active node that failed or will the whole thing
> just
> grind to a screeching halt? I know we can setup a 2 active/ 2passive node
> setup, but we wanted to research the 2-to-1 possibility as well.
> Thanks!
>
> --
> Message posted via http://www.droptable.com
|||Very helpful. Question: So now, even with RAID 10, the single point of
failure becomes the shared drive array? How does one go about creating a
failover for this?
Respectfully,
Michael Wiederhold
michael@.auctionarms.com
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%230j6BCZtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi
> You don't need a 1:1 relationship between nodes.
> And forget the terms active / passive, it is totally out of date and does
> not describe SQL Server 2000/2005 clustering.
> You have an instance of SQL Server running on an node.
> If you have 2 nodes, it could run on any of those nodes at any one time.
> If you had 3 nodes, it could run on any of those nodes, at any one time.
> If you have 2 nodes, one sits around waiting for a failover. If you have 3
> nodes, 2 sit around waiting for a failover.
> If you have more than one SQL Server instance running on the cluster, then
> the instances can run on any of the nodes.
> The instances could be all running on the same node, or dispersed over the
> various nodes.
> Quite often, people run with 3 nodes and 2 instances or 4 nodes with 3
> instances. There is nothing stopping you running 3 instances on 3 nodes, 4
> instances on 4 nodes, or any combination of up to 16 instances over 4
> nodes. You just need to make sure that you have enough resources, that in
> worst case, one node could run all instances.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Michael G via droptable.com" <forum@.droptable.com> wrote in message
> news:541CE6C54F90F@.droptable.com...
>
|||Hi
Most high end SAN's have a block level copy mechanism so that you can
"mirror" that data to another SAN, preferably at another location.
EMC has SRDF that can run synchronous or asynchronous over fast WAN links.
Tape backups must never be forgotten either.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"michael wiederhold" <michael@.auctionarms.com> wrote in message
news:%23RhSzuztFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Very helpful. Question: So now, even with RAID 10, the single point of
> failure becomes the shared drive array? How does one go about creating a
> failover for this?
> Respectfully,
> Michael Wiederhold
> michael@.auctionarms.com
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%230j6BCZtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks. Really do appreciate your participation in this newsgroup.
Respectfully,
Michael Wiederhold
michael@.auctionarms.com
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ONHGyz8tFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Most high end SAN's have a block level copy mechanism so that you can
> "mirror" that data to another SAN, preferably at another location.
> EMC has SRDF that can run synchronous or asynchronous over fast WAN links.
> Tape backups must never be forgotten either.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "michael wiederhold" <michael@.auctionarms.com> wrote in message
> news:%23RhSzuztFHA.3068@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts
Friday, February 24, 2012
Tuesday, February 14, 2012
Clustered index with a truncate table operation question.
I have a situation at here that appears once in a while:
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:
> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:
> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>
Friday, February 10, 2012
Clustered index and file space question
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
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" wrote in message news:002c01c376e5$98=532500$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 LacefieldMCDBA
--=_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" 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 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"= 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
=---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" wrote in message news:002c01c376e5$98=532500$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 LacefieldMCDBA
--=_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"= wrote in message news:eiYE$r4dDHA.560@.t=k2msftngp13.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, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi" 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 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"= 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
=---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" wrote in message news:002c01c376e5$98=532500$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 LacefieldMCDBA
--=_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" wrote in message news:O2YRAu4dDHA.568@.T=K2MSFTNGP11.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 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"= wrote in message news:eiYE$r4dDHA.560@.t=k2msftngp13.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, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi" 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 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"= 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
=---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" wrote in message news:002c01c376e5$98=532500$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 LacefieldMCDBA
--=_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"= 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
=---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" wrote in message news:O2YRAu4dDHA.568@.T=K2MSFTNGP11.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 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"= wrote in message news:eiYE$r4dDHA.560@.t=k2msftngp13.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, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Tibor Karaszi" 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 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"= 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
=---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" wrote in message news:002c01c376e5$98=532500$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 LacefieldMCDBA
--=_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
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
Subscribe to:
Posts (Atom)