MSSQL 2000 not releasing memory after use?

[mr. modus]

I'm using MSSQL 2000 Enterprise Edition and CFMX 6.1.
I have an enormous table of 7.7million records. I'm in the process of creating
an archiving tool (via CF) that will remove records on a monthly basis and
insert them into an archive table.
The problem is when I run any query on the table, for instance select MAX(ID),
the memory usage shown in the resource manager shoots up to around 650,000K,
which is expected. However the problem I'm having is that once the query is
complete sqlserver still shows that it's using 600,000+K. Why doesn't the
server release this memory? It's causing the SQL server to run extremely slow
even after the query is complete.

Any ideas?

[SQLMenace]

The server doesn't release the memory because it keeps the records in the
cache, that way if you rerun the query it will be much faster
This is normal behavior.
Do you have an index on the ID column? 650MB seems a little steep on a table
of 7.7 million records
Check out the execution plan and make sure that you get an index seek or scan
instead of a table scan

[mr. modus]

Is there anyway inside the cfquery tag that I can tell SQL server not to cache
the query?
The queries I'm running will not be run multiple times so there is no need to
cache them.
ID is my Primary Key.

[philh]

Which service is grabbing the memory? Is it CF? SQL Server normally reserves
all the memory it can at startup, so I doubt you're seeing a "spike" in SQL's
memory usage.

The best way to design this function is to put it in SQL. If the process is
truly "monthly", you can just run a scheduled job against a stored procedure.
If some interaction is needed for parameters, you can still offload the heavy
lifting to SQL, using a one-time scheduled job.

Let me know if you're interested in either of these options. IMHO, monkeying
around with 7.7 million records in the CF mem space is just asking for trouble.