Nawet najlepiej skonfigurowany SQL Server wymaga ciągłej uwagi administratora. Fakt wystąpienia większości, jeżeli nie wszystkich, błędów może być automatycznie raportowany operatorom. Natomiast obowiązkiem administratora jest:

• Sprawdzanie, czy niepożądane osoby nie uzyskały dostępu do SQL Servera lub danych w nim przechowywanych (ponieważ hakerzy znają standardowe metody wykorzystywane przez SQL Server i system Microsoft Windows do informowania o włamaniach, a ponadto potrafią je blokować, nie można na podstawie braku wpisów w dzienniku zabezpieczeń czy dzienniku SQL Servera wnosić o braku udanych prób przejęcia kontroli nad systemem).
• Optymalizacja wydajności systemu bazodanowego.
• Bieżące rozwiązywanie problemów wynikających z codziennej aktywności użytkowników, np. kaskadowego blokowania transakcji czy zablokowania konta użytkownika.

Poniższy rozdział zawiera opis technik pozwalających na zrealizowanie dwóch ostatnich celów.

Optymalizacja wydajności systemu bazodanowego

Wydajność jest terminem względnym — system, który dla niektórych użytkowników jest bardzo wolny, dla innych może być wzorem wydajności. Tak więc, aby móc porównywać wydajniejsze i mniej wydajne rozwiązania, należy przyjąć pewną konwencję, w ramach której wydajność mierzona jest dwoma wskaźnikami:

1. Liczbą operacji przeprowadzanych na sekundę („przepustowość” systemu).
2. Czasem potrzebnym na zwrócenie klientowi pierwszego bajta wyniku („czas reakcji” systemu).

O ile wartość pierwszego wskaźnika zależy przede wszystkim od dostępnych dla SQL Servera zasobów systemowych, o tyle wartość drugiego zależy głównie od logicznej i fizycznej struktury bazy danych i aplikacji klienckich. Istotny wpływ na oba wskaźniki ma również wykorzystywana przez serwer i klientów metoda pobierania i przetwarzania danych.

Ponadto, aby możliwe było ocenianie wpływu działań podejmowanych przez administratora na wydajność systemu, konieczne jest utworzenie linii bazowej zawierającej zastane wartości najważniejszych liczników wydajności.

Na końcową wydajność systemu mają wpływ jego wszystkie składniki, począwszy od komputera i systemu operacyjnego, poprzez SQL Server, fizyczną i logiczną strukturę bazy danych, skończywszy na aplikacji klienckiej. Co więcej, niewydajność jednego z tych elementów może negatywnie wpłynąć na wyniki monitorowania wydajności innego. Właśnie dlatego strojenie (optymalizacja) baz danych jest umiejętnością w dużej mierze praktyczną, nabywaną poprzez zdobywanie indywidualnych doświadczeń, a nie wyuczoną.

Aby uniknąć wielogodzinnych poszukiwań odpowiedzi na pytania typu „Dlaczego wykonanie raportu X zajmuje tyle czasu, skoro wywołuje on jedynie Y procedur systemowych i zwraca średnio Z danych?”, monitorowanie wydajności należy przeprowadzać, zaczynając od monitorowania wydajności komputera, poprzez śledzenie pracy systemu operacyjnego, analizę wykorzystania zasobów systemowych przez SQL Server i sprawdzenie struktury bazy danych, a kończąc na monitorowaniu wydajności aplikacji klienckiej i poszczególnych uruchomionych w niej raportów czy zestawień.

Zasoby komputera

Wydajność SQL Servera, jak każdego serwera bazodanowego, zależy przede wszystkim od czterech czynników:

1. Pamięci operacyjnej.
2. Mocy obliczeniowej.
3. Wydajności systemu wejścia-wyjścia.
4. Przepustowości lokalnej sieci komputerowej.

Brak któregokolwiek z tych zasobów ma zgubny wpływ na wykorzystanie pozostałych i wydajność całego systemu bazodanowego. Na przykład niewystarczająca ilość pamięci operacyjnej spowoduje coraz częstsze odwoływanie się do danych zapisanych na dysku, a w rezultacie przeciążenie systemu wejścia-wyjścia, które dla systemów Microsoft Windows natychmiast skutkuje obniżeniem wydajności samego systemu, co powoduje powstanie kolejki procesów (w tym procesów SQL Servera) czekających na dostęp do procesora, a to z kolei niemożność bieżącego odbierania i wysyłania danych do klientów itd..

Ponieważ SQL Server działa jedynie w środowisku systemów operacyjnych Microsoft Windows, a w praktyce produkcyjne bazy danych uruchamiane są wyłącznie w środowisku serwerów tej firmy, wszystkie cztery zasoby możemy monitorować, korzystając z systemowego narzędzia Wydajność zawierającego dwie konsole: Monitor systemu oraz Dzienniki wydajności i alerty. Interesuje nas raczej zapisanie wyników do pliku niż ich bieżąca analiza na ekranie, utworzymy więc dziennik liczników.

Poniższe punkty zawierają krótki opis architektury SQL Servera i sposobów wykorzystania przez niego poszczególnych zasobów komputera.

Pamięć operacyjna

Wirtualna pamięć przydzielona SQL Serwerowi wykorzystywana jest do przechowywania zarówno procesów serwera (ang. Executables), jak i danych. Maksymalna ilość pamięci przydzielonej SQL serwerowi zależy od wersji systemu operacyjnego i na przykład dla systemu Windows 2000 Datacenter Server wynosi 32 GB.

SQL Server dostępną pamięć przydziela poszczególnym procesom realizującym żądania klienckie. Domyślnie ilość fizycznej pamięci operacyjnej dostępnej SQL Serverowi jest konfigurowana dynamicznie, co sprowadza się do tego, że o ile tylko system posiada co najmniej 5 MB dostępnej pamięci fizycznej, to kolejne żądanie zarezerwowania jej dla SQL Servera zostanie zrealizowane. Możliwe jest także przydzielenie minimalnej i maksymalnej ilości dostępnej dla SQL Servera fizycznej pamięci. Opcje te możemy ustawić albo konfigurując właściwości serwera poprzez konsolę Enterprise Manager, albo wykonując procedurę sp_configure. Większość opcji tej procedury dostępna jest dopiero po wyświetleniu opcji zaawansowanych:

 EXEC sp_configure GO name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- allow updates 0 1 0 0 default language 0 9999 0 0 max text repl size (B) 0 2147483647 65536 65536 nested triggers 0 1 1 1 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 0 0 show advanced options 0 1 0 0 user options 0 16383 0 0 EXEC sp_configure 'show advanced option','1' RECONFIGURE EXEC sp_configure GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE 􀂪statement to install. name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- affinity mask 0 2147483647 0 0 allow updates 0 1 0 0 awe enabled 0 1 0 0 c2 audit mode 0 1 0 0 cost threshold for parallelism 0 32767 5 5 cursor threshold -1 2147483647 -1 -1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 fill factor (%) 0 100 0 0 index create memory (KB) 704 2147483647 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 32 0 0 max server memory (MB) 4 2147483647 2147483647 2147483647 max text repl size (B) 0 2147483647 65536 65536 max worker threads 10 32767 255 255 media retention 0 365 0 0 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 0 0 nested triggers 0 1 1 1 network packet size (B) 512 65536 4096 4096 open objects 0 2147483647 0 0 priority boost 0 1 0 0 query governor cost limit 0 2147483647 0 0 query wait (s) -1 2147483647 -1 -1 recovery interval (min) 0 32767 0 0 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 0 0 scan for startup procs 0 1 0 0 set working set size 0 1 0 0 show advanced options 0 1 1 1 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 16383 0 0 

