Оптимізація продуктивності зазвичай виконується за такими характеристиками
- Завантаження процесора
- Використання пам'яті
- Попадання в кеш
- дискова підсистема
- оптимізація запитів
Для пам'яті діє правило чим більше тим краще ,. Коли її починає не вистачати привід
перевірити чому ж вона забита. Про це докладніше нижче.
Управління пам'яттю SQL Server.
Memory manager (ММ) є основним елементом, який управляє розподілом пам'яті в SQL сервері. Даний компонент автоматично розподіляє доступну SQL сервера пам'ять, знижуючи необхідність ручної настройки. Після завантаження SQL ММ визначає первинний об'єм розподіленої пам'яті і далі по мірі зміни навантаження динамічно резервує або звільняє оперативну пам'ять. Таким чином, ММ управляє взаємодією SQL сервера з операційної системи в контексті управління пам'яттю. Основні комноненти:
1. Memory Clerks (Інтерфейс, який використовується споживачами для управління пам'яттю, кожен споживач має один або кілька клерків пам'яті, використовується для розподілу, звільнення і моніторингу використання пам'яті, кожен клерк пов'язаний з брокером, sys .dm _os _memory _clerks)
2. Memory Objects (Кучі пам'яті, які використовують інтерфейс клерків пам'яті щоб отримати доступ до page allocator для виділення сторінок. Memory Objects не використовують інтерфейси віртуальної або загальної пам'яті, цей елемент використовує тільки механізм розподілу сторінок. Багато компонентів SQL Server звертаються безпосередньо до MO, минаючи клерки пам'яті. МО надають можливість розподілити діапазони пам'яті довільного розміру., sys.dm_os_memory_objects)
3. Memory Nodes (Головне завдання цього компонента полягає у визначенні області виділення пам'яті на вузлі NUMA , Якщо пам'яті не вистачає може бути виділена з сусіднього вузла, sys.dm_os_memory_nodes)
4. Memory Brokers (відстежує запити пам'яті від компонентів SQL і зіставляє з поточними показники її використання. Грунтуючись на отриманій інформації, брокер обчислює «оптимальний» розмір пам'яті, яка може бути розподілена між компонентами. Брокер повідомляє компоненти про своїх обчисленнях, після цього кожен компонент використовує ці відомості для подальшого використання пам'яті.)
5. Memory Pools (використовується щоб встановити ліміти пам'яті, internal - механізми SQLOS, Default -пул за замовчуванням для всього іншого). Більш докладно все давно описано на Хабре Max server memory і min server memory Хоча управління буферним кешем відбувається автоматично всередині SQL Server, однак адміністратори можуть регулювати максимальний і мінімальний розмір розподіляє пам'яті для цього буфера. Параметр min server memory позначає кордон, нижче якої Buffer Pool НЕ буде на вимогу звільняти зайняту пам'ять. При первинному завантаженні пул не займає пам'ять, зазначену в min server memory. Використовується мінімально необхідний обсяг, який обчислюється автоматично. Розмір пулу при необхідності в подальшому збільшується. EXEC sp_configure 'show advanced option', '1'; --Set the maximum amount of memory to 4096 MB: EXEC sp_configure 'max server memory (MB)', 4096 RECONFIGURE WITH OVERRIDE --Display the newly set configuration: EXEC sp_configure 'max server memory (MB)' --Set 'show advanced options 'back to default: EXEC sp_configure' show advanced options ', 0 RECONFIGURE WITH OVERRIDE
- моніторинг використання пам'яті SQL Server --Memory broker (брокер пам'яті) є компонентом SQLOS. Брокер пам'яті відповідає за розподіл пам'яті між різними компонентами SQL Server відповідно до їх запитами. SELECT p. name AS resource_governor_pool_name, b. memory_broker_type, b. allocations_kb AS current_memory_allocated_kb, b. allocations_kb_per_sec AS allocation_rate_in_kb_per_sec, b. future_allocations_kb AS near_future_allocations_kb, b. target_allocations_kb, b. last_notification AS last_memory_notification FROM sys. dm_os_memory_brokers b INNER JOIN sys. resource_governor_resource_pools p ON p. pool_id = b. pool_id 
--Виделеніе пам'яті windows просіходіт через клерки SELECT type, name, memory_node_id, sum (pages_kb + virtual_memory_reserved_kb + virtual_memory_committed_kb + awe_allocated_kb + shared_memory_reserved_kb + shared_memory_committed_kb) AS TotalKB FROM sys. dm_os_memory_clerks GROUP BY type, name, memory_node_id 
Buffer pool - це область в пам'яті, яка використовується для кешування сторінок, даних таблиць і їх індексів, розмір сторінок 8Кб. Використання Buffer pool зменшує введення / виведення в файл бази даних і таким чином збільшує продуктивність сервера і є основним споживачем пам'яті в SQL Server. Checkpoint - буферний пул не очищати Lazywriter активізується коли вільно менше 25% буферного пулу 
- Вміст буферного пулу SELECT obj. name AS TableName, ind. name AS IndexName, part. object_id AS ObjectID, part. index_id AS IndexID, part. partition_number AS PartitionNumber, buf. page_level AS IndexLevel, alloc. type_desc AS AllocationType, buf. page_type AS PageType, buf. page_id AS PageNumber FROM sys. dm_os_buffer_descriptors buf INNER JOIN sys. allocation_units alloc ON alloc. allocation_unit_id = buf. allocation_unit_id INNER JOIN sys. partitions part ON part. hobt_id = alloc. container_id INNER JOIN sys. indexes ind ON ind. object_id = part. object_id AND ind. index_id = part. index_id INNER JOIN sys. objects obj ON obj. object_id = part. object_id WHERE buf. database_id = db_id () AND alloc. type IN (1, 3) AND obj. is_ms_shipped = 0 SELECT obj. name AS TableName, ind. name AS IndexName, part. object_id AS ObjectID, part. index_id AS IndexID, part. partition_number AS PartitionNumber, buf. page_level AS IndexLevel, alloc. type_desc AS AllocationType, buf. page_type AS PageType, buf. page_id AS PageNumber FROM sys. dm_os_buffer_descriptors buf INNER JOIN sys. allocation_units alloc ON alloc. allocation_unit_id = buf. allocation_unit_id INNER JOIN sys. partitions part ON part. partition_id = alloc. container_id INNER JOIN sys. indexes ind ON ind. object_id = part. object_id AND ind. index_id = part. index_id INNER JOIN sys. objects obj ON obj. object_id = part. object_id WHERE buf. database_id = db_id () AND alloc. type = 2 AND obj. is_ms_shipped = 0 ORDER BY TableName, IndexID, PageNumber 
Лічильники в performance monitor FROM sys. [Dm_os_performance_counters] where (counter_name like '% Buffer cache hit ratio%' OR counter_name LIKE 'Lazy Write%') 
У SQL Server 2014 buffer pool може бути розширений в незалежну пам'ять, наприклад, на диск SSD. Таке розширення називається Buffer Pool Extension. Детальніше можна прочитати тут .