Showing posts with label program. Show all posts
Showing posts with label program. Show all posts

Monday, March 19, 2012

Memory usage increasing

Hi,
I open one of the modules in my program and then close
it. I did not end the program. The memory usage in the
SQL server does not decrease or remove from it. I check
it from the Process Info at the Management folder in the
database. How to release the memory from the SQL server?
Is it the programming method that I am using or this
behaviour is normal?
Please help. Thanks
If you are referring to the database server, this behaviour is normal. SQL
Server will try to claim all system memory for itself, less ~5 MB, upon
startup. It works more efficiently that way, hence the often repeated
advice that you should try to use a dedicated database sever. Thus, you
might have 100 connections and close all of them, but the memory used by SQL
Server will not decrease.
If you want to, you can limit the min. and max. amount of memory SQL Server
can use from Enterprise Manager.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"carina" <carina_a@.yahoo.com> wrote in message
news:19fd501c44ddd$038649a0$a501280a@.phx.gbl...
> Hi,
> I open one of the modules in my program and then close
> it. I did not end the program. The memory usage in the
> SQL server does not decrease or remove from it. I check
> it from the Process Info at the Management folder in the
> database. How to release the memory from the SQL server?
> Is it the programming method that I am using or this
> behaviour is normal?
> Please help. Thanks

Memory usage increasing

Hi,
I open one of the modules in my program and then close
it. I did not end the program. The memory usage in the
SQL server does not decrease or remove from it. I check
it from the Process Info at the Management folder in the
database. How to release the memory from the SQL server?
Is it the programming method that I am using or this
behaviour is normal?
Please help. ThanksIf you are referring to the database server, this behaviour is normal. SQL
Server will try to claim all system memory for itself, less ~5 MB, upon
startup. It works more efficiently that way, hence the often repeated
advice that you should try to use a dedicated database sever. Thus, you
might have 100 connections and close all of them, but the memory used by SQL
Server will not decrease.
If you want to, you can limit the min. and max. amount of memory SQL Server
can use from Enterprise Manager.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"carina" <carina_a@.yahoo.com> wrote in message
news:19fd501c44ddd$038649a0$a501280a@.phx.gbl...
> Hi,
> I open one of the modules in my program and then close
> it. I did not end the program. The memory usage in the
> SQL server does not decrease or remove from it. I check
> it from the Process Info at the Management folder in the
> database. How to release the memory from the SQL server?
> Is it the programming method that I am using or this
> behaviour is normal?
> Please help. Thanks

Memory usage increasing

Hi,
I open one of the modules in my program and then close
it. I did not end the program. The memory usage in the
SQL server does not decrease or remove from it. I check
it from the Process Info at the Management folder in the
database. How to release the memory from the SQL server?
Is it the programming method that I am using or this
behaviour is normal?
Please help. ThanksIf you are referring to the database server, this behaviour is normal. SQL
Server will try to claim all system memory for itself, less ~5 MB, upon
startup. It works more efficiently that way, hence the often repeated
advice that you should try to use a dedicated database sever. Thus, you
might have 100 connections and close all of them, but the memory used by SQL
Server will not decrease.
If you want to, you can limit the min. and max. amount of memory SQL Server
can use from Enterprise Manager.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"carina" <carina_a@.yahoo.com> wrote in message
news:19fd501c44ddd$038649a0$a501280a@.phx
.gbl...
> Hi,
> I open one of the modules in my program and then close
> it. I did not end the program. The memory usage in the
> SQL server does not decrease or remove from it. I check
> it from the Process Info at the Management folder in the
> database. How to release the memory from the SQL server?
> Is it the programming method that I am using or this
> behaviour is normal?
> Please help. Thanks

Memory Usage Grows over time

I am very new to TSQL and the program that follows loops many times over several hours. In task manager I have noticed that the PF usage has grown from 1.08 to 2.05 over several hours and results in a virtual memory shortage error being displayed. I am inclined to believe I am doing something wrong and accumulating some stack space. Can anyone tell me what I am doing wrong? PF does drop back to normal when I drop out of SQL.

