How to shrink LOB data in sql server 2017

Question

How to shrink LOB data in sql server 2017

March 22, 2022 at 6:44 am

This was removed by the editor as SPAM

Viewing 8 posts - 16 through 22 (of 22 total)

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

fgrodriguez SSC Veteran Points: 271 More actions · Answer 1

I think you have not used "sys.dm_db_index_physical_stats" correctly.

I attach a Query that could help you. Contains various flags indicating whether to rebuild indexes, reorganize indexes, or compact lobs. The limits for calculating the flags can be revised. The Fill Factor should be taken into account.

Finally, note that the query on large tables can be slow, and that the fragmentation you may have in your development environment will not be the same as the one you have in production.

Excuse my English, it is a translation with google translator.

  SELECT
      dt.is_memory_optimized Is_memory_optimized, id.name Indice, id.index_id Index_id,
      id.type Index_type, id.type_desc Index_type_desc,
      indexstats.partition_number Index_partition_number,
      indexstats.index_level Index_level, indexstats.index_depth Index_depth,
      indexstats.alloc_unit_type_desc Alloc_unit_type_desc,
      indexstats.avg_fragmentation_in_percent Avg_fragmentation_in_percent,
      indexstats.page_count Page_count,
      indexstats.avg_page_space_used_in_percent Avg_page_space_used_in_percent,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type > 0 AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 30 AND indexstats.page_count > 1000)
            --Fragmencion Interna
            OR (indexstats.avg_fragmentation_in_percent > 30 AND indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count >= 10)  --Debería ver FILL FACTOR
        ),
        1, 0
      ) AS Reconstruccion,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type > 0 AND
            indexstats.avg_fragmentation_in_percent BETWEEN 5 AND 30 AND indexstats.page_count > 1000,
        1, 0
      ) AS Reorganizar,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type = 0 AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.page_count >= 100)
            --Fragmencion Interna
            OR (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count >= 10) --Debería ver el FILL FACTOR
        ),
        1, 0
      ) AS Reconstruccion_heap,

      IIF(
        indexstats.alloc_unit_type_desc <> N'IN_ROW_DATA' AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.page_count > 5)
            --Fragmencion Interna
            OR (indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count > 5) --Debería ver FILL FACTOR
        ),
        1, 0
      ) AS  Compactar_lob

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'YOUR TABLE'), NULL, NULL, 'DETAILED') AS indexstats
      LEFT JOIN sys.tables dt
        ON dt.object_id = indexstats.object_id
      LEFT JOIN sys.schemas sc
        ON dt.schema_id = sc.schema_id
      LEFT JOIN sys.indexes id
        ON id.object_id = indexstats.object_id
          AND indexstats.index_id = id.index_id
    WHERE id.is_disabled = 0
    ORDER BY sc.name, dt.name, id.index_id, indexstats.partition_number, indexstats.index_level

fgrodriguez SSC Veteran Points: 271 More actions · Answer 2

Obviously, it is an example of Query. Depending on the system, this query may be more or less useful.

If it is necessary to compact the lobs, the statement should be of the style:

ALTER INDEX index ON table REORGANIZE WITH (LOB_COMPACTION = ON)

This reply was modified 2 years, 10 months ago by fgrodriguez.
This reply was modified 2 years, 10 months ago by fgrodriguez.

Michael L John One Orange Chip Points: 27060 More actions · Answer 3

fgrodriguez wrote:

I think you have not used "sys.dm_db_index_physical_stats" correctly. I attach a Query that could help you. Contains various flags indicating whether to rebuild indexes, reorganize indexes, or compact lobs. The limits for calculating the flags can be revised. The Fill Factor should be taken into account.

Finally, note that the query on large tables can be slow, and that the fragmentation you may have in your development environment will not be the same as the one you have in production.

