Lock Pages in Memory

Lock Pages in Memory
Przypisanie użytkownika na którym działa usługa SQL Server do tego uprawnienia powoduje, że pamięć używana przez SQL Server nie trafi do pliku stronicowania.
Funkcjonalność wspierana przez SQL Server od wersji 2005. Nie wspierana w edycji Express.

Zagrożenia
Może powodować niestabilność systemu Windows. Opisane dokładnie tu:  https://blogs.msdn.microsoft.com/psssql/2012/03/20/setfileiooverlappedrange-can-lead-to-unexpected-behavior-for-sql-server-2008-r2-or-sql-server-2012-denali/

LPiM vs. Task Manager
Po włączeniu uprawnień, Task Manager przestanie pokazywać prawidłowo ilości pamięci używanej przez SQL Server. Wartości pokazywane przez Perfmon są prawidłowe.
Jeśli nie mamy włączonego LPiM, SQL Server do alokowania pamięci używa wywołań VirtualAlloc API. To pozwala na alokowanie pamięci, która może być stronicowana. Włączenie LPiM powoduje, że SQL Server zaczyna używać wywołań AllocateUserPhysicalPages. Pamięć alokowana w ten sposób nie jest stronicowana. Liczniki używane przez Task Manager'a pokazują wyłącznie pamięć alokowaną przez wywołania VirtualAlloc. Informacja znaleziona na http://sqlinthewild.co.za/index.php/2016/01/19/stop-using-task-manager-to-check-sqls-memory-usage/


Włączenie
Sposób włączenia różni się w zależności od edycji i wersji SQL Server. Krok wymagany zawsze, to samo przypisanie użytkownika, na którym działa usługa SQL Server do grupy uprawnień:

Jeśli mamy wersję 2012 lub nowszą, nie trzeba robić nic więcej (poza restartem usługi). Jeśli nie mamy tej wersji, to musimy wykonać dodatkowe czynności (różne w zależności od edycji):

	Enterprise / Data Center	Standard / Business Intelligence
SQL Server 2005/2008/2008R2 (64bit)	-	Zainstalować SQL Hotfix KBA 970070. Włączyć trace flag 845
SQL Server 2005/2008/2008R2 (32bit)	Włączyć opcję  „AWE enabled”	Włączyć opcję „AWE enabled”

Sprawdzenie, czy działa
1. W czasie uruchamiania SQL Server w errorlogu powinien pojawić się wpis "Using locked pages in the memory manager"
2. Zapytanie:

 select   
 osn.node_id,   
 osn.memory_node_id,   
 osn.node_state_desc,   
 omn.locked_page_allocations_kb  
 from sys.dm_os_memory_nodes omn  
 inner join sys.dm_os_nodes osn   
 on (omn.memory_node_id = osn.memory_node_id)  
 where osn.node_state_desc <> 'ONLINE DAC'  

zwraca w kolumnie locked_page_allocations_kb wartości większe od zera.

Dodatkowe informacje
1. https://www.brentozar.com/archive/2009/11/christian-bolton-on-sql-server-memory/
2. https://support.microsoft.com/en-us/kb/2659143
Powiązany z:
1. Min & Max Server Memory

Min & Max Server Memory

Maksimum Server Memory
Ustawienie dostępne od SQL Server 2005.
Domyślnie SQL Server jest skonfigurowany tak, by używać całej dostępnej pamięci RAM. Wartość wpisana do konfiguracji to 2147483647MB (maksymalna wielkość typu Integer). SQL Server nie zajmuje jednak więcej niż ilość RAM dostępna na serwerze.
Pamięć ta po przypisaniu jej do SQL Server jest trzymana tak długo, aż nie nastąpi żądanie zwolnienia ze strony systemu Windows (np. w czasie backupu serwera Windows wbudowanym narzędziem "Kopia zapasowa systemu Windows"). Gdy SQL Server alokuje dla siebie całą dostępną pamięć RAM, nastąpi większe użycie pliku stronicowania systemu Windows.
Jest to ustawienie konfigurowane na poziomie poszczególnych instancji SQL Server - w przypadku instalacji wielu instancji należy skonfigurować na każdej osobno. Ustawienie dotyczy jedynie silnika relacyjnego, nie dotyczy SSRS, SSAS, SSIS...

Problemy powodowane przez brak określenia Max Server Memory

