sp_configure – część 1

Bardzo często korzystamy z procedury sp_configure w celu ustawienia pewnych opcji SQL Server. Jak wiemy po wykonaniu procedury sp_configure otrzymujemy kilkadziesiąt wierszy z różnymi ustawieniami – przynajmniej kilka z nich umie wymienić każdy, jednakże są też mniej znane czy też mające status obsolete. W moich następnych kilku postach opiszę wszystkie opcje dostępne w SQL Server 2008.

Na pierwszy ogień idzie trochę danych ogólnych o sp_configure i reconfigure. W kolejnych odcinkach pojawią się najpierw opcje niezaawansowane, a później zaawansowane.

Trochę ogólników

Aktualnie w SQL Server 2008 SP1 Enterprise jest dokładnie 68 różnych ustawień, którymi możemy zarządzać przy pomocy sp_configure. Do listy opcji i ich aktualnych ustawień możemy się “dobrać” odwołując widok systemowy:
SELECT *
FROM sys.configurations

Wynik pracy polecenia będzie +- taki:

Co ciekawego? Ano kilka rzeczy się znajdzie:

value – wartość skonfigurowana

value_in_use – wartość obecnie używana – czyli że ta wartość zostanie zastąpiona przez wartość z kolumny value po restarcie SQL’a

description/is_dynamic – czyli coś czego nie uświadczy się w sp_configure; pierwsze pole jest jasne, natomiast is_dynamic = 1 oznacza że zmiana wartości jest możliwa bez restaru SQL Servera (wystarczy polecenie reconfigure), warość 0 w tym polu oznacza konieczność restartu SQL Servera celem załadowania zmian.

Poniżej lista opcji, które wymagają restartu SQL Servera aby zaczęły działać:

• affinity I/O mask
• awe enabled
• c2 audit mode
• common criteria compliance enabled
• EKM provider enabled
• fill factor (%)
• lightweight pooling
• locks
• max worker threads
• open objects
• priority boost
• remote access
• scan for startup procs
• set working set size
• user connections

Jak widać nie ma ich wiele – ale są ;-)

Reconfigure

Skoro już wiemy gdzie jakich opcji szukać wypadało by wiedzieć jak już używać. Poniższe polecenia pokazują jak się należy obchodzić z pewnymi opcjami :)

SELECT ‘przed zmiana                ’,
name,
Cast(VALUE AS INT) AS VALUE,
Cast(value_in_use AS INT) AS value_in_use
FROM sys.configurations
WHERE configuration_id = 103 – user connections
GO

sp_configure ‘user connections’, 1000
GO

SELECT ‘po sp_configure             ’,
name,
Cast(VALUE AS INT) AS VALUE,
Cast(value_in_use AS INT) AS value_in_use
FROM sys.configurations
WHERE configuration_id = 103 – user connections
GO

RECONFIGURE
GO

RECONFIGURE with override
GO

SELECT ‘po reconfigure with override’,
name,
Cast(VALUE AS INT) AS VALUE,
Cast(value_in_use AS INT) AS value_in_use
FROM sys.configurations
WHERE configuration_id = 103 – user connections

Poniżej kod, zmieniający wartość ustawienia ‘user connections’, efekt na mojej instancji testowej był taki:

Należy zwrócić uwagę że opcja user connections, jest opcją która wymaga restartu SQL Server celem zaaplikowania. Co ciekawe samo wywołanie polecenia reconfigure zostało zakończone błędem z komunikatem że zmiany ad hoc na widokach systemowych ! Ale już z reconfigure with override daje rade – zapisać że taka wartość w konfiguracji (ale nie modyfikuje wartości) aktualnie używanych.

Co powoduje dodanie opcji with override? Samo reconfigure zanim zapisze zmiany sprawdza ich poprawność, tzn. czy wartości są w odpowiednim zakresie – nie tylko z zakresu zmiennej ale i rekomendacji – tzn. np. recovery interval powinno być <= 60. No dobrze ale czy ograniczanie liczby połączeń do 1000 jest błędem ? Nie :) nasz problem powodowany jest ustawieniem innej opcji: allow updates, która ma status obsolete (ale o tym w następnym odcinku). Tutaj pozostaje nam jeszcze ważna uwaga że reconfigure przepisuje (można to zobrazować przepisaniem wartości z kolumny value na value_in_use dla opcji dynamicznych) ponownie wszystkie opcje! Dodanie opcji with override powoduje zapisanie zmian do katalogów systemowych bez sprawdzania zawartości zmian – a co za tym idzie w naszym przypadku dajemy radę :)

Dla jasności dodam jeszcze czym jest sp_configure? Procedura systemowa odpowiedzialna za listowanie oraz modyfikowanie pewnych opcji SQL Server. Można ją wywołać na kilka sposobów:

sp_configure – zwróci ustawienia wszystkich opcji

sp_configure ‘nazwa opcji’ – zwróci ustawienia jednej wybranej opcji

sp_configure ‘nazwa opcji’, wartosc – zmieni ustawienia jednej wybranej opcji.

W następnym odcinku omówione zostanie omówione opcje podstawowe (16 sztuk).