0

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc.

here is the query:

--============================================================
--checking current memory settings and usage
--marcelo miorelli
--16-feb-2014
--============================================================
               
SELECT R.[instance],
       R.[Logical CPU Count],
       R. [Hyperthread Ratio],
       R.[Physical CPU Count],
       R.[Physical Memory (GB)],
       k.[Max server memory (GB)],
     FOO.Memory_usedby_Sqlserver_MB,
     FOO.Locked_pages_used_Sqlserver_MB,
       k.[Min server memory (GB)],
       k.[optimize for ad hoc workloads],
       R.affinity_type_desc,
    FOO.process_physical_memory_low,
    FOO.process_virtual_memory_low,
       R.virtual_machine_type_desc,
       R.sqlserver_start_time
FROM (

                SELECT [instance]=@@servername,
                       cpu_count AS [Logical CPU Count], 
                       hyperthread_ratio AS [Hyperthread Ratio],
                cpu_count/hyperthread_ratio AS [Physical CPU Count], 
                CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)], 
                affinity_type_desc, 
                virtual_machine_type_desc, 
                sqlserver_start_time
                FROM sys.dm_os_sys_info WITH (NOLOCK) 
                

) R 
INNER JOIN (


                SELECT [instance] = @@servername,
                       [Max server memory (GB)]   = CONVERT(decimal(12,2),CAST(p.[max server memory (MB)] AS DECIMAL(12,2))/1024.00),
                       [Min server memory (GB)]   = CONVERT(decimal(12,2),CAST(P.[min server memory (MB)] AS DECIMAL(12,2))/1024.00),
                       [min memory per query (MB)]= CONVERT(decimal(12,2),CAST(P.[min memory per query (KB)] AS DECIMAL(12,2))/1024.00),
                       p.[optimize for ad hoc workloads]

                FROM ( SELECT name, [value_in_use] 
                         FROM sys.configurations) t 
                        PIVOT (MAX([value_in_use]) 
                               FOR name IN (
                                             [min server memory (MB)], 
                                             [min memory per query (KB)], 
                                             [max server memory (MB)], 
                                             [optimize for ad hoc workloads]
                                            )) p


            ) K
ON R.instance = K.instance

INNER JOIN (

                            SELECT [instance] = @@servername,
                            CONVERT(decimal(12,2),CAST(physical_memory_in_use_kb AS DECIMAL(12,2))/1024.00) AS Memory_usedby_Sqlserver_MB,
                            CONVERT(decimal(12,2),CAST(locked_page_allocations_kb AS DECIMAL(12,2))/1024.00) AS Locked_pages_used_Sqlserver_MB,
                            CONVERT(decimal(18,2),CAST(total_virtual_address_space_kb AS DECIMAL(18,2))/1024.00/1024.00) AS Total_VAS_in_GB,
                            process_physical_memory_low,
                            process_virtual_memory_low
                            FROM sys.dm_os_process_memory t
        

           ) FOO
ON R.instance = foo.instance
OPTION (RECOMPILE);


this query gives me this - currently on a not busy dev server:

enter image description here

My question is related to physical_memory_in_use_kb and locked_page_allocations_kb from the dmv sys.dm_os_process_memory.

How can I find what objects or whatever sql server is holding in that memory?

I can see that in my case there is a minimum memory set to 2 Gb but the memory in use is over 5Gb.

It might as well be that I actually do not need to know as there is probably no memory pressure at the moment, but still, is there a way to find out what sql server is holding in there memory?

1 Answer 1

3

[...] is there a way to find out what sql server is holding in there memory?

Yes and no, depending on what you mean by "holding in there memory" (sic).

If you mean what's using memory in the broad sense of the term for the instance, then DBCC MEMORYSTATUS() is an easy way to look at memory usage across the instance that will work as far back as needed and give a breakdown of locked pages usage as well.

There are also the DMVs that deal with SQLOS, such as sys.dm_os_memory_clerks, broker, nodes, and a whole host of other items including memory that isn't considered part of max server memory such as sys.dm_os_loaded_modules, sys.dm_os_stacks, etc., that can also be used to get similar (though not exact parity) information.

If you mean what's using memory in the granular sense of the term, then you're going to essentially be out of luck as the only real structure that's documented is a page, which only covers the buffer pool. Additionally, memory is so transient that it will change just by looking at it. You'd have to run a query to get the majority of the data, which will in of itself cause those items to change. Now, you could take a snapshot of the current memory usage and a dump of the process and look into it, but I doubt that'd be helpful.

I could be misinterpreting what you're asking about in terms of what's using the memory, but typically you'll keep it high level, check the clerks and see if anything is severely abnormal, and then go forward based on that. In your example you're over min server memory but way under max server memory, so there isn't much impetus for SQL Server to trim anything at the moment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.