Aby ustalić ilość pamięci, w której możliwe będzie buforowanie danych (procesom serwera przydzielana jest zawsze taka sama ilość pamięci operacyjnej) należy określić wartości opcji min server memory i max server memory:

 EXEC sp_configure 'max server memory','192' EXEC sp_configure 'min server memory', '128' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'max server memory (MB)' changed from 255 to 192. Run the 􀂪RECONFIGURE statement to install. DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'min server memory (MB)' changed from 0 to 128. Run the 􀂪RECONFIGURE statement to install. 

Zarezerwowanie minimalnej ilości pamięci operacyjnej na potrzeby SQL Servera poprawi wydajność systemu bazodanowego, jeżeli usługa serwera okresowo jest bezczynna (system usuwa takie usługi z pamięci) lub jeżeli na tym samym komputerze uruchomiony jest inny serwer, który może „zawłaszczyć sobie” całą dostępną pamięć operacyjną.

Możliwe jest również przydzielenie na stałe określonej części pamięci operacyjnej SQL Serwerowi. W tym wypadku należy ustawić wartość obu poprzednich opcji na tą samą wartość, a następnie ustawić wartość opcji set working set size na 1:

 EXEC sp_configure 'max server memory','128' EXEC sp_configure 'min server memory', '128' EXEC sp_configure 'set working set size', '1' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'max server memory (MB)' changed from 192 to 128. Run the 􀂪RECONFIGURE statement to install. DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'min server memory (MB)' changed from 128 to 128. Run the 􀂪RECONFIGURE statement to install. DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'set working set size' changed from 1 to 1. Run the RECONFIGURE 􀂪statement to install. 

SQL Server minimalizuje liczbę operacji wejścia-wyjścia, starając się buforować wszystkie odczytane z dysku strony danych. Jedynie brak dostępnej pamięci operacyjnej zmusi go do przerwania buforowania kolejnych danych.

Kolejna opcją, której ustawienie może poprawić wykorzystanie pamięci przez SQL Server, jest opcja max worker threads. Określa ona liczbę wątków, które mogą zostać uruchomione na żądanie klientów. Domyślna wartość 255 jest optymalna dla serwerów obsługujących wiele żądań jednocześnie (SQL Server pozwala na nawiązanie jednoczesnych połączeń przez 32 767 klientów). Ponieważ każdy wątek rezerwuje 512 KB pamięci operacyjnej, w przypadku systemów obsługujących mniejszą liczbę klientów, zmniejszenie tej liczby może poprawić wydajność serwera:

 EXEC sp_configure 'max worker threads','100' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'max worker threads' changed from 255 to 100. Run the RECONFIGURE 􀂪statement to install. 

Oprócz danych, pamięć operacyjna przydzielana jest również procesom serwera:

• Podczas uruchamiana usługa SQL Server rezerwuje dla siebie ok. 4 MB pamięci RAM.
• Każde połączenie sieciowe klienta rezerwuje 24 KB pamięci.

Tworząc linię bazową dostępnych zasobów komputera, należy dodać do dziennika następujące liczniki związane z pamięcią:

1. Pamięć\Dostępna pamięć (MB) — wartość tego licznika nie powinna spaść poniżej 5.
2. Pamięć\Strony/s — wartość licznika nie powinna utrzymywać się stale powyżej 0.
3. Pamięć\Odczyty stron/s — wartość licznika nie powinna przekraczać 5.
4. SQL Server: Buffer Manager\Buffer Cache Hit Ratio — wartość licznika nie powinna spadać poniżej 95.

Procesor

SQL Server jest programem wielowątkowym — poszczególne zadania mogą być realizowane przez osobne wątki programu. Większość wątków SQL Servera działa w trybie użytkownika systemu Windows NT/2000, ale kilka (wątek przeprowadzający punkt kontrolny, wątek odpowiedzialny za logowanie użytkowników oraz wątek realizujący obsługę bibliotek sieciowych serwera) uruchomianych jest w trybie chronionym.

Poszczególne żądania klientów realizowane są według następującego schematu:

1. Jeżeli wątek jest już uruchomiony, SQL Server przydziela żądaniu realizujący je wątek.
2. Jeżeli wątek nie jest uruchomiony, a liczba aktywnych wątków jest mniejsza od maksymalnej, uruchomiony zostaje nowy watek.
3. Jeżeli wątek nie jest uruchomiony, a usługa ODS wykorzystuje już wszystkie dostępne wątki, żądanie klienta czeka, aż któryś z aktywnych wątków zakończy swoje działanie.

Ponadto niektóre zadania (szczególnie w wersji Enterprise) realizowane są równolegle przez klika wątków. Do takich operacji należą m.in. wykonywanie i odtwarzanie kopii zapasowych, pobieranie danych z kilku tabel w ramach jednego zapytania czy równoległe odczytywanie kilku indeksów.

Taka architektura sprawia, że SQL Server potrafi (i taka jest jego domyślna konfiguracja) wykorzystać wszystkie dostępne procesory komputera, na którym został zainstalowany. Liczbę procesorów, które będą wykorzystywane przez wątki SQL Servera możemy programowo określić, ustawiając wartość opcji affinity mask:

 sp_configure 'affinity mask', '15' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'affinity mask' changed from 0 to 15. Run the RECONFIGURE 􀂪statement to install. 

Powyższy skrypt umożliwił SQL Serverowi korzystanie z pierwszych czterech procesorów (binarną reprezentacją liczby 15 jest 00001111). Ograniczenie liczby dostępnych procesorów może wpłynąć na wydajność SQL Servera poprzez ograniczenie liczby przełączeń pomiędzy wątkami uruchomionymi w trybie użytkownika a wątkami działającymi w trybie chronionym, dzięki pozostawieniu procesorów dostępnych systemowi operacyjnemu.

Ustawiając wartość opcji priority boost na 1, możemy uruchomić wątki SQL Servera z priorytetem 13 (domyślnym priorytetem jest 7):

 sp_configure 'priority boost', '1' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'priority boost' changed from 0 to 1. Run the RECONFIGURE 􀂪statement to install. 

