Проблемы производительности из-за неэффективного использования памяти буферного пула

Автор: Paul Randal, Performance issues from wasted buffer pool memory

В апреле я запустил опрос, в котором просил вас прислать информацию о ваших буферных пулах — сколько памяти используется для страниц файлов данных и какая часть этой памяти хранит пустое пространство. Я получил данные с 1394 серверов по всему миру — спасибо!

Причина, по которой меня это интересует и должна интересовать вас, заключается в том, что память — один из важнейших ресурсов, используемых SQL Server. Если у вас недостаточно памяти, ваша рабочая нагрузка будет страдать, потому что:

• Вы будете выполнять больше операций чтения ввода-вывода, поскольку большая часть рабочей нагрузки не может поместиться в буферном пуле.
• Вы будете выполнять больше операций записи ввода-вывода, так как «ленивому писателю» (lazywriter) придётся вытеснять грязные страницы из буферного пула.
• Вы можете столкнуться с ожиданиями RESOURCE_SEMAPHORE, потому что запросы не могут получить необходимые им назначения памяти для выполнения запросов.
• Вы можете вызвать чрезмерное количество перекомпиляций планов, если кэш планов слишком ограничен.
• И целый ряд других проблем.

Одной из проблем с памятью, которую Кимберли подробно обсуждала в прошлом году (и подробно обучает этому на наших курсах по настройке производительности), является раздувание кэша однократно используемых планов (single-use plan cache bloat), когда большая часть кэша планов заполнена планами, которые используются один раз и никогда больше не пригодятся. Вы можете прочитать об этом в трёх постах в её категории Plan Cache, а также о том, как выявить раздувание кэша планов и что с этим можно сделать.

Эта статья посвящена памяти, которую буферный пул использует для хранения страниц файлов данных, и тому, насколько эффективно она используется.

Отслеживание плотности данных

Динамическое административное представление sys.dm_os_buffer_descriptors предоставляет информацию, хранимую буферным пулом для каждой страницы файла данных в памяти (в коде это называется структурой BUF). Одним из элементов, отслеживаемых этой структурой, является free_space_in_bytes для каждой страницы. Эта метрика обновляется в реальном времени по мере внесения изменений на страницы в памяти (вы легко можете проверить это сами) и поэтому является надёжным показателем плотности данных в используемой части буферного пула.

Плотность данных? Думайте об этом как о том, насколько плотно заполнена страница файла данных строками данных, индексов или LOB-данными. Чем больше свободного места на странице, тем ниже плотность данных.

Страницы с низкой плотностью данных вызываются:

• Очень широкими строками данных (например, в таблице с фиксированным размером строки в 5000 байт на страницу поместится только одна строка, что приведёт к потере примерно 3000 байт на страницу).
• Разбиениями страниц из-за случайных вставок в заполненные страницы или обновлений строк на заполненных страницах. Такие разбиения страниц приводят к логической фрагментации, которая влияет на производительность сканирования диапазонов, к низкой плотности данных на страницах данных/индексов и к увеличению нагрузки на журнал транзакций (см. Насколько дороги расщепление страниц с точки зрения журнала транзакций?).
• Удалениями строк, когда освободившееся пространство не будет повторно использовано из-за характера вставок в таблицу/индекс.

Страницы с низкой плотностью данных могут негативно влиять на производительность SQL Server, потому что чем ниже плотность записей на страницах в таблице:

• Тем больше дискового пространства требуется для хранения данных (и их резервного копирования).
• Тем больше операций ввода-вывода требуется для чтения данных в память.
• Тем больше памяти буферного пула требуется для хранения дополнительных страниц.

Решения для низкой плотности данных

Итак, что вы можете с этим сделать? Существует несколько решений для низкой плотности страниц:

