Prev: sp_delete_backuphistory, how does it work
Next: Microsoft Responds to the Evolution of Online Communities
From: stavros on 4 May 2010 19:35
Hi,

I have a database that's a little over 1 TB in size. Here's the
output of sp_spaceused:

database_name database_size unallocated space
MyBigDB 1331068.69 MB 403552.74 MB

reserved data index_size unused
935236744 KB 717736272 KB 216731360 KB 769112 KB

About half of the reserved space is taken up by one table, and
figuring out a maintenance strategy for this table is my problem.

Currently, the database is pretty well defragmented, as you can see
from the ratio of reserved to unused space. The problem is that I
have other databases on this server, and I can't afford to perpetually
dedicate 400 GB of unallocated space to this database.

The data files are about 60% full. 15% - 20% full would be plenty
enough extra room to account for data growth for the foreseeable
future. Of course, if I shrink the data files, the fragmentation on
my large table (expectedly) goes through the roof. If I then rebuild
the clustered index on my large table, the fragmentation is resolved,
but the data files grow out of control and I'm again faced with all
this wasted space. Rebuilding with SORT_IN_TEMPDB = ON makes no
difference.

What's a good management strategy here? I can see needing an extra
400 GB of space when I want to rebuild this index, but isn't there any
way I can reclaim that space without fragmenting the data all over
again? It would be surprising and unfortunate if SQL Server required
that much unallocated space, given that my data growth might not fill
that in 10 years. What's a "reasonable" amount of unallocated space?

BTW, it may be noteworthy that an "alter index rebuild" finishes in
about an hour, but I always end up killing an "alter index reorganize"
on this table after it runs for 8 hours or so. That seems contrary to
what I've read about the two processes, but I've never figured out
what's going on.

TIA...

Stavros
From: Greg Linwood on 4 May 2010 22:11
Hi Stavros

Any chance you could post the DDL for the table + indexes?

If the table has a Clustered Index, you might have no choice but to keep
defragging / rebuilding it b/c ordering a table's storage by definition
means that there's no way to reclaim space other than defrag or rebuild.
Storing the table on a HEAP doesn't have this problem (as row storage
location isn't defined & therefore space is reclaimed naturally) so changing
it's structure might help you live without dependence on defragging

Cheers,
Greg Linwood
SQL Server MVP

"stavros" <stavros(a)mailinator.com> wrote in message
news:1a076a35-d044-4e54-bee3-80bef0e81b55(a)k17g2000pro.googlegroups.com...
> Hi,
>
> I have a database that's a little over 1 TB in size. Here's the
> output of sp_spaceused:
>
> database_name database_size unallocated space
> MyBigDB 1331068.69 MB 403552.74 MB
>
> reserved data index_size unused
> 935236744 KB 717736272 KB 216731360 KB 769112 KB
>
> About half of the reserved space is taken up by one table, and
> figuring out a maintenance strategy for this table is my problem.
>
> Currently, the database is pretty well defragmented, as you can see
> from the ratio of reserved to unused space. The problem is that I
> have other databases on this server, and I can't afford to perpetually
> dedicate 400 GB of unallocated space to this database.
>
> The data files are about 60% full. 15% - 20% full would be plenty
> enough extra room to account for data growth for the foreseeable
> future. Of course, if I shrink the data files, the fragmentation on
> my large table (expectedly) goes through the roof. If I then rebuild
> the clustered index on my large table, the fragmentation is resolved,
> but the data files grow out of control and I'm again faced with all
> this wasted space. Rebuilding with SORT_IN_TEMPDB = ON makes no
> difference.
>
> What's a good management strategy here? I can see needing an extra
> 400 GB of space when I want to rebuild this index, but isn't there any
> way I can reclaim that space without fragmenting the data all over
> again? It would be surprising and unfortunate if SQL Server required
> that much unallocated space, given that my data growth might not fill
> that in 10 years. What's a "reasonable" amount of unallocated space?
>
> BTW, it may be noteworthy that an "alter index rebuild" finishes in
> about an hour, but I always end up killing an "alter index reorganize"
> on this table after it runs for 8 hours or so. That seems contrary to
> what I've read about the two processes, but I've never figured out
> what's going on.
>
> TIA...
>
> Stavros

From: Uri Dimant on 5 May 2010 13:35
stavros
What takes space and time is rebuilding NCI as they have pointers to the
actual data, take a look at the size of the indexes and I agree with Greg ,
it would be nice seeing your table definition


"stavros" <stavros(a)mailinator.com> wrote in message
news:1a076a35-d044-4e54-bee3-80bef0e81b55(a)k17g2000pro.googlegroups.com...
> Hi,
>
> I have a database that's a little over 1 TB in size. Here's the
> output of sp_spaceused:
>
> database_name database_size unallocated space
> MyBigDB 1331068.69 MB 403552.74 MB
>
> reserved data index_size unused
> 935236744 KB 717736272 KB 216731360 KB 769112 KB
>
> About half of the reserved space is taken up by one table, and
> figuring out a maintenance strategy for this table is my problem.
>
> Currently, the database is pretty well defragmented, as you can see
> from the ratio of reserved to unused space. The problem is that I
> have other databases on this server, and I can't afford to perpetually
> dedicate 400 GB of unallocated space to this database.
>
> The data files are about 60% full. 15% - 20% full would be plenty
> enough extra room to account for data growth for the foreseeable
> future. Of course, if I shrink the data files, the fragmentation on
> my large table (expectedly) goes through the roof. If I then rebuild
> the clustered index on my large table, the fragmentation is resolved,
> but the data files grow out of control and I'm again faced with all
> this wasted space. Rebuilding with SORT_IN_TEMPDB = ON makes no
> difference.
>
> What's a good management strategy here? I can see needing an extra
> 400 GB of space when I want to rebuild this index, but isn't there any
> way I can reclaim that space without fragmenting the data all over
> again? It would be surprising and unfortunate if SQL Server required
> that much unallocated space, given that my data growth might not fill
> that in 10 years. What's a "reasonable" amount of unallocated space?
>
> BTW, it may be noteworthy that an "alter index rebuild" finishes in
> about an hour, but I always end up killing an "alter index reorganize"
> on this table after it runs for 8 hours or so. That seems contrary to
> what I've read about the two processes, but I've never figured out
> what's going on.
>
> TIA...
>
> Stavros


 | 
Pages: 1
Prev: sp_delete_backuphistory, how does it work
Next: Microsoft Responds to the Evolution of Online Communities