Ustawiając wartość opcji lightweight pooling na 1, spowodujemy, że zadania wielowątkowe nie będą realizowane poprze kilka wątków, a poprzez uruchomienie w ramach pojedynczego wątku kilku włókien (ang. Fiber). Ponieważ przełączanie pomiędzy włóknami jest szybsze i wymaga mniej zasobów niż przełączanie pomiędzy wątkami, ustawienie tej opcji jest zalecane dla systemów wieloprocesorowych, w których wykorzystanie procesorów utrzymuje się na wysokim poziomie, a liczba przełączeń pomiędzy trybem użytkownika i chronionym jest duża:

 sp_configure 'lightweight pooling', '1' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'lightweight pooling' changed from 0 to 1. Run the RECONFIGURE 􀂪statement to install. 

Tworząc linię bazową dostępnych zasobów komputera, należy dodać do dziennika następujące liczniki związane z procesorem:

1. Procesor\Czas procesora (%) — wartość licznika nie powinna stale przekraczać 90.
2. Procesor\Czas uprzywilejowany (%) — wartość licznika nie powinna stale przekraczać 10.
3. System\Długość kolejki procesora — wartość licznika nie powinna przekraczać 5, przy czym średnia wartość nie powinna przekraczać 3.
4. System\Przełączanie kontekstu/s — przy wartości powyżej 7000 należy przełączyć SQL Server w tryb włókien.

Dysk twardy

SQL Server jest programem wielowątkowym — poszczególne zadania mogą być realizowane przez osobne wątki programu. Większość wątków SQL Servera działa w trybie użytkownika systemu Windows NT/2000, ale kilka (wątek przeprowadzający punkt kontrolny, wątek odpowiedzialny za logowanie użytkowników oraz wątek realizujący obsługę bibliotek sieciowych serwera) uruchomianych jest w trybie chronionym.

Poszczególne żądania klientów realizowane są według następującego schematu:

1. Jeżeli wątek jest już uruchomiony, SQL Server przydziela żądaniu realizujący je wątek.
2. Jeżeli wątek nie jest uruchomiony, a liczba aktywnych wątków jest mniejsza od maksymalnej, uruchomiony zostaje nowy watek.
3. Jeżeli wątek nie jest uruchomiony, a usługa ODS wykorzystuje już wszystkie dostępne wątki, żądanie klienta czeka, aż któryś z aktywnych wątków zakończy swoje działanie.

Ponadto niektóre zadania (szczególnie w wersji Enterprise) realizowane są równolegle przez klika wątków. Do takich operacji należą m.in. wykonywanie i odtwarzanie kopii zapasowych, pobieranie danych z kilku tabel w ramach jednego zapytania czy równoległe odczytywanie kilku indeksów.

Taka architektura sprawia, że SQL Server potrafi (i taka jest jego domyślna konfiguracja) wykorzystać wszystkie dostępne procesory komputera, na którym został zainstalowany. Liczbę procesorów, które będą wykorzystywane przez wątki SQL Servera możemy programowo określić, ustawiając wartość opcji affinity mask:

 sp_configure 'affinity mask', '15' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'affinity mask' changed from 0 to 15. Run the RECONFIGURE 􀂪statement to install. 

Powyższy skrypt umożliwił SQL Serverowi korzystanie z pierwszych czterech procesorów (binarną reprezentacją liczby 15 jest 00001111). Ograniczenie liczby dostępnych procesorów może wpłynąć na wydajność SQL Servera poprzez ograniczenie liczby przełączeń pomiędzy wątkami uruchomionymi w trybie użytkownika a wątkami działającymi w trybie chronionym, dzięki pozostawieniu procesorów dostępnych systemowi operacyjnemu.

Ustawiając wartość opcji priority boost na 1, możemy uruchomić wątki SQL Servera z priorytetem 13 (domyślnym priorytetem jest 7):

 sp_configure 'priority boost', '1' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'priority boost' changed from 0 to 1. Run the RECONFIGURE 􀂪statement to install. 

Ustawiając wartość opcji lightweight pooling na 1, spowodujemy, że zadania wielowątkowe nie będą realizowane poprze kilka wątków, a poprzez uruchomienie w ramach pojedynczego wątku kilku włókien (ang. Fiber). Ponieważ przełączanie pomiędzy włóknami jest szybsze i wymaga mniej zasobów niż przełączanie pomiędzy wątkami, ustawienie tej opcji jest zalecane dla systemów wieloprocesorowych, w których wykorzystanie procesorów utrzymuje się na wysokim poziomie, a liczba przełączeń pomiędzy trybem użytkownika i chronionym jest duża:

 sp_configure 'lightweight pooling', '1' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'lightweight pooling' changed from 0 to 1. Run the RECONFIGURE 􀂪statement to install. 

Tworząc linię bazową dostępnych zasobów komputera, należy dodać do dziennika następujące liczniki związane z procesorem:

1. Procesor\Czas procesora (%) — wartość licznika nie powinna stale przekraczać 90.
2. Procesor\Czas uprzywilejowany (%) — wartość licznika nie powinna stale przekraczać 10.
3. System\Długość kolejki procesora — wartość licznika nie powinna przekraczać 5, przy czym średnia wartość nie powinna przekraczać 3.
4. System\Przełączanie kontekstu/s — przy wartości powyżej 7000 należy przełączyć SQL Server w tryb włókien.

Dysk twardy

SQL Server odczytuje i zapisuje strony i zakresy danych, wywołując podsystem wejścia- -wyjścia systemu operacyjnego. W przypadku systemu Windows 2000 dane zawsze odczytywane i zapisywane są w blokach o wielkości 64 KB, odpowiadających zakresom danych.

Ponieważ wykonanie większości instrukcji języka Transact-SQL wymaga odczytania o wiele większej liczby danych niż liczba danych zwróconych użytkownikowi (np. odczytanie kilku tabel i indeksów), SQL Server stosuje technikę odczytu z wyprzedzeniem (informacje o odczytanych z wyprzedzeniem stronach poznamy, włączając statystyki IO w programie Query Analyzer). Technika ta stosowana jest w trzech przypadkach:

1. Podczas odczytu danych z dużych tabel — menadżer buforów, zamiast odczytywać zapisane w różnych jednostkach alokacji dane sekwencyjne, odczytuje maksymalnie 32 zakresy zapisane w sąsiednich jednostkach alokacji i umieszcza pobrane dane w buforze.
2. Podczas odczytu i przeszukiwania indeksów.
3. Podczas wyszukiwania danych w indeksach niegrupujących.

Strony przechowywane w buforze są okresowo odczytywane przez SQL Server. Ponieważ operacja ta wymaga odczytania danych bezpośrednio z pamięci operacyjnej, nie zwiększa ona obciążenia systemu wejścia-wyjścia. Podczas takiego odczytywania wszystkie strony przechowujące dane niezapisane na dysku twardym oznaczane są jako brudne (ang. dirty). Ponadto sprawdzane jest, ile razy w ciągu określonego czasu wystąpiło żądanie pobrania danych zapisanych na danej stronie. Jeżeli dane nie były pobierane, strona zostaje usunięta z bufora, przy czym strony brudne, przed skasowaniem zostają zapisane na dysku twardym. Niezależnie od procesu skanowania i usuwania stron zapisanych w buforze, podczas wykonywania punktu kontrolnego wszystkie brudne strony zostają zapisane na dysku twardym.

