• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

MS SQL Server memory

Hello,

How can I see the memory consumption for MS SQL Server.
Is it possible to use min and max memory parameters without impact performance.

Thanks
bibi


0
bibi92
Asked:
bibi92
1 Solution
 
JaseemKCommented:
-- You can see the memory usage by SQL by viewing the server's properties (from SQL Management Studio) and viewing the 'Memory' value from the list on the right.

-- Set max memory parameter:
-- You can select the 'Memory' page from the list on the left and view the Min and Max values.
-- You can set fixed limits to memory usage by SQL here. You should in fact set a Max since in SQL 2008 leaving it as dynamically allocated may cause memory leaks on the server.

-- the dbcc command below also outputs detailed info on memory usage by the SQL server.
dbcc memorystatus

HTH
0
 
lcohanDatabase AnalystCommented:
A different approach would be to put the commands below in a SQL SP and run it:

set nocount on
select [object_name],counter_name,cntr_value as current_value, 'Around 3000 and represents the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Buffer Cache hit ratio' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Idealy there should be no waits. Cumulative number that represents the number of requests per second that had to wait for a free page.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Free list stalls/sec' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total number of pages on all free lists.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Free Pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Ideal number of pages in the buffer pool.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Target Pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Compare the number of targeted pages against the stolen pages. If the number of stolen pages does not stabilize over time, the server may eventually get into internal physical memory pressure. '      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Stolen pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Around 300 and if it drops below 300 seconds indicates memory pressure.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Page life expectancy' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, '600 to 800 normal day / 800 to 1000 bussy day / above 1000 may indicate SQL blocking'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Logical Connections' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total SQL server memory needed.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Target Server Memory (KB)' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total SQL server memory available.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Total Server Memory (KB)' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')





Setting max and min server memory settings in SQL Server 2005/2008 governs the max and min size of the buffer pool for single page allocations. It doesn't include CLR, multi-page allocations, direct Windows allocations, process/thread overhead etc.

Optimizing Server Performance Using Memory Configuration Options: http://msdn.microsoft.com/en-us/library/ms177455.aspx

How to determine proper SQL Server configuration settings: http://support.microsoft.com/kb/319942/EN-US/

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup as follow:

• If you have 3-4 GB memory, include the /3GB switch in the startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

• When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

• The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.
0
 
NormanMainaCommented:
The easiest way to see memory utilization by the sql server process is by opening Task Manager ,going to the processes tab and looking for sqlsevr.exe
In there you can also sort according to memory usage and CPU usage.

I disagree with JaseemK's comments - the sql servers memory can be set to 2gb for example but its only utilizing 1.5gb.
The DBCC MEMORYSTATUS command is intended to be a diagnostic tool for Microsoft Customer Support Services and teh output is too technical to make useful meaning of.

I agree with Icohan on the use of performance counters.

The other way you can go about it is to actually setup perfomance counters using the windows perfmon tool and use it to monitor your servers memory consumption for a given period ,say 24 hours and analyze that data in a gragh or excel format.Thats the capability offered by using the performance counters.
Memory utilization of your server may be peaking at certain periods -and you want to know which process is the culprit or you want to know the trend of memory utilization for a given period.

http://msdn.microsoft.com/en-us/library/ms176018.aspx
http://raulsantosneto.wordpress.com/2010/02/22/top-10-list-system-monitor-performance-monitor-and-performance-counters/

0
 
bibi92Author Commented:
Thanks regards bibi
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now