Need help configuring memory space for mssearch & SQL

We recently installed a new dual-CPU Xenon server with 4 GB of RAM. I have the
SQL server configured for "Automatic" memory space allocation. Currently, SQL
is using about 1.5 GB of memory and mssearch is using about 20 MB. mssearch is
being hit much more intensively then the SQL server, and it is paging heavily.
I know how to set a static memory space size for the SQL server, but I don't
know how to set how much memory mssearch will use. Should I just set a static
memory size for SQL to restrict its size, or is there some way to specify how
much mssearch should use?
What are reasonable memory space settings for SQL and mssearch on a 4GB system?
On our system, searching is much more intensive then other SQL operations.
Phil Sherrod
(phil.sherrod 'at' sandh.com)
http://www.dtreg.com (decision tree modeling)
http://www.nlreg.com (nonlinear regression)
http://www.LogRover.com (web traffic analysis)
http://www.NewsRover.com (Usenet newsreader)
Phil,
You can set how much memory the MSSearch service will use via
sp_fulltext_service 'resource_usage' <value>, where <value> is 1 - 5 with 3
being the default. If you set the resource_usage level to 5 (dedicated), the
MSSearch service will use up to a max of 512Mb of RAM. Note, this RAM is
below the 3Gb limit and the service will ONLY use this amount of RAM if no
other service is using or requesting additional RAM as it is passive in
nature. The max of 512Mb is a hard-coded limit and cannot be increased. This
is why SQL Server's memory needs to be set to "fixed memory" and not dynamic
memory as SQL Server will request more memory, if it needs it and therefore
the MSSearch will not be able to use that memory.
Q. Should I just set a static memory size for SQL to restrict its size, or
is there some way to specify how much mssearch should use?
A. No, you need to do both, set static memory for sql server as well as set
sp_fulltext_service 'resource_usage' to 5.
Q. What are reasonable memory space settings for SQL and mssearch on a 4GB
system?
A. For SQL Server that would depend upon your processes, but for the
MSSearch service the max is 512Mb.
Regards,
John
"Phil Sherrod" <phil.sherrod@.REMOVETHISsandh.com> wrote in message
news:c7WdnRoLcZRzNAfcRVn-3g@.giganews.com...
> We recently installed a new dual-CPU Xenon server with 4 GB of RAM. I
have the
> SQL server configured for "Automatic" memory space allocation. Currently,
SQL
> is using about 1.5 GB of memory and mssearch is using about 20 MB.
mssearch is
> being hit much more intensively then the SQL server, and it is paging
heavily.
> I know how to set a static memory space size for the SQL server, but I
don't
> know how to set how much memory mssearch will use. Should I just set a
static
> memory size for SQL to restrict its size, or is there some way to specify
how
> much mssearch should use?
> What are reasonable memory space settings for SQL and mssearch on a 4GB
system?
> On our system, searching is much more intensive then other SQL
operations.
> --
> Phil Sherrod
> (phil.sherrod 'at' sandh.com)
> http://www.dtreg.com (decision tree modeling)
> http://www.nlreg.com (nonlinear regression)
> http://www.LogRover.com (web traffic analysis)
> http://www.NewsRover.com (Usenet newsreader)

Need help approximating how much hard drive space I need for my DB table.

In our SQL Server database we will have a table that will be populated with about 2000 records
per day. That is2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytes
of available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,
based solely on the number of records entered into this table per day. The problem is I don't
know how to calculate how much hard drive space we need. I think I read that using varchar,
sql server 2005 really optimizes a database. Here is a typical example of data in our
database. I put dots on three lines between the first and last sample record to just
illustrate that there are many records in between.

Basically we only need 8 months of data at a time in the table and then we can purge
records older than 8 months.
Can someone help me approximate how much hard drive space I might need for 8 months of data,
given the following sample record in the database?

Sample: -->34.5 4.08 10.6 .0012

Sample Table in my DB just for illustration:

(PPsquare inch) (Diameter) (Weight gm) (coeffOfSatFriction)

34.5 4.08 10.6 .0012
.
.
.
21.7 3.54 6.22 .019

Why dont you monitor your db size. Get the size on a sunday evening and check the same information on friday evening, you will see how much your db has increased. monitor this over a couple of week and you will get a better estimate. Also, create a job that will alert you if the db size reaches 70% of allocated space.|||Well actually right now we are in design mode and no dat is in the database yet. The software to populate the tables in the database has not even be written at this point in time. Basically we are required to calculate the estimated hard drive space over an 8 month period. Then based on the approximated database size we will make a decision from there. So I need to figure out a way to calculate a good estimate of the size of that data table in the database. I don't know if SQL server does any type of data compression if necessary?|||

Hi,

The estimate depends entirely on the type of data you want to store. For an article on data types sizes, you can have a look at this article:

http://msdn2.microsoft.com/en-us/library/ms187752.aspx

Based on the sample in your post, you could use a decimal with a precision of 9 (http://msdn2.microsoft.com/en-us/library/ms187746.aspx for decimal data type) which takes 5 bytes for each field. So, your estimate would be something like this:

5 (bytes) x 4 (fields) x 2000 (records) x 5 (days) x 36(approx 36 weeks in 8 months) = 7 200 000 bytes = 6.87 Megs of data. So those 50 gigs of data would be more than enough to keep your table.

|||

Thank you very much for the help!

Kind regards