thanks in advance

-Soup-

use sm

declare @.test_date as smalldatetime

declare @.ticker char(6)

declare @.buy_date smalldatetime

declare @.buy_price smallmoney

declare @.sell_date_5d smalldatetime

declare @.sell_price_5d smallmoney

declare @.sell_date_10d smalldatetime

declare @.sell_price_10d smallmoney

declare @.sell_date_20d smalldatetime

declare @.sell_price_20d smallmoney

if object_id('sm.dbo.Open_High_v2')is not null

drop table sm.dbo.Open_High

create table sm.dbo.Open_High

( ticker char(6),

detect_date smalldatetime,

buy_date smalldatetime,

buy_price smallmoney,

sell_date_5d smalldatetime,

sell_price_5d smallmoney,

sell_date_10d smalldatetime,

sell_price_10d smallmoney,

sell_date_20d smalldatetime,

sell_price_20d smallmoney,

)

if object_id('sm.dbo.Temp_Price')is null

create table sm.dbo.Temp_Price

(

price char (6),

Trade_Date smalldatetime,

[Open] smallmoney,

High smallmoney,

Low smallmoney,

[Close] smallmoney,

Volume int

)

if object_id('date_list') is not null

begin

close date_list

deallocate date_list

end

declare date_list cursor for

select distinct ticker, trade_date

from price

order by Ticker asc, Trade_Date asc

open date_list

fetch next from date_list into @.ticker,@.test_date

while (@.@.fetch_status=0)

begin

set @.buy_date=NULL

set @.buy_price=NULL

set @.sell_date_5d=NULL

set @.sell_price_5d=NULL

set @.sell_date_10d=NULL

set @.sell_price_10d=NULL

set @.sell_date_20d=NULL

set @.sell_price_20d=NULL

insert into sm.dbo.temp_price

select Top 5 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.buy_date=(select min(trade_date)from sm.dbo.temp_price)

set @.buy_price=(select [open] from sm.dbo.temp_price where trade_date=@.buy_date)

set @.sell_price_5d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_5d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

insert into sm.dbo.temp_price

select Top 10 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.sell_price_10d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_10d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

insert into sm.dbo.temp_price

select Top 20 ticker,trade_date,[open],high,low,[close],volume

from price as p

where p.Ticker=@.Ticker and p.Trade_date>@.Test_date

order by p.Trade_Date asc

set @.sell_price_20d=(select max(high)from sm.dbo.temp_price)

set @.sell_date_20d=(select top 1 (trade_date) from sm.dbo.temp_price where high = @.sell_price_5d order by trade_date asc)

truncate table sm.dbo.temp_price

BEGIN TRANSACTION;

insert into sm.dbo.open_high_v2

values

(

@.ticker,

@.test_date,

@.buy_date,

@.buy_price,

@.sell_date_5d,

@.sell_price_5d,

@.sell_date_10d,

@.sell_price_10d,

@.sell_date_20d,

@.sell_price_20d

)

COMMIT TRANSACTION;

fetch next from date_list into @.ticker,@.test_date

end

close date_list

deallocate date_list

select * from sm.dbo.Open_High

One cause may be you're using transactions without error checking which reserves memory. If you're just inserting without error checking, you don't need to use transactions.

Adamus

|||

transactions was a desparate attempt to stop pf growth. pf growth was present before transaction start and commit were added.

Thanks for the idea.

-Soup-

|||

After looking at the code further, it appears the overhead you're concerned with is a necessary evil in order to accomplish your task.

Although performance should always be a concern, sometimes the elephant in the living room serves a productive and inevitable purpose.

Adamus

|||

Without further informaion on what I am doing incorrectly in TSQL, I guess it is time to turn in a bug report to the SQL developement team. Repeated loops of a program appear to discover a problem with TSQL or SQL server. Is this an acceptable trouble report process, or do I need to go somewhere else to enter this as a formal trouble report?

-Soup-

|||

I didn't understand your trouble..

