Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Кол-во оперативной памяти на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Доброго времени суток!

СУБД: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Выполнил на сервере запрос:
select *
from  sys.dm_exec_query_resource_semaphores


В результате получаю:
total_memory_kb - 20462120

Если я правильно понимаю то total_memory_kb это то сколько серверу доступно памяти.
Если же глянуть в св-ва сервера БД то там max.server memory - 28 000 Мб. Самому же серверу Винда+SQL выделено - 32Гб.

Как так получается? сколько же в результате у сервера SQL памяти в распоряжении?

К сообщению приложен файл. Размер - 8Kb
31 окт 13, 13:53    [15058528]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Сейчас посмотрел на еще парочке серверов БД, там похожая картина.
31 окт 13, 15:45    [15059313]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33960
Блог
andrew shalaev,

http://technet.microsoft.com/ru-ru/library/ms366321(v=sql.90).aspx
31 окт 13, 16:02    [15059438]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Критик,

Спасибо за ссылку, но я читал это и раньше, но не могу понять разницу в цифрах все равно.
31 окт 13, 16:07    [15059473]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Критик
andrew shalaev,

http://technet.microsoft.com/ru-ru/library/ms366321(v=sql.90).aspx

автор
Имя столбца Тип данных Описание
target_memory_kb bigint Допускает использование назначения в килобайтах.

OMG WTF facepalm
31 окт 13, 16:12    [15059512]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33960
Блог
http://technet.microsoft.com/ru-ru/library/ms366321(v=sql.90).aspx
Представление sys.dm_exec_query_resource_semaphores выдает общее состояние памяти выполнения запроса ресурса


а вовсе не объем памяти, доступной серверу
31 окт 13, 16:22    [15059593]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5133
Если я правильно понимаю:
1) 20 гб - сколько используется
2) 28 гб - ограничение сверху для sql
3) 32 гб всего на сверере
Это нормально.
Поправьте меня, если ошибся.
И зачем вам 4 гб на систему? У вас там что то еще крутится?
31 окт 13, 16:40    [15059758]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
andrew shalaev
Доброго времени суток!

СУБД: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Выполнил на сервере запрос:
select *
from  sys.dm_exec_query_resource_semaphores


В результате получаю:
total_memory_kb - 20462120

А чего вы small семафор выкинули? Он тоже используется, для легких запросов. Где цена запроса меньше 3 и требуемая память меньше 5 MB. так что не 20462120, а 20564520.

andrew shalaev
Если я правильно понимаю то total_memory_kb это то сколько серверу доступно памяти.
Неа, не правильно. Это количество памяти доступной серверу для хранения промежуточных результатов при выполнении запросов. Основные потребители памяти в запросах это Sort и Hash.

andrew shalaev
Как так получается? сколько же в результате у сервера SQL памяти в распоряжении?


Выполните вот этот запрос, может станет понятнее как распределяется память в SQL Server:

+
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 PATINDEX('%:Memory Manager%', object_name)> 0 THEN 'Memory Manager'
    WHEN PATINDEX('%:Buffer Manager%', object_name)> 0 THEN 'Buffer Manager'
    WHEN PATINDEX('%:Plan Cache%', object_name)> 0 THEN 'Plan Cache'
    WHEN PATINDEX('%:Buffer Node%', object_name)> 0 THEN 'Buffer Node' -- 2008
    WHEN PATINDEX('%:Memory Node%', object_name)> 0 THEN 'Memory Node' -- 2012
    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 '%:Buffer Node%' 
    OR object_name LIKE '%:Buffer Manager%' 
    OR object_name LIKE '%:Memory Node%' 
    OR object_name LIKE '%:Plan Cache%')
  AND (counter_name LIKE '%pages %' 
    OR counter_name = 'Page life expectancy' 
    OR counter_name LIKE '%Node Memory (KB)%')
    OR  (object_name LIKE '%:Memory Manager%'
        AND RTRIM(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)',
                                    -- for 2012
                                    'Free Memory (KB)',              'Reserved Server Memory (KB)',
                                    'Database Cache Memory (KB)',    'Stolen Server Memory (KB)')
      )

