How to tell how much memory?

[katbear]

Hi,

I am confused about SQL Server and memory.

On both 2000 and 2005, how can I tell how much memory is available for SQL Server to use?

SQL Server 2000:
When I look at server properties -> memory, I see that the 2000 server is set to use memory dynamically. Min is 0, Max is 2047, or 2GB.

However, the 2000 server only HAS 2GB memory to begin with. I know a large portion of that goes to the OS, so how much is REALLY left over for SQL Server 2000 to use?

When I look at perfmon, I see that Total Server Memory is 1.6 MB, that is, that is how much is assigned to SQL Server. Is that right?

SQL Server 2005:
Then, on SQL Server 2005, there is 4GB of ram on the server. Again, when I look at server properties -> memory, I see that Min is 0, and Max is 2147483647 MB, which is obviously more than 4GB!

And again, when I look at perfmon, I see that Total Server Memory is also 1.6 GB.

So my question is, since the 2005 server has 4GB available, and is set to use the max amount, how come only 1.6GB is available for SQL Server? Shouldn't it be higher than on the 2000 box??

Maybe I am missing something here. Any insights?

Thanks

 

[mrdenny]

With the SQL 2000 server which only has 2 Gigs you need to lower the max amount of memory by 512. This will leave some memory for the OS to work with. If you don't do this you will kill your performance.

With the SQL 2005 server the max is currently set for the devault of 2 PB. This will have the same impact and choke the OS. You need to lower the max to 3584 (3.5 GB) so that the OS has room to work. If you are on the 32bit version of Windows and SQL you need to enable AWE within SQL so that you can use memory over 2 Gigs.

When you enable AWE for the SQL Server task manager will show an incorrect amount of memory in use.

The reason that SQL 2000 is only using 1.6 Gigs of RAM is that Windows is using the other ~400 Megs. Check out the performance tab in task manager and see what the amount of physical memory is left is set to.

The same will go for the SQL 2005 server as Windows only uses the first 2 Gigs of memory to run it self, and SQL won't breach the 2 Gig limit without AWE being enabled.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

for your 2000 server.
What version of SQL 2000 do you installed? Standard or Enterprise. If it is Standards SQL it can only use up to 2gb even if you enable 3gb and awe. In your case where you only have 2gb installed SQL will only use 1.7gb leaving 300mb for the OS. By the way, that should be plenty for the OS as long as you don't have any onther programs installed on this server. The OS by itself doesn't need alot of memory. SQL Server is a memory hog. This is one of the reasons why you should make your SQL Server dedicated to SQL only.

On to the 2005 server.
Again the verion of your OS and SQL will determine how much memory you can use here. The max amount of memory available to 2005 is dependant upon the max ammount the server can handle.

In both cases you have dynamically assigned memory to your SQL Server. It is going to take what it needs for the procedural cache and data cache. SQL Server usually holds on to the memory it aquires. So if there are other programs installed on your server it needs to send it's memory requests to SQL. In most cases SQL does not want to give up the memory.
The procedural cache is cached query plans and other objects. The data cache is recent data that has been accessed. A good indicator on weather or not you need more memory is the Max Page Life expectancy counter in Perfmon/SQL Buffer Manager: If it is above 300 you are ok. That value is how long a page stays in memory (in seconds)

Here is a very good link to some FAQ's for memory,

http://blogs.msdn.com/slavao/archive/category/9005.aspx

Also,
to view your max memory you can use look up sp_configure from BOL.
In your case for the 2005 server I would use sp_configure to set the max memory to 3.5 GB. (Assuming nothing else is installed on this server.)

In 2005 there are some very good dynamic managment views.
Here is an example to view what is in your procedural cache

select cacheobjtype
       ,usecounts
       ,size_in_kb
       ,dbname
       ,sum(total_worker_time/1000) as tot_CPU_time_in_ms
       ,sum(total_physical_reads) as tot_physical_reads
       ,sum(total_logical_reads) as tot_logical_reads
       ,sum(total_logical_writes) as tot_logical_writes
       ,[executable plan] as ec_count
       ,[Cursor] as cursors
       ,sql_text
