|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > How does SQL 2000 dynamically manage memory?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How does SQL 2000 dynamically manage memory?
|
|
|
| I have an application server connecting to my SQL 2000
server and the performance is poor for large
transactions. The network is fast and hardly shows any
utilization. After looking at the code and app server
resources I suspect the SQL server. The processors are
that heavily used (25% under the workload described) yet
available memory doesn't drop below 200MB. It seems like
the SQL server should consume more memory since it's set
to dynamically allocate memory. How does it determine how
much memory it can and should use so it won't crash the OS?
| |
| BP Margolin 2002-08-01, 7:25 pm |
| Randy,
> the performance is poor for large transactions
99 times out of 100, poor performance is due to poorly written code or a
lack of necessary indexes.
Have you had a chance to use the Index Tuning Wizard to see if it suggests a
different indexing strategy?
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Randy" <rcolumb@cogentric.com> wrote in message
news:019501c239bd$95dcc170$36e
f2ecf@tkmsftngxa12...
> I have an application server connecting to my SQL 2000
> server and the performance is poor for large
> transactions. The network is fast and hardly shows any
> utilization. After looking at the code and app server
> resources I suspect the SQL server. The processors are
> that heavily used (25% under the workload described) yet
> available memory doesn't drop below 200MB. It seems like
> the SQL server should consume more memory since it's set
> to dynamically allocate memory. How does it determine how
> much memory it can and should use so it won't crash the OS?
| |
| linda deng[MS] 2002-08-01, 8:25 pm |
| Hi Randy,
By default, SQL Server changes its memory requirements dynamically, based
on available system resources. It dynamically occupies and releases the
memory from or to the operating system. If SQL Server needs more memory,
it queries the operating system to determine whether free physical memory
is available and uses the available memory. If SQL Server does not need the
memory currently allocated to it, it releases the memory to the operating
system. The option to dynamically use memory can be overridden using the
min server memory, max server memory, and set working set size server
configuration options. However, it is strongly recommended that you keep
the SQL Server to dynamically manage the memory. For more information about
the memory of SQL Server, please refer to Microsoft SQL Server Books Online.
As for the application performance, there are some useful KB articles to
help you to locate the problem. Please refer to the information under the
link http://support.microsoft.com/defaul...;en-us;Q224587.
If anything is unclear, please let me know.
Sincerely,
Linda Deng
Microsoft Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
| |
| Gert-Jan Strik 2002-08-02, 5:25 am |
| use sp_Lock to check if there is a locking problem. If the transaction
is really stressing the system, then either I/O should be > 80% and/or
CPU should be > 80% during execution.
Hope this helps,
Gert-Jan
Randy wrote:
>
> I have an application server connecting to my SQL 2000
> server and the performance is poor for large
> transactions. The network is fast and hardly shows any
> utilization. After looking at the code and app server
> resources I suspect the SQL server. The processors are
> that heavily used (25% under the workload described) yet
> available memory doesn't drop below 200MB. It seems like
> the SQL server should consume more memory since it's set
> to dynamically allocate memory. How does it determine how
> much memory it can and should use so it won't crash the OS?
|
|
|
|
|