Best way to reduce table size 2

[icemel]

Ok, we've decided NOT to shrink our database. However, we still have the problem of too much free space in tables.

What is the best way to get rid of the free space? I was thinking just rebuild and repopulate the table.

We only have a few tables with this problem.

Also, what is the best way to avoid free space problems in the future? I know changing data column types to the most efficient type is a good idea. For example, using varchars vs chars and using smalldatetimes. Anything else?

Thanks

 

[SQLDenis]

Defragment your tables and if this doesn't help rebuild your indexes


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLBill]

Defragging will create more free space as it moves the index pages around. To 'reclaim' free space, you really need to use DBCC SHRINKFILE or DBCC SHRINKDATABASE.

Here's the BUT....but you must consider two questions:
1. how much free space do you have?
2. how much does your database grow by?

Free space allows your data to continue to fill the database without having to take resources to expand. So if you have 10 Gig of space and your db grows by 1 GB a day - then the database doesn't need to expand for another 9 or 10 days. If you shrink it, then it will need to grow sooner and maybe more often.

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

Hi, SQLDenis -

What is the proper way to "defragment tables"? I wasn't aware that you could specifically do this in SQL Server, but then, there's a lot I don't know! (I've only been SQL DBA for 3 months now)

And SQLBill, you say in order to reclaim the free space from defragging, you need to run DBCC SHRINKFILE, but won't this UNDO the work you did with defragmenting? I thought this command really fragments indexes and data.

Thanks!

 

[SQLDenis]

By free space are you talking about space on the pages after you run DBCC SHOWCONTIG? If that is the case defragment
If you are talking about DB follow SQLBill's advice

But in general if you have pages with 4K free space I would definitely defragment the table to improve I/O performance



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[icemel]

But in general if you have pages with 4K free space I would definitely defragment the table to improve I/O performance

Yes, but HOW do you go about defragging a TABLE? I see that you can use DBCC commands to defrag indexes, but what about tables?

Thanks

 

[SQLDenis]

The (Clustered) Index is the table

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[icemel]

Hmm, I guess I don't understand that!

How can the clustered index be the table? My understanding is that table data and indexes are completely separate entities.

And what if you don't have a clustered index on the table?

Am I missing something here?

 

[SQLDenis]

nonclustered indexes are not part of the table but have a pointer to the table data
clustered indexes are part of the table and the the data is stored in the index (this is a simple explanation)

That's why when you don't have a clustered index you can't defragment a table

Lookup index in BOL it is explained in more detail and better than what I just wrote here

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[icemel]

Ok, thanks

 

[yelworcm]

A sort of cheat to defragment tables that have no clustered index is to create a clustered index on it, then drop it. Only the index pages are dropped, and the data pages stay in a nice ordered pattern, until you start updating data again. The problem with that is if you create or drop a clustered index, all of the non-clustered indexes have to get updated with new row pointers, which can take all sorts of time for million+ row tables.

 

[icemel]

Ok, some more questions

Defragging will create more free space as it moves the index pages around. To 'reclaim' free space, you really need to use DBCC SHRINKFILE or DBCC SHRINKDATABASE.

So, to clarify, after you run DBCC INDEXDEFRAG, it does or does NOT make the data file smaller?

That is, the free space is still there, but your indexes are more efficient? Is that the only benefit?

Although BOL states:

"DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed."

Which seems to imply that the data file would be physically smaller after this operation.

Also, SQLDenis, could you please tell me where to look in BOL regarding this topic? The topics under that commands don't really explain in detail.

Thanks!

 

[SQLDenis]

we are talking about 2 different free spaces here

When you defragment the pages the pages that were removed become free space in the Database MDF/NDF files

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[icemel]

So, the data file size is not affected?

 

[mrdenny]

We seem to have moved from table size to database file size. Are you trying to manage the free space within the table is self, or the size of the database file?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

FILE SIZE

We want the database (and hence the data file smaller), as the database almost 50% unused space - which means the file is excessively large as well, yes? And I would also think the backups take longer too - is this also correct?

I understand that the database will continue to grow, but we are having a major storage problem at the moment, and NO we cannot add new disks to the server. It's maxed out.

We are upgrading our systems in a few months, but until then, we are sol and need to figure how to free up some space ASAP.

I don't want to add to performance problems by shrinking the database either. We don't need that.

Thanks

 

[hmckillop]

You can change the fillfactor on the indexes and rebuild them. Then run SHRINKFILE and it will reclaim the space.

Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation. When an index page fills up, SQL Server must take time to split the index page to make room for new rows, which is quite expensive. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The value of the original FILLFACTOR is stored with the index in sysindexes.

When FILLFACTOR is specified, SQL Server rounds up the number of rows to be placed on each page. For example, issuing CREATE CLUSTERED INDEX ... FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent. Assume that SQL Server calculates that 5.2 rows is 33 percent of the space on a page. SQL Server rounds so that six rows are placed on each page.
You must drop and recreate your indexes to for this to work.

Also note, this will have performance ramifications, so you should spend a wee bit more time and understand what is going on with the data before doing this.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[yelworcm]

The only command that will affect file size is DBCC SHRINKFILE. OK, Inserts and updates will have the opposite effect in sufficient numbers, but reducing the size of a table or index just leaves more room for other tables and indexes in the same file. Remember, SQL Server (and any other major RDBMS) does not like to grow files. It causes fragmentation on the physical disk, and causes delays while the new diskspace is "formatted" according to the needs of the RDBMS.

 

[hmckillop]

Agreed in the first statement the only command that will effect file size is DBCC SHRINKFILE.
But if all your indexes are padded out with lots of blank pages allowing for future updates, it wont reclaim this space.
In order to do this you rebuild your indexes primarily your clustered index, with a smaller fill factor and then as I stated before use DBCC SHRINKFILE.
But as I also stated it will cause issues for performance and chances are if it is a frequently updated database, the database size will increase anyway.
But at least then you can DBCC reindex and then shrink again.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[icemel]

Hi,

Thanks for all the responses! However, no one has addressed my one critical question:

Why would you run DBCC SHRINKFILE after you rebuild/defragment your indexes?

If running DBCC SHRINKFILE causes index fragmentation as everyone has said, it seems to defeat the purpose of rebuilding the indexes.

Wouldn't you want to shrink the first 1st, THEN run the index rebuild/defrag?

And what about this statment from BOL:

"DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed." >> REMOVED - this seems to imply that it would make the data file smaller.