Аптымізацыя прадукцыйнасці звычайна выконваецца па наступных характарыстыках
- загрузка працэсара
- выкарыстанне памяці
- Кантакт з кэш
- дыскавая падсістэма
- аптымізацыя запытаў
Для памяці дзейнічае правіла чым больш тым лепш ,. Калі яе пачынае не хапаць нагода
праверыць чым жа яна забітая. Пра гэта падрабязней ніжэй.
Кіраванне памяццю 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. Больш падрабязна можна прачытаць тут .