Memory usage of SQL Server 2005 Mobile Edition

Hi forum readers,

we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.

We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?

Does anyone have a deeper insight into that server an can answer my questions.

Best regards,
Tobias

No, we don't load entire 5MB database into program memory. Only required pages are loaded to the buffer pool as and when required.

When database is loaded, lot of runtime structures are maintained for multi proc/thread synchronization. Some of these are maintained in shared memory. This total shared memory size figure is dependent mainly on the configurable parameters buffer pool size, max database size etc. If you increase them the figure goes up.

Assuming buffer pool size is the default (160 pages ~ 160*4096), we can roughly estimate the total size to be 800 KB + (max database size/1024) KB.

For ex: for a 60MB max size database, it would roughly 860 KB.

for a 2GB max size database, it would be roughly 2800 KB

Apart from this there would be a lot of other objects that are created & maintained as the client requests. For ex rowsets/cursors. It will be too difficult to estimate these as these are created based on the client code/usage.

Thanks - Raja

|||

Hi Raja,

Thank you very much, this gives us a better understanding of the required memory and the loading process.

Best Regards,

Tobias

Memory Reserve

Hi All
I just want to know when some queries has completed on SQL 2000 whether the
memory that reserved for it will release after completion the process.
In current sql server which Im using having major issue with memory usage.
It is catching memory when some process is happening, but it won't release
that memory even after hours of that process took place.
Please send some advise
Thaks in advance
JanakaReleasing the memory after a query has executed would defeat the whole purpose of caching data. We
do not want that. I suggest you investigate the issue further and see if it is a real problem to you
(how do you determine that the behavior of SQL Server is a problem), and if for instance setting a
max for memory solves your problem. Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Janaka Sampath" <janakaj@.lankaequities.com> wrote in message
news:uubaDh27FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi All
>
> I just want to know when some queries has completed on SQL 2000 whether the
> memory that reserved for it will release after completion the process.
> In current sql server which Im using having major issue with memory usage.
> It is catching memory when some process is happening, but it won't release
> that memory even after hours of that process took place.
>
> Please send some advise
> Thaks in advance
> Janaka
>

Memory Reserve

Hi All
I just want to know when some queries has completed on SQL 2000 whether the
memory that reserved for it will release after completion the process.
In current sql server which Im using having major issue with memory usage.
It is catching memory when some process is happening, but it won't release
that memory even after hours of that process took place.
Please send some advise
Thaks in advance
Janaka
Releasing the memory after a query has executed would defeat the whole purpose of caching data. We
do not want that. I suggest you investigate the issue further and see if it is a real problem to you
(how do you determine that the behavior of SQL Server is a problem), and if for instance setting a
max for memory solves your problem. Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Janaka Sampath" <janakaj@.lankaequities.com> wrote in message
news:uubaDh27FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi All
>
> I just want to know when some queries has completed on SQL 2000 whether the
> memory that reserved for it will release after completion the process.
> In current sql server which Im using having major issue with memory usage.
> It is catching memory when some process is happening, but it won't release
> that memory even after hours of that process took place.
>
> Please send some advise
> Thaks in advance
> Janaka
>

Memory Reserve

Hi All
I just want to know when some queries has completed on SQL 2000 whether the
memory that reserved for it will release after completion the process.
In current sql server which Im using having major issue with memory usage.
It is catching memory when some process is happening, but it won't release
that memory even after hours of that process took place.
Please send some advise
Thaks in advance
JanakaReleasing the memory after a query has executed would defeat the whole purpo
se of caching data. We
do not want that. I suggest you investigate the issue further and see if it
is a real problem to you
(how do you determine that the behavior of SQL Server is a problem), and if
for instance setting a
max for memory solves your problem. Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Janaka Sampath" <janakaj@.lankaequities.com> wrote in message
news:uubaDh27FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi All
>
> I just want to know when some queries has completed on SQL 2000 whether th
e
> memory that reserved for it will release after completion the process.
> In current sql server which Im using having major issue with memory usage.
> It is catching memory when some process is happening, but it won't release
> that memory even after hours of that process took place.
>
> Please send some advise
> Thaks in advance
> Janaka
>

Memory release

I am using SQL Server 2000 SE. When I used my application, the SQL server memory go on increasing & it will never come down. Application runs very slowly. Can anybody suggest how to release memory from sql server.

Thanks in advance.
SQL Server will release memory when something else on the machine requires the memory. If it is allocating more memory, then the SQL Server needs that memory to process requests. Just because the application is "running slowly" doesn't mean it is caused by SQL Server memory utilization.|||

Hi Michael, thank you very much.

I'm a SQL new programmer. I want to ask more.

Suppose some users use my application concurrently. They run different reports and these reports use almost server memory. I saw this in server. After that these users close reports. I watch memory in server, it isn't released. If new report runs, in this case SQL sever/OS will automatically reallocate memory for new task-report. SQL server/OS work this way, isn't it?

Thanks.

Memory Question

Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
--
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!

Memory Question

Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gig
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!
SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!
|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3 Gig
> in size. And the entire process costs 500M in memory usage. So every time
> after I run it, my system (as mentioned) just slows down to the point I have
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
> Thx in advance!
|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!

Memory Question

Hi,
Does SQL Server (Developer Edition, on XP Pro) ever release memory space
after processing a query? I have a query that runs against a database, 3 Gi
g
in size. And the entire process costs 500M in memory usage. So every time
after I run it, my system (as mentioned) just slows down to the point I have
to restart the sqlserver.exe to release that 500M memory space. Any idea?
Thx in advance!SQL Server releases memory only on OS request. Don't restart the service, if
any other application needs memory, it gets it. This is by design, because
SQL Server caches data and execution plans, so it can work faster.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!|||Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every tim
e
> after I run it, my system (as mentioned) just slows down to the point I ha
ve
> to restart the sqlserver.exe to release that 500M memory space. Any idea?
'
> Thx in advance!|||In addition to the other comments if you only have about 500MB I suggest you
set the MAX Memory setting in SQL Server to less than the max to try and
always leave some memory for the OS and other apps you run on your XP
machine. While SQL Server will release some memory when they demand it the
process can get a little painful on a workstation where lots of other things
are going on and there is little memory.
Andrew J. Kelly SQL MVP
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CB2E54EB-E94C-49EC-BBA3-00C89FCDDF4F@.microsoft.com...
> Hi,
> Does SQL Server (Developer Edition, on XP Pro) ever release memory space
> after processing a query? I have a query that runs against a database, 3
Gig
> in size. And the entire process costs 500M in memory usage. So every
time
> after I run it, my system (as mentioned) just slows down to the point I
have
> to restart the sqlserver.exe to release that 500M memory space. Any
idea?
> Thx in advance!

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.developersdex.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
>> For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
>> maximum set in the Management Studio. DOes it ever release and free up
>> the memory. For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
>> Darin
>> *** Sent via Developersdex http://www.developersdex.com ***
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creat
ing execution plans are
expensive operations. This is why SQL Server caches plans and pages. How wou
ld SQL Server know when
to release something? SQL Server will release memory when the OS becomes pre
ssured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max
server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.
gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>