existing clustered index on a single numeric field.
These indexes were added a few weeks ago.
Over the weekend, I imported about a million records into
these two tables (each started with roughly that many
records). Given that, I would have expected to see the
database double in disk space used...instead the database
has QUADRUPLED in size.
It is currently taking up 11 GB of space with 0
available.
I did a test run of this import a few weeks ago and when
I check that database, it shows that its taking up 6.4 GB
with 1.8 GB available. The really wierd thing is that
record counts for all the tables in the two databases are
within less than one percent of each other...so they both
have basically the same number of records but one is
taking up twice the space as the other.
I know that when you add a clustered index to an existing
table that the engine needs twice the allocated space to
perform the clustered indexing of the records in the
table. So i was wondering, since I've added so many
records...
Is it possible that SQL Server is having to rebuild the
table structure somewhat akin to when the index was
created in the first place?
That would account for the drastic difference in actual
versus expected disk space usage. It would also account
for why the previous imported database is showing the
expected size...since it has had a chance to rebuild
itself and release the extra space.
I would have thought that the engine would add the
records properly sorted to avoid having to do this
rebuild operation but I have no other explanation for the
situation.
Please help!!!!
(and thanks in advance for any help)Tom,
You don't say how you imported the new rows, which does make a difference.
However, yes, an insert that caused page and extext splits will eat up more
space than the data alone would suggest.
Check your table(s) by using DBCC SHOWCONTIG to check the density of storage
on each server. If, as I suspect, your second server is fairly loosely
packed, then you will need to do a DBCC DBREINDEX.
Russell Fields
"Tom Russell" <trussell@.incentusbenefits.com> wrote in message
news:005e01c36b27$1b28ef70$a401280a@.phx.gbl...
> I have a database with two large tables both with an
> existing clustered index on a single numeric field.
> These indexes were added a few weeks ago.
> Over the weekend, I imported about a million records into
> these two tables (each started with roughly that many
> records). Given that, I would have expected to see the
> database double in disk space used...instead the database
> has QUADRUPLED in size.
> It is currently taking up 11 GB of space with 0
> available.
> I did a test run of this import a few weeks ago and when
> I check that database, it shows that its taking up 6.4 GB
> with 1.8 GB available. The really wierd thing is that
> record counts for all the tables in the two databases are
> within less than one percent of each other...so they both
> have basically the same number of records but one is
> taking up twice the space as the other.
> I know that when you add a clustered index to an existing
> table that the engine needs twice the allocated space to
> perform the clustered indexing of the records in the
> table. So i was wondering, since I've added so many
> records...
>
> Is it possible that SQL Server is having to rebuild the
> table structure somewhat akin to when the index was
> created in the first place?
>
>
> That would account for the drastic difference in actual
> versus expected disk space usage. It would also account
> for why the previous imported database is showing the
> expected size...since it has had a chance to rebuild
> itself and release the extra space.
> I would have thought that the engine would add the
> records properly sorted to avoid having to do this
> rebuild operation but I have no other explanation for the
> situation.
> Please help!!!!
>
> (and thanks in advance for any help)|||This is a multi-part message in MIME format.
--=_NextPart_000_002D_01C36B19.4B2CAF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My $0.02 - has a DBCC DBREINDEX already been done on these tables? =Since you need 1.2 X <table size> in free space to rebuild a clustered =index, this would account for the database being about 2.5X the size of =the table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Russell" <trussell@.incentusbenefits.com> wrote in message =news:02f901c36b39$ebc044a0$a301280a@.phx.gbl...
Thanks a ton for responding, Russell.
I imported the new data via a VB app that just iterates through a file and gathers data to be inserted into the relevant tables. The clustered column is a bigint value that is used to tie the two tables together. Its always increasing (impersonates an identity field) so, theoretically anyway, as records are added the order on this column should be maintained.
As far as splits\etc go, that makes sense that you would see some percentage of overhead over and above just the amount of raw data. But would this type of overhead account for 2 - 2.5 times the space required for just the data?
I ran SHOWCONTIG...for the two tables in question, here are the results:
Table: 'Claim' (533576939); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned........................: 165102
- Extents Scanned.......................: 20755
- Extent Switches.......................: 20874
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.86% [20638:20875]
- Logical Scan Fragmentation ..............: 0.10%
- Extent Scan Fragmentation ...............: 39.28%
- Avg. Bytes Free per Page................: 272.7
- Avg. Page Density (full)................: 96.63%
DBCC SHOWCONTIG scanning 'ClaimServiceLines' table...
Table: 'ClaimServiceLines' (549576996); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned........................: 180601
- Extents Scanned.......................: 22704
- Extent Switches.......................: 22903
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.57% [22576:22904]
- Logical Scan Fragmentation ..............: 0.15%
- Extent Scan Fragmentation ...............: 44.24%
- Avg. Bytes Free per Page................: 181.0
- Avg. Page Density (full)................: 97.76%
Which appear to be within reason...also, the available space is 0 when i look at the properties for this database.
Any thoughts? Should i reindex?
>--Original Message--
>Tom,
>You don't say how you imported the new rows, which does make a difference.
>However, yes, an insert that caused page and extext splits will eat up more
>space than the data alone would suggest.
>Check your table(s) by using DBCC SHOWCONTIG to check the density of storage
>on each server. If, as I suspect, your second server is fairly loosely
>packed, then you will need to do a DBCC DBREINDEX.
>Russell Fields
--=_NextPart_000_002D_01C36B19.4B2CAF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My $0.02 - has a DBCC DBREINDEX =already been done on these tables? Since you need 1.2 X in free =space to rebuild a clustered index, this would account for the database being =about 2.5X the size of the table.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Russell"
--=_NextPart_000_002D_01C36B19.4B2CAF80--|||Thanks for the info, Tom.
I suspect that that is what is occuring. I haven't
issued a DBREINDEX myself.
But I was wondering if given the number of records
recently added that perhaps the db engine itself forced
some kind of reindex?
However, I thought that the new records should have been
added in sorted order by SQL SERVER in order to avoid
rebuilding the clustered IDX. So at this point, I'm not
sure what to think...
Are there any administrative functions that I can use to
see what is going on with this issue?
>--Original Message--
>My $0.02 - has a DBCC DBREINDEX already been done on
these tables? Since you need 1.2 X <table size> in free
space to rebuild a clustered index, this would account
for the database being about 2.5X the size of the table.
>--
>Tom
>|||This is a multi-part message in MIME format.
--=_NextPart_000_008B_01C36B1E.36681E90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Another thought - have you created a database maintenance plan? If so, =it is possible that the index was rebuilt through that mechanism.
You can take a look at the STATS_DATE() function. Index stats are =updated when the index is rebuilt. However, stats can be updated =without rebuilding an index, so it may be of little help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Russell" <trussell@.incentusbenefits.com> wrote in message =news:0bc101c36b3e$bfc63c10$a001280a@.phx.gbl...
Thanks for the info, Tom.
I suspect that that is what is occuring. I haven't issued a DBREINDEX myself.
But I was wondering if given the number of records recently added that perhaps the db engine itself forced some kind of reindex?
However, I thought that the new records should have been added in sorted order by SQL SERVER in order to avoid rebuilding the clustered IDX. So at this point, I'm not sure what to think...
Are there any administrative functions that I can use to see what is going on with this issue?
>--Original Message--
>My $0.02 - has a DBCC DBREINDEX already been done on these tables? Since you need 1.2 X <table size> in free space to rebuild a clustered index, this would account for the database being about 2.5X the size of the table.
>-- >Tom
>
--=_NextPart_000_008B_01C36B1E.36681E90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Another thought - have you created a =database maintenance plan? If so, it is possible that the index was rebuilt =through that mechanism.
You can take a look at the =STATS_DATE() function. Index stats are updated when the index is rebuilt. =However, stats can be updated without rebuilding an index, so it may be =of little help.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Russell"
--=_NextPart_000_008B_01C36B1E.36681E90--|||> But I was wondering if given the number of records
> recently added that perhaps the db engine itself forced
> some kind of reindex?'
No, SQL Server does not do this by itself.
An option to DBREINDEX is DBCC INDEXDFRAG. As it only "shuffle pages" it doesn't require remoely as
much free space. And it generally don't produce as much log records.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Tom Russell" <trussell@.incentusbenefits.com> wrote in message
news:0bc101c36b3e$bfc63c10$a001280a@.phx.gbl...
> Thanks for the info, Tom.
> I suspect that that is what is occuring. I haven't
> issued a DBREINDEX myself.
> But I was wondering if given the number of records
> recently added that perhaps the db engine itself forced
> some kind of reindex?
> However, I thought that the new records should have been
> added in sorted order by SQL SERVER in order to avoid
> rebuilding the clustered IDX. So at this point, I'm not
> sure what to think...
> Are there any administrative functions that I can use to
> see what is going on with this issue?
>
>
>
>
> >--Original Message--
> >My $0.02 - has a DBCC DBREINDEX already been done on
> these tables? Since you need 1.2 X <table size> in free
> space to rebuild a clustered index, this would account
> for the database being about 2.5X the size of the table.
> >
> >--
> >Tom
> >
>
No comments:
Post a Comment