Re: [Emerald] Consolidation

Josh Hillman ( (no email) )
Fri, 4 Feb 2000 16:20:53 -0500

If you end up allocating too much memory and SQL 6.5 won't start back up,
see:

Changing Configuration Values When SQL Server Won't Start
http://support.microsoft.com/support/kb/articles/q173/0/90.asp

Josh

----- Original Message -----
From: Jeff Woods <jwoods@deltacomm.com>
To: <emerald@iea-software.com>
Sent: Friday, February 04, 2000 12:26 PM
Subject: Re: [Emerald] Consolidation

> The next biggest thing you can do is give SQL *much* more memory.
>
> 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

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart