Re: Problem > 1 GB memory
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 1 Aug 2005 14:13:13 +0000 (UTC)
Antar (antonio.arranz@xxxxxxxxx) writes:
> I'm kind of a newbie on DB management but I have to deal with a huge DB
> used for real time operations. I got a temporal table where current
> data is stored to work with frecuently, and then a table for each past
> month (historic tables, that is). The issue is that each of these
> tables are 1 GB. When the user wants to display data for a past month,
> the SQL Server process inmediatly jumps from 50 MB to > 1 GB on memory
> (I guess it loads the whole table for that month on memory) and when
> the user wants to look up more than a month at once he gets time-outed.
> Is there any way to 1) not to load the whole table on memory in order
> to work with it? -it'd be slower but is just a huge query once in a
> while- and, if not, 2) "unload" the table from memory once the user is
> done? (kind of "if not ops performed upon the table within xx seconds,
> free the memory and go back to those nice 50 MB).
No, there are no knobs to achieve exactly what you are looking for. But
as we shall see, there are other means.
SQL Server maintains a cache of data that is accessed frequently. This is
because access to main memory is much faster than access to disk. The
default strategy is to grab as much memory that is available, and only if
other applications needs memory, SQL Server will yield. Thus on a server
where SQL Server is the only application, it's perfectly normal to see
SQL Server to have 80% of the available physical memory. It is possible
to configure SQL Server to keep its memory usage below a certain level,
and this can be useful on a machine where there are other applications,
for instance an Exchange server. Or a developer's workstation. If you
have table with monthly data @ 1 GB each, you should not have other things
running on that machine, anyway.
Thus, so far it's kind of expected that memory usage increases when the
users accesses the 1GB table. But assuming that the user does not really
want to see all 1GB, then probably too much is loaded. The cure for this
is to look at the query and the indexes on the table. Say that the user
only wants to see data for customer X. If there is no index on customer
id, then SQL Server has to scan the entire table to find the records
for customer X. This will indeed lead to the entire table being loaded
into memory. The benefit here, is that if the user asks for data for
customer Y, the the table can be scanned in memory rather on disk,
and will give better response time.
However, if the user asks for data for another month, then that table
has to be loaded into memory. And if the available memory on the machine
is. say, 1.25 GB, then the first table must be flushed from the cache.
The strategy for how data is flushed from the cache are somewhat more
sophisticated, but let's not go into this here. Not least, since I don't
know it too well. But so much can be said that there is not really any
documented way to force selected pages from the buffer cache.
Now, if the customerID column is indeed indexed, the situation is different.
Then SQL Server can seek that index to go directly to the index nodes
for that customer, and then read the related data pages. If there are
only ten records for that customer, this drastically reduces the amount
of data that has to be read.
Thus, it appears what you need is to look into query tuning and adding
better indexes. 1GB tables requires good indexing.
Another note is that having one table per month is not really practical.
What if the user wants to see data from different months? The normal
approach is to add the date to the primary key of the table, and
have one big table. Now, with 1 GB of data per month, you get 60 GB
for five years of data, and slicing this data up in smaller pieces may
simplify management somewhat. This can be achieved by still having
table slices, but uniting them in a partitioned view. Note that this
point does not really apply your problem. Had you had one big table,
with the clustered index on date, but no other indexes, the scenario
would have been exactly the same, as what SQL Server loads into the cache
is not tables, but pages of 8192 bytes.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.
- References:
- Problem > 1 GB memory
- From: Antar
- Problem > 1 GB memory
- Prev by Date: Problem > 1 GB memory
- Next by Date: Insert or update into a Image column causes data to double in size
- Previous by thread: Problem > 1 GB memory
- Next by thread: Insert or update into a Image column causes data to double in size
- Index(es):
Relevant Pages
|
