October 29, 2008 at 10:42 am
Heya,
Which performance monitor can be used to determine the CURRENT memory usage of SQL Server 2005?
FYI, I want to reduce the max memory allocated to SQL Server (from 7 GB to 6 GB), but before doing that i want to know that how much actual memory is being utilized by sql server.
Thanks,
Usman
October 29, 2008 at 11:53 am
I was just looking at this stuff:
Target Server Memory (KB) and Total Server Memory (KB)
Target Server Memory indicates the total amount of dynamic memory SQL Server is willing to consume. Total Server Memory indicates the amount of memory currently assigned to SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2008 at 11:54 am
I monitored these two, but these two are almost static, i want to know the MEMORY sql server IS CURRENTLY COSUMING (say at a particular load time).
Thanks,
Usman
October 29, 2008 at 2:27 pm
The memory SQL server is currently consuming is not readily available, but you can get info on this indirectly by this counter:
Memory:Available Bytes
This is the memory available to new processes. If this is a large value, consistently, your app could do with fewer resources.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 29, 2008 at 2:32 pm
My Problem is different......i have set the MAX MEMORY variable of SQL Server to 7GB. Now i want to know that out of this 7GB MAX MEMORY, how much memory SQL Server is actually using at a particular instance of time.
so any idea how to see that?
October 29, 2008 at 2:36 pm
usman.tanveer (10/29/2008)
My Problem is different......i have set the MAX MEMORY variable of SQL Server to 7GB. Now i want to know that out of this 7GB MAX MEMORY, how much memory SQL Server is actually using at a particular instance of time.so any idea how to see that?
So have you enabled AWE (32-bit) or are you on a 64-bit platform?
There are a few AWE perfmon counters that could give the info you are looking for, if it is AWE you are using to extend the mem space available.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2008 at 11:54 am
I am on 32Bit, but i am not using AWE option.
October 30, 2008 at 11:58 am
usman.tanveer (10/30/2008)
I am on 32Bit, but i am not using AWE option.
Then you are using at most 3 GB (if you have the 3-gb switch enabled) or 2 GB (if the 3-gb switch is not enabled), assuming you have only one SQL instance installed.
How many SQL instances do you have installed, and are there any other resource-hungry apps running on that machine?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2008 at 6:09 pm
I have assinged max memory to 7 GB, so how can it be using 3 or 2 GB at max?
More over how can i find which switch is on?
No there are no other processess running on the machine, its just the sql server.
October 30, 2008 at 6:31 pm
usman.tanveer (10/30/2008)
I have assinged max memory to 7 GB, so how can it be using 3 or 2 GB at max?More over how can i find which switch is on?
No there are no other processess running on the machine, its just the sql server.
On 32-bit platforms, applications are restricted to a max of 2 GB virtual memory address space (3 gb if the /3gb switch is turned on). This is a hard limit that cannot be exceeded, unless one uses AWE, and even then only the data cache can benefit from the extra address space (not the procedure cache, nor any other memory area).
To see whether the /3gb switch is turned on, go to My Computer, right-click and choose Properties. Click the Advanced tab, click the Settings button under Startup and Recovery and click the Edit button. If the switch is enabled, you will see it at the bottom, just as in this sample:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /3GB /PAE
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 31, 2008 at 9:13 am
Thanks alot, this certainly is very useful information. Now what about 64 BIT? i am assuming that AWE is not required for 64 BIT, so how can i monitor the current memory consumption for 64 BIT?
October 31, 2008 at 10:37 am
usman.tanveer (10/31/2008)
Thanks alot, this certainly is very useful information. Now what about 64 BIT? i am assuming that AWE is not required for 64 BIT, so how can i monitor the current memory consumption for 64 BIT?
You may still need AWE with 64-bit.
I know it sounds counter-intuitive, but I've read somewhere that it is best to enable it.
I'm not an expert in this area though. Perhaps someone else will be able to assist in this thread.
Here is a good link:
http://www.sqlservercentral.com/articles/News/abitabout64bit/1360/
This link discusses the AWE connection, although there is oodles of other links out there that discuss the same thing:
http://www.teratrax.com/articles/sql_server_64_bit.html
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply