Optymalizacja bazy SQL Server

0

Witam
Mój zespół będzie przejmować projekt - system, który posiada dość dużą bazę ~szacunkowy rozmiar 200 GB. System korzystający z bazy danych operując na niej działa niezwykle wolno. Moim wstępnym pomysłem jest usprawnienie działania oprogramowania. Oprogramowanie wykorzystuje procedury do każdego zapytania. Czyli z poziomu oprogramowania wywoływane są procedury, które zawierają poszczególne zapytania do bazy - nie wiem na ile to jest dobre rozwiązanie, ale tak przyjęto x lat temu i tego się trzymano.

Z uwagi na to, że nie mam doświadczenia w pracy z takimi bazami szukam punktów zaczepienia, zależy mi na poprawie wydajności. System jest scentralizowany, myślałem wstępnie nad może rozproszeniem zastosowanie fragmentacji poziomej, pionowej. Jednak trzeba było to jakoś mądrze rozłożyć. Dwa moja wiedza na temat rozproszonych baz danych jest akademicka znam pojęcia, robiłem proste przykłady dla siebie, ale nie miałem przyjemności utrzymywać komercyjnego systemu tego typu.

Z drugiej strony baza będzie cały czas puchła.

Ogólnie fajnie jakby ktoś podzielił się doświadczeniami w tym zakresie. Bo na tym mi zależy.

Pozdrawiam

1

Zależy do czego ta baza jest używana i ilu ma użytkowników.
Jeżeli chodzi o optymalizację to nie ma wyjścia jak wyszukać wąskie gardła i optymalizować zapytania. Jeżeli konstrukcja aplikacji jest zwalona to i tam wypadałoby poprawiać.
Plus taki że zapytania są w procedurach.

2

Odpal aplikację z profilerem, porób coś, zobacz, co zajmuje najwięcej czasu. Jeśli to wywołania procedur, to zidentyfikuj te procedury, a potem wyznacz plany wykonania dla nich.

0

System jest scentralizowany, myślałem wstępnie nad może rozproszeniem zastosowanie fragmentacji poziomej, pionowej

Zanim to zrobisz, najpierw trzeba poszukać tzw. "low hanging fruit". Parę pomysłów:

  • Somekind dobrze radzi - wrzuć to pod profiler, zobacz, które zapytania są powolne, może trzeba dodać jakieś indeksy.
  • Sprawdź, czy statystyki dotyczące danych są aktualne. Nie wiem jak to się robi w MS SQL, ale niemal każdyzaawansowany RDBMS ma jakieś polecenie do przeliczenia statystyk używanych w optymalizacji zapytań - niby baza powinna odświeżać je przyrostowo, ale algorytmy przyrostowe zwykle nie są zbyt dokładne i po jakimś czasie warto przeliczyć od zera i zweryfikować czy optymalizator bazuje na sesnownych założeniach.
  • Sprawdź plany powolnych zapytań i zobacz, czy optymalizator nie robi czegoś głupiego. Na pewno masz tam jakiś EXPLAIN czy coś takiego. Czasami nawet w najlepszych systemach baz danych optymalizator potrafi się mocno pomylić i trzeba go "naprowadzić" - np. upraszczając zapytanie.
  • Sprawdź, czy nie masz problemów z fragmentacją i nieusuniętymi danymi. Nie jestem od tego ekspertem w MSQL, ale tu jest fajny filmik, który wyjaśnia, co i jak, i dlaczego framgentacja ma kluczowe znaczenie dla zdrowia bazy, zwłaszcza fragmentacja wewnętrzna: . W innych bazach jest coś takiego jak REORGANIZE / VACUUM / COMPACT, które w niektórych patologicznych przypadkach potrafi podnieść wydajność o dwa rzędy wielkości (miałem taki przypadek w PostgreSQL, jeszcze zanim wprowadzili auto-vacuum).

W drugiej kolejności możesz próbować skalować pionowo, czyli wzmocnić maszynę, na której to stoi. Oczywiście po zdiagnozowaniu wąskiego gardła. Jeśli problemem są odczyty, może pomóc dorzucenie więcej RAMu, aby cache działał wydajniej. Może też pomóc zmiana HDD na SSD SLC (niestety będzie kosztowne, bo zwykłe konsumenkie tanie pamięci SSD MLC się do takich zastosowań nie nadają).

W trzeciej kolejności możesz próbować skalować poziomo, czyli popartycjonować i rozproszyć na wiele maszy.... Zaraz, o czym ja gadam. To jest MSSQL. To się niestety nie skaluje poziomo. :(

http://dba.stackexchange.com/questions/10776/how-does-one-scale-sql-server-2008-or-2012

Najlepsza jest odpowiedź "Like gbn says, SQL doesn't really scale out like other RDBMs's do." Tak jakby inne RDMSy się skalowały... :D :D :D

0

@mariano901229 ja od maja pracuję w projekcie, który oparty jest o MSSQL i tak jak u ciebie zapytania są w procedurach. Najpierw tak jak pisali poprzednicy sqlprofilerem zbadałem co dokładnie powoduje opóźnienia. W kolejnych krokach okazało się, że wyrzucenie niektórych zapytań z procedur do np widoku rozwiązywało problem. Niestety część rzeczy musiałem przeprojektować gdyż sam projekt zawierał błędy i nie było innej opcji jak tylko zaoranie mechanizmu i stworzenie go od początku. Istotnym problemem były też raporty. Gość, który się tym wcześniej zajmował nie znał chyba funkcji analitycznych i używał wbudowanych mechanizmów po stronie interfejsu użytkownika w efekcie był robiony select np z zakresu dat cały rok i aby wypluć podsumowanie rekordy były zliczane i sumowane w UI. Tego typu rzeczy też znacznie spowalniały całość procesu więc nie ma jednej odpowiedzi co zrobić. Trzeba siedzieć sprawdzać i optymalizować one by one.

2

Zacznij od zdefiniowania potrzeb.
Jakie są oczekiwania klienta?
Co jest za wolne? O ile ma przyspieszyć? Czy przyspieszenie o 3s wystarczy? A może o 30%? A może 10x?
Jak szybko zwalnia baza (czy stopniowo w ciągu 10 lat, czy dramat w ciągu miesiąca).
Jak szybko przyrasta baza.
Czy są "piki" - okresowe szczytowe obciążenia systemu - w ciągu doby lub miesiąca.
Ile jest aplikacji korzystających z bazy.
Jak wygląda sesja użytkownika - ta pozytywna i ta za wolna - pod względem dokładnej listy wykonywanych zapytań (lub SP).

Potem dodaj na stałe logowanie zapytań trwających >= t [ms], np. http://blog.brianhartsock.com/2008/12/16/quick-and-dirty-sql-server-slow-query-log/

Potem masz kilka opcji:

  • najprostsza: poszukać zapytań które można przerobić bez zmiany API procedur oraz struktury bazy i poprawić te procedury
  • trudniejsza: poszukać zapytań, dla których warto zrobić zmiany struktury bazy nie pociągających za sobą zmiany API procedur
  • potem masz zmiany które pociągną za sobą zmiany API (dodatkowe parametry, wiele rekordów zamiast 1, statystyki w SQL zamiast UI...)
  • potem masz zmiany architektury bazy (sharding, część na NoSQL)

Jeśli to i tak będzie za mało:

  • ulepszasz sprzęt (SSD, więcej dysków, więcej RAM)
  • zatrudniasz DBA-ja (na stałe lub z określonym założonym celem)
0

odnośnie optymalizacji kodu SQL polecam: optymalizacjasql.blogspot.com

1 użytkowników online, w tym zalogowanych: 0, gości: 1