W wersji 2000 konfiguracja systemu wejścia-wyjścia serwera jest w dużym stopniu zautomatyzowana i z poziomu języka Transact-SQL możemy jedynie określić maksymalny czas potrzebny serwerowi do wykonania procesu odtwarzania spójności danych (proces ten uruchamiany jest podczas startu SQL Servera). Zmieniając domyślną wartość (0) opcji recovery interval (min), określimy, ile minut może zająć odtworzenie spójnego obrazu danych, a więc jak często brudne strony muszą zostać zapisane na dysku (domyślna wartość spowoduje dostosowanie wartości parametru do rzeczywistego, okresowo mierzonego obciążenia serwera):

 sp_configure 'recovery interval (min)', '2' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'recovery interval (min)' changed from 0 to 2. Run the 􀂪RECONFIGURE statement to install. 

Zmiana domyślnej wartości może poprawić wydajność wysoce bezawaryjnego, wyposażonego w wydajne zasilacze awaryjne systemu, poprzez zmniejszenie częstotliwości występowania silnie obciążającego serwer (a szczególne układ wejścia-wyjścia) procesu tworzenia punktu kontrolnego.

Tworząc linię bazową dostępnych zasobów komputera, należy dodać do dziennika następujące liczniki związane z dyskiem twardym:

1. Dysk fizyczny\Czas dysku (%) — wartość licznika nie powinna stale przekraczać 85.
2. Dysk fizyczny\Średnia długość kolejki dysku — wartość licznika nie powinna przekraczać 4, przy czym średnia wartość nie powinna przekraczać 2.
3. Dysk fizyczny\Bajty odczytu dysku/s — wartość licznika nie powinna przekraczać 90% maksymalnej, określonej przez producenta urządzenia, liczby odczytywanych bajtów na sekundę.
4. Dysk fizyczny\Bajty zapisu dysku/s — wartość licznika nie powinna przekraczać 90% maksymalnej, określonej przez producenta urządzenia, liczby zapisywanych bajtów na sekundę.

Sieć

W przeciwieństwie do poprzednich zasobów, zasoby sieciowe, które mają bezpośredni wpływ na wydajność systemu bazodanowego, nie są wyłącznie zasobami komputera, na którym uruchomiono SQL Server. Głównymi czynnikami, które wpływają na wydajność sieci komputerowej są:

• Karta sieciowa komputera, na którym uruchomiono SQL Server.
• Umieszczenie komputera w silnie obciążonym segmencie sieci.
• Przepustowość lokalnej sieci komputerowej.
• Liczba danych przesyłanych pomiędzy serwerem a klientami.

Ponieważ z poziomu języka Transact-SQL na żaden z tych czynników nie mamy wpływu, pozostaje nam jedynie (o ile administrator serwera bazowanego nie jest jednocześnie administratorem sieci) monitorować dostępność zasobów sieciowych.

Tworząc linię bazową dostępnych zasobów komputera, do dziennika należy dodać następujące liczniki związane z siecią:

• Interfejs sieciowy\bajty odebrane/s — wartość licznika nie powinna przekraczać 90% maksymalnej, określonej przez producenta urządzenia, liczby odbieranych bajtów na sekundę.
• Interfejs sieciowy\bajty wysłane/s — wartość licznika nie powinna przekraczać 90% maksymalnej, określonej przez producenta urządzenia, liczby wysyłanych bajtów na sekundę.

W ten sposób utworzyliśmy dziennik zawierający 14 liczników. Dziennik ten może zostać wykorzystany do utworzenia linii bazowej — w takim wypadku należy uruchomić go w godzinach dużego i niewielkiego obciążenia systemu (np. o 11 rano i 19 po południu), zapisać wyniki, a następnie okresowo (np. co miesiąc) uruchamiać go w tych godzinach i porównywać otrzymane wyniki z poprzednimi.

Jeżeli wartości liczników nie odpowiadają podanym powyżej, należy rozbudować system komputerowy o brakujący zasób lub zasoby.

System operacyjny

Źle skonfigurowany system operacyjny nawet na najszybszym komputerze będzie działał wolno i niestabilnie. Po upewnieniu się, że powodem niskiej wydajności systemu bazodanowego nie jest po prostu brak pewnych zasobów sprzętowych, należy upewnić się, czy wina nie leży po stronie systemu operacyjnego komputera, na którym uruchomiono SQL Server.

Ponieważ tematem tej książki nie jest konfiguracja i optymalizacja systemów operacyjnych firmy Microsoft, poniżej znajdują się jedynie ogólne wskazówki, które mogą być przydatne administratorom baz danych.

Błędy konfiguracji systemu operacyjnego

Systemy Windows NT\2000 zawierają narzędzie pomagające wykrywać i diagnozować błędy w konfiguracji samego systemu operacyjnego i zainstalowanych programów. Uruchamiając narzędzie Podgląd zdarzeń, mamy możliwość prześledzenia obu interesujących nas w tym momencie dzienników:

• Dziennik systemu zawiera informacje, ostrzeżenia i błędy zgłoszone przez procesy systemu operacyjnego.
• Dziennik aplikacji zawiera analogiczne dane zgłoszone przez procesy klienckie, w tym SQL Server.

Ponieważ systemy NT i 2000 kilkakrotnie próbują uruchomić nieodpowiadające usługi, odnotowane w dzienniku systemu błędy w ich konfiguracji mogą wielokrotnie spowolnić działanie całego systemu. Ta sama uwaga dotyczy większości programów klienckich — chociaż to system zarządza ich dostępem do zasobów, miałem okazję przekonać się, że wydajność specjalistycznego serwera spadła o 90% z powodu uruchamianego programu antywirusowego, który próbował sprawdzić zawartość pozostawionej w napędzie CD „porysowanej” płyty.

Monitorowanie wydajności systemu operacyjnego

Niektóre z liczników umożliwiających monitorowanie wydajności systemu operacyjnego zostały już dodane do dziennika podczas monitorowania zasobów sprzętowych (brak któregoś z wcześniej wymienionych zasobów wpływa nie tylko na obniżenie wydajności SQL Servera, ale także wydajności systemu operacyjnego). Teraz do tej listy dodamy kolejne liczniki:

1. Plik stronicowania\Użycie (%) — jeżeli wartość licznika przekracza 85% zadeklarowanej wielkości pliku stronicowania, należy zwiększyć jego rozmiar początkowy.
2. Plik stronicowania\Użycie szczytowe (%) — jeżeli wartość licznika przekracza 95% zadeklarowanej wielkości pliku stronicowania. należy zwiększyć jego rozmiar początkowy.
   
   Plik pagefile.sys tworzony jest podczas uruchamiania systemu operacyjnego i jeżeli w trakcie jego pracy zostanie zapełniony — zostaje powiększony (domyślna konfiguracja systemu). W rezultacie następuje fragmentowanie tego pliku, co nieskorzystanie wpływa na wydajność systemu operacyjnego. Wielkość początkowa i maksymalna pliku powinny być takie same, wyznaczone na podstawie odczytów dwóch powyższych liczników.
