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:
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 ?locked_pages_memory_kb
should also have some value.