Full text memory usage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 12 Apr 2007 21:44 GMT

Hi

I work with SQL 2005 and I know how it uses RAM but I know just some basic
stuff about Full-text catalogs and since we plan to implement them in the
future I need to know a few things.

1. Can you set min and max memory for full text service like it can be set
for SQL service?
2. Does it start using RAM as it needs it until it hits maximum and then
release it only if some other process needs RAM like SQL does?
3. How much RAM could be expected for Full text sevice to use on 20GB
catalog? (just estimate)
4. On x86 windows, full text service is limited to max 3GB of RAM (with /3GB
switch in boot.ini probably). Is on x64 Windows limitation 4GB and on IA64
there is no such limitations? Can you confirm that?

Thanks
Tom

Reply to this Message

Hilary Cotter - 13 Apr 2007 12:22 GMT

answers inline.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi
>
[quoted text clipped - 4 lines]
> 1. Can you set min and max memory for full text service like it can be set
> for SQL service?

No.

> 2. Does it start using RAM as it needs it until it hits maximum and then
> release it only if some other process needs RAM like SQL does?

Not really, like any other windows application it can see up to 2 Gigs on a
32 bit system, on a 64 bit system it may see then entire physical memory. It
uses what it needs. Depending on the other applications running on this
server its working memory set may be paged to disk. It makes extensive use
of the file system cache.

> 3. How much RAM could be expected for Full text sevice to use on 20GB
> catalog? (just estimate)

We have about 12 20 gig catalogs - memory used is 40 Mgs.

That depends on your queries mainly. The indexer runs in process with SQL
Server.

> 4. On x86 windows, full text service is limited to max 3GB of RAM (with
> /3GB switch in boot.ini probably). Is on x64 Windows limitation 4GB and on
> IA64 there is no such limitations? Can you confirm that?

That is correct on 64 bit systems applications provided they are coded for
it can see the entire physical ram. So your entire catalogs could be in ram
on systems with larger memory.

> Thanks
> Tom

Reply to this Message

sql411@nospam.com - 20 Apr 2007 01:20 GMT

With SQL Server 2005, you typically will try to limit it to not starve the
OS. So, in an X64 environment with 12GB -- I might give 10 to SQL and allow
2 for the OS (everything else).

When you introduce full-text on this system of total 12GB, how do you
prevent FTE from starving the OS?

So, I would set 8GB for SQL and leave 4GB for the OS+FTE+everything else.
Is there nothing to guarentee that FTE doesn't over utilize that 4GB and
cause OS level latency?

I thought there might be some level of governing this by using the:
ft crawl bandwidth (max) & ft notify bandwidth (max)

sp_configure options but it doesn't seem that is what the purpose of those
are for.

Thanks,
Robert Towne

Reply to this Message

Hilary Cotter - 20 Apr 2007 03:58 GMT

the full-text engine does not compete with the OS. SQL Server will complete
with other application, like sql fts. Set
sp_configure 'max server memory'

to 2 gigs less than the total memory, so fts has enough memory to operate.

> With SQL Server 2005, you typically will try to limit it to not starve the
> OS. So, in an X64 environment with 12GB -- I might give 10 to SQL and
[quoted text clipped - 16 lines]
> Thanks,
> Robert Towne

Reply to this Message

sql411@nospam.com - 20 Apr 2007 04:58 GMT

OK, i was thinking that FTE in 2005 ran in a seperate memory space than what
is specified in SQL Server's 'max server memory'. Very helpful to know &
many thanks..

> the full-text engine does not compete with the OS. SQL Server will complete
> with other application, like sql fts. Set
> sp_configure 'max server memory'
>
> to 2 gigs less than the total memory, so fts has enough memory to operate.

Reply to this Message

Tom - 20 Apr 2007 10:49 GMT

I thought the same as Robert.
So if FTS operates with large full-text indexes they will be cached in
memory space under SQLService?
How can I determin how much memory that SQL uses is actually used for
full-text catalog and how much for other data?
I usually use "Database pages" counter to see how much data and indexes are
cached in RAM.

Tom

> OK, i was thinking that FTE in 2005 ran in a seperate memory space than
> what
[quoted text clipped - 8 lines]
>> to 2 gigs less than the total memory, so fts has enough memory to
>> operate.

Reply to this Message

Hilary Cotter - 20 Apr 2007 15:59 GMT

SQL FTE doesn't use much ram, but SQL Server will apply memory pressure on
all other applications on this box. That is why it is important to cap sql
server so SQL FTE has room to operate.

I don't think ms has exposed counters to determine how much memory of the
SQL Engine SQL FTS uses (some of the indexing operations are integrated into
the engine), but the indexes are not caches in SQL Server's data cache. They
are part of the file system cache.

>I thought the same as Robert.
> So if FTS operates with large full-text indexes they will be cached in
[quoted text clipped - 18 lines]
>>> to 2 gigs less than the total memory, so fts has enough memory to
>>> operate.

Reply to this Message