3. Procesor\Przerwania/s — gwałtowny wzrost wartości licznika świadczy o źle skonfigurowanym lub uszkodzonym urządzeniu podłączonym do komputera. Średnia wartość licznika zależy od liczby i rodzaju zainstalowanych urządzeń i dla większości typowych konfiguracji nie powinna przekraczać 400.
4. Dysk fizyczny\Średnia liczba bajtów dysku\Transfer — ponieważ zapis i odczyt dużych bloków danych jest bardziej efektywny, średnia wartość licznika powinna być jak najwyższa (powyżej 50 000). W przeciwnym razie należy określić, która usługa lub aplikacja często zapisują lub odczytują małe ilości danych, a następnie właściwie skonfigurować taką usługę (aplikację).
5. Dysk fizyczny\Transfery dysku/s — o ile wartość maksymalna powinna być jak najwyższa, o tyle wysoka wartość średnia świadczy o ponawianych, z powodu kolejkowania operacji wejścia-wyjścia, próbach pobrania danych z dysku, o jego złej konfiguracji lub uszkodzeniu.
6. Interfejs sieciowy\Długość kolejki wyjściowej — wartość licznika nie powinna przekraczać 5, przy czym średnia wartość nie powinna przekraczać 3.
7. Interfejs sieciowy\Usunięte pakiety wychodzące — wysoka wartość licznika świadczy o osiągnięciu takiego stopnia obciążenia sieci, że poprawne pakiety, pomimo przechowywania ich przez pewien czas w buforze, zostają usunięte z powodu braku dostępu do nośnika.

SQL Server

Kolejnym etapem, po zoptymalizowaniu pracy sytemu operacyjnego, jest optymalizacja pracy SQL Servera. W tym wypadku, oprócz narzędzia Wydajność, można skorzystać z konsoli Enterprise Manager (sekcje Managment \SQL Server Agent\Display Error Log, Managment\SQL Server Logs, Managment\Current Activity), instrukcji języka Transact- -SQL, statystyk zwracanych przez program Query Analyzer oraz narzędzia SQL Profiler.

Aby tworzone linie bazowe zawierały wiarygodne dane, przed ich utworzeniem należy usunąć z bufora strony danych oraz prekompilowane plany wykonania instrukcji języka Transact-SQL:

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. 

Wykorzystanie zasobów sprzętowych przez SQL Server

Domyślna konfiguracja SQL Servera umożliwia mu dynamiczne przydzielanie sobie dostępnych zasobów sprzętowych i w większości przypadków nie ma potrzeby jej zmieniania. Jeżeli jednak konfiguracja systemu nie odpowiada konfiguracji zalecanej przez firmę Microsoft (np. komputer, na którym uruchomiono SQL Server pełni równocześnie funkcję serwera terminali czy kontrolera domeny), zmiana domyślnych ustawień może poprawić wydajność serwera bazodanowego. Stopień wykorzystania przez SQL Server zasobów komputera poznamy, dodając do dziennika następujące liczniki (kolejno zostały przedstawione liczniki związane z dostępną pamięcią i procesorem):

1. SQL Server: Buffer Manager\Total pages — niska, w stosunku do wielkości baz danych, liczba świadczy o braku dostępnej pamięci operacyjnej.
2. SQL Server: Memory Manager: Total Server Memory — jeżeli wartość licznika stale przekracza 90% zainstalowanej w komputerze pamięci operacyjnej, świadczy to o jej braku.
3. Proces\Błędy stron/s\sqlservr — stale utrzymująca się powyżej 0 wartość licznika świadczy o odwoływaniu się przez SQL Server do niedostępnych danych (danych, które nie zostały zapisane w buforze lub zostały zablokowane przez inny proces).
4. Proces\Bajty prywatne\sqlservr — wartość licznika nie powinna spadać poniżej 5 MB.
5. Procesor\Czas użytkownika (%) — stałe utrzymywanie się wysokich wartości licznika może świadczyć o konkurowaniu SQL Servera z innymi procesami użytkownika o dostęp do procesora.
6. Proces\Czas procesora (%)\sqlservr — porównując wskazania tego licznika i poprzedniego możemy sprawdzić, czy to SQL Server wygrywa konkurencję z innymi procesami użytkownika o dostęp do procesora.

Błędy SQL Servera

Tak jak błędy w konfiguracji systemu operacyjnego nieskorzystanie wpływają na wydajność systemu i wszystkich uruchomionych programów, błędy w konfiguracji i działaniu SQL Servera nieskorzystanie wpływają na wydajność systemu bazodanowego.

Z poziomu języka Transact-SQL możemy, wywołując dwie nieudokumentowane procedury systemowe, przeglądać dziennik błędów SQL Servera. Pierwsza procedura, sp_enumerrorlogs, zwraca informacje o istniejących dziennikach błędów (nowy dziennik tworzony jest automatycznie podczas uruchamiania serwera):

 USE master EXEC sp_enumerrorlogs GO Archive # Date Log File Size (Byte) ----------- ------------------- -------------------------- 0 12/15/2002 12:51 2072 1 12/15/2002 10:06 2425 2 12/14/2002 19:17 2294 3 12/14/2002 18:58 2294 4 12/14/2002 18:53 2438 5 12/14/2002 10:54 2274 6 12/14/2002 10:50 2274 (7 row(s) affected) 

Aktualny dziennik ma numer 0, ostatni (przechowywanych jest 7 dzienników) — numer 6. Kolejne uruchomienie SQL Servera spowoduje dodanie do numerów dzienników 1 i usunięcie najstarszego pliku dziennika.

Zawartość wybranego dziennika błędów możemy odczytać, wykonując procedurę sp_readerrorlog:

 USE master EXEC sp_readerrorlog 4 GO ERRORLOG.4 ContinuationRow ----------------------------------------------------------------------------------- --- 2002-12-14 16:30:02.96 server Microsoft SQL Server 2000 - 8.00.100 (Intel X86) 0 Apr 18 2000 01:19:00 0 Copyright (c) 1988-2000 Microsoft Corporation 0 Enterprise Edition on Windows NT 5.0 (Build 2195:) 0 2002-12-14 16:30:03.01 server Copyright (C) 1988-2000 Microsoft Corporation. 0 2002-12-14 16:30:03.01 server All rights reserved. 0 2002-12-14 16:30:03.01 server Server Process ID is 688. 0 2002-12-14 16:30:03.01 server Logging SQL Server messages in file 'D:\Program 􀂪Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 0 2002-12-14 16:30:03.14 server SQL Server is starting at priority class 'normal'(1 CPU 􀂪detected). 0 2002-12-14 16:30:03.78 server Working Set size set to 129664 kilobytes. 0 2002-12-14 16:30:04.00 server User Mode Scheduler configured for fiber processing 0 2002-12-14 16:30:04.04 server Using dynamic lock allocation. [2500] Lock Blocks, 􀂪[5000] Lock Owner Blocks 0 2002-12-14 16:30:04.10 server Attempting to initialize Distributed Transaction 􀂪Coordinator. 0 2002-12-14 16:30:06.50 spid3 Starting up database 'master'. 0 . . . 2002-12-14 18:53:31.48 spid3 SQL Server is terminating due to 'stop' request from 􀂪Service Control Manager. 0 (31 row(s) affected) 

