HEAP fragmentation percent is high

Question

HEAP fragmentation percent is high

juliava

SSC-Addicted

Points: 440
More actions
April 27, 2021 at 7:18 pm

#3876489

Hello, HEAP fragmentation percent is high how to reorganize or rebuild? I already ran job maintenance job and dint help

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

I'd have a read on this page:

https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-heaps-and-their-fragmentation/#:~:text=In%20SQL%20Server%2C%20heaps%20are%20rightly%20treated%20with,that%20space%20is%20allocated%20and%20forward%20pointers%20used.

But basically, "alter table <tablename> rebuild" will rebuild your heap.

Does that table need to be a heap or would it benefit from an index?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 2

Would you put your heap table name in the code below, run it, and post the results? That will help determine which, if any, index is best for a clustering index on your current heap.

DECLARE @table_name_pattern nvarchar(128)
SET @table_name_pattern = '<your_heap_table_name>'

SELECT
    --IDENTITY(int, 1, 1) AS ident,
    DB_NAME(mid.database_id) AS Db_Name,
    CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
    dps.row_count,
    OBJECT_SCHEMA_NAME(mid.object_id) + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
    mid.equality_columns, mid.inequality_columns, 
    LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1 AS incl_col_count,
    mid.included_columns,
    user_seeks, user_scans, cj1.max_days_active, unique_compiles, 
    last_user_seek, last_user_scan, 
    CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
    CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
    system_seeks, system_scans, last_system_seek, last_system_scan,
    CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
    CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
    mid.statement, mid.object_id, mid.index_handle
--INTO #index_missing
FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
CROSS JOIN (
    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WITH (NOLOCK) WHERE name = 'tempdb'
) AS cj1
LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
    mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
    migs.group_handle = mig.index_group_handle
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = mid.object_id AND
    dps.index_id IN (0, 1)
WHERE
    1 = 1 
    AND mid.database_id = DB_ID()
    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
ORDER BY
    --avg_total_user_cost * (user_seeks + user_scans) DESC,
    Db_Name,
    Table_Name,
    equality_columns, inequality_columns,
    user_seeks DESC

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

juliava SSC-Addicted Points: 440 More actions · Answer 3

if i have table not HEAPA and cant mannually rebuild or reorganize,persantage still the same,what else can be done?

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 4

If you have a table with a clustered index that you cannot rebuild or reorganize, I think the ONLY other thing you could do to reduce the fragmentation percentage would be to truncate it (ie delete all of the data), but that likely isn't what you want to do either.

A good question to ask at this point is - what problem are you trying to solve? Is fragmentation causing performance issues OR is it just you saw a high number and wanted to get the number lower?

Fragmentation isn't a good thing and changing fill factor can help reduce the fragmentation that happens on a table (won't help current fragmentation level, but may help future fragmentation). BUT if the table is highly fragmented AND not causing any problems, it may be safe to ignore. This is especially true if the table is small.

If a query is slow, it may be worth investigating if the query should be optimized OR if defragmenting the table/index is going to improve performance. If you have a test system, this should be fairly easy to test out.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

juliava wrote:

if i have table not HEAPA and cant mannually rebuild or reorganize,persantage still the same,what else can be done?

How big or small is the table? Some tables are just too small to do anything with when it comes to REBUILDs.

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