SQL memory usage with freetext indexing

Question

Does full text search and freetext indexing drastically increase the memory usage of a SQL Server database instance?

Today, on my development machine, I installed the fulltext feature on a SQL Server 2014 instance and created a catalog on one of my tables with a fulltext index. I set the index to track changes automatically. The table I used it on has about 5000 records.

Next, I started testing this feature by running some queries. The last query that I've run so far is the following:

SELECT
 'Title' AS [MatchFrom],
 [ShopItem].[Title],
 [ShopItem].[Description],
 [SubCategory].[Name] AS [SubCategory],
 [FreeTextTable].[Rank]
FROM
 FREETEXTTABLE([ShopItem], [Title], 'Paper') AS [FreeTextTable]
 INNER JOIN [ShopItem]
 ON [FreeTextTable].[Key] = [ShopItem].[Id]
 INNER JOIN [SubCategory]
 ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
 'Description' AS [MatchFrom],
 [ShopItem].[Title],
 [ShopItem].[Description],
 [SubCategory].[Name] AS [SubCategory],
 [FreeTextTable].[Rank]
FROM
 FREETEXTTABLE([ShopItem], [Description], 'paper') AS [FreeTextTable]
 INNER JOIN [ShopItem]
 ON [FreeTextTable].[Key] = [ShopItem].[Id]
 INNER JOIN [SubCategory]
 ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
UNION
SELECT
 'Subcategory',
 [ShopItem].[Title],
 [ShopItem].[Description],
 [SubCategory].[Name],
 [FreeTextTable].[Rank]
FROM
 FREETEXTTABLE([SubCategory], [Name], 'Paper') AS [FreeTextTable]
 INNER JOIN [SubCategory]
 ON [FreeTextTable].[Key] = [SubCategory].[Id]
 INNER JOIN [ShopItem]
 ON [ShopItem].[SubCategoryId] = [SubCategory].[Id]
ORDER BY
 [FreeTextTable].[Rank] DESC

It was at this point that my computer warned me that it's low on memory:

enter image description here

I quickly realised that the SQL server instance was using an unusual amount of memory (it's moving between 6 gigabytes and 9 gigabytes)

enter image description here

Now I'm quite concerned that implementing any freetext functionality on my live system would cause a strain on my server (a cloud VM that currently has a total of 6GB RAM), but I don't know how to confirm exactly what it is that's using so much memory. I'm currently a solo developer on a new project without any database administrators.

---

EDIT:

Memory usage still increasing:

enter image description here

---

SQL version (this is on my development machine running Windows 10 64bit, not the server running my application)

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 (Build 14393: )

The memory usage next day:

enter image description here

enter image description here

• Shanky: Can you please share some outputs with me. 1. select @@Version 2. select * from sys.dm_os_process_memory. What is max server memory you have set and what is total RAM on the system ?
• Carel: @Shanky: see update to the post with information you requested
• Shanky: First I suggest you apply SQL Server 2014 SP2 ASAP, without that its useless to discuss any memory issue. Other thing you did not included all columns in output, or do you ?. Your SQL Server mem utilization is 130 MB and I guess locked_pages_memory_kb should also have some value.
• Carel: @Shanky: I did indeed leave out some columns, now included. What would be the effect of SP2? The version on the server (which I don't think I can change) is: Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64) May 27 2016 15:06:08 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
• Shanky: Again you posted something different, previous output of select @@version shows developer edition while this output shows express edition. I guess you are dealing with express edition which has 1 G memory limitation

Answer 1

As Shanky implied in the comments, your Max Server Memory value is probably not changed from the default. SQL is designed to use everything at its disposal.

I've written a script, based off work from Jonathan Kehayias of SQLskills, that will let you know the value you should use.

Jon advises reserving the following amount of RAM for the operating system:

• 1 GB of RAM for the OS
• plus 1 GB for each 4 GB of RAM installed from 4 – 16 GB
• plus 1 GB for every 8 GB RAM installed above 16 GB RAM

Anything left over from that, can be allocated to SQL Server, assuming it's a dedicated instance.

If your VM only has 6 GB available, the recommended value is 3 GB. To change it, you would write something like this (or change it in the Management Studio UI):

EXEC sp_configure 'show advanced', 1;
EXEC sp_configure 'max server memory (MB)', 3072;
RECONFIGURE WITH OVERRIDE;
GO

• Carel: I have not implemented any freetext functionality on the server yet. The information provided was from my development machine running Windows 10 with 16GB of RAM. Are you saying that if I configure sql to only use let's say 3GB, that it would use only that much? And the instance would be stable? Something to note, the server serves as both a database server and web server with IIS (both for live and QA), and it has a bunch of C# console applications scheduled to run at various times during the day.
• Randolph West: If you have other stuff running on the box (i.e. not dedicated to SQL Server), you'll have to drop the allocated RAM, yes. For the record, I have a VM running a production database with 1.5GB max server memory allocated. On 2012 and higher, that's all it'll use. As to your question about stability, yes, it'll be stable. The RAM is to allow SQL to keep stuff in memory. If it can't, it'll just page to disk, making it slower. That's your trade-off. Dit sal nog werk.