2

I have a server that has 32GB of RAM and SQL Server 2014 Enterprise installed over it. I want to try the impact of Buffer Pool Extension (BPE) on Full Text Population.

I created a table and populated it with dummy information till it reached 100GB of size.

First I tried populating the full text index before BPE. It took 3 hours. Then I tried populating the full text index with BPE configured on a 140GB SSD with 120GB allocated memory. The weird thing is the population of the full text index didn't get any faster.

I used the following query to create the BPE on SSD then restarted the instance:

alter server configuration
set buffer pool extension
on ( filename = 'Z:\Cache.bpe' , size = 120gb )

The questions are :

  1. How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?

  2. Does BPE affect FullText index population?

  3. Did I miss any Server or Buffer Pool configuration?

2

How to check the buffer pool usage of SQL Server ( I want to know if the BPE is used )?

You can query sys.dm_os_buffer_descriptors

An example would be something like:

SELECT   CONVERT(NUMERIC(38, 2), COUNT(*) * 8 / 1024.0) AS cached_data_mb,
         CONVERT(NUMERIC(38, 2), SUM(CONVERT(BIGINT, free_space_in_bytes)) / 1024. / 1024.) AS free_mb,
         CASE database_id
              WHEN 32767 THEN 'ResourceDb'
              ELSE DB_NAME(database_id)
         END AS database_name
FROM     sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_data_mb DESC;

Did I miss any Server or Buffer Pool configuration?

It doesn't appear that way from the documentation. There aren't a lot of options presented.

Does BPE affect FullText index population?

You're at the distinct disadvantage of using two features together that not a lot of people use separately. You may have to do your own experiments here.

One thing to be aware of is that BPE activity is single threaded, and each I/O is limited to a single 8K page. This might make them ineffective with Full Text.

I'd much rather stick an appropriate amount of RAM in the server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.