Skip to content

Latest commit

 

History

History
106 lines (72 loc) · 5.36 KB

sys-sp-xtp-force-gc-transact-sql.md

File metadata and controls

106 lines (72 loc) · 5.36 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sys.sp_xtp_force_gc (Transact-SQL)
Manually release memory related to deleted rows of in-memory data that are eligible for garbage collection.
MikeRayMSFT
mikeray
randolphwest
08/21/2024
sql
system-objects
reference
sys.sp_xtp_force_gc_TSQL
sys.sp_xtp_force_gc
sys.sp_xtp_force_gc
TSQL

sys.sp_xtp_force_gc (Transact-SQL)

[!INCLUDE sqlserver]

Causes the in-memory engine to release memory related to deleted rows of in-memory data that are eligible for garbage collection, which haven't yet been released by the process.

In cases where a large volume of in-memory data has been released, and where the memory isn't soon be needed for other in-memory data, this procedure can free up memory for other uses. If you anticipate the memory being used soon for other in-memory data, freeing it here would only cause extra overhead, as it would need to be reallocated for the new data.

For more information on memory-optimized TempDB metadata out of memory errors, see memory-optimized TempDB metadata (HkTempDB) out of memory errors.

The sys.sp_xtp_force_gc system stored procedure was introduced in [!INCLUDE sssql22-md] CU 1 and [!INCLUDE sssql19-md] CU 13. This stored procedure isn't currently supported on [!INCLUDE ssazure-sqldb] and [!INCLUDE ssazuremi-md].

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sys.sp_xtp_force_gc
    [ [ @dbname = ] 'database_name' ]
[ ; ]

Arguments

[ @dbname = ] 'database_name'

The database to release unused memory for memory-optimized tables. @dbname is sysname.

  • When the @dname parameter isn't specified, only system-level memory structures in the instance are considered.

  • When the @dname parameter provided is tempdb, the memory structures related to Memory-optimized TempDB metadata are affected.

  • When the @dname parameter provided is a user database, the memory structures related memory-optimized tables are affected.

Therefore, you might expect to see different results when executing sys.sp_xtp_force_gc: without a parameter, with @dbname = N'tempdb', or with @dbname = a user database name.

Return code values

0 for success. Nonzero for failure.

Permissions

Requires membership in the db_owner fixed database role.

Remarks

Memory-optimized garbage collection happens normally and automatically in response to memory pressure. You can manually trigger garbage collection with sys.sp_xtp_force_gc. You can observe the reduction in memory cleanup in sys.dm_xtp_system_memory_consumers. In [!INCLUDE sssql22-md], the sys.dm_xtp_system_memory_consumers dynamic management view has improved insights specific to Memory-optimized TempDB metadata.

Contrast with sys.sp_xtp_checkpoint_force_garbage_collection, which marks checkpoint files used in the merge operation with the log sequence number (LSN) after which they aren't needed and can be garbage collected. Also, sys.sp_xtp_checkpoint_force_garbage_collection moves the files whose associated LSN is lower than the log truncation point to FILESTREAM garbage collection.

Prior to [!INCLUDE sssql22-md], execute this stored procedure twice.

Examples

To execute garbage cleanup on system-level memory structures and memory-optimized TempDB metadata in [!INCLUDE sssql22-md]:

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc;
GO

To execute garbage cleanup on system-level memory structures and memory-optimized TempDB metadata prior to [!INCLUDE sssql22-md]:

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc;
GO

EXEC sys.sp_xtp_force_gc;
GO

Related content