Re: How to clear memory usage after executing queries ?
From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/23/04
- Next message: ChrisR: ""disallowed construct" on indexed view"
- Previous message: CJR: "Holidays"
- In reply to: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Next in thread: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Reply: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Dec 2004 08:37:04 -0800
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.
>
>
>
- Next message: ChrisR: ""disallowed construct" on indexed view"
- Previous message: CJR: "Holidays"
- In reply to: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Next in thread: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Reply: Tom Moreau: "Re: How to clear memory usage after executing queries ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