-- Convert values from pages and KB to MB and rename counters accordingly
UPDATE @Perf
SET 
  counter_name = REPLACE(REPLACE(counter_name, 'pages', '(MB)'), '(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

-- Update counter/object names so they look like in 2012
UPDATE PC
SET 
  object_name = REPLACE(object_name, 'Buffer', 'Memory'),
  counter_name = ISNULL(M.NewName, counter_name)  
FROM @Perf PC
  LEFT JOIN
  (
    SELECT 'Free (MB)' AS OldName, 'Free Memory (MB)' AS NewName UNION ALL
    SELECT 'Database (MB)', 'Database Cache Memory (MB)' UNION ALL
    SELECT 'Stolen (MB)', 'Stolen Server Memory (MB)' UNION ALL
    SELECT 'Reserved (MB)', 'Reserved Server Memory (MB)' UNION ALL
    SELECT 'Foreign (MB)', 'Foreign Node Memory (KB)'
  ) 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')


-- Build Memory Tree
DECLARE @MemTree TABLE (Id int, ParentId int, counter_name nvarchar(128), formatted_value NUMERIC(20, 2), ShortName NVARCHAR(20))

-- Level 5
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = 1226,
  ParentId = 1225,
  instance_name + ' (MB)' as counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Plan Cache' 
  AND counter_name IN ('Cache (MB)')
  AND instance_name <> '_Total'

-- Level 4
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 (MB)')
  AND instance_name = '_Total'

UNION ALL

SELECT
  Id = 1222,
  ParentId = 1220,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE 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 (MB)' 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

-- Level 3
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = CASE counter_name 
           WHEN 'Granted Workspace Memory (MB)' THEN 1110 
           WHEN 'Stolen Server Memory (MB)' THEN 1220 
           ELSE 1210
         END,
  ParentId = CASE counter_name 
               WHEN 'Granted Workspace Memory (MB)' THEN 1100 
               ELSE 1200 
             END,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Memory Manager' 
  AND counter_name IN ('Stolen Server Memory (MB)', 'Database Cache Memory (MB)', 'Free Memory (MB)', 'Granted Workspace Memory (MB)')

-- Level 2
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = CASE WHEN counter_name = 'Maximum Workspace Memory (MB)' 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 (MB)', 'Maximum Workspace Memory (MB)') 

-- Level 1
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 (MB)')

-- Results:

-- PLE and Memory Grants
SELECT counter_name AS [Counter Name], cntr_value as Value
FROM @Perf
WHERE 
  object_name = 'Memory Manager' 
  AND counter_name IN ('Memory Grants Outstanding', 'Memory Grants Pending', 'Page life expectancy')

-- Memory tree
;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,2)) As Perc, CAST(NULL AS DECIMAL(20,2)) 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,2)) AS Perc,
  CAST(ISNULL(1.0*MT.formatted_value/NULLIF(CTE.TargetServerMemory, 0),0) AS DECIMAL(20,2)) AS PercOfTarget
FROM @MemTree MT
  INNER JOIN CTE ON MT.ParentId = CTE.ID
)
SELECT 
  counter_name AS [Counter Name], formatted_value AS [Memory MB], Perc AS [% of Parent], PercOfTarget AS [% of Target]
FROM CTE
ORDER BY ISNULL(ID, 10000), formatted_value DESC

То что показывает sys.dm_exec_query_resource_semaphores фактически соответствует только ветке Workspace Memory.
31 окт 13, 22:44    [15061622]     Ответить | Цитировать Сообщить модератору
 Re: Кол-во оперативной памяти на сервере  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mind
andrew shalaev
Если я правильно понимаю то total_memory_kb это то сколько серверу доступно памяти.
Неа, не правильно. Это количество памяти доступной серверу для хранения промежуточных результатов при выполнении запросов.
Поправка: доступно не серверу, а конкретному пулу ресурсов. Для 2005го это наверное одно и то же, а вот начиная с 2008 не совсем.
31 окт 13, 23:45    [15061839]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить