{"id":63,"date":"2023-02-09T19:29:41","date_gmt":"2023-02-09T17:29:41","guid":{"rendered":"http:\/\/130.61.57.200\/?p=63"},"modified":"2023-04-12T09:42:28","modified_gmt":"2023-04-12T07:42:28","slug":"memory-allocation-in-sql-server","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2023\/02\/09\/memory-allocation-in-sql-server\/","title":{"rendered":"Memory Allocation in SQL Server"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>SET NOCOUNT ON;\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\nSET LOCK_TIMEOUT 10000;\n\nDECLARE @ServiceName nvarchar(100)\nSET @ServiceName =\n                  CASE\n                    WHEN @@SERVICENAME = &#039;MSSQLSERVER&#039; THEN &#039;SQLServer:&#039;\n                    ELSE &#039;MSSQL$&#039; + @@SERVICENAME + &#039;:&#039;\n                  END\n\nDECLARE @Perf TABLE (\n  object_name nvarchar(20),\n  counter_name nvarchar(128),\n  instance_name nvarchar(128),\n  cntr_value bigint,\n  formatted_value numeric(20, 2),\n  shortname nvarchar(20)\n)\nINSERT INTO @Perf (object_name, counter_name, instance_name, cntr_value, formatted_value, shortname)\n  SELECT\n    CASE\n      WHEN CHARINDEX(&#039;Memory Manager&#039;, object_name) &gt; 0 THEN &#039;Memory Manager&#039;\n      WHEN CHARINDEX(&#039;Buffer Manager&#039;, object_name) &gt; 0 THEN &#039;Buffer Manager&#039;\n      WHEN CHARINDEX(&#039;Plan Cache&#039;, object_name) &gt; 0 THEN &#039;Plan Cache&#039;\n      WHEN CHARINDEX(&#039;Buffer Node&#039;, object_name) &gt; 0 THEN &#039;Buffer Node&#039; -- 2008\n      WHEN CHARINDEX(&#039;Memory Node&#039;, object_name) &gt; 0 THEN &#039;Memory Node&#039; -- 2012\n      WHEN CHARINDEX(&#039;Cursor&#039;, object_name) &gt; 0 THEN &#039;Cursor&#039;\n      ELSE NULL\n    END AS object_name,\n    CAST(RTRIM(counter_name) AS nvarchar(100)) AS counter_name,\n    RTRIM(instance_name) AS instance_name,\n    cntr_value,\n    CAST(NULL AS decimal(20, 2)) AS formatted_value,\n    SUBSTRING(counter_name, 1, PATINDEX(&#039;% %&#039;, counter_name)) shortname\n  FROM sys.dm_os_performance_counters\n  WHERE (object_name LIKE @ServiceName + &#039;Buffer Node%&#039;     -- LIKE is faster than =. I have no idea why\n  OR object_name LIKE @ServiceName + &#039;Buffer Manager%&#039;\n  OR object_name LIKE @ServiceName + &#039;Memory Node%&#039;\n  OR object_name LIKE @ServiceName + &#039;Plan Cache%&#039;)\n  AND (counter_name LIKE &#039;%pages %&#039;\n  OR counter_name LIKE &#039;%Node Memory (KB)%&#039;\n  OR counter_name = &#039;Page life expectancy&#039;\n  )\n  OR (object_name = @ServiceName + &#039;Memory Manager&#039;\n  AND counter_name IN (&#039;Granted Workspace Memory (KB)&#039;, &#039;Maximum Workspace Memory (KB)&#039;,\n  &#039;Memory Grants Outstanding&#039;, &#039;Memory Grants Pending&#039;,\n  &#039;Target Server Memory (KB)&#039;, &#039;Total Server Memory (KB)&#039;,\n  &#039;Connection Memory (KB)&#039;, &#039;Lock Memory (KB)&#039;,\n  &#039;Optimizer Memory (KB)&#039;, &#039;SQL Cache Memory (KB)&#039;,\n  -- for 2012\n  &#039;Free Memory (KB)&#039;, &#039;Reserved Server Memory (KB)&#039;,\n  &#039;Database Cache Memory (KB)&#039;, &#039;Stolen Server Memory (KB)&#039;)\n  )\n  OR (object_name LIKE @ServiceName + &#039;Cursor Manager by Type%&#039;\n  AND counter_name = &#039;Cursor memory usage&#039;\n  AND instance_name = &#039;_Total&#039;\n  )\n\n-- Add unit to &#039;Cursor memory usage&#039;\nUPDATE @Perf\nSET counter_name = counter_name + &#039; (KB)&#039;\nWHERE counter_name = &#039;Cursor memory usage&#039;\n\n-- Convert values from pages and KB to MB and rename counters accordingly\nUPDATE @Perf\nSET counter_name = REPLACE(REPLACE(REPLACE(counter_name, &#039; pages&#039;, &#039;&#039;), &#039; (KB)&#039;, &#039;&#039;), &#039; (MB)&#039;, &#039;&#039;),\n    formatted_value =\n                     CASE\n                       WHEN counter_name LIKE &#039;%pages&#039; THEN cntr_value \/ 128.\n                       WHEN counter_name LIKE &#039;%(KB)&#039; THEN cntr_value \/ 1024.\n                       ELSE cntr_value\n                     END\n\n-- Delete some pre 2012 counters for 2012 in order to remove duplicates\nDELETE P2008\n  FROM @Perf P2008\n  INNER JOIN @Perf P2012\n    ON REPLACE(P2008.object_name, &#039;Buffer&#039;, &#039;Memory&#039;) = P2012.object_name\n    AND P2008.shortname = P2012.shortname\nWHERE P2008.object_name IN (&#039;Buffer Manager&#039;, &#039;Buffer Node&#039;)\n\n-- Update counter\/object names so they look like in 2012\nUPDATE PC\nSET object_name = REPLACE(object_name, &#039;Buffer&#039;, &#039;Memory&#039;),\n    counter_name = ISNULL(M.NewName, counter_name)\nFROM @Perf PC\nLEFT JOIN (SELECT\n  &#039;Free&#039; AS OldName,\n  &#039;Free Memory&#039; AS NewName\nUNION ALL\nSELECT\n  &#039;Database&#039;,\n  &#039;Database Cache Memory&#039;\nUNION ALL\nSELECT\n  &#039;Stolen&#039;,\n  &#039;Stolen Server Memory&#039;\nUNION ALL\nSELECT\n  &#039;Reserved&#039;,\n  &#039;Reserved Server Memory&#039;\nUNION ALL\nSELECT\n  &#039;Foreign&#039;,\n  &#039;Foreign Node Memory&#039;) M\n  ON M.OldName = PC.counter_name\n  AND NewName NOT IN (SELECT\n    counter_name\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;)\nWHERE object_name IN (&#039;Buffer Manager&#039;, &#039;Buffer Node&#039;)\n\n\n-- Build Memory Tree\nDECLARE @MemTree TABLE (\n  Id int,\n  ParentId int,\n  counter_name nvarchar(128),\n  formatted_value numeric(20, 2),\n  shortname nvarchar(20)\n)\n\n-- Level 5\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id = 1226,\n    ParentId = 1225,\n    instance_name AS counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Plan Cache&#039;\n  AND counter_name IN (&#039;Cache&#039;)\n  AND instance_name &lt;&gt; &#039;_Total&#039;\n\n-- Level 4\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id = 1225,\n    ParentId = 1220,\n    &#039;Plan &#039; + counter_name AS counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Plan Cache&#039;\n  AND counter_name IN (&#039;Cache&#039;)\n  AND instance_name = &#039;_Total&#039;\n\n  UNION ALL\n\n  SELECT\n    Id = 1222,\n    ParentId = 1220,\n    counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Cursor&#039;\n  OR (object_name = &#039;Memory Manager&#039;\n  AND shortname IN (&#039;Connection&#039;, &#039;Lock&#039;, &#039;Optimizer&#039;, &#039;SQL&#039;))\n\n  UNION ALL\n\n  SELECT\n    Id = 1112,\n    ParentId = 1110,\n    counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;\n  AND shortname IN (&#039;Reserved&#039;)\n  UNION ALL\n  SELECT\n    Id = P.ParentID + 1,\n    ParentID = P.ParentID,\n    &#039;Used Workspace Memory&#039; AS counter_name,\n    SUM(used_memory_kb) \/ 1024. AS formatted_value,\n    NULL AS shortname\n  FROM sys.dm_exec_query_resource_semaphores\n  CROSS JOIN (SELECT\n    1220 AS ParentID\n  UNION ALL\n  SELECT\n    1110) P\n  GROUP BY P.ParentID\n\n-- Level 3\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id =\n        CASE counter_name\n          WHEN &#039;Granted Workspace Memory&#039; THEN 1110\n          WHEN &#039;Stolen Server Memory&#039; THEN 1220\n          ELSE 1210\n        END,\n    ParentId =\n              CASE counter_name\n                WHEN &#039;Granted Workspace Memory&#039; THEN 1100\n                ELSE 1200\n              END,\n    counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;\n  AND counter_name IN (&#039;Stolen Server Memory&#039;, &#039;Database Cache Memory&#039;, &#039;Free Memory&#039;, &#039;Granted Workspace Memory&#039;)\n\n-- Level 2\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id =\n        CASE\n          WHEN counter_name = &#039;Maximum Workspace Memory&#039; THEN 1100\n          ELSE 1200\n        END,\n    ParentId = 1000,\n    counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;\n  AND counter_name IN (&#039;Total Server Memory&#039;, &#039;Maximum Workspace Memory&#039;)\n\n-- Level 1\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id = 1000,\n    ParentId = NULL,\n    counter_name,\n    formatted_value,\n    shortname\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;\n  AND counter_name IN (&#039;Target Server Memory&#039;)\n\n-- Level 4 -- &#039;Other Stolen Server Memory&#039; = &#039;Stolen Server Memory&#039; - SUM(Children of &#039;Stolen Server Memory&#039;)\nINSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname)\n  SELECT\n    Id = 1222,\n    ParentId = 1220,\n    counter_name = &#039;&lt;Other Memory Clerks&gt;&#039;,\n    formatted_value = (SELECT\n      SSM.formatted_value\n    FROM @MemTree SSM\n    WHERE Id = 1220)\n    - SUM(formatted_value),\n    shortname = &#039;Other Stolen&#039;\n  FROM @MemTree\n  WHERE ParentId = 1220\n\n-- Results:\n\n-- PLE and Memory Grants\nSELECT\n  &#091;Counter Name] = P.counter_name + ISNULL(&#039; (Node: &#039; + NULLIF(P.instance_name, &#039;&#039;) + &#039;)&#039;, &#039;&#039;),\n  cntr_value AS Value,\n  RecommendedMinimum =\n                      CASE\n                        WHEN P.counter_name = &#039;Page life expectancy&#039; AND\n                          R.Value &lt;= 300 -- no less than 300\n                        THEN 300\n                        WHEN P.counter_name = &#039;Page life expectancy&#039; AND\n                          R.Value &gt; 300 THEN R.Value\n                        ELSE NULL\n                      END\nFROM @Perf P\nLEFT JOIN -- Recommended PLE calculations\n(SELECT\n  object_name,\n  counter_name,\n  instance_name,\n  CEILING(formatted_value \/ 4096. * 5) * 60 AS Value -- 300 per every 4GB of Buffer Pool memory or around 60 seconds (1 minute) per every 819MB\nFROM @Perf PD\nWHERE counter_name = &#039;Database Cache Memory&#039;) R\n  ON R.object_name = P.object_name\n  AND R.instance_name = P.instance_name\nWHERE (P.object_name = &#039;Memory Manager&#039;\nAND P.counter_name IN (&#039;Memory Grants Outstanding&#039;, &#039;Memory Grants Pending&#039;, &#039;Page life expectancy&#039;)\n)\nOR -- For NUMA\n(\nP.object_name = &#039;Memory Node&#039;\nAND P.counter_name = &#039;Page life expectancy&#039;\nAND (SELECT\n  COUNT(DISTINCT instance_name)\nFROM @Perf\nWHERE object_name = &#039;Memory Node&#039;)\n&gt; 1\n)\nORDER BY P.counter_name DESC, P.instance_name\n\n-- Get physical memory\n-- You can also extract this information from sys.dm_os_sys_info but the column names have changed starting from 2012\nIF OBJECT_ID(&#039;tempdb..#msver&#039;) IS NOT NULL\n  DROP TABLE #msver\nCREATE TABLE #msver (\n  ID int,\n  Name sysname,\n  Internal_Value int,\n  Value nvarchar(512)\n)\nINSERT #msver EXEC master.dbo.xp_msver &#039;PhysicalMemory&#039;\n\n-- Physical memory, config parameters and Target memory\nSELECT\n  min_server_mb = (SELECT\n    CAST(value_in_use AS decimal(20, 2))\n  FROM sys.configurations\n  WHERE name = &#039;min server memory (MB)&#039;),\n  max_server_mb = (SELECT\n    CAST(value_in_use AS decimal(20, 2))\n  FROM sys.configurations\n  WHERE name = &#039;max server memory (MB)&#039;),\n  target_mb = (SELECT\n    formatted_value\n  FROM @Perf\n  WHERE object_name = &#039;Memory Manager&#039;\n  AND counter_name IN (&#039;Target Server Memory&#039;)),\n  physical_mb = CAST(Internal_Value AS decimal(20, 2))\nFROM #msver\n\n-- Memory tree\n;\nWITH CTE\nAS (SELECT\n  0 AS lvl,\n  counter_name,\n  formatted_value,\n  Id,\n  NULL AS ParentId,\n  shortname,\n  formatted_value AS TargetServerMemory,\n  CAST(NULL AS decimal(20, 4)) AS Perc,\n  CAST(NULL AS decimal(20, 4)) AS PercOfTarget\nFROM @MemTree\nWHERE ParentId IS NULL\nUNION ALL\nSELECT\n  CTE.lvl + 1,\n  CAST(REPLICATE(&#039; &#039;, 6 * (CTE.lvl)) + NCHAR(124) + REPLICATE(NCHAR(183), 3) + MT.counter_name AS nvarchar(128)),\n  MT.formatted_value,\n  MT.Id,\n  MT.ParentId,\n  MT.shortname,\n  CTE.TargetServerMemory,\n  CAST(ISNULL(1.0 * MT.formatted_value \/ NULLIF(CTE.formatted_value, 0), 0) AS decimal(20, 4)) AS Perc,\n  CAST(ISNULL(1.0 * MT.formatted_value \/ NULLIF(CTE.TargetServerMemory, 0), 0) AS decimal(20, 4)) AS PercOfTarget\nFROM @MemTree MT\nINNER JOIN CTE\n  ON MT.ParentId = CTE.Id)\nSELECT\n  counter_name AS &#091;Counter Name],\n  CASE\n    WHEN formatted_value &gt; 0 THEN formatted_value \n    ELSE NULL\n  END AS &#091;Memory MB],\n  Perc*100 AS &#091;% of Parent],\n  CASE\n    WHEN lvl &gt;= 2 THEN PercOfTarget *100\n    ELSE NULL\n  END AS &#091;% of Target]\nFROM CTE\nORDER BY ISNULL(Id, 10000), formatted_value DESC;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[28],"class_list":["post-63","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-memory"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/63","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=63"}],"version-history":[{"count":2,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":69,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/63\/revisions\/69"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}