Share via

sys.dm_fts_memory_buffers

  • Article

Returns information about memory buffers belonging to a specific memory pool that are used as part of a full-text crawl or a full-text crawl range. This dynamic management view can be used to determine the shared memory usage of a full-text population.

Column name Data type Description

pool_id

int

ID of the allocated memory pool.

0 = Small buffers

1 = Large buffers

memory_address

varbinary(8)

Address of the allocated memory buffer.

name

nvarchar(8000)

Name of the shared memory buffer for which this allocation was made.

is_free

bit

Current state of memory buffer.

0 = Busy

1 = Free

row_count

int

Number of rows that this buffer is currently handling.

bytes_used

int

Amount, in bytes, of memory in use in this buffer.

percent_used

int

Percentage of allocated memory used.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Significant joins of this dynamic management view

Relationship Cardinalities

From To Relationship

dm_fts_memory_buffers.pool_id

dm_fts_memory_pools.pool_id

Many-to-one

Examples

A. Returning shared memory usage for all active populations.

The following example returns the shared memory usage for all active full-text populations.

SELECT SUM(pools.buffer_size) FROM sys.dm_fts_memory_pools pools
    JOIN sys.dm_fts_memory_buffers buffers 
    ON (pools.pool_id = buffers.pool_id)
    WHERE buffers.is_free = 0

B. Determining the efficiency of full-text shared memory usage

The following example returns the average percentage of memory that is being used for all active full-text memory buffers.

SELECT AVG(percent_used) AS "memory usage efficiency" 
    FROM sys.dm_fts_memory_buffers 
    WHERE is_free = 0

See Also

Reference

Dynamic Management Views and Functions
Full-Text Search Related Dynamic Management Views

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added the examples.
Changed content:
  • Fixed the description of the is_free field.