• Изменить схему таблицы (например, вертикальное секционирование, использование типов данных меньшего размера).
• Изменить ключевые столбцы индекса (обычно применимо только к кластерным индексам — например, изменить ведущий ключ кластеризации со случайного значения, такого как непоследовательный GUID, на последовательный GUID или столбец идентификаторов).
• Использовать FILLFACTOR индекса для уменьшения разбиений страниц.
• Периодически перестраивать проблемные индексы.
• Рассмотреть возможность включения сжатия данных для некоторых таблиц и индексов.

Часть «потраченного впустую» пространства может быть результатом правильного управления индексами, где для страниц данных и индексов установлен низкий FILLFACTOR для смягчения разбиений страниц. Но я подозреваю, что это составляет лишь небольшую часть того, что мы видим в данных.

Цель этой статьи — не объяснить, как внести все изменения для уменьшения объёма свободного пространства, хранящегося в памяти, а проинформировать вас о том, что эта проблема существует. Очень часто ожидания PAGEIOLATCH преобладают в системах, потому что к подсистеме ввода-вывода выполняется больше операций ввода-вывода, чем необходимо, из-за таких вещей, как плохие планы, вызывающие сканирования таблиц, или низкая плотность данных. Если вы сможете понять, что проблема не в подсистеме ввода-вывода, то вы как администратор базы данных можете что-то с этим сделать.

Полезный код для анализа

Ниже приведён скрипт для анализа буферного пула с разбивкой по базам данных: сколько места занимается в буферном пуле и какая часть этого места является пустым пространством. Для систем с сотнями гигабайт используемой памяти этот запрос может выполняться долго:

SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'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_descriptors
GROUP BY [database_id];
GO

И вот пример вывода:

DatabaseName        MBUsed   MBEmpty
------------------- -------- ---------
Resource Database   51       11
ProdDB              71287    9779
master              2        1
msdb                481      72
ProdDB2             106      17
model               0        0
tempdb              2226     140

Ниже приведён скрипт, который разбивает данные по таблицам и индексам во всех базах данных, использующих пространство в буферном пуле. Я фильтрую системные объекты, а также индексы, где объём используемого пространства в буферном пуле меньше 100 МБ. Вы можете использовать его для выявления таблиц и индексов, требующих работы, чтобы позволить SQL Server более эффективно использовать память буферного пула и повысить производительность вашей рабочей нагрузки.

EXEC sp_MSforeachdb
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
    FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
USE [?]
SELECT
    ''?'' 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 -- Занимает более 100 МБ
ORDER BY [FreeSpacePC] DESC;
END';

И вот пример вывода с использованием более полного скрипта:

Database Object index_id Index        Type         TotalMB FreeSpaceMB FreeSpacePC
-------- ------ -------- ------------ ------------ ------- ----------- -----------
ProdDB   TableG 1        TableG_IX_1  CLUSTERED    531     130         24.5
ProdDB   TableI 1        TableI_IX_1  CLUSTERED    217     48          22.2
ProdDB   TableG 2        TableG_IX_2  NONCLUSTERED 127     27          21.8
ProdDB   TableC 1        TableC_IX_1  CLUSTERED    224     47          21.4
ProdDB   TableD 3        TableD_IX_3  NONCLUSTERED 1932    393         20.4
ProdDB   TableH 1        TableH_IX_1  CLUSTERED    162     33          20.4
ProdDB   TableF 5        TableF_IX_5  NONCLUSTERED 3128    616         19.7
ProdDB   TableG 9        TableG_IX_9  NONCLUSTERED 149     28          19.1
ProdDB   TableO 10       TableO_IX_10 NONCLUSTERED 1003    190         19
ProdDB   TableF 6        TableF_IX_6  NONCLUSTERED 3677    692         18.8
.

Это здорово, потому что это гораздо менее навязчивый способ выяснить, какие таблицы и индексы имеют проблемы с плотностью данных, чем выполнение sys.dm_db_index_physical_stats. Вы можете модифицировать скрипт, чтобы он создавал таблицу для хранения всех данных по всем базам данных и нарезал их так, как вам нужно. У меня есть гораздо более полный скрипт, который я использую на клиентских системах, но этот предоставит вам много полезных данных.