SQL Server Performance Forum – Threads Archive

Memory usage

Hi there, We are running SQL Server 2000 Standard edition sp3 on Win2k.
The server has 3.98GB, but because of the standard edition only 2GB is used. SQL memory is configured as fixed sized to 2048MB and SET WORKING SIZE is true, ie "Reservre physical memory for SQL Serever" is checked in Enterprize manager. When I look at PerfMon on SQLServer:Memory Manager/Target Server Memory or Total Server Memory I see 1682MB. Even if I increase the fixed size memory to be even more than 2048MB I can’t see any change in those counters.
Also the counter "Working Set" of "Process" object for sqlsrvr shows 1798MB and the Available MB in the Memory object shows 1686MB
Q1:
where has the rest of the memory gone ? where are the other ~300mb ?
Q2:
what’s the different between all the obove memory counters that show around 1682mb and the "Working Set" counter which shows 1798MB
any explanantions ? thanks Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Are you starting SQL server services with any particular switch such as -g?
Are there any other applications sharing the server resources? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

None of those. No -g switch and the server is dedicated to SQL Server. Add to mention again, the server has almost 4GB RAM. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

To monitor the amount of memory being used by SQL Server, examine the following performance counters: -Process: Working Set
-SQL Server: Buffer Manager: Buffer Cache Hit Ratio
-SQL Server: Buffer Manager: Total Pages
-SQL Server: Memory Manager: Total Server Memory (KB) The remaining amount of memory 384mb will be used by MemToLeave area that is default values in SQL 2000. MemToLeave is calculated as:
max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting (default is 128MB on SQL 7.0 and 256MB on SQL Server 2000) I feel you’re not using AWE enabled. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you very much Satya.
That all makes sense. The Cache hit ratio is always around 100%.
Total pages are 208K which is about 1670MB and that is around the value of Total server memory. You are right, I don’t use AWE because we are running Standard edition. Can you please refer to some sotves about those 384MB for MemToLeave. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers that are running in-process such as COM objects, extended stored procedures, and linked servers. Any other allocations that occur in MemToLeave from these other components are not included because SQL Server has no knowledge of these memory requests. Memory that is used by general memory consumers in the server, including parsing or normalization, locks, transaction context, internal data structures describing the in-memory metadata for tables and indexes, and others. This value is the total number of 8 KB buffers. Some of these may be stolen buffers from the buffer pool, and others may be from MemToLeave. Run DBCC MEMORYSTATUS for more information on memroy allocation/usage on that server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

A client of ours has a server with 3gb of memory and SQL Server is configured in Enterprise Manager to use 0 – 3gb. Are you saying it may never use more than 2gb anyway because of limitations in windows? I’ve been checking this box quite frequently lately and it has never more than ~2gb of memory used. I figured it was because SQL Server thought it had enough and didn’t allocate more, but perhaps that is not the case then? /linus —
http://anticAPSLOCK.com

quote:Originally posted by mccabe A client of ours has a server with 3gb of memory and SQL Server is configured in Enterprise Manager to use 0 – 3gb. Are you saying it may never use more than 2gb anyway because of limitations in windows?

What edition of SQL Server is your client using? — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

I realized after I posted that it was the edition of SQL Server you were refering to in previous posts. They have standard edition, and from what I’ve found out that means max 2gb of mem?
The pricing difference between Standard and Enterprise is quite a chunk of money, iirc.
For the sole reason of allowing SQL Server to use more memory, it does not seem worth the cost. /linus —
http://anticAPSLOCK.com

quote:Originally posted by mccabe They have standard edition, and from what I’ve found out that means max 2gb of mem?

Yes.

quote:Originally posted by mccabe The pricing difference between Standard and Enterprise is quite a chunk of money, iirc.
For the sole reason of allowing SQL Server to use more memory, it does not seem worth the cost.

You know that there are more differences, don’t you?
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6335 — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

And you will get more benefits if you spend more, and cannot compare between the SE & EE advantages. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

quote:Originally posted by satya And you will get more benefits if you spend more…

What do you mean exactly? — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

Like if you spend $$$ on acquiring Enterprise Edition you will get most of it to utilise and for standard edition you may not need much to spend as you don’t get more as compared to Enteprise Edition. We’ve 2 services at our end one uses Standard Edition and has intermittent performance issues and whereas another system with Enterprise Edition which never had blip on performance basis. (hardware is same) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Back to original question: Same case with one customer I work.
OS: Advanced Server.
SQL: Standard Month before, 1 GByte and a lot of pages/sec.(55 GB database size).
No more than 5-6 Gbyte to OS. Now, 3 Gbytes. Sql standard see 3gb in properties.
Only use 1680 no more no less. There is no others applications, no AWE, no -g.
Pages/sec near 0 all the time, performance of course improve. The question is: Satya formula, explain actual situation. But what about previus situation?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Any PERFMON stats before and after memory upgrade? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

All works fine, much better than before. About Perform, I have to wait to week end. On last friday, before memory upgrade, integrity job fail. Database was set to single user. One guy try to change that. This guy, confuse single user with simple recovery model, so he change simple to full[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!], when is not backup policy. On monday night call me because no one can use database, there is no space at all. At customer, after back to simple and shrink log, etc., I find one big table with allocation errors. I fix it after 5 hours. So, all manteinance jobs, will run on this week end. Luis Martin Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check whether any disk related issues persists, you may never know the small issues might contributing a big problem which is an indirect process. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, I think the default value for MemToLeave is 256MB for SQL2K. I’ve seen several cases where increase to 384MB made a difference by starting the service with -g384 switch. Is the documentation on MemToLeave incorrect in respect to 256MB being a default for 2K?

Rdjabarov (good to see you here apart from DBforums) For the default values mentioned, the MemToLeave area evaluates to 256 MB on SQL 7.0 and 384 MB on SQL 2000. The -g startup parameter can be used to increase the external needs setting and this must be deployed carefully and prior testing to ensure no issue persists on production system. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>