There are someother way there to achive your desired output.. why not, you can try to rewrite your query in PROPER & best way.. as other says..

|||I have to withdraw previous post about memory growing over time. Something other than this program must have been the problem. Regarding Proper & best way, as I said I am new to TSQL and have know idea how this program differs from Proper & best way. I do know that this program may have been a learning experience for me, but I allowed it to execute for 11 Days, yes Days and it did not complete. After only 8 hours a C++ program did the job. I suspect my skill at TSQL is at fault.|||

For improving performance of above SQL queries, You can use "table" data type instead of physical temp tables temp_price and Open_High_v2. Another problem is cursor. First you can take all data from "price" table into another temp table. This temp table also derived by "table" datatype. And apply cursor on this table. Next you can remove "Begin Transaction" and "commit transaction" statements...

Next check performance of TSQL...

Jefy

|||Lay off the crack pipe Jefy

Wednesday, March 7, 2012

Memory problems

Hi!

Sometimes i have memory problem when my program wants to access an sql mobile database.
SqlXXXdll could not be loaded...please reintsall it...

I used .Net Compact Framework Remote Performance Monitor but it was not so helpful to find the memory
problem.

After that i tried dumpmem utility:
http://support.microsoft.com/default.aspx?scid=kb;en-us;326164
and i really see there is no more memory above the heap:

Process Memory Dump
MainProject.exe

Virtual Memory Dump
======================
Legend
C - Committed
R - Reserved
F - Free

RO - Read Only
RW - Read Write
WC - Write Copy
X - Execute
XR - Execute Read
XRW - Execute Read Write
XWC - Execute Write Copy
G - Guard
NA - No Access
NC - No Cache

