SQL Server Performance

how to work out what memory sql server is ACTUALLY using

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Aug 10, 2012.

  1. Trev256b Member

    the max server memory setting is 6.5 GB.
    1) how can i determine how much exactly is being used by the sql server processes only?
    for example 6.5 GB is automatically assigned to sql server, but not all of that is needed. i have never been able to work out if there is a counter that can isolate this in performance monitor. this would be extremely handy to measure so memory settings could be set to allow the operating system optimum memory, and to complete a performance baseline. anyone know?

    2) also, are there any DMVs for logging this info for versions 2005 and upwards?
  2. Shehap MVP, MCTS, MCITP SQL Server

    You can use dbccmemorystatus to get both of the amount of memory preserved for SQL Service and the amount of memory comiitted for SQL Service within the first output of Memory manager.

    Irrespective that , I do think that you have Dynamic memory option not static option as all of server memory is going to be assigned for SQL Service which is not recommended and thereby you have to preserve a fixed amount of memory for SQL Service with keeping a margin of 3 GB at least for other OS services and MemToLeave section (Out of SQL buffer pool used by SQL Service also to raise up other external OS components such CLR , Extended procedures..etc)

    You can set this amount of memory using the below T-SQL query:

    EXECsys.sp_configureN'max server memory (MB)',N'3000'

    GO

    RECONFIGUREWITHOVERRIDE

    GO

    For the 2nd question , you can get the accumulated memory usage using that DMV named sys.dm_os_memory_clerks as follows:

    selectsum(single_pages_kb)+sum(multi_pages_kb)+sum(virtual_memory_committed_kb) fromsys.dm_os_memory_clerks

    Kindly let me know if any further help is needed

Share This Page