SQL Server Buffer Pool Extension in FullText Index Population

Question

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?

Answer 1

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.