FROM 
 (  select p.cacheobjtype+ '('+p.objtype+ ')' as cacheobjtype
           ,p.usecounts
           ,p.size_in_bytes/1024 as size_in_kb
           ,case when pa.value=32767 Then 'Resourcedb'
                 else ISNULL(db_name(cast(pa.value as int)),
                 convert(sysname, pa.value))
            end as dbname
           ,pdo.cacheobjtype as pdo_cacheobjtype
           ,stat.total_worker_time
           ,stat.total_physical_reads
           ,stat.total_logical_writes
           ,stat.total_logical_reads
           ,stat.total_elapsed_time
           ,sql.text as sql_text
       FROM sys.dm_exec_cached_plans p
       outer apply sys.dm_exec_plan_attributes (p.plan_handle) pa
       inner join sys.dm_exec_query_stats stat 
             on p.plan_handle = stat.plan_handle
       outer apply sys.dm_exec_cached_plan_dependent_objects (p.plan_handle) as pdo
       outer apply sys.dm_exec_sql_text (p.plan_handle) as sql
       WHERE pa.attribute = 'dbid'
) t1
PIVOT(
count(pdo_cacheobjtype) for pdo_cacheobjtype in ([executable plan],[cursor])
)as pvt2
GROUP by cacheobjtype
         ,usecounts
         ,size_in_kb
         ,dbname
         ,[executable plan]
         ,[cursor]
         ,sql_text

So my question is, since the 2005 server has 4GB available, and is set to use the max amount, how come only 1.6GB is available for SQL Server? Shouldn't it be higher than on the 2000 box??

That depends on what activity is going on for both servers. SQL will only take memory as it is needed.

I hope this helps.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Thanks for the info!

When I try to enable AWE on the 2005 box, I get this error:

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process. (Microsoft SQL Server, Error: 5845)

Thanks

 

[ptheriault]

That is correct. You have to enable lock pages in memory for the account that the SQL Server service is running on.

Here is how to do that.

http://msdn2.microsoft.com/en-US/library/ms190730.aspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Is "Adminstrators" a good choice for this option?

 

[ptheriault]

Is your SQL Service running under and administror group account?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

After I changed "Lock Pages In Memory" to use Administrators, I still got the error:

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process. (Microsoft SQL Server, Error: 5845)

But if I look under Memory settings, I see that the changes show up. AWE is checked, and Max Memory is 3584.

But perfmon still shows 1.6 GB available to SQL Server. How can I "really" check how much memory is available to SQL Server??

Thanks

 

[ptheriault]

Give it time to aquire more memory. I won't take it right away. Unless you set the min memory to more than 1.6 gb.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Hmmm,

I ran sp_configure, and this is what shows up:

min server memory (MB) 0 2147483647 0 0

Does not correspond to my max memory setting of 3584.

Wouldn't this seem to indicate that my changes did not take?

Thanks

 

[ptheriault]

in sp_configure you want to look at max server memory.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Yes, max server memory is 2147483647

So it looks like the settings did not take, even though it shows up that way in the memory configuration interface

obviously it is not correct

for some reason, setting "lock pages in memory" to administrator did not allow me to make the changes

 

[mrdenny]

The setting you want to look at is called Max server memory. The line you have posted is the min server memory. The min possible value for the "min server memory" setting is 0 and the max possible value for the "min server memory" is 2147483647. Max server memory should be 1 line up on the output from sp_configure.

When changing values using sp_configure don't forget to run RECONFIGURE afterwords.

exec sp_configure 'max server memory', 1524
reconfigure
go

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

kat,
do what Denny said!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Yes, I am looking at max server memory.

Anyhow, don't I want to run:

exec sp_configure 'max server memory', 3584 (?)
reconfigure
go

Also, how do I know if AWE was even enabled, considering I kept getting the "lock pages in memory" error??

Thanks

 

[mrdenny]

Yes that will set the max memory setting for the SQL 2005 server.

sp_configure will also tell you if awe is enabled.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Help.

What am I doing wrong here? Each time I try to enable AWE I get this message:

Configuration option 'awe enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 2
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

I have set "Lock pages in memory" to use the Administrators account. That didn't work. Next, I tried the account that runs the SQL Server service. That didn't work either.

1.) Does change this option require a reboot?
2.) Who should own the "Lock pages in memory" privilege??

Thanks

 

[katbear]

Ok, I restarted the SQL Server service. The sp_configure 'awe_enabled', '1' and reconfigure commands no longer give me any error.

However, when I run sp_configure, I still get:

awe enabled 0 1 1 0

The last value should be a 1 now, correct??

 

[ptheriault]

That's correct. the last value should be one. Did you restart the services after running sp_configure?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Hi,

after a series of restarts, FINALLY the change took

my fault i'm sure

thanks