March 25, 2004 at 2:29 pm
Dear experts,
I would greatly appreciate if you can provide the following helps:
1. How to check the amount of memory an instance of sQL 2000 is currently used/allocated.
2. How can I control the amout of Memory allocate for an instance of sQL server.
Many thanks in advance. Have a great day!
Sincerely yours,
Hung
March 25, 2004 at 2:37 pm
First, for checking how much memory an instance is using, you can use PerfMon with Total Server Memory (KB) counter, or query sysperfinfo.
To check the total memory used by the SQL Server executable. For a default instance of SQL Server execute:
SELECT cntr_value/1024 as 'MBs used'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Memory Manager' and counter_name = 'Total Server Memory (KB)'
For a Named instance, use the following code instead, where InstanceName is the second part of your Named Instance name, for example SERVERNAME\INSTANCENAME:
SELECT cntr_value/1024 as 'MBs used'from master.dbo.sysperfinfowhere object_name = 'MSSQL$InstanceName:Memory Manager' and counter_name = 'Total Server Memory (KB)'
This query returns the total MBs used by SQL Server. Of course, this number can fluctuate from second to second. Using the System Monitor may become necessary in order to track trends in memory utilization.
2. You can modify the memory settings by right-clicking Properties of the server from EM, and selecting the Memory tab. You can assign a fixed memory allocation. Hope this helps!
March 25, 2004 at 5:06 pm
Dear Pearlknows.com,
The commanand work very well and are a great help for me.
Many thanks again. I greatly appreciate it. Have a great day!
Sincerely yours,
Hung
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply