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:

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

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:

---

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:

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