2E000000 10000 F NA
2E010000 2000 R NA Image <--Heap start
2E012000 1000 C RW Image
2E013000 43000 R NA Image
2E056000 1000 C RO Image
2E057000 1000 R NA Image
2E058000 1000 C RO Image
2E059000 1000 R NA Image
2E05A000 1000 C RO Image
2E05B000 1000 R NA Image
2E05C000 4000 F NA
2E060000 1000 C RW Private
2E061000 F000 F NA
2E070000 B000 R NA Private
2E07B000 5000 C RW Private
2E080000 2F000 C RW Private
2E0AF000 1000 F NA
2E0B0000 12000 C RW Private
2E0C2000 1D000 R NA Private
2E0DF000 1000 F NA
2E0E0000 2F000 C RW Private
2E10F000 1000 F NA
2E110000 2F000 C RW Private
2E13F000 1000 F NA
2E140000 9000 C RW Private
2E149000 26000 R NA Private
2E16F000 1000 F NA
2E170000 F000 R NA Private
2E17F000 1000 C RW Private
2E180000 F000 R NA Private
2E18F000 1000 C RW Private
2E190000 E000 R NA Private
2E19E000 2000 C RW Private
2E1A0000 E000 R NA Private
2E1AE000 2000 C RW Private
2E1B0000 F000 R NA Private
2E1BF000 1000 C RW Private
2E1C0000 10000 C RW Private
2E1D0000 2F000 C RW Private
2E1FF000 1000 F NA
2E200000 11000 C RW Private
2E211000 F000 F NA
2E220000 17000 C RW Private
2E237000 18000 R NA Private
2E24F000 21000 F NA
2E270000 97000 C RW Private
2E307000 9000 R NA Private
2E310000 97000 C RW Private
2E3A7000 9000 R NA Private
2E3B0000 10000 F NA
2E3C0000 1B000 C RW Private
2E3DB000 5000 R NA Private
2E3E0000 1B000 C RW Private
2E3FB000 5000 R NA Private
2E400000 2F000 C RW Private
2E42F000 1000 F NA
2E430000 E000 R NA Private
2E43E000 2000 C RW Private
2E440000 2F000 C RW Private
2E46F000 21000 F NA
2E490000 2F000 C RW Private
2E4BF000 11000 F NA
2E4D0000 2F000 C RW Private
2E4FF000 1000 F NA
2E500000 2F000 C RW Private
2E52F000 1000 F NA
2E530000 10000 C RW Private
2E540000 2F000 C RW Private
2E56F000 1000 F NA
2E570000 2F000 C RW Private
2E59F000 1000 F NA
2E5A0000 2F000 C RW Private
2E5CF000 1000 F NA
2E5D0000 2F000 C RW Private
2E5FF000 1000 F NA
2E600000 2F000 C RW Private
2E62F000 1000 F NA
2E630000 2F000 C RW Private
2E65F000 1000 F NA
2E660000 2F000 C RW Private
2E68F000 1000 F NA
2E690000 2F000 C RW Private
2E6BF000 1000 F NA
2E6C0000 2F000 C RW Private
2E6EF000 21000 F NA
2E710000 10000 C RW Private
2E720000 10000 C RW Private
2E730000 1F000 R NA Private
2E74F000 31000 F NA
2E780000 2F000 C RW Private
2E7AF000 1000 F NA
2E7B0000 2F000 C RW Private
2E7DF000 1000 F NA
2E7E0000 2F000 C RW Private
2E80F000 11000 F NA
2E820000 1E000 R NA Private
2E83E000 2000 C RW Private
2E840000 2F000 C RW Private
2E86F000 1000 F NA
2E870000 2F000 C RW Private
2E89F000 1000 F NA
2E8A0000 2F000 C RW Private
2E8CF000 1000 F NA
2E8D0000 2F000 C RW Private
2E8FF000 1000 F NA
2E900000 F000 R NA Private
2E90F000 1000 C RW Private
2E910000 97000 C RW Private
2E9A7000 9000 R NA Private
2E9B0000 F000 R NA Private
2E9BF000 1000 C RW Private
2E9C0000 D000 R NA Private
2E9CD000 3000 C RW Private
2E9D0000 1000 C RW Private
2E9D1000 2E000 R NA Private
2E9FF000 11000 F NA
2EA10000 2F000 C RW Private
2EA3F000 1000 F NA
2EA40000 2F000 C RW Private
2EA6F000 1000 F NA
2EA70000 2F000 C RW Private
2EA9F000 1000 F NA
2EAA0000 24000 C RW Private
2EAC4000 B000 R NA Private
2EACF000 1000 F NA <-- the free space between the dlls and the heap
2EAD0000 1000 R NA Image <-- and this is where the first non xip dll starts

What i dont understand is there are a lot of big free blocks in the heap (31000 == 196KB), i called the
GC.Collect and the GC.WaitForPendingFinalizers but its not shrinked physically (maybe just logically).

1) Is there any solution to remove this big free gaps in the heap area?
2) Is there any solution to determine what are these big blocks in the heap 97000 == 600Kb. (Ok i know code reviewStick out tongue)
I load more 40 png pictures and maybe they consumes a lot of mem.
3) Is there any solution to analyze memory more deep with any tool?

Thanks for help!

what is the purpose for load 40 pictures

do not load them once, because they consumes the memory.

just load them one by one ....

advice:

do not work with GC, let the system do it normally

|||1. ok it doesnt need to load every pics at once but i have a form where i need 20 icon (i use it dynamically)
2. i dont work with gc just checked what occurs with the heap in the real memory

I dont know why was this post answer to my question...

Memory problems

Hi!

Sometimes i have memory problem when my program wants to access an sql mobile database.
SqlXXXdll could not be loaded...please reintsall it...

I used .Net Compact Framework Remote Performance Monitor but it was not so helpful to find the memory
problem.

After that i tried dumpmem utility:
http://support.microsoft.com/default.aspx?scid=kb;en-us;326164
and i really see there is no more memory above the heap:

Process Memory Dump
MainProject.exe

Virtual Memory Dump
======================
Legend
C - Committed
R - Reserved
F - Free

RO - Read Only
RW - Read Write
WC - Write Copy
X - Execute
XR - Execute Read
XRW - Execute Read Write
XWC - Execute Write Copy
G - Guard
NA - No Access
NC - No Cache

