Re: How to clear memory usage after executing queries ?
From: Stephen Dybing [MSFT] (stephd_at_online.microsoft.com)
Date: 12/24/04
- Next message: JJ Wang: "URGENT: distribution agent failure: NT AUTHORITY\ANONYMOUS LOGON"
- Previous message: John Bell: "Re: How to get tree by OPENXML?"
- 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 17:32:46 -0800
Uh, Tom, if the pages that have been the cache the longest are constantly
being referenced, they'll remain in the cache. It's the least recently
accessed pages that get purged. I'm quite sure you knew that so it must be
vacation time. :-)
-- Sincerely, Stephen Dybing This posting is provided "AS IS" with no warranties, and confers no rights. Please reply to the newsgroups only, thanks. "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OwlWOFU6EHA.2568@TK2MSFTNGP11.phx.gbl... > No, SQL Server will push pages out of memory only if it has to read new > pages into memory. The pages that get purged are those that have been > there > the longest. If you simply run one query and never run anything else, > those > pages will still be there. > > -- > 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:571924C2-EB39-45B1-9FFF-B694314F425F@microsoft.com... > Thank you. > So say that SQL Server has cached many data in memory, and if you do not > query SQL Server for a while, this cached data will be dropped from memory > ? > How long before this data will be dropped from the cache ? > > So, say now SQL Server used up 1.7 gig of memory, and if you do not query > SQL Server for a while, SQL Server memory usage will drop from 1.7 gig ? > > Thanks. > > "Tom Moreau" wrote: > >> That's essentially what happens. It ages out the oldest pages and then >> goes >> to disk for anything not in cache. In the ideal world, your entire DB >> would >> fit in cache. >> >> -- >> 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:23C3AEE0-4767-4CAC-A8A3-BC502DE65086@microsoft.com... >> Thanks a lot. >> If the memory that SQL Server has is not enough for the query to run, >> will >> it just make the query to execute slower ? >> >> Thanks. >> >> "Tom Moreau" wrote: >> >> > I wouldn't worry. If you think your OS might need more than 300MB, you >> can >> > cap SQL Server memory at 1.5. SQL Server automatically yields memory >> > back >> > to the OS anyway. >> > >> > -- >> > 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:D2F23B51-BEE0-4E1C-84E7-9612D393B93D@microsoft.com... >> > Thank you. >> > So even if the SQL Server memory usage reached 1.7 gig (the machine has >> > 2 >> > gig of >> > memory), I should just leave this alone right? >> > >> > Thanks. >> > >> > >> > "Tom Moreau" wrote: >> > >> > > 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. >> > > > >> > > > >> > > > >> > > >> > > >> > >> > >> >> >
- Next message: JJ Wang: "URGENT: distribution agent failure: NT AUTHORITY\ANONYMOUS LOGON"
- Previous message: John Bell: "Re: How to get tree by OPENXML?"
- 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
|