Baza danych

Niektórzy uważają, że optymalizacja (strojenie) bazy danych zaczyna się właśnie na tym etapie — monitorowania i poprawy wydajności pracy konkretnej bazy danych użytkownika. Zakładają tym samym, że komputer posiada niezbędne zasoby sprzętowe, a system operacyjny i serwer bazodanowy są poprawnie skonfigurowane i bezbłędnie działające. Niestety, nie zawsze jest to prawdą. Wynika z tego, że zanim przeprowadzimy optymalizację bazy danych, musimy upewnić się, że żaden z tych trzech elementów nie jest wąskim gardłem systemu — w przeciwnym razie praca poświecona na strojenie bazy danych pójdzie na marne.

Fizyczna struktura bazy danych

Wielkość, uporządkowanie i operacje wykonywane na plikach bazodanowych mają istotny wpływ na wydajność bazy danych. Aby zapewnić maksymalną wydajność. należy:

1. Umieścić pliki bazy danych na odrębnym dysku twardym lub macierzy dyskowej, najlepiej połączonej poprzez własny port wejścia-wyjścia.
2. Umieścić pliki dziennika transakcyjnego na odrębnym, wydajnym dysku twardym lub macierzy dyskowej połączonej poprzez własny port wejścia-wyjścia.
3. Utworzyć kilka plików bazy danych i umieścić je na odrębnych dyskach twardych.
4. Tworząc grupy plików, zapisać na odrębnych dyskach dane tabeli i powiązane z tą tabelą indeksy.
5. Uporządkować fizyczną strukturę plików bazy danych poprzez regularne defragmentowanie dysków.
6. Uporządkować logiczną strukturę plików bazy danych, regularnie wykonując instrukcje: DBCC CHECKALLOC, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CLEANTABLE, DBCC INDEXDEFRAG.
7. Stale monitorować procent wykorzystania plików bazodanowych, w razie potrzeby zwiększając lub zmniejszając ich wielkość.

Podczas instalowania SQL Servera w systemie rejestrowany jest m.in. obiekt narzędzia Wydajność — SQL Server:Databases. Odczytując wartość liczników tego obiektu, poznamy m.in.:

1. Łączną wielkość wszystkich plików danych wybranej bazy (SQL Server:Databases\ Data File(s) Size (KB)\baza_danych).
2. Łączną wielkość wszystkich plików dziennika transakcyjnego wybranej bazy danych (SQL Server:Databases\Log File(s) Size (KB)\baza_danych).
3. Stopień wykorzystania plików dziennika transakcyjnego wybranej bazy danych (SQL Server: Databases\Percent Log Used\baza_danych) — jeżeli wartość licznika przekracza 80, należy zwiększyć rozmiar plików, zmienić model odzyskiwania bazy danych albo zwiększyć częstotliwość wykonywania kopii zapasowej dziennika.
4. Liczbę operacji zwiększania wielkości plików dziennika transakcyjnego wybranej bazy danych (SQL Server: Databases\Log Growths\baza_danych) — jeżeli wartość licznika stale rośnie, należy podjąć kroki zmierzające do ograniczenia liczby kosztownych operacji zwiększania rozmiaru plików.
5. Liczbę operacji zmniejszania rozmiaru plików dziennika transakcyjnego wybranej bazy danych (SQL Server: Databases\Log Shrinks\baza_danych) — jeżeli wartość licznika stale rośnie, należy podjąć kroki zmierzające do ograniczenia liczby kosztownych operacji zmniejszania rozmiaru plików.
6. Liczbę aktywnych transakcji (SQL Server: Databases\Active Transactions\ baza_danych).
7. Liczbę transakcji realizowanych w ciągu sekundy (SQL Server:Databases\ Transactions/sec\baza_danych).

Logiczna struktura bazy danych

Projektując wydajną bazę danych, należy uwzględnić konfigurację systemu, w którym baza będzie działała. Szczegółowe wskazówki dotyczące projektowania bazy danych, tworzenia tabel, tworzenia i roli indeksów (indeksy mają decydujący wpływ na wydajność bazy danych), procedur składowanych, funkcji użytkownika i wyzwalaczy znajdują się w drugiej części książki. Oddzielnym problemem jest zoptymalizowanie bazy danych replikowanej pomiędzy serwerami bazodanowymi (podstawowe informacje o replikacji baz danych znajdują się w rozdziale 21.).

Ponieważ nawet najlepiej zaprojektowana i zaprogramowana baza danych może okazać się niewydajna, należy okresowo sprawdzać wartości liczników:

1. SQL Server: Access Methods\Full Scans/sec — wysoka wartość licznika świadczy albo o braku indeksów, albo o niewłaściwie sformułowanych zapytaniach (zapytaniach, których wykonanie powoduje odczyt całego indeksu). Metody optymalizacji zapytań i zalety wynikające ze stosowania argumentów typu SARG w zapytaniach zostały opisane w rozdziale 4.
2. SQL Server: Access Methods\Page Split/sec — wysoka wartość licznika świadczy o źle dobranym współczynniku wypełnienia indeksów.
3. SQL Server: Locks\Locks Timeouts/sec — wysoka wartość licznika świadczy o źle zaprojektowanych tabelach lub niewłaściwej strategii blokowania dostępu do danych.
4. SQL Server: Locks\Number of Deadlocks/sec — wysoka wartość licznika świadczy o źle zaprojektowanych tabelach lub niewłaściwej strategii blokowania dostępu do danych.
5. SQL Server: SQL Statistics\SQL Compilations/sec — wysoka wartość licznika świadczy o braku procedur lub funkcji systemowych lub o niewłaściwym odwoływaniu się do obiektów (korzyści wynikające ze stosowania pełnych nazw obiektów zostały opisane w rozdziale 12.).

SQL Profiler

SQL Profiler jest instalowanym wraz z SQL Serverem programem monitorującym bieżącą aktywność bazy danych. Za jego pomocą możemy m.in. znaleźć najdłużej wykonywaną instrukcję języka Transact-SQL, poznać liczbę otwieranych kursorów itd. Plik śledzenia (dziennik programu Profiler) możemy również zdefiniować, wywołując procedury systemowe SQL Servera:

1. Aby utworzyć nowy plik śledzenia, należy wywołać procedurę sp_trace_create. Poniżej znajduje się pełna definicja procedury:
   
   

    sp_trace_create [@traceid =] id_pliku OUTPUT , [@options =] opcja , [@tracefile =] 'plik' [, [@maxfilesize =] wielkość] [, [@stoptime =] 'czas_zatrzymania'] 
   

   
   
   gdzie:
   
   opcja może przyjmować wartości: 0 (tworzony jest plik śledzenia), 2 (po osiągnięciu maksymalnej wielkości automatycznie tworzony jest nowy plik dziennika), 4 (po wystąpieniu błędu, który uniemożliwił zapisanie zdarzenia do pliku, SQL Server zostanie zatrzymany) lub 6 (obie opcje zostaną ustawione).
   
   Przykład:
   
   

    DECLARE @tid as INT SET @tid = 51 EXEC sp_trace_create @traceid = @tid OUTPUT, @options = 0, @tracefile = N'D:\logowania' GO The command(s) completed successfully. 
   