1. Brak pamięci na inne programy uruchamiane na serwerze Windows
2. Brak pamięci na procesy SQL Server, które nie korzystają z zakresu pamięci kontrolowanej przez Min & Max Server Memory (np. Linked Server, proceduy CLR...)
3. Zwiększone użycie pliku stronicowania
4. W przypadku instalacji wielu instancji SQL Server - jedna z instancji może mieć zbyt dużo przydzielonej pamięci, a inna zbyt mało.

Sposoby określenia prawidłowej wartości Max Server Memory

1. Kalkulator on-line http://sqlmax.chuvash.eu/
2. Inne źródła (np. na stronie Brent'a Ozar'a ) zalecają zostawienie 10% lub 4GB dla Windows
3. W książce "Podręcznik administratora SQL Server 2014" podana jest tabela:

Pamięć w systemie (GB)	Pamięć dla systemu Windows (GB)	Pamięć dla SQL Server (GB)
16	4	12
32	4	28
64	4	60
128	8	120
256	8	248

Sposób z kalkulatorem on-line jest chyba najlepszy, bo pozwala na określenie pamięci w zależności od procesorów i użycia pamięci przez inne aplikacje.

Minimum Server Memory
Ustawienie dostępne od SQL Server 2005.
Wielkość pamięci, której SQL Server ma nie oddawać w momencie żądania ze strony innych aplikacji lub systemu Windows. Domyślnie ustawiona na 0. Jest to ustawienie konfigurowane na poziomie poszczególnych instancji SQL Server - w przypadku instalacji wielu instancji należy skonfigurować na każdej osobno. Ustawienie dotyczy jedynie silnika relacyjnego, nie dotyczy SSRS, SSAS, SSIS...
To nie jest pamięć zajmowana przez SQL Server zaraz po starcie. SQL Server przy starcie alokuje tylko tyle pamięci, ile jest mu potrzebne. Jednak gdy podczas pracy ilość pamięci używanej przez SQL Server przekroczy limit określony w Min Server Memory, to limit ten będzie przestrzegany.

Problemy powodowane przez brak określenia Min Server Memory
1. Możliwe obniżenie wydajności SQL Server - w przypadku żądania pamięci przez inne aplikacje, oddaje zbyt dużo pamięci, musi więc częściej sięgać do danych z dysków twardych


Ustawianie parametrów
Polecenie:

 EXEC sys.sp_configure N'show advanced options', N'1'   
 RECONFIGURE WITH OVERRIDE  
 GO  
 EXEC sys.sp_configure N'min server memory (MB)', N'12000'  
 GO  
 EXEC sys.sp_configure N'max server memory (MB)', N'24000'  
 GO  
 RECONFIGURE WITH OVERRIDE  
 GO  
 EXEC sys.sp_configure N'show advanced options', N'0'   
 RECONFIGURE WITH OVERRIDE  
 GO  

