This applies to SQL 6.5 ONLY, and may differ under SQL 7.0, which I've
never run. DO NOT DO THIS for SQL 7.0 unless you REALLY, REALLY know what
you're doing!
First, know for CERTAIN how much RAM is physically in the machine. If
you're not sure, CHECK under Admin | NT Diagnostics. Note this number, in
MEGS.
1. Open SQL Enterprise Manager
2. Double-click on the server name to establish a connection.
3. RIGHT click on the server name, and go down to configure.
4. Click on the configuration tab.
5. Find the line that says MEMORY
You will want to update the column "Current" in the row "Memory".
Do *NOT* do this if you are unsure of what you're doing, or of the math I'm
about to list. If you allocate TOO much RAM to SQL, you may not be able to
even boot the system without major effort.
Take the total number of MEGS in your physical system, and SUBTRACT *at
least* 32 megs, PLUS enough RAM for anything else you may run on that
machine. (Use TASK MANAGER to find out how much RAM you may need to
reserve, which should be enough for NT, plus any other applications, plus
about 16 MB *more* than that as a cushion, or other caching). When in
doubt, subtract out enough reserve RAM that you are CERTAIN it will never
be exhausted.
What you are about to do is give ALL of the remaining RAM exclusively to
SQL. Thus, if the machine has 192 MB, and you subtract out 64 MB of RAM as
reserve, you're going to give SQL 128 MB of RAM for exclusive use, and the
machine will effectively have *ONLY* 64 MB of RAM for NT and other apps --
SQL will take the 128 MB you give it and won't let go of it.
So, let's say you've come up with a number of 128 MB that you're goingt o
give exclusively to SQL. How do you calculate what to put in the MEMORY
row where we're looking? Easy. Multiply by 512.
128 MB of RAM times 512 = 65536. You'd place "65536" here, click OK, and
then reboot. This number will NOT take effect until you restart SQL.
Before you change this number, note where the default is set. I BELIEVE it
is set to 8192, or 16 MB of RAM. (I may be wrong, it may be 32 MB, or
16384). In any case, the default isn't NEAR enough for even a lightly
loaded calls table -- the complete Emerald DB (of which CALLS is the
largest, by far) ought to fit with plenty of room to spare in the
reserved-for-SQL memory space.
Again, if unsure, don't give TOO much to SQL, or you might not have enough
to load the kernel and other apps you need. But you ought to give it more
than the default amount that SQL starts off with. SQL server likes
RAM. If you only have 64 MB of RAM in your SQL server, consider upgrading
to at least 192 MB (which means you should be able to give 128 MB solely to
SQL).
Giving this RAM to SQL, plus keeping the Calls table trim, will keep your
SQL server humming quickly.
The test as to whether you need to increase RAM or decrease the size of
Calls by trimming is this:
In Emerald, open a user (any user you know has logged on recently).
Click on Services
Double-click any login service to open it.
Click on the "Time On" tab
Put in a date range in which you KNOW this user logged on
Click UPDATE
Note how long the search takes to put some data in the list. If this is
the FIRST search you've performed since the last time SQL server rebooted,
it might take as long as several minutes, depending on the size of your
Calls table.
Now close this user, and repeat for another user, a different one.
If the second search takes LONGER than a couple of seconds, your DB isn't
fitting entirely in RAM, meaning that EVERY search has to go to disk. Even
if the first search took five minutes, if there was enough RAM, and the DB
small enough, SQL would have cached it ALL, making subsequent searches
lightning fast. Again, if it wasn't a matter of a couple of seconds on
the second search, give more RAM to SQL, and trim the calls table as I
discussed in prior messages.
And again, my third recommendation is still important, too. Use a defrag
utility on your server every few months. Your DB can get fragmented
beyond belief, and if it winds up with thousands of fragments (a year or so
of not being handled), it can slow your system to a crawl, or even make it
unstable, rebooting with BSoD's. Defragging is crucial, but it has to be
done when SQL is DOWN, so if you do this, make sure you have backup RADIUS
elsewhere, as it can take an hour or more to defrag a badly screwedup drive.
1) Give SQL lots of RAM
2) Keep Calls Small
3) Defrag twice a year.
Happy SQL.
At 10:33 AM 2/4/00 -0600, you wrote:
>At 11:19 AM 02/04/00 -0500, you wrote:
> >If you don't need them other than for tech, or for rooting out line
> >campers, consolidate, and then kill.
> >
>
>Thanks for the help, Jeff. We started with Emerald about 6 months ago so
>this should help get our DB in order and keep it that way.
>
>Thanks again,
>David
>
>
>For more information about this list (including removal) go to:
>http://www.iea-software.com/support/maillists/liststart
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart