- 1. Bezpieczny serwer SQL
- 2. Bilans obciążenia podsystemu dyskowego
- 3. Zarządzanie pamięcią
- 4. Oczekiwanie na serwerze
- 5. Budowanie indeksów
- 6. Optymalizuj ciężkie zapytania.
- 7. Ogólna redukcja obciążenia SQL
( SQL ) SQL (2010)
Ta notatka nie udaje pełnego zbadania tematu wydajności serwera SQL, w każdym razie dobrze byłoby przeczytać Liczniki wydajności SQL Server i Windows , Siedem najbardziej przydatnych liczników wydajności , Monitorowanie skuteczności serwera MS SQL. Praktyczne zalecenia , Użyj tabeli sysperfinfo, aby rozwiązać problemy z programem SQL Server . Możesz łatwo znaleźć dziesiątki podobnych artykułów w języku rosyjskim i tysiącach w języku angielskim. W BOL jest wiele przydatnych artykułów - Optymalizuj wydajność bazy danych tempdb
Powiem ci tylko, jak w praktyce wykorzystałem wszystkie te przydatne informacje, aby zoptymalizować kamputer za pomocą serwera SQL obsługującego portal, który odwiedza ćwierć miliona odwiedzających dziennie (prawdopodobnie następnym razem będzie jeszcze dwa razy). Ten obozowicz Opiera się na piekielnym kontrolerze SAS, który obsługuje ponad 10 skrzynek pełnych dysków. Każdy jest postrzegany jako jeden dysk SCSI. Oprócz kontrolera SAS, camputer ma 16 GB pamięci, dwa czterordzeniowe xeona i kilka zwykłych dysków.
1. Bezpieczny serwer SQL
Pierwszym zadaniem, które rozwiązałem, jest ochrona tego kampera przed nieautoryzowanym dostępem z sieci. Zrobiłem to w zwykły sposób - zapora sprzętowa była zapchana w tym campoo.
2. Bilans obciążenia podsystemu dyskowego
Zapewniłem równoważenie obciążenia kontrolera SAS, dzieląc wszystkie główne bazy - opisałem tę technologię na moim chomiku - Krojenie grafiki w pamięci SQL. . W rezultacie każda poważna podstawa moich kłamstw opiera się na zestawie dysków:
Ponadto wszystkie bazy danych są oddzielone różnymi dyskami, kopiami zapasowymi, indeksami i tak dalej. Bazy z rekordową pracą nawet nie w Simple, ale w BulkLogged. W rezultacie głównym dyskiem uruchamianym podczas uruchamiania serwera SQL jest dysk, na którym znajduje się TempDB. To on drży na tym klipie wideo - gdy rozmiar kolejki dysków dramatycznie wzrasta.
Oczywiste jest, że na takim załadowanym dysku w ogóle nic nie powinno być umieszczane. Zasadniczo MS zaleca utworzenie jednego pliku TempDB dla każdego procesora na serwer. A ponieważ dysk tempdb jest jedynym prawdziwym dyskiem, jaki mam, utworzę tempdb na 8 dyskach.
3. Zarządzanie pamięcią
Ustawiłem serwer SQL na tryb AWE, mimo że mam wszystkie bity 64-bitowe - zarówno Windows, jak i serwer. Pierwsze pytanie, w którym chciałem się upewnić, że serwer SQL ma wystarczającą ilość pamięci RAM. Niestety, wadliwy mikrasoft nie zdołał nawet zapewnić, że Menedżer zadań Windows działał normalnie, co powodowało Ctrl-Alt-Del. Niestety, pokazuje całkowicie nieadekwatne liczby.
Istnieje również druga wersja tych samych cyfr, które pokazuje select * z master.dbo.sysperfinfo.
Istnieje trzeci wariant tych samych liczb - liczniki Perfomance. Wszystkie liczby są różne. Nie jest jasne, w jakie liczby wierzyć, ale wydaje się, że pracownicy Microsoftu twierdzą, że okazali się mniej wadliwi niż inni:
Jeśli wierzysz w liczniki wydajności, możesz zobaczyć, że mam 14 GB dostępnej pamięci (co jest logiczne w ogólności), ale chciałem, aby zabrał ich 5,2 GB. Jest to wyraźna nieadekwatność algorytmu microsoft - aby móc używać pamięci RAM z pamięcią podręczną danych, ale nie używać pamięci RAM. I stale pompuj dane z dysków. Orakla ma wersję serwera SQL, która generalnie nie może wyciągać dysków - po prostu odczytuje wszystkie dane w pamięci RAM. Oczywiście w tym przypadku nie ma potrzeby indeksowania, wypełniania, dzielenia danych i innych sztuczek - dostęp do pamięci RAM jest natychmiastowy, co najmniej milion razy szybszy niż dostęp do dysków (nawet przykręcony do kontrolera SAS).
Poza tym niezrozumiałym ruchem z dyskami, jestem także zdezorientowany algorytmem microsoft przez obecność ogromnych wartości PageFault - ten licznik oznacza, że wymagana strona danych nie została znaleziona w pamięci RAM. Ale ogólnie rzecz biorąc, ponieważ byłem przekonany, że mam znacznie więcej pamięci niż serwer SQL chciałby zająć, uznałem ten etap optymalizacji za kompletny.
4. Oczekiwanie na serwerze
Oprócz pamięci zazwyczaj analizuje się, czego oczekuje serwer. W moim przypadku widać, że serwer nie ma szczególnych wąskich gardeł. Ale trzeba było się upewnić, przynajmniej w celu przejścia do następnego etapu optymalizacji - budynek indeksowy . W końcu trasa musi być zapisana w sieci (na inny SQL) - tak, aby nie wpływać na zoptymalizowany SQL. Aby to zrobić, musisz przynajmniej upewnić się, że sieć nie jest wąskim gardłem serwera.
5. Budowanie indeksów
Kluczem do efektywnego działania SQL są normalne indeksy. Pozwalają na natychmiastowe odwoływanie się do żądanego rekordu, a nie poprzez iterowanie rekordów na dysku. SQL 2005. Egzamin 70-431. Strona 121. „Aby znaleźć wiersz w tabeli zawierającej 2,5 miliona wierszy, SQL Server musi spojrzeć na wszystkie trzy strony danych. Tylko wtedy, gdy w tabeli jest ponad 300 milionów wierszy, SQL Server będzie musiał przeglądać cztery strony, aby znaleźć właściwy wiersz.” Dlatego konieczne jest rozpoczęcie od wszystkich zaleceń Tuning Advisor'a.
Aby osiągnąć efektywne działanie indeksów, najpierw musimy poprawnie zaprojektować bazę. Ogólnie rzecz biorąc, maksymalna liczba relacji odniesienia, która pozwala optymalizatorowi natychmiast trzymać się żądanych powiązanych danych. W przypadku witryny działającej na tym serwerze - zrobiłem kilkanaście takie schematy.
Indeksy należy również pamiętać na etapie programowania SqL. Na przykład podczas pisania indeksów spowolnij pracę (muszą zostać odbudowane). Dlatego w czasie masowych nagrań w kodzie procedury muszą być wyłączone. I tabla, w której przechowywane są statystyki portalu - nie ma w ogóle żadnych indeksów, ponieważ rekord zawsze tam trafia, a administrator raz w miesiącu wysyła żądanie. Istnieje jednak wiele pytań dotyczących wydajnego programowania SQL, a nie tylko indeksów - na przykład wskazówek WITH (NOLOCK) - im więcej, tym szybciej i szybciej wszystko działa.
Wskaźniki potrzebują i działają poprawnie. O aktualizowaniu statystyk indeksów - możesz przeczytać tutaj. Używanie statystyk przez optymalizator zapytań Microsoft SQL Server 2005
6. Optymalizuj ciężkie zapytania.
Aby zoptymalizować pojedyncze ciężkie zapytania, możesz użyć wbudowanego MS. Ale na przykład istnieją osobne programy atskie Strzał SQL . Ale żądania, które nie mogą zostać zoptymalizowane, powiedzmy do kilku sekund - muszą zostać całkowicie zrezygnowane. Jak to zrobić?
Przechowuję tak duże żądania na najwyższym poziomie aplikacji. Aby to zrobić, wszystkie ciężkie selekcje są po prostu uruchamiane w zadaniu, a użytkownik z witryny otrzymuje po prostu niezbyt trafny, ale natychmiastowy wynik. Tak więc w moich projektach wszystkie ciężkie żądania są wyświetlane w zadaniach i nie wpływają na reaktywność portalu.
Oto przykład. Na montażu Zespół do pracy ze standardowymi profilami ASP.NET na poziomie SQL wykonane takie procedura, która wezwany do zadania . W ten sposób użytkownik witryny natychmiast otrzymuje wynik żądania, które uważa się za godziny.
7. Ogólna redukcja obciążenia SQL
Oto niektóre z moich ulubionych metod:
1. Wykonywanie wszystkich żądań w Application_start i Session_start. Na przykład tutaj fragment global.asax . Jak widać, wprowadziłem niezbędne zestawy wiele razy na początku sesji. I na stronach po prostu dostaję się z pamięci bez odwracania do SQL.
2. Kiedy bardzo żądania kursora RS.Movenext Ma sens pobranie całego zestawu rekordów jednocześnie z FOR XML. Produktywność nie zwiększy się tutaj w każdej sytuacji, ale może to być również opcja zmniejszenia obciążenia SQL.
3. Inną metodą zmniejszenia obciążenia jest wygenerowanie gotowych fragmentów html z comboboxami przy użyciu zadania SQL i dołączenie tego gotowego fragmentu do dyrektywy Asp <! - # include (lub literal).
4. Przeniesienie wszystkich typowych zadań witryny ( Wykonuj okresowe zadania w ASP.NET ) w nocy.
Po dostrojeniu wydajności serwera SQL, zapomnij o dostosowaniu kopia zapasowa danych .