TSQL memory related queries

Question

I need to find if /3GB switch and /PAE is enabled on a server.

Also, I want to know the size of page file and physical RAM on the server.

I can check them manually but how can I check them using TSQL on both SQL 2000 and SQL 2005?

Answer 1

this looks like one http://sugeshkr.blogspot.com/2007/12/check-if-3gb-is-configured-or-not.html

 If(Select Virtual_Memory_In_Bytes/1024/(2048*1024) from Sys.dm_os_Sys_Info) < 1
Begin

PRINT '/3GB Switch Not Configured in Boot.Ini (CHECK)'

End

Answer 2

Use WMI:

• The Win32_OperatingSystem class exposes the PAEEnabled property
• The Win32_ComputerSystem class exposes the SystemStartupOptions property that contains the boot.ini parameters (pre-Vista)
• Post Vista you need to use the Boot Configuration Data WMI Provider to see if /3gb is enabled, but I'm not sure how.

To run WMI queries, use ExecuteWQL from the Policy Based Management framework (which you should be using anyway for the audit task you describe, see Administering Servers by Using Policy-Based Management).

PowerShell can also read WMI. Ultimately, WQL queries can be run straight from T-SQL using sp_OACreate and friends.