How much memory?
This is a question I would ask at interviews .. You have 12Gb of ram on your new SQL Server ..how do you get SQL Server to use it and how do you know if SQL Server is actually using the memory?
I've had some discussion on this matter with two reports raised claiming SQL Server was only using 200Mb of ram whereas it should be using much more.
http://msdn2.microsoft.com/en-us/library/aa196705(SQL.80).aspx is where to look.
For my interview the answers were , enable awe in sql server, set maximum memory, check target memory in sysperfinfo ( there's also a perfmon counter but DBAs prefer SQL solutions! ) The wrong answer was do nothing and use Task Manager. ( with w2k you also need /PAE .. w2k3 should have this switch by default ).
select * from master.dbo.sysperfinfo where counter_name like '%server memory%'
should show something like this:-
object_name counter_name instance_name cntr_value cntr_type
---------------------------------------------------------------------------------------------------------------------------------------------------
SQLServer:Memory Manager Target Server Memory(KB) 11444552 65536
SQLServer:Memory Manager Total Server Memory (KB) 11444552 65536
(2 row(s) affected)
It's also worth noting that awe can be pretty nasty if you don't limit max memory, I won't bother to repeat the kb article.