Review and Analyze your SQL Server Memory BottleNecks using DMV's

While i have been reviewing our SQL Server performance and its capacity, i had to look at Memory Utilization and its bottlenecks.

Likewise, Most of you would have been in such situation and eager to know whats using somuch of Memory.
Here you go with the approach and can you can completely debug the memory usage.

--- Find SQL Database with Memory Usage----

SELECT @@SERVERNAME as SERVERNAME,(CASE WHEN ([database_id] = 32767)THEN 'Resource Database'ELSE DB_NAME ([database_id]) END) AS [DatabaseName],COUNT (*) * 8 / 1024 AS [MBUsed],SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]FROM sys.dm_os_buffer_descriptorsGROUP BY [database_id];GO


--- Find SQL Indexes Stored in Memory----


EXEC

FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
USE [?]
SELECT @@Servername AS SERVERNAME,
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
FROM
(SELECT
allocation_unit_id,
SUM (CASE WHEN ([is_modified] = 1)
THEN 1 ELSE 0 END) AS [DPCount],
SUM (CASE WHEN ([is_modified] = 1)
THEN 0 ELSE 1 END) AS [CPCount],
SUM (CASE WHEN ([is_modified] = 1)
THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
SUM (CASE WHEN ([is_modified] = 1)
THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB
ORDER BY [FreeSpacePC] DESC;
END'
sp_MSforeachdb N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name] ;


--- Find SQL Procedures with High Memory Usage ----


EXEC sp_MSforeachdbN'
USE [?]
SELECT TOP 10
[Procedure] = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,CPUTime = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS Qt
WHERE qt.dbid = db_id() -- Filter by current database
ORDER BY total_physical_reads DESC;'



--- Find SQL Queries with High Memory Usage ----


EXEC sp_MSforeachdbN'
USE [?]
SELECT
TEXT
,query_plan
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
FROM sys.dm_exec_query_memory_grants emg
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)
ORDER BY emg.requested_memory_kb DESC;'


--- Find Database pages stored in Memory----  @@SERVERNAME AS SERVERNAME, (CASE WHEN ([database_id] = 32767)THEN 'Resource Database'
ELSE DB_NAME ([database_id]) END) AS [DatabaseName], Page_type, COUNT(page_type)as [Number of pages] from sys.dm_os_buffer_descriptors
group by database_id, page_type order by [Number of pages] DESC, [database_id] desc


--- Find Cached Plans thier Query stored in Memory----


select
objtype
p
( TOP 100000, .size_in_bytes, usecounts,[sql].[text])from sys.dm_exec_cached_plans pouter
ORDER
apply sys.dm_exec_sql_text (p.plan_handle) sql BY p.size_in_bytes DESC


--- Find Number of Cached Plans and Object Type stored in Memory----


select objtype, count(objtype)as NumberofCachedplans from sys.dm_exec_cached_plans pouter
group apply sys.dm_exec_sql_text (p.plan_handle) sql by objtype--- Find Number of Object Types stored in Memory----select cacheobjtype, COUNT(cacheobjtype) from sys.dm_exec_cached_plansgroup by cacheobjtype


...Happy SQLing
Thanks for Visiting and Sharing your Views


select