Table size not reducing after large data delete and index rebuid

Question

Post reply

Table size not reducing after large data delete and index rebuid

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 1

Thanks for this useful script Eric!

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

I believe that performing a clustered index reorganization is always an online operation, and LOB_COMPACTION (on by default) will also reclaim deallocated LOB pages, which is what Kumar really needs.

What do you guys think?

ALTER INDEX

http://technet.microsoft.com/en-us/library/ms188388.aspx

REORGANIZE
Specifies the index leaf level will be reorganized. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

ALTER INDEX PK_ProductPhoto_ProductPhotoID

ON Production.ProductPhoto

REORGANIZE WITH ( LOB_COMPACTION = ON );

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Eric M Russell (4/23/2014)
I believe that performing a clustered index reorganization is always an online operation, and LOB_COMPACTION (on by default) will also reclaim deallocated LOB pages, which is what Kumar really needs.
What do you guys think?
ALTER INDEX
http://technet.microsoft.com/en-us/library/ms188388.aspx
REORGANIZE
Specifies the index leaf level will be reorganized. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.
ALTER INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE WITH ( LOB_COMPACTION = ON );

Certainly should work.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

Also SORT_IN_TEMPDB = { ON | OFF } depending on which filegroup (default or tempdb) has available free work space.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

S_Kumar_S (4/23/2014)
Jeff
replying to your questions:
1.This table is more like your OrderDetail table. There might be some updates daily but very few records (may be less than 50) get updated daily.
2.I have enough free space >600 GB. Are you suggesting creating a new table with varchar(max), load data in it and then rename tables here?
Jeff Moden (4/23/2014)
Ok... let's shift gears here a bit.
This is a 160GB table and there are several types of performance and space savings that many may not have considered. Before I can make any suggestions in that area, I have some questions...
1. What type of table is this? Is it an OLTP table that suffers regular updates to existing rows or is it more like an Audit or Order Detail Table where the data is either written once and never updated (Audit Table) or only the latest 30 days might be updated for things like backorder fulfillment (Order Detail Table)?
2. How much temporary free space do you have. To you have about 180GB that could be used as a temporary work area so that we can fix the conversion of TEXT to BLOB and future reindexing problems (even if this IS an OLTP Table) as well as making backups (and related DR restores) sing if this isn't an OLTP table?

Exactly but much, much more. I'm suggesting that a "crawler" be built to partition the table one month at a time, do a final true up when it's done, and then swap-rename the tables. Then you could split the index maintenance to only those partitions that needed it. For a really BIG bang for the buck, each monthly partition should live on a separate filegroup with each filegroup containing only one file. Then, you only have to backup the filegroups that actually suffered an update. Usually, that's limited to only the last 1 or 2 partitions and all the rest can be set to read only, backed up once, and never need a back on those partitions ever again. I'm in the process of doing this on a 310GB call recording table that currently takes 6 hours to backup. Once I'm done with it, I'll only need to backup the latest partition and, just before the end of the month when it is most full, that should only take about 6 minutes. I'll also be able to move the Read Only stuff from expensive and cherished SAN hardware to lower cost iSCSI disks in the process.

I do have to tell you that if you partition the table and you thought it was all too easy to do, then you've probably done it wrong and have condemned large parts of your disk to a Read Only status.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)