Avatar of marrowyung
marrowyung
 asked on

GHOST cleanup process in SQL 2017.

hi all


We are running an AG with SQL 2017 and from time to time we see in the log this is happening and it takes considerable amount of CPU cycle:



another SQL 2017, exactly the same SQL 2017 patch level AG do not have this every minutes.


and we found this:

https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-ver15


we see this one in the Primary replica but not in the secondary replica, any idea on what is it ?


any idea on what it is ?


we suspect that this is one of the process make SQL server slow, the link said this process is not recommended to disable. 



Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
marrowyung

ASKER
Hi all,

I read this one:

https://www.sqlskills.com/blogs/paul/turning-off-the-ghost-cleanup-task-for-a-performance-gain/

it say:
There is a way to turn off the ghost cleanup task, using trace flag 661, as documented in KB 920093. Be careful though!!! If you disable the ghost cleanup task, the space taken up by deleted records will *NOT* be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.
One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.

so in case I turn the trace ON and let space occupied growth more, is rebuilding an index can record the ghost record automatically ?

this is the index rebuild script I am using:

  
-- Ensure a USE  statement has been executed first. 
SET NOCOUNT ON; 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @partitioncount bigint; 
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint; 
DECLARE @partitions bigint; 
DECLARE @frag float; 
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names. 
SELECT 
    object_id AS objectid, 
    index_id AS indexid, 
    partition_number AS partitionnum, 
    avg_fragmentation_in_percent AS frag 
INTO #work_to_do 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') 
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; 
-- Declare the cursor for the list of partitions to be processed. 
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; 
-- Open the cursor. 
OPEN partitions; 
-- Loop through the partitions. 
WHILE (1=1) 
    BEGIN; 
        FETCH NEXT 
           FROM partitions 
           INTO @objectid, @indexid, @partitionnum, @frag; 
        IF @@FETCH_STATUS < 0 BREAK; 
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) 
        FROM sys.objects AS o 
        JOIN sys.schemas as s ON s.schema_id = o.schema_id 
        WHERE o.object_id = @objectid; 
        SELECT @indexname = QUOTENAME(name) 
        FROM sys.indexes 
        WHERE  object_id = @objectid AND index_id = @indexid; 
        SELECT @partitioncount = count (*) 
        FROM sys.partitions 
        WHERE object_id = @objectid AND index_id = @indexid; 
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. 
        IF @frag < 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
        IF @frag >= 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
        IF @partitioncount > 1 
                    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); 
                            EXEC (@command); 
        --PRINT N'Executed: ' + @command; 
    END; 
-- Close and deallocate the cursor. 
CLOSE partitions; 
DEALLOCATE partitions; 
-- Drop the temporary table. 
DROP TABLE #work_to_do; 
GO 


Open in new window


Do it need to modify to do the same thing on REMOVING ghost record?

marrowyung

ASKER
from here:

https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-ver15

it says other once turn off trace 661, by:

turn it ON:

DBCC TRACEON (661, -1)  

turn it OFF:

DBCC TRACEOFF (661, -1)
?

Some action needs to be taken to remove the ghosted records. One option is to execute an index rebuild, which will move data around on pages. Another option is to manually run sp_clean_db_free_space (to clean all database data files) or sp_clean_db_file_free_space (to clean a single database datafile), which will delete ghosted records.

so MUST I run both sp_clean_db_free_space and sp_clean_db_file_free_space  together? or just sp_clean_db_free_space  ?

and if we need to add the trace to the startup configuration, how we do it ?
-T 661; ?

ASKER CERTIFIED SOLUTION
marrowyung

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61