Nie wymaga restartu usługi SQL Server. Może być bezpiecznie użyte w trakcie pracy systemu (polecenie "Reconfigure with override" powoduje między innymi wyczyszczenie cache'a planów zapytań, więc możliwe jest obniżenie wydajności).

Informacje dodatkowe:
Powiązany z:
1. konfiguracja Lock Pages in Memory
2. Buffer Pool
3. Wielkość pliku stronicowania dla SQL Server
4. https://blogs.msdn.microsoft.com/sqlsakthi/2011/03/11/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it/
5. Dokumentacja wraz z informacjami o wyjątkach nie korzystających z tej puli pamięci: https://support.microsoft.com/en-us/kb/2663912
6. SQL Server nie uruchamia się - - zbyt mała wartość Max Server Memory
/>

Problem z szyfrowaniem podczas instalacji SQL Server 2012

Symptomy
Podczas instalowania SQL Server 2012 pojawiają się błędy:

Feature: Database Engine Services

Status: Failed: see logs for details

Reason for failure: An error occurred during the setup process of the feature.

Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

Component name: SQL Server Database Engine Services Instance Features

Component error code: 0x84B10001Error description: There was an error generating the XML document.Error help link:               http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0xE0C083E6%400x689C89C0&EvtType=0xE0C083E6%400x689C89C0

W setup bootstrap w pliku logu mamy wpis

System.Security.Cryptography.CryptographicException: The specified file could not be decrypted

Powód:

Problem związany jest z poprawką do Windows: KB3004375

Rozwiązanie:

1. Odinstalować poprawkę KB3004375

2. Zainstalować SQL Server

3. Zainstalować poprawkę  KB3004375

Dokumentacja:

https://support.software.dell.com/appassure/kb/147903

Instant File Initialization

Instant File Initialization 

Konto usługi SQL powinno posiadać uprawnienia systemowe ”Wykonaj zadania konserwacyjne woluminów” (ang. Perform Volume Maintenance Tasks).

Powód:
W czasie rozszerzania pliku danych (lub tworzenia) plik nie tylko zwiększa swoją wielkość, ale także cała jego nowa zawartość jest "czyszczona" - wypełniana zerami. Zajmuje to pewien czas. W czasie rozszerzania plik jest niedostępny.
Nadanie uprawnienia "Perform Volume Maintenance Tasks" powoduje, że zawartość nie jest czyszczona, a jedynie alokowane jest miejsce.  
Nie dotyczy to plików logu transakcyjnego SQL Server, który zawsze jest zerowany.

Instant File Initialization jest niedostępne, gdy włączone jest TDE. 

Skutki pozytywne:
1. Znakomite skrócenie czasu rozszerzania (tworzenia) pliku danych
2. Skrócenie czasu niedostępności pliku danych podczas rozrzerzania
3. W przypadku startu serwera - skrócenie czasu startu (bo pliki Tempdb nie są czyszczone)
4. W przypadku przywracania bazy danych - skrócenie czasu, bo plik bazy nie jest czyszczony.

Skutki negatywne
1. Możliwość odczytania starej zawartości przez Administratora SQL Server. Tyle tylko, że i tak zazwyczaj jest adminem Windows i może zewnętrznymi narzędziami odczytać dowolny fragment dysku.

Jak włączyć:

• W SQL2016 - możliwość w czasie instalacji - opcja "Grant Perform Volume Maintenance privilege to SQL Server Database Engine Service". Jeśli pominiemy - wciąż pozostaje metoda ręcznego nadania uprawnienia.

• Dla SQL Server 2008, 2008 R2, 2012 i 2014 - metoda ręcznego nadania uprawnienia:
  Uruchomić secpol.msc i dodać konto, na którym działa usługa SQL Server do listy kont uprawnionych. Konieczny restart usługi SQL Server !

Uwagi:
1. Uprawnienie może być nadpisywane przez uprawnienia z domeny AD.
2. Dostępne od Windows XP i Windows Server 2003 z uwagi na https://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx
3. Może być blokowane w SQL Server przez TraceFlag 1806 

Sprawdzenie:

 USE [master]   
 GO  
 IF DB_ID('test') IS NOT NULL DROP DATABASE test   
 GO  
 DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS  
 CREATE DATABASE [test] CONTAINMENT = NONE   
 ON PRIMARY (   
 NAME = N'test',   
 FILENAME = N'D:\MSSQL\test.mdf' , SIZE = 1048576KB , FILEGROWTH = 65536KB )   
 LOG ON (   
 NAME = N'test_log',   
 FILENAME = N'L:\MSSQL\test.ldf' , SIZE = 1048576KB , FILEGROWTH = 65536KB   
 )  
 GO  
 DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS  
 IF DB_ID('test') IS NOT NULL DROP DATABASE test   
 GO  
 EXEC sp_readerrorlog 0, 1  

Wynik przed włączeniem:
2016-03-25 17:49:38.990 spid57 Zeroing D:\MSSQL\test.mdf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:49:39.030 spid57 Zeroing L:\MSSQL\test.ldf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:50:36.040 spid57 Zeroing completed on D:\MSSQL\test.mdf (elapsed = 57050 ms)
2016-03-25 17:51:28.310 spid57 Zeroing completed on L:\MSSQL\test.ldf (elapsed = 109275 ms)

Wynik po włączeniu:

2016-03-25 17:56:06.540 spid52 Zeroing L:\MSSQL\test.ldf from page 0 to 131072 (0x0 to 0x40000000)
2016-03-25 17:56:50.170 spid52 Zeroing completed on L:\MSSQL\test.ldf (elapsed = 43619 ms)

Różnice:

Czas utworzenia bazy spadł o 60% , w logu wpisy jedynie o zerowaniu pliku log.

Dokumentacja: 
https://msdn.microsoft.com/pl-pl/library/ms175935(v=sql.110).aspx