2E000000 10000 F NA
2E010000 2000 R NA Image <--Heap start
2E012000 1000 C RW Image
2E013000 43000 R NA Image
2E056000 1000 C RO Image
2E057000 1000 R NA Image
2E058000 1000 C RO Image
2E059000 1000 R NA Image
2E05A000 1000 C RO Image
2E05B000 1000 R NA Image
2E05C000 4000 F NA
2E060000 1000 C RW Private
2E061000 F000 F NA
2E070000 B000 R NA Private
2E07B000 5000 C RW Private
2E080000 2F000 C RW Private
2E0AF000 1000 F NA
2E0B0000 12000 C RW Private
2E0C2000 1D000 R NA Private
2E0DF000 1000 F NA
2E0E0000 2F000 C RW Private
2E10F000 1000 F NA
2E110000 2F000 C RW Private
2E13F000 1000 F NA
2E140000 9000 C RW Private
2E149000 26000 R NA Private
2E16F000 1000 F NA
2E170000 F000 R NA Private
2E17F000 1000 C RW Private
2E180000 F000 R NA Private
2E18F000 1000 C RW Private
2E190000 E000 R NA Private
2E19E000 2000 C RW Private
2E1A0000 E000 R NA Private
2E1AE000 2000 C RW Private
2E1B0000 F000 R NA Private
2E1BF000 1000 C RW Private
2E1C0000 10000 C RW Private
2E1D0000 2F000 C RW Private
2E1FF000 1000 F NA
2E200000 11000 C RW Private
2E211000 F000 F NA
2E220000 17000 C RW Private
2E237000 18000 R NA Private
2E24F000 21000 F NA
2E270000 97000 C RW Private
2E307000 9000 R NA Private
2E310000 97000 C RW Private
2E3A7000 9000 R NA Private
2E3B0000 10000 F NA
2E3C0000 1B000 C RW Private
2E3DB000 5000 R NA Private
2E3E0000 1B000 C RW Private
2E3FB000 5000 R NA Private
2E400000 2F000 C RW Private
2E42F000 1000 F NA
2E430000 E000 R NA Private
2E43E000 2000 C RW Private
2E440000 2F000 C RW Private
2E46F000 21000 F NA
2E490000 2F000 C RW Private
2E4BF000 11000 F NA
2E4D0000 2F000 C RW Private
2E4FF000 1000 F NA
2E500000 2F000 C RW Private
2E52F000 1000 F NA
2E530000 10000 C RW Private
2E540000 2F000 C RW Private
2E56F000 1000 F NA
2E570000 2F000 C RW Private
2E59F000 1000 F NA
2E5A0000 2F000 C RW Private
2E5CF000 1000 F NA
2E5D0000 2F000 C RW Private
2E5FF000 1000 F NA
2E600000 2F000 C RW Private
2E62F000 1000 F NA
2E630000 2F000 C RW Private
2E65F000 1000 F NA
2E660000 2F000 C RW Private
2E68F000 1000 F NA
2E690000 2F000 C RW Private
2E6BF000 1000 F NA
2E6C0000 2F000 C RW Private
2E6EF000 21000 F NA
2E710000 10000 C RW Private
2E720000 10000 C RW Private
2E730000 1F000 R NA Private
2E74F000 31000 F NA
2E780000 2F000 C RW Private
2E7AF000 1000 F NA
2E7B0000 2F000 C RW Private
2E7DF000 1000 F NA
2E7E0000 2F000 C RW Private
2E80F000 11000 F NA
2E820000 1E000 R NA Private
2E83E000 2000 C RW Private
2E840000 2F000 C RW Private
2E86F000 1000 F NA
2E870000 2F000 C RW Private
2E89F000 1000 F NA
2E8A0000 2F000 C RW Private
2E8CF000 1000 F NA
2E8D0000 2F000 C RW Private
2E8FF000 1000 F NA
2E900000 F000 R NA Private
2E90F000 1000 C RW Private
2E910000 97000 C RW Private
2E9A7000 9000 R NA Private
2E9B0000 F000 R NA Private
2E9BF000 1000 C RW Private
2E9C0000 D000 R NA Private
2E9CD000 3000 C RW Private
2E9D0000 1000 C RW Private
2E9D1000 2E000 R NA Private
2E9FF000 11000 F NA
2EA10000 2F000 C RW Private
2EA3F000 1000 F NA
2EA40000 2F000 C RW Private
2EA6F000 1000 F NA
2EA70000 2F000 C RW Private
2EA9F000 1000 F NA
2EAA0000 24000 C RW Private
2EAC4000 B000 R NA Private
2EACF000 1000 F NA <-- the free space between the dlls and the heap
2EAD0000 1000 R NA Image <-- and this is where the first non xip dll starts

