0

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

6
  • 1
    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 ?
    – Shanky
    Oct 12, 2016 at 16:56
  • @Shanky: see update to the post with information you requested
    – Carel
    Oct 13, 2016 at 9:11
  • 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.
    – Shanky
    Oct 13, 2016 at 10:51
  • @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)
    – Carel
    Oct 13, 2016 at 11:23
  • 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
    – Shanky
    Oct 13, 2016 at 11:41

1 Answer 1

2

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
2
  • 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.
    – Carel
    Oct 13, 2016 at 9:18
  • 1
    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. Oct 13, 2016 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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