Optymalizacja wydajności jest zazwyczaj przeprowadzana zgodnie z następującymi cechami.
- Obciążenie procesora
- Wykorzystanie pamięci
- Uderzenie pamięci podręcznej
- Podsystem dysku
- Optymalizacja zapytań
Dla pamięci reguła jest tym lepsza, im lepiej. Kiedy zaczyna brakować powodu
sprawdź, co jest zatłoczone. Więcej na ten temat poniżej.
Zarządzanie pamięcią SQL Server.
Menedżer pamięci (MM) jest głównym elementem zarządzającym alokacją pamięci w SQL Server. Ten komponent automatycznie przydziela dostępną pamięć serwera SQL, zmniejszając potrzebę ręcznej konfiguracji. Po załadowaniu SQL MM określa początkową ilość rozproszonej pamięci, a następnie dynamicznie rezerwuje lub zwalnia pamięć RAM w miarę zmiany obciążenia. W ten sposób MM zarządza interakcją serwera SQL z systemem operacyjnym w kontekście zarządzania pamięcią. Główne elementy:
1. Urzędnicy zajmujący się pamięcią (Interfejs używany przez konsumentów do zarządzania pamięcią, każdy konsument ma jednego lub więcej urzędników pamięci, służy do przydzielania, wydawania i monitorowania wykorzystania pamięci, każdy urzędnik jest powiązany z brokerem, sys .dm _os _memory _clerks)
2. Obiekty pamięci (sterty pamięci, które wykorzystują interfejs urzędników pamięci, aby uzyskać dostęp do alokatora stron w celu alokacji stron. Obiekty pamięci nie używają interfejsów pamięci wirtualnej lub współdzielonej; ten element używa tylko mechanizmu alokacji stron. Wiele komponentów SQL Server odnosi się bezpośrednio do MO, pomijanie urzędników pamięci MO zapewnia możliwość przydzielania zakresów pamięci o dowolnym rozmiarze., sys.dm_os_memory_objects)
3. Węzły pamięci (Głównym zadaniem tego komponentu jest określenie obszaru alokacji pamięci w węźle NUMA , jeśli nie ma wystarczającej ilości pamięci, można ją przydzielić z następnego węzła, sys.dm_os_memory_nodes)
4. Brokery pamięci (monitorują żądania pamięci z komponentów SQL i porównują je z bieżącymi stopniami wykorzystania. Na podstawie otrzymanych informacji broker oblicza „optymalny” rozmiar pamięci, który można rozdzielić między składniki. Broker powiadamia komponenty swoich obliczeń, po czym każdy komponent wykorzystuje te informacje do dalszego wykorzystania pamięci.)
5. Pule pamięci (używane do ustawiania limitów pamięci, wewnętrzne - mechanizmy SQLOS, Domyślne - domyślna pula dla wszystkiego innego). Bardziej szczegółowo, wszystko jest opisane w Habre Maksymalna pamięć serwera i pamięć min serwera Chociaż pamięć podręczna bufora jest zarządzana automatycznie w SQL Server, administratorzy mogą dostosować maksymalną i minimalną pamięć alokowaną dla tego bufora. Parametr pamięci serwera min wskazuje limit, poniżej którego pula buforów nie zwolni zajętej pamięci na żądanie. Po początkowym załadowaniu pula nie zajmuje pamięci określonej w min. Pamięci serwera. Wykorzystywana jest minimalna wymagana objętość, która jest obliczana automatycznie. Wielkość puli, jeśli to konieczne, dalej wzrasta. EXEC sp_configure 'show advanced option', '1'; - Ustaw maksymalną pamięć serwera (MB), 4096 RECONFIGURE WITH OVERRIDE - Ustaw nowo ustawioną konfigurację: EX server, max set server memory opcje zaawansowane 'powrót do wartości domyślnych: EXEC sp_configure' pokaż zaawansowane opcje ', 0 RECONFIGURE WITH OVERRIDE 
- monitorowanie użytkowania pamięci SQL Server - broker pamięci (broker pamięci) jest składnikiem SQLOS. Broker pamięci odpowiada za przydzielanie pamięci między różnymi komponentami SQL Server zgodnie z ich żądaniami. SELECT p. nazwa AS resource_governor_pool_name, b. memory_broker_type, b. allocations_kb AS current_memory_allocated_kb, b. allocations_kb_per_sec ASrate_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 
Pamięć okna --Vydelenie prosihodit przez urzędników wybrać typ, nazwa, memory_node_id, suma (pages_kb + virtual_memory_reserved_kb + virtual_memory_committed_kb + awe_allocated_kb + shared_memory_reserved_kb + shared_memory_committed_kb) Jak TotalKB Z sys. dm_os_memory_clerks GROUP BY typ, nazwa, memory_node_id 
Pula buforów to obszar w pamięci używany do buforowania stron, tabel danych i ich indeksów, rozmiar strony wynosi 8 KB. Korzystanie z puli buforów redukuje dane wejściowe / wyjściowe do pliku bazy danych, a tym samym zwiększa wydajność serwera i jest głównym konsumentem pamięci w SQL Server. Punkt kontrolny - pula buforów nie kasuje Lazywriter jest aktywowany, gdy mniej niż 25% puli buforów jest wolne 
- Zawartość puli buforów SELECT obj. nazwa AS TableName, ind. nazwa AS IndexName, część. object_id AS ObjectID, część. index_id AS IndexID, część. AS numer_partycji Numer partycji, buf. page_level AS IndexLevel, przydział. type_desc AS AllocationType, buf. typ_strony AS PageType, buf. page_id AS PageNumber Z sys. dm_os_buffer_descriptors buf INNER JOIN sys. jednostki_alokacji przydzielają przydział. alloc_unit_id = buf. alloc_unit_id INNER JOIN sys. część partycji na części. hobt_id = alloc. container_id INNER JOIN sys. indeksy ind ind object_id = part. object_id AND ind. index_id = part. index_id INNER JOIN sys. obiekty obj ON obj. object_id = part. object_id WHERE buf. database_id = db_id () AND alloc. wpisz IN (1, 3) AND obj. is_ms_shipped = 0 SELECT obj. nazwa AS TableName, ind. nazwa AS IndexName, część. object_id AS ObjectID, część. index_id AS IndexID, część. AS numer_partycji Numer partycji, buf. page_level AS IndexLevel, przydział. type_desc AS AllocationType, buf. typ_strony AS PageType, buf. page_id AS PageNumber Z sys. dm_os_buffer_descriptors buf INNER JOIN sys. jednostki_alokacji przydzielają przydział. alloc_unit_id = buf. alloc_unit_id INNER JOIN sys. część partycji na części. partition_id = alloc. container_id INNER JOIN sys. indeksy ind ind object_id = part. object_id AND ind. index_id = part. index_id INNER JOIN sys. obiekty 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 
Liczniki w monitorze wydajności z sys. [dm_os_performance_counters] gdzie (nazwa_licznika jak '% współczynnik trafień w pamięci podręcznej bufora%' LUB nazwa_licznika LIKE 'Leniwy zapis%') 
W SQL Server 2014 pula buforów może zostać rozszerzona na pamięć nieulotną, na przykład na dysku SSD. To rozszerzenie nazywa się rozszerzeniem puli buforów. Czytaj więcej tutaj .