Re: How to clear memory usage after executing queries ?

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 11:42:04 -0500

The second query will likely pick up a lot of its data pages in cache, since
the first query put them there. SQL Server loves memory and the more you
feed it the merrier. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message
news:21BFB863-85BC-4DA4-93B6-58810CD95AA1@microsoft.com...
Thank you all for your replies.
Yes, I would like to keep the data in cache for subsequent queries, but I
got scared when the memory usage reached 1.7 gig (the machine has 2 gig of
memory). So, I should just leave this alone ?
If I do query
select distinct(packet_contract) from packet, then do query
select packet_data from packet where packet_time >= dateadd(hour, -96,
getdate()) and packet_contract = 'ABCD', will the cache be cleared because
the 2nd query is a different query than the 1st query ?
Thanks.
"Tom Moreau" wrote:
> You don't need to do that.  SQL Server will take as much memory as it
wants.
> You can limit this with sp_configure.
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message
> news:2A608602-0DE6-46C8-971F-2F7F9C68ECF7@microsoft.com...
> We have a SQL Server 2000 database that has about 10 million records. The
> database has 1 table, and the table has 3 columns.
> CREATE TABLE [dbo].[Packet] (
> [PACKET_TIME] [datetime] NOT NULL ,
> [PACKET_CONTRACT] [varchar] (8) NOT NULL ,
> [PACKET_DATA] [varchar] (2000) NOT NULL )
>
> I have a clustered indexed for column ([PACKET_CONTRACT],[PACKET_TIME]). I
> found this index execute the query that I want the fastest.
>
> When I query the database, for example:
> -select distinct(packet_contract) from packet (Returns 600 records)
> -select packet_data from packet where packet_time >= dateadd(hour, -96,
> getdate()) and packet_contract = 'ABCD' (returns 135,000 records)
>
> the memory usage for SQL Server shot up from 50,000 K to 275,000 K.
> The database currently has about 4 million records.
>
> I tried the following statements, but the memory is still not cleared up:
> -DBCC DROPCLEANBUFFERS
> -dbcc freeproccache
>
> The only way to clear up the memory usage is by stopping SQL Server
Service
> Manager.
> How can I clear up the memory usage after executing a query without
stopping
> SQL Server Service Manager ?
> Thanks a lot.
>
>
>

Relevant Pages

  • Re: FTS Performance in SQL 2005
    ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Full Text Search Performance
    ... Without actually testing English vs. Arabic & using the Neutral wordbreaker ... itself account for this poor query performance. ... See SQL Server 2000 BOL title "SQL Full-text Search ... machine's memory configuration you should be able to ensure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Full Text Search Performance
    ... You're welcome, Ali, ... Beta2 release for MSDN subscribers) as the new version of SQL Server has ... No, this is not normal query for a table of this size, but the number of ... > about resource usage, memory and drives...etc. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem > 1 GB memory
    ... > Is there any way to 1) not to load the whole table on memory in order ... SQL Server maintains a cache of data that is accessed frequently. ... If there is no index on customer ...
    (comp.databases.ms-sqlserver)
  • Re: Memory Usage/Hog
    ... > Secondly, without having more detail information about my application, it would be difficult to ... There are several ways a query plan can be re-used. ... > that the OS uses to ask SQL Server for the memory back. ...
    (microsoft.public.sqlserver.server)
Loading