2. Dodać do pliku zdarzenie, którego wystąpienie zostanie zanotowane, możemy poprzez wykonanie procedury sp_trace_setevent:
   
   

    sp_trace_setevent [@traceid =] id_pliku , [@eventid =] id_zdarzenia , [@columnid =] id_kolumny , [@on =] aktywne 
   

   
   
   gdzie:
   
   id_pliku określa plik śledzenia (aby można było dodać zdarzenia, śledzenie musi być zatrzymane),
   id_zdarzenia określa obserwowane zdarzenie i może przyjmować wartości z zakresu od 10 do 117. Opis poszczególnych zdarzeń znajduje się w dokumentacji BOL,
   id_kolumny dookreśla obserwowane zdarzenie i może przyjmować wartości z zakresu od 1 do 44. Opis poszczególnych zdarzeń znajduje się w dokumentacji BOL,
   Aktywne określa, czy wystąpienie zdarzenia będzie dodane do pliku (1) czy nie(0).
   
   Na przykład, aby dodać do pliku śledzenia zdarzenia logowania użytkownika i zanotować nazwę użytkownika systemu operacyjnego, który połączył się z SQL Serverem, napiszemy:
   
   

    DECLARE @wl as bit SET @wl = 1 EXEC sp_trace_setevent @traceid = 51, @eventid = 14, @columnid = 6, @on = @wl GO The command(s) completed successfully. 
   

3. Wybrać zdarzenia określonego typu, których wystąpienie będzie lub nie będzie dodane do pliku śledzenia, możemy poprzez procedurę:
   
   

    sp_trace_setfilter [@traceid =] id_pliku , [@columnid =] id_kolumny , [@logical_operator =] operator , [@comparison_operator =] operator_porównania , [@value =] wartość 
   

   
   
   gdzie:
   
   operator określa operator koniunkcji (1) lub alternatywy (0),
   operator_porównania może przyjmować jedną z wartości wymienionych w tabeli 20.1.
   
   

   Tabela 20.1. Dopuszczalne operatory porównania procedury sp_trace_setfilter

   Wartość	Opis
   0	Równy
   1	Różny
   2	Większy niż
   3	Mniejszy niż
   4	Większy lub równy
   5	Mniejszy lub równy
   6	Zgodny ze wzorcem
   7	Niezgodny ze wzorcem

   
   
   Aby ograniczyć zapisywane do pliku śledzenia zdarzenia logowania wyłącznie do logowania administratora systemu, napiszemy:
   
   

    sp_trace_setfilter @traceid = 51 , @columnid = 6 , @logical_operator = 1 , @comparison_operator = 6 , @value = N'Admin%' GO The command(s) completed successfully. 
   

4. Po utworzeniu pliku śledzenia, zdefiniowaniu monitorowanych zdarzeń i ewentualnym dodaniu filtrów możemy uruchomić zapisywanie zdarzeń do pliku, wykonując procedurę sp_trace_setstatus:
   
   

    sp_trace_setstatus [@traceid =] id_pliku , [@status =] status 
   

   
   
   gdzie:
   
   status może przyjmować jedną z trzech wartości: 0 — zatrzymanie śledzenia, 1 — uruchomienie śledzenia, 2 — zatrzymanie śledzenia i usunięcie definicji pliku śledzenia.
   
   Aby uruchomić przykładowy plik śledzenia, napiszmy:
   
   

    sp_trace_setstatus 51,1 GO The command(s) completed successfully. 
   

5. Informacje o wszystkich lub o określonym pliku śledzenia możemy poznać, wywołując funkcję systemową fn_trace_getinfo:
   
   

    SELECT * FROM ::fn_trace_getinfo(default) GO traceid property value ----------- ----------- ---------------------------- 51 1 0 51 2 D:\PerfLogs\logowania 51 3 NULL 51 4 NULL 51 5 0 (5 row(s) affected) 
   

   
   
   gdzie:
   
   1 — zwraca wartość atrybutu @options pliku śledzenia,
   2 — nazwę pliku,
   3 — rozmiar maksymalny,
   4 — czas zatrzymania śledzenia,
   5 — aktualny status śledzenia.

Program kliencki

Cały wysiłek administratora bazy danych poświęcony na optymalizację wydajności bazy danych może pójść na marne, jeśli programista stworzył niewydajny program kliencki. W takim wypadku nawet wart kilkadziesiąt tysięcy serwer skonfigurowany przez najlepszych specjalistów nie poprawi wydajności całego systemu.

Czynnikiem związanym programem klienckim, który ma zasadniczy wpływ na wydajność systemu bazodanowego jest sposób pobierania i przetwarzania danych. Jeżeli dane przetwarzane są wyłącznie po stronie serwera (lub poprzez serwer bazodanowy i dodatkowy serwer, który implementuje logikę biznesową), należy się liczyć z koniecznością przesyłania wielu informacji poprzez sieć (w takim wypadku np. posortowanie dopiero co pobranego zbioru rekordów według wartości innego argumentu spowoduje wysłanie zapytania do serwera, który jeszcze raz wyśle klientowi jego wynik — te same dane, ale inaczej posortowane). Zaletą tego rozwiązania jest wykonywanie operacji na zbiorach poprzez SQL Server, a nie program kliencki — optymalizator SQL Servera wielokrotnie szybciej sortuje czy filtruje zbiory danych niż program odwołujący się do obiektu ADO Recordset. Rozwiązanie to wymaga jednak wydajnego systemu bazodanowego. Jeżeli natomiast przetwarzanie raz pobranych danych odbywa się po stronie klienta, należy bardzo dokładnie rozpatrzyć kwestie związane z blokowaniem dostępu do danych — np. technologia ADO pozwala na porównanie pobranych danych z aktualnymi danymi zapisanymi w bazie przed podjęciem próby zapisania ich w bazie.

Możemy ocenić, czy wąskim gardłem systemu nie jest właśnie program kliencki, odczytując zawartość poniższych liczników:

1. SQL Server:General Statistics\User Connections — jeżeli wydajność systemu bazodanowego spada proporcjonalnie do liczby połączonych klientów, a poprzednio upewniono się, SQL Serwerowi nie brakuje żadnych zasobów systemowych, może świadczyć to o tym, że wina leży właśnie po stronie programu klienckiego.
2. SQL Server:SQL Statistics\Batch Requests/sec — duża liczba aktualizacji wsadowych może być powodem blokowania dostępu do zasobów i spadku wydajności systemu bazodanowego.

