The below SQL Script will help you to identify how much memory consumed by SQL Sessions and memory free for the max memory allocated to SQL Instance
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 10000;
DECLARE @ServiceName NVARCHAR(100)
SET @ServiceName = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' T
HEN 'SQLServer:' ELSE 'MSSQL$' + @@SERVICENAME + ':' END
DECLARE @Perf TABLE (
object_name NVARCHAR(20)
,counter_name NVARCHAR(128)
,instance_name NVARCHAR(128)
,cntr_value BIGINT
,formatted_value NUMERIC(20, 2)
,shortname NVARCHAR(20)
)
INSERT INTO @Perf (
object_name
,counter_name
,instance_name
,cntr_value
,formatted_value
,shortname
)
SELECT CASE WHEN CHARINDEX('Memory Manager', object_name) > 0
THEN 'Memory Manager' WHEN CHARINDEX('Buffer Manager', object_name) > 0
THEN 'Buffer Manager' WHEN CHARINDEX('Plan Cache', object_name) > 0
THEN 'Plan Cache' WHEN CHARINDEX('Buffer Node', object_name) > 0 THEN 'Buffer Node'
WHEN CHARINDEX('Memory Node', object_name) > 0 THEN 'Memory Node'
WHEN CHARINDEX('Cursor', object_name) > 0 THEN 'Cursor' ELSE NULL END AS object_name
,CAST(RTRIM(counter_name) AS NVARCHAR(100)) AS counter_name
,RTRIM(instance_name) AS instance_name
,cntr_value
,CAST(NULL AS DECIMAL(20, 2)) AS formatted_value
,SUBSTRING(counter_name, 1, PATINDEX('% %', counter_name)) shortname
FROM sys.dm_os_performance_counters
WHERE (
object_name LIKE @ServiceName + 'Buffer Node%'
OR object_name LIKE @ServiceName + 'Buffer Manager%'
OR object_name LIKE @ServiceName + 'Memory Node%'
OR object_name LIKE @ServiceName + 'Plan Cache%'
)
AND (
counter_name LIKE '%pages %'
OR counter_name LIKE '%Node Memory (KB)%'
OR counter_name = 'Page life expectancy'
)
OR (
object_name = @ServiceName + 'Memory Manager'
AND counter_name IN (
'Granted Workspace Memory (KB)'
,'Maximum Workspace Memory (KB)'
,'Memory Grants Outstanding'
,'Memory Grants Pending'
,'Target Server Memory (KB)'
,'Total Server Memory (KB)'
,'Connection Memory (KB)'
,'Lock Memory (KB)'
,'Optimizer Memory (KB)'
,'SQL Cache Memory (KB)'
,
'Free Memory (KB)'
,'Reserved Server Memory (KB)'
,'Database Cache Memory (KB)'
,'Stolen Server Memory (KB)'
)
)
OR (
object_name LIKE @ServiceName + 'Cursor Manager by Type%'
AND counter_name = 'Cursor memory usage'
AND instance_name = '_Total'
)
UPDATE @Perf
SET counter_name = counter_name + ' (KB)'
WHERE counter_name = 'Cursor memory usage'
UPDATE @Perf
SET
counter_name = REPLACE(REPLACE(REPLACE(counter_name, ' pages', ''),
' (KB)', ''), ' (MB)', '')
,formatted_value = CASE WHEN counter_name LIKE '%pages' THEN cntr_value / 128.
WHEN counter_name LIKE '%(KB)' THEN cntr_value / 1024. ELSE cntr_value END
DELETE P2008
FROM @Perf P2008
INNER JOIN @Perf P2012 ON
REPLACE(P2008.object_name, 'Buffer', 'Memory') = P2012.object_name
AND P2008.shortname = P2012.shortname
WHERE P2008.object_name IN (
'Buffer Manager'
,'Buffer Node'
)
UPDATE PC
SET object_name = REPLACE(object_name, 'Buffer', 'Memory')
,counter_name = ISNULL(M.NewName, counter_name)
FROM @Perf PC
LEFT JOIN (
SELECT 'Free' AS OldName
,'Free Memory' AS NewName
UNION ALL
SELECT 'Database'
,'Database Cache Memory'
UNION ALL
SELECT 'Stolen'
,'Stolen Server Memory'
UNION ALL
SELECT 'Reserved'
,'Reserved Server Memory'
UNION ALL
SELECT 'Foreign'
,'Foreign Node Memory'
) M ON M.OldName = PC.counter_name
AND NewName NOT IN (
SELECT counter_name
FROM @Perf
WHERE object_name = 'Memory Manager'
)
WHERE object_name IN (
'Buffer Manager'
,'Buffer Node'
)
DECLARE @MemTree TABLE (
Id INT
,ParentId INT
,counter_name NVARCHAR(128)
,formatted_value NUMERIC(20, 2)
,shortname NVARCHAR(20)
)
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = 1226
,ParentId = 1225
,instance_name AS counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Plan Cache'
AND counter_name IN ('Cache')
AND instance_name <> '_Total'
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = 1225
,ParentId = 1220
,'Plan ' + counter_name AS counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Plan Cache'
AND counter_name IN ('Cache')
AND instance_name = '_Total'
UNION ALL
SELECT Id = 1222
,ParentId = 1220
,counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Cursor'
OR (
object_name = 'Memory Manager'
AND shortname IN (
'Connection'
,'Lock'
,'Optimizer'
,'SQL'
)
)
UNION ALL
SELECT Id = 1112
,ParentId = 1110
,counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Memory Manager'
AND shortname IN ('Reserved')
UNION ALL
SELECT Id = P.ParentID + 1
,ParentID = P.ParentID
,'Used Workspace Memory' AS counter_name
,SUM(used_memory_kb) / 1024. AS formatted_value
,NULL AS shortname
FROM sys.dm_exec_query_resource_semaphores
CROSS JOIN (
SELECT 1220 AS ParentID
UNION ALL
SELECT 1110
) P
GROUP BY P.ParentID
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = CASE counter_name WHEN 'Granted Workspace Memory'
THEN 1110 WHEN 'Stolen Server Memory' THEN 1220 ELSE 1210 END
,ParentId = CASE counter_name WHEN 'Granted Workspace Memory'
THEN 1100 ELSE 1200 END
,counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Memory Manager'
AND counter_name IN (
'Stolen Server Memory'
,'Database Cache Memory'
,'Free Memory'
,'Granted Workspace Memory'
)
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = CASE WHEN counter_name = 'Maximum Workspace Memory'
THEN 1100 ELSE 1200 END
,ParentId = 1000
,counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Memory Manager'
AND counter_name IN (
'Total Server Memory'
,'Maximum Workspace Memory'
)
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = 1000
,ParentId = NULL
,counter_name
,formatted_value
,shortname
FROM @Perf
WHERE object_name = 'Memory Manager'
AND counter_name IN ('Target Server Memory')
INSERT @MemTree (
Id
,ParentId
,counter_name
,formatted_value
,shortname
)
SELECT Id = 1222
,ParentId = 1220
,counter_name = '<Other Memory Clerks>'
,formatted_value = (
SELECT SSM.formatted_value
FROM @MemTree SSM
WHERE Id = 1220
) - SUM(formatted_value)
,shortname = 'Other Stolen'
FROM @MemTree
WHERE ParentId = 1220
SELECT [Counter Name] =
P.counter_name + ISNULL(' (Node: ' + NULLIF(P.instance_name, '') + ')', '')
,cntr_value AS Value
,RecommendedMinimum = CASE WHEN P.counter_name = 'Page life expectancy'
AND R.Value <= 300
THEN 300 WHEN P.counter_name = 'Page life expectancy'
AND R.Value > 300 THEN R.Value ELSE NULL END
FROM @Perf P
LEFT JOIN
(
SELECT object_name
,counter_name
,instance_name
,CEILING(formatted_value / 4096. * 5) * 60 AS Value
FROM @Perf PD
WHERE counter_name = 'Database Cache Memory'
) R ON R.object_name = P.object_name
AND R.instance_name = P.instance_name
WHERE (
P.object_name = 'Memory Manager'
AND P.counter_name IN (
'Memory Grants Outstanding'
,'Memory Grants Pending'
,'Page life expectancy'
)
)
OR
(
P.object_name = 'Memory Node'
AND P.counter_name = 'Page life expectancy'
AND (
SELECT COUNT(DISTINCT instance_name)
FROM @Perf
WHERE object_name = 'Memory Node'
) > 1
)
ORDER BY P.counter_name DESC
,P.instance_name
IF OBJECT_ID('tempdb..#msver') IS NOT NULL
DROP TABLE #msver
CREATE TABLE #msver (
ID INT
,Name SYSNAME
,Internal_Value INT
,Value NVARCHAR(512)
)
INSERT #msver
EXEC master.dbo.xp_msver 'PhysicalMemory'
SELECT min_server_mb = (
SELECT CAST(value_in_use AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
)
,max_server_mb = (
SELECT CAST(value_in_use AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
)
,target_mb = (
SELECT formatted_value
FROM @Perf
WHERE object_name = 'Memory Manager'
AND counter_name IN ('Target Server Memory')
)
,physical_mb = CAST(Internal_Value AS DECIMAL(20, 2))
FROM #msver
;
WITH CTE
AS (
SELECT 0 AS lvl
,counter_name
,formatted_value
,Id
,NULL AS ParentId
,shortname
,formatted_value AS TargetServerMemory
,CAST(NULL AS DECIMAL(20, 4)) AS Perc
,CAST(NULL AS DECIMAL(20, 4)) AS PercOfTarget
FROM @MemTree
WHERE ParentId IS NULL
UNION ALL
SELECT CTE.lvl + 1
,CAST(REPLICATE(' ', 6 * (CTE.lvl)) + NCHAR(124) + REPLICATE(NCHAR(183), 3)
+ MT.counter_name AS NVARCHAR(128))
,MT.formatted_value
,MT.Id
,MT.ParentId
,MT.shortname
,CTE.TargetServerMemory
,CAST(ISNULL(1.0 * MT.formatted_value / NULLIF(CTE.formatted_value, 0), 0)
AS DECIMAL(20, 4)) AS Perc
,CAST(ISNULL(1.0 * MT.formatted_value / NULLIF(CTE.TargetServerMemory, 0), 0)
AS DECIMAL(20, 4)) AS PercOfTarget
FROM @MemTree MT
INNER JOIN CTE ON MT.ParentId = CTE.Id
)
SELECT counter_name AS [Counter Name]
,CASE WHEN formatted_value > 0 THEN formatted_value ELSE NULL END AS [Memory MB]
,Perc AS [% of Parent]
,CASE WHEN lvl >= 2 THEN PercOfTarget ELSE NULL END AS [% of Target]
FROM CTE
ORDER BY ISNULL(Id, 10000)
,formatted_value DESC;