Excuse my English, it is a translation with google translator.

  SELECT
      dt.is_memory_optimized Is_memory_optimized, id.name Indice, id.index_id Index_id,
      id.type Index_type, id.type_desc Index_type_desc,
      indexstats.partition_number Index_partition_number,
      indexstats.index_level Index_level, indexstats.index_depth Index_depth,
      indexstats.alloc_unit_type_desc Alloc_unit_type_desc,
      indexstats.avg_fragmentation_in_percent Avg_fragmentation_in_percent,
      indexstats.page_count Page_count,
      indexstats.avg_page_space_used_in_percent Avg_page_space_used_in_percent,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type > 0 AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 30 AND indexstats.page_count > 1000)
            --Fragmencion Interna
            OR (indexstats.avg_fragmentation_in_percent > 30 AND indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count >= 10)  --Debería ver FILL FACTOR
        ),
        1, 0
      ) AS Reconstruccion,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type > 0 AND
            indexstats.avg_fragmentation_in_percent BETWEEN 5 AND 30 AND indexstats.page_count > 1000,
        1, 0
      ) AS Reorganizar,

      IIF(
        indexstats.alloc_unit_type_desc = N'IN_ROW_DATA' AND id.type = 0 AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.page_count >= 100)
            --Fragmencion Interna
            OR (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count >= 10) --Debería ver el FILL FACTOR
        ),
        1, 0
      ) AS Reconstruccion_heap,

      IIF(
        indexstats.alloc_unit_type_desc <> N'IN_ROW_DATA' AND (
            --Fragmentación Externa
            (indexstats.avg_fragmentation_in_percent > 5 AND indexstats.page_count > 5)
            --Fragmencion Interna
            OR (indexstats.avg_page_space_used_in_percent <= 85 AND indexstats.page_count > 5) --Debería ver FILL FACTOR
        ),
        1, 0
      ) AS  Compactar_lob

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'YOUR TABLE'), NULL, NULL, 'DETAILED') AS indexstats
      LEFT JOIN sys.tables dt
        ON dt.object_id = indexstats.object_id
      LEFT JOIN sys.schemas sc
        ON dt.schema_id = sc.schema_id
      LEFT JOIN sys.indexes id
        ON id.object_id = indexstats.object_id
          AND indexstats.index_id = id.index_id
    WHERE id.is_disabled = 0
    ORDER BY sc.name, dt.name, id.index_id, indexstats.partition_number, indexstats.index_level

Good query.

However, your usage of 30% to rebuild and 5% to 30% to reorganize are based on made up numbers. That recommendation has FINALLY been removed by Microsoft. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

Additionally, reorg does not do what you think it does.

An in-depth analysis and discussion on indexes and fragmentation was put together by Jeff Moden and Ed Wagner.

I suggest you start here for some eye-opening things related to indexes and maintenance.

https://www.youtube.com/watch?v=jx-FuNp4fOA

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

olegserdia Old Hand Points: 381 More actions · Answer 4

Output of the dm_db_index_physical_stats:

Attachments:

You must be logged in to view attached files.

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

Things look about as "packed" as they're going to get. Not including what you posted before (graphic below for convenience), how much drive space do you have left on that machine?

The reason why I'm asking because REORGANIZE will take a month of Sundays for any LOB work and it won't work just one time through. It took me 10 runs to fix a test table once after some deletes like you did. And, contrary to popular belief, REORGANIZE is quite the pig when it comes to log file usage for such things.

That pretty much leaves moving the LOB data out of the mdf file to another file and an index rebuild just isn't going to do that for you. It's going to be a "build a new file, build a new table on that file, copy the data from old to new, drop the old table, and rename the new table. That's also not going to work very well if you try to do it all at once. Now might be a good time to consider partitioning the table for the future. That'll make future drops almost instant and keep you from having to do shrinks because you'll simply grow back into the smaller space that your delete (drop a partition, really) will leave in the future.

The trouble is, that's temporarily going to require twice the space since shrinks don't seem to be working for you (and Paul Randal has an article about how LOBs interfere with shrinks https://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/ ).

As Paul also says, "there's no good way" to move LOB data from MDF to other file groups ( https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ ). It's always going to be painful. I did it once about 8 years ago. I was lucky.. I had the disk space to do it.

The alternative to all this is to ask yourself the question... aren't you going to grow into the space you just freed up anyway? Is it REALLY imperative to remove that now free space from the MDF file? My answer is, probably not AND it also gives you room to rebuild indexes without the file having to grow.

--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)

Danishtj Newbie Points: 2 More actions · Answer 6

To shrink a data or log file.

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases and then right-click the database that you want to shrink.
Point to Tasks, point to Shrink,
and then click Files

fgrodriguez SSC Veteran Points: 271 More actions · Answer 7

First of all, I would like to indicate that the previously set query had the objective of obtaining data: % fragmentation and % of used space. Its goal is not to provide a solution to index fragmentation, but to help olegserdia.

Thus, with the results provided by olegserdia, it can be seen that the lobs are perfect: there is no fragmentation and the % of space used is very high. So I think it's not the lobs that are wasting the space. I suppose that they are measurements made on the development environment (in which only inserts have been made) and not on the production environment (in which inserts and deletes have been made).

This being the case, I agree with Jeffrey Moden: it is a good time to redesign the database, or at least the table you are having problems with: either partitioning it or relocating the lobs to another group of files with the TEXTIMAGE_ON option