Target Memory threadshold in Perfmon--URGENT

Question

Target Memory threadshold in Perfmon--URGENT

Felipe Venancio

Mr or Mrs. 500

Points: 539
More actions
March 21, 2005 at 12:35 pm

#166338

Does someone knows if the threadshold Target Memory is
How much memory the Sql Server would like to use
or
How much memory Sql Server can use???
I saw the firts one on Microsoft website and the second on http://www.sql-serer-performance.com

Thanks and Regars,
Felipe

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Target memory is the amount of memory that SQL would like to use, if it matches the total server memory counter then SQL has all of the memory that it needs.

Just to recap...

SQL Standard can use a maximum of 2GB

SQL Enterprise can use a heck of a lot more

Tim McCain SSC Veteran Points: 203 More actions · Answer 2

If you are running WIndows 2000 Advanced server (and have enough memory), you can insert the pae extension into the boot.ini to allow it to access more memory. Then you can run sp_configure to activate AWE on SQL Server (only has to be standard version to do this). Be careful about doing this unless you have adequate memory as SQL Server will use every last bit of the 4 GB when it wants to, so have enough left for the OS and anything else you are running. About half way down on this article will give you the basics.

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx

Good luck.

Felipe Venancio Mr or Mrs. 500 Points: 539 More actions · Answer 3

My problem is because, my max memory is 3Gb, on Total Memory i have

900Mb and on Target Server i have 1500Gb. But on threadshol Memory Available (Mb) i have 2000Mb.

Available Mb= 2000MB

Target Memory= 15000Mb

Total Memory= 900Mb

I don't know why my Sql isn't use the available memory??

Regards and Thanks

Felipe

Tim McCain SSC Veteran Points: 203 More actions · Answer 4

I'm sorry Felipe, you kinda lost me. Let me see if I have this straight:

The server running SQL Server has 1.5 TB or 15 GB RAM?

You are using SQL Standard, so you only have 2 GB RAM allocated to SQL Server to use?

SQL Server is only using 900 MB of RAM?

Is this close? What editions of the OS and SQL Server are you using?

Felipe Venancio Mr or Mrs. 500 Points: 539 More actions · Answer 5

I have 3GB RAM on my server.

I'm using Sql Server Enterprise Edition

Sql Server is only using 900MB.

Windows 2000 server.

Thanks

Felipe

Tim McCain SSC Veteran Points: 203 More actions · Answer 6

When you run sp_configure against the master DB in query analyzer, what does it show for AWE, max server memory, and min server memory; also what is the working set size? You'll need to use the show advanced options=1 to get these.

It is dependant upon your environment, but if you are willing to allocate 2 GB of memory to SQL Server to always use, set max and min for 2 GB. SQL Server will then allocate all of the 2 GB and will not release until shut down.

BOL has all of the options to change the memory size within SQL Server, search for sp_configure.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

It is actually possible that sql server doesn't need to use any more memory - it will only use what it needs - are your database(s) user base small ?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Felipe Venancio Mr or Mrs. 500 Points: 539 More actions · Answer 8

All my databaseas are small.

If Sql Server doesn't need to use any more memory, why threadshold Target Memory is bigger than Total Memory?

Regards and Thanks

Felipe

Tim McCain SSC Veteran Points: 203 More actions · Answer 9

As Colin stated, you may only need that much memory, if so, great. As Mike said previously, by default SQL Server uses 2147483647 K of memory for its 'max server memory' option, with the default 'min server memory' option being zero. SQL Server will only use the amount of memory it needs to perform whatever table cache, SP, etc. you are running; unless you reserve a specific amount of memory for SQL Server, it will dynamically use, and release, memory as needed. Although you never stated where you obtained these number from, I am assuming your "threshold memory" is total process memory, which is 2GB by default on standard server. "Target memory" would be the 'max server memory' allocated by SQL Server, and the "Total memory" is the in-process memory presently being used by SQL Server. To check your min and max memory settings for SQL Server, either use query manager sp_configure, or in Enterprise Manager look at the Properties of the Server, select memory and see what is selected for the min and max options.

Felipe Venancio Mr or Mrs. 500 Points: 539 More actions · Answer 10

I saw the "Target Memory" and "Total Memory" parameters on Windows Perfmon.

"Target Memory" is the total amount of dynamic memory the server is willing to consume.

and

"Total Memory" is Total amount of dynamic memory the server is currently consuming.

My 'min server memory' is 0, and 'max server memory' is 3Gb. I have 3Gb of Memory on my server.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 11

run this:-

select * from master.dbo.sysperfinfo where counter_name like '%server memory%'

The two figures show the memory available and the memory sql server will/does use. In general you wouldn't really use more memory than the size of your databases.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

ArashS SSC Veteran Points: 289 More actions · Answer 12

hey u guys... i need some help with this also!..

I am having my doubts that our SQL is using the memory fully... especialy because AWE is set, but i noticed that the Group Policy User setting is blank!.. Nobody has defined a user that has permissions to run AWE.

After reading this thread, ive done all the perfmon counters and sp_configure etc. The numbers shows our SQL is doing okej... but i am still having my doubts.. pleasee.. can u guys have a look at it, and tell me your oppinion.

see the statistics below...

Thank you all very much

Arash from Denmark.

------------------------------------------------------------------------------------

I am running windows server 2003 32-bit with SQL server 2005 enterprise.

The server has 16GB physical memory. The minimum setting for AWE is 12GB and the max setting is set to 12GB... is this wrong?

sys.dm_os_performance_counters cntr_value cntr_type

SQLServer:Memory Manager Target Server Memory (KB) 1228800065792

SQLServer:Memory Manager Total Server Memory (KB) 1228800065792

sp_configure

name min max config_value run_value

awe enabled 0 1 1 1

min memory per query (KB)51221474836471024 1024

min server memory (MB)0214748364712000 12000

max server memory (MB)16214748364712000 12000

------------------------------------------------------------------------------------

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 13

your server has 12gb allocated to it mostly for the buffer cache. It's set correctly - you don't need permission to use awe ( well not in that sense anyway )

If you go to my web site you can download some rdls which will show how the memory is being used by your server ( sql 2005 onwards ), you don't need SSRS or anything http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 14

Um, just to clarify, if your server only has 3GB of memory, setting the /PAE switch in the boot.ini file and setting AWE in SQL will do nothing for you. That only comes into play when you are on a 32-bit system and you have more than 4GB of memory installed that you want SQL to access, and that is only available for the buffer cache.

One thing that you can do if you think SQL is hitting memory pressure (besides setting your min/max memory settings correctly like others have stated) is to add the /3GB switch to the boot.ini file and reboot the server. That will limit Windows to 1GB of memory for the OS, leaving the rest for applications.

Joie Andrew
"Since 1982"