How to Check Memory Usage in SQL Server

Why How to Check Memory Usage in SQL Server is important

-- Measure memory for a high-value order search
SET STATISTICS TIME, IO ON;
GO
SELECT o.id , c.name , o.total_amount , o.order_date
FROM   Orders      o
JOIN   Customers   c ON c.id = o.customer_id
WHERE  o.total_amount > 1000
ORDER  BY o.total_amount DESC;
GO
SET STATISTICS TIME, IO OFF;

-- Overall process memory
SELECT physical_memory_in_use_kb , large_page_allocations_kb , process_physical_memory_low
FROM sys.dm_os_process_memory;

-- Memory by clerk (top 5)
SELECT TOP (5) clerk_name , pages_kb / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

-- Memory requested & granted for the last 50 queries
SELECT TOP (50)
    DB_NAME(st.dbid)          AS database_name,
    SUBSTRING(st.text,1,100)  AS sql_snippet,
    qs.total_memory_usage_kb  AS total_kb,
    qs.max_memory_usage_kb    AS peak_kb
FROM sys.dm_exec_query_stats         qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_memory_usage_kb DESC;

-- Per-table buffer pool pages (ecommerce context)
SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id)        AS table_name,
    COUNT(*) * 8 / 1024             AS buffer_mb
FROM sys.dm_os_buffer_descriptors b
JOIN sys.dm_db_partition_stats  p
     ON  p.partition_id = b.partition_id
WHERE p.object_id IN (OBJECT_ID('dbo.Customers'),
                      OBJECT_ID('dbo.Orders'),
                      OBJECT_ID('dbo.Products'))
GROUP BY p.object_id
ORDER BY buffer_mb DESC;