What i dont understand is there are a lot of big free blocks in the heap (31000 == 196KB), i called the
GC.Collect and the GC.WaitForPendingFinalizers but its not shrinked physically (maybe just logically).

1) Is there any solution to remove this big free gaps in the heap area?
2) Is there any solution to determine what are these big blocks in the heap 97000 == 600Kb. (Ok i know code reviewStick out tongue)
I load more 40 png pictures and maybe they consumes a lot of mem.
3) Is there any solution to analyze memory more deep with any tool?

Thanks for help!

what is the purpose for load 40 pictures

do not load them once, because they consumes the memory.

just load them one by one ....

advice:

do not work with GC, let the system do it normally

|||1. ok it doesnt need to load every pics at once but i have a form where i need 20 icon (i use it dynamically)
2. i dont work with gc just checked what occurs with the heap in the real memory

I dont know why was this post answer to my question...

Memory problem

I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
Hi,
I guess that the SQL Server is configured to use Dynamic Memory
Allocations.
SQL Server memory will continue to grow as long as there is no RAM pressure
in
the machine. As long as SQL Server feels some pressure on memory pool, it
will
start to release memory for other applications use or to avoid swapping.
Try
setting the Min Server and Max Server memory to a know value then run the
same
applications. You should see the server memory grow to that limit and then
maintain on that level.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Memory problem

I have an accounting program that is loaded on a server
and have 7 users. The program seem to have a memory leak.
The has server 1 gig of memory after the program runs for
a few hours the memory usage climes from 150meg used to
870meg, used all of witch is used by the sql instance. I
then have to have eveyone get out of the program stop the
sql server then restart it. Is there any thing out there
that can help me(beside buying a new accounting program
that is writen well),some type of auto resource recycling,
or auto restarting the sql instance with out causing the
users any to be down.
thank
Daniel Barnaby
Network Tech
Belton School District #124
hi Daniel,
"Daniel Barnaby" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:57e701c42d34$9e0bcfa0$a301280a@.phx.gbl...
> I have an accounting program that is loaded on a server
> and have 7 users. The program seem to have a memory leak.
> The has server 1 gig of memory after the program runs for
> a few hours the memory usage climes from 150meg used to
> 870meg, used all of witch is used by the sql instance. I
> then have to have eveyone get out of the program stop the
> sql server then restart it. Is there any thing out there
> that can help me(beside buying a new accounting program
> that is writen well),some type of auto resource recycling,
> or auto restarting the sql instance with out causing the
> users any to be down.
you must first understand memory usage by SQL Server...
in few words, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
it's quite a normal behaviour...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Monday, February 20, 2012

Memory Low after running for a day

A program is used to insert data record by record to a SQL Server (2000)
using transaction. After a day, 300M or memory was used up. What would be
the cause of such "memory leak" and how to releasing the memory other than
reboot? Thank you.> A program is used to insert data record by record to a SQL Server (2000)
> using transaction. After a day, 300M or memory was used up. What would be
> the cause of such "memory leak" and how to releasing the memory other than
> reboot? Thank you.
This is not a memory leak, this is how it has to work. SQL Server caches
execution plans and the data, grabs memory when it needs it, but releases it
only on a demand. If you have to, you can limit the memory SQL Server uses
with the maximum memory configuration option
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org