SQL Server umożliwia ograniczenie maksymalnego kosztu wykonania zapytania. Ustawiając wartość opcji query governor cost limit, uniemożliwimy klientom wykonywanie zapytań, których czas realizacji przekracza określoną liczbę sekund:

 sp_configure 'query governor cost limit', '30' RECONFIGURE GO DBCC execution completed. If DBCC printed error messages, contact your system 􀂪administrator. Configuration option 'query governor cost limit' changed from 0 to 30. Run the 􀂪RECONFIGURE statement to install. 

Monitorowanie bieżącej aktywności użytkowników

Aby zapewnić spójność danych, SQL Server blokuje dostęp użytkownikom do modyfikowanych w określonym czasie danych. W zależności od zastosowanej metody blokowania dostęp może być ograniczony do pojedynczych pól, wierszy, kolumn lub tabel. Tak czy inaczej, każda, nawet najbardziej liberalna polityka blokowania dostępu do modyfikowania danych może spowodować wystąpienie zakleszczeń oraz blokad kaskadowych.

Zakleszczenie występuje wtedy, gdy użytkownicy po zablokowaniu dostępu do pewnych zasobów, do momentu zakończenia transakcji (a więc zwolnienia zablokowanych zasobów) wymagają uzyskania dostępu do zasobów zablokowanych przez drugiego użytkownika. Taki problem jest automatycznie wykrywany i rozwiązywany przez SQL Server poprzez wycofanie mniej kosztownej transakcji i zatwierdzenie transakcji, która została wybrana na „zwycięzcę”.

Blokada kaskadowa występuje wtedy, gdy zasoby zostały zablokowane przez pierwszego użytkownika, drugi użytkownik, po zablokowaniu innych zasobów, oczekuje na zakończenie transakcji pierwszego użytkownika i dopóki to nie nastąpi, blokuje inne zasoby, trzeci użytkownik do zakończenia transakcji wymaga dostępu do zasobów zablokowanych przez drugiego itd. Ponieważ taki problem nie jest automatycznie rozwiązywany przez SQL Server, administrator powinien stale monitorować aktywność użytkowników.

Procedura sp_who

Procedura zwraca informacje o procesach wszystkich lub wybranych użytkowników.

Składnia:

[@login_name =] 'login'

gdzie:

login określa użytkownika. Użycie słowa Active spowoduje wyświetlenie informacji wyłącznie o aktywnych procesach wszystkich użytkowników.

 sp_who GO spid ecid status loginame hostname blk dbname cmd ------ ------ ------------ ------------------- --------- ---- -------- -------- 􀂪--------- 1 0 background sa 0 NULL LAZY 􀂪WRITER 2 0 sleeping sa 0 NULL LOG 􀂪WRITER 3 0 background sa 0 master 􀂪SIGNAL HANDLER . . . 53 0 sleeping LOLEK\Administrator LOLEK 0 master 􀂪AWAITING COMMAND 54 0 sleeping jacek LOLEK 0 Northwind 􀂪AWAITING COMMAND 55 0 sleeping agatka LOLEK 0 Northwind 􀂪AWAITING COMMAND (15 row(s) affected) Procedura sp_who2 

Nieudokumentowana procedura sp_who2 zwraca dodatkowe informacje o procesach użytkowników, takie jak czas, w którym proces użytkownika miał dostęp do procesora, czas wykonania ostatniej instrukcji i nawa programu klienckiego:

 sp_who2 'active' GO 

Procedura sp_lock

Listę wszystkich blokad lub blokad założonych przez wybranego klienta możemy poznać, wywołując procedurę sp_lock.

Składnia:

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

gdzie:

spid1 jest numerem procesu klienckiego. Numer procesu danego klienta możemy poznać albo odczytując zawartość tabeli master.dbo.sysprocesses, albo wywołując procedurę sp_who.

Poniższy przykład pokazuje sytuację, w której dostęp do wszystkich wierszy tabeli Categories został zablokowany przez użytkownika Agatka (proces o numerze spid = 55) i w rezultacie zapytanie użytkownika Jacek (spid = 54) odwołujące się do tej tabeli oczekuje na zwolnienie zasobów (nazwę obiektu możemy poznać, wywołując funkcję OBJECT_NAME):

 sp_lock GO spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 51 1 85575343 0 TAB IS GRANT 52 6 0 0 DB S GRANT 54 6 0 0 DB S GRANT 54 6 2041058307 1 PAG 1:100 IS GRANT 54 6 2041058307 1 KEY (010086470766) S WAIT 54 6 2041058307 0 TAB IS GRANT 55 6 2041058307 2 KEY (6f02aca3cb50) X GRANT 55 6 2041058307 2 KEY (a001cca9fb2e) X GRANT 55 6 2041058307 2 KEY (f50154104fc9) X GRANT 55 6 2041058307 0 TAB IX GRANT 55 6 2041058307 2 KEY (050255028615) X GRANT 55 6 2041058307 1 KEY (03000d8f0ecc) X GRANT 55 6 2041058307 1 KEY (06003f7fd0fb) X GRANT 55 6 2041058307 1 KEY (0500d1d065e9) X GRANT 55 6 2041058307 2 KEY (d20213c0d76f) X GRANT 55 6 2041058307 1 KEY (010086470766) X GRANT 55 6 2041058307 2 KEY (8f01c7be1f0c) X GRANT 55 6 2041058307 1 KEY (08000c080f1b) X GRANT 55 6 2041058307 1 KEY (0400b4b7d951) X GRANT 55 6 2041058307 1 KEY (020068e8b274) X GRANT 55 6 2041058307 1 KEY (07005a186c43) X GRANT 55 6 2041058307 2 KEY (3002356af06e) X GRANT 55 6 2041058307 1 PAG 1:100 IX GRANT 55 6 2041058307 1 PAG 1:101 IX GRANT 55 6 2041058307 2 KEY (8002f2ea92dc) X GRANT 55 6 0 0 DB S GRANT 

Instrukcja KILL

Określony proces użytkownika możemy zakończyć, wykonując instrukcję KILL.

Składnia:

KILL spid | UOW | UOW WITH {COMMIT | ABORT} [STATUSONLY]

gdzie:

UOW określa transakcję rozproszoną.
COMMIT spowoduje zatwierdzenie transakcji rozpoczętej przez dany proces.
ABORT spowoduje wycofanie transakcji rozpoczętej przez dany proces.
STATUSONLY zwraca informację o liczbie transakcji, które muszą zostać wycofane z powodu zakończenia procesu i może zostać użyty wyłącznie w przypadku zakończenia procesu o statusie ROLLBACK.

Na przykład, aby zakończyć proces Agatki i pozwolić Jackowi na odczytanie danych z tabeli Categories, napiszemy:

 kill 55 GO The command(s) completed successfully. 

Autor: Marcin Szeliga

Spis treści

• Optymalizacja wydajności systemu bazodanowego
• Monitorowanie bieżącej aktywności użytkowników

Podstawą kursu jest wydana przez Helion książka Transact-SQL. Czarna księga.

Wszystkie ćwiczenia przygotowane są dla SQL Server 2000 i korzystają z przykładowych baz danych Northwind lub Pubs.