настройка производительности sql server

[Patrick]

Здравствуйте еще раз! Хочу получить максимум производительности из имеющегося железа сервера.

Что имеем по железу:
- Сервер HPe DL560 g9
- 8 штук дисков SAS 600gb 15k
- 1 штука контроллер NVMe SSD Intel на 2Tb
- 2 CPU Intel Xeon
- 256 гб ОЗУ

На сервере должны лежать базы 1С предприятия и SQL 2016.
Вопрос по настройке распределения памяти SQL сервера и кэш буфера.

Немного рассуждений по теме...
Решено разместить Buffer Pool на SSD но непонятно как определить его размер?
Я настраиваю по статье https://docs.microsoft.com/en-us/sq...ws/buffer-pool-extension?view=sql-server-2017
и https://docs.microsoft.com/en-us/sq...er-configuration-options?view=sql-server-2017
и там написано:

The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend a ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal. For information about setting the max_server_memory option, see Server Memory Server Configuration Options.

Если я все правильно понял то размер буфера для ssd определяется соотношением 1:16.
Так же написано что что бы определить текущее значение выделенной памяти нужно использовать T-SQL запрос

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

После выполнения запроса получаю такие данные:
Memory_usedby_Sqlserver_MB = 1964
Locked_pages_used_Sqlserver_MB = 0
Total_VAS_in_MB = 134217727
process_physical_memory_low = 0
process_virtual_memory_low = 0

Прочитав все это из статьи выше понял - надо настроить правильно параметр max server memory

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO

В итоге вопросы:
1. Какой размер буфера будет в моем случае исходя из моего железа и количества памяти??
2. Нужно ли трогать и перенастраивать параметр max server memory? Какое количество ОЗУ использует SQL сервер по умолчанию?? Всю память или нет?

Подскажите плиз в целом по проблеме...

-----Подумал и добавил------

Еще вдогонку вопрос.

Имеет ли смысл переместить системную базу SQL сервера - tempdb на SSD ?

 

[alxmel]

В этой же статье, которую ты скинул и написано про размер буффера.

The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend a ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.

На всякий случай добавлю.

Buffer Pool – область памяти (самая большая), которую SQL Server в основном использует для хранения страниц с данными. Любая страница, которая считывается с диска сначала помещается туда, а уже потом используется по назначению (для считывания или изменения данных). Страницы в памяти могут находиться в двух состояниях: так называемые «чистые» страницы, данные в которых никогда не изменялись, и «грязные», в которые были внесены изменения, но еще не сохранены обратно на диск с помощью процессов checkpoint или lazy writer. Buffer Pool Extension позволяет «увеличить» размер Buffer Pool используя для этого SSD диск. Это может быть полезно, если у вас есть возможность добавить SSD диск в ваш сервер, но нет возможности расширить оперативную память или вынести все часто используемые данные на SSD в силу каких-то ограничений. Например, можно использовать один дешевый SSD в качестве кэша в BPE не предъявляя к нему никаких требований по надежности, в то время как размещение всей или части базы данных потребует применения более дорогих и надежных систем.

Включить опцию очень легко. Для этого достаточно использовать команду ALTER SERVER CONFIGURATION и указать расположение файла и его размер, который будет использоваться в качестве Buffer Pool Extension.

alter server configuration
set buffer pool extension
on ( filename = 'SSD_DISK:\MyCache.bpe' , size = 128 gb );
go

После выполнения данной команды создается указанный нами файл, который сразу начинается использоваться. SQL Server может использовать его только для кэширования «чистых» страниц. Изменить расположение файла или его размер невозможно. Для этого требуется отключить BPE и включить его заново с другими параметрами. Отключается Buffer Pool Extension следующей командой.

alter server configuration
set buffer pool extension off;
go

Плюсы Buffer Pool
Ее рекомендуется использовать для OLTP систем, в которых преобладают запросы на чтение данных.
Нет никаких рисков потери данных, т.к. BPE кэширует только «чистые» страницы. При сбое диска BPE просто отключается.
Не требуется вносить никаких изменений в приложения – все начинает работать сразу, как только вы включаете опцию.


Минусы
- Возможность будет доступна только в редакции Enterprise Edition.
- Вы не можете задать файл для BPE размером меньше, чем размер текущей оперативной памяти. По крайней мере судя по моим тестам с использованием SQL Server 2014 CTP1, вы будете получать ошибку вида:

Msg 868, Level 16, State 1, Line 18
Buffer pool extension size must be larger than the current memory allocation threshold 2048 MB. Buffer pool extension is not enabled.

- Максимальный поддерживаемый размер оперативной памяти 128 Гб, т.е. обладатели систем с большим размером памяти воспользоваться опцией не смогут. И это довольно неприятный момент, т.к. такой и больший объемы вполне доступны покупателям даже для серверов начального уровня.
- Размер файла BPE не может превышать 32 размера вашей оперативной памяти. Т.е. максимально допустимый размер для него будет 32 x 128 Гб = 4 Тб. Рекомендуется задавать размер BPE не более 4-10 размеров оперативной памяти.
- Естественно располагать файл следует на быстром SSD диске, иначе смысл опции полностью теряется. И, хотя нет никаких требований к надежности, следует учесть, что если вдруг диск выйдет из строя – вы останетесь без BPE, что может существенно повлиять на производительность вашей системы, если она сильно зависит от этой опции.
http://olontsev.ru/2013/08/buffer-po...2014-overview/

 

[Patrick]

Спасибо за гайд.
Попробовал инициализировать буферный пул получаю ошибку:

Сообщение 864, уровень 16, состояние 1, строка 1
Попытка инициализировать расширение буферного пула с размером 1048576000 КБ при максимально допустимом размере 536870904 КБ.

Я поставил 1000гб, повторюсь физически памяти 256гб в сервере. Что я делаю не так??

-----Подумал и добавил------

Попробовал убавить размер буфера до 500гб - получаю ошибку вида

Сообщение 5123, уровень 16, состояние 21, строка 1
Операция CREATE FILE вызвала ошибку операционной системы 5(Отказано в доступе.) при попытке открыть либо создать физический файл "F:\DBCache.bpe".

-----Подумал и добавил------

Убавил до 500ГБ и все заработало.
Спасибо!!
Единственное что так и не догнал это то почему размер буффера ограничен 536870904 КБ??