SQL Server Administration: Przekręcony licznik


Tytuł mógłby sugerować problem motoryzacyjny typu: podejrzanie niski przebieg w starym aucie. Jednak będzie on poświęcony bazom danych i pewnej specyficznej cesze kolumn. Mowa o właściwości IDENTITY, która pozwala nam zwiększać (lub zmniejszać) – o ustaloną stałą wielkość – wartość każdego nowego wiersza w tabeli. Używa się jej często razem z ograniczeniem PRIMARY KEY w celu stworzenia efektywnego surogatu klucza głównego.                

Oto fragment kodu definiującego kolumnę z właściwością IDENTITY:

Składnia jest bardzo prosta: pierwszy argument to ziarno, czyli ustalenie, z jakiego pułapu zaczynamy, natomiast poprzez drugi argument definiujemy przyrost. Wszystkim programiści znają właściwość  T-SQL’a, więc nie trzeba tego tutaj tłumaczyć. Ważniejsze jest to, co się może zdarzyć chwilę później:

Samochód pojedzie dalej, ale przypadku MS SQL’a już tak dobrze nie będzie. Popatrzmy na przykładową symulację:

Inaczej mówiąc, nigdzie dalej nie pojedziemy. Jeden rekord udało na się dodać, ponieważ poleceniem CHECKIDENT ustawiliśmy nowy, maksymalny seed dla kolumny ID. Zatem ten się jeszcze zmieścił:

Ale następne dostały już komunikat Arithmetic overflow occurred.

Pół biedy, jeżeli dotyczy to tabeli z logami np. błędów. Wystarczy wtedy zrobić TRUNCATE tabeli, a licznik sam się zresetuje. Gorzej jest, gdy wygenerowanego ID używamy w innych tabelach, budując na jego podstawie jakieś relacje. Typ INT jesteśmy jeszcze wstanie rozszerzyć na BIGINT, ale potem już jest trudniej, bo trzeba kombinować z typem DECIMAL/NUMERIC.

W każdym razie dobrze jest wcześniej wiedzieć, kiedy taka sytuacja może nastąpić, bo ewentualną zmianę trzeba zaplanować. A co, jeśli taki błąd zdarzy się w weekend, poza oknem serwisowym? Do poniedziałkowego poranka może już być niezły bałagan…

Zacznijmy zatem od sprawdzenia, jakie w ogóle typy danych mogą mieć właściwość Identity.

Typy danych dla właściwości Identity

Najprościej będzie sprowokować SQL’a do tego, by sam nam podpowiedział, dla jakiej kolumny możemy ustawić tę właściwość:

Komunikat błędu jest zatem jasny i nie pozostawia wątpliwości. W poniższej tabeli zebrałem te informacje.

Nazwy typów Decimal i Numeric są synonimami, więc umieściłem je w jednym wierszu. Komunikat dodatkowo wskazuje na to, że ten typ danych koniecznie musi mieć skalę ustawioną na zero. Natomiast wszystkie kolumny z właściwością IDENTITY nie mogą być szyfrowane i muszą być ograniczone do NOT NULL. Trudność z typem danych Decimal i Numeric polega również na tym, że w tabelce przedstawiłem maksymalną precyzję typu. Jednak nic nie stoi na przeszkodzie, by założyć sobie np. kolumnę z precyzją NUMERIC(1,0) dla którego minimum wyniesie -9, a maximum 9. Co prawda nie spotkałem się nigdy z sytuacją, by w ogóle ktoś ustawiał właściwość IDENTITY na typie Decimal i Numeric, ale potrafię sobie wyobrazić taką potrzebę. Poza tym taka hipotetyczna możliwość istnieje, trzeba więc być przygotowanym na wszystko…

Zatem do dzieła!

Nie wywarzamy otwartych drzwi

Domyślałem się, że prawdopodobnie już wcześniej ktoś ten problem analizował i mógł znaleźć dobre rozwiązanie. Dosyć szybko trafiłem na stronę z całkiem sprytnym kodem przeczesującym bazę pod kątem właściwości IDENTITY (by Frank Kalis):

Działa świetnie, przeszukuje główne typy użyte w Identity, pokazuje nawet procentowe wypełnienie zakresu. A czego nam brakuje?

Na pewno typu DECIMAL/NUMERIC. Ponadto, chciałbym zrobić automat, który zaszyty w jobie okresowo sprawdzałby właśnie procentowe wypełnienie zakresu, ale dodatkowo uwzględniałby też dynamikę przyrostu. To znaczy, że poza alarmem po przekroczeniu np. 90% zakresu danego typu, chciałbym wiedzieć, kiedy ten zakres się skończy w przyszłości. Alarm zatem uruchamiałby się np. na trzy miesiące przed tą datą, niezależnie od obecnego wypełnienia zakresu. No i ostania sprawa – chciałbym być informowany w postaci raportu e-mail tylko wtedy, jeżeli założone progi będą przekroczone. Generalnie im mniej mejli, tym jesteś zdrowszy i mniej zobojętniały. J W pierwszej kolejności jednak dodajmy typ DECIMAL/NUMERIC.

Tabela zakresów

W związku z tym, że chcemy badać dynamikę przyrostu, musimy gdzieś nasze dane przechować, by móc je porównać i użyć do obliczeń. Na każdej monitorowanej instancji mam taką bazę (przyjmijmy, że nazywa się _SQL_), gdzie w dedykowanych schematach trzymam pożyteczne dane związane z administracją danej instancji. W tej bazie, w schemacie RANGE, założę właśnie tabelę zakresów. Zakładam stałą tabelę, ponieważ nie chcę za każdym razem tworzyć CTE dla różnych wariacji typu DECIMAL/NUMERIC. Skończona procedura powinna dawać wybór lokalizacji, w której będziemy chcieli założyć te tabele, ponieważ niektórzy administratorzy mogą być zmuszeni do trzymania takich danych w bazie tempdb. Z oczywistych powodów ta wersja nie będzie poprawnie analizować dynamiki przyrostu identyfikatorów zaraz po restarcie instancji, ale zakładając, że badamy tę dynamikę codziennie, można na to przymknąć oko.

Dodatkowo wprowadziłem kilka zmian w stosunku do oryginalnego kodu. Między innymi po to, aby rozróżnić różne precyzje w typie DECIMAL/NUMERIC, dodałem odpowiednie złączenie w kodzie, dołożyłem kolumnę Precision w tabelce TypeRange oraz poprawiłem obliczenia procentowego wypełnienia typu dla ujemnych przyrostów (funkcja ABS).

Kod tabeli zakresów wygląda następująco:

Na początku uzupełniamy tabelę o standardowe typy danych:

Natomiast wypełnienie typem DECIMAL/NUMERIC wykonujemy dynamicznym kodem, eliminując mnóstwo powtórzeń:

W efekcie nasza tabela słownikowa powinna wyglądać następująco:

… i tak aż do precyzji 38, czyli w sumie 80 wierszy w tabeli.

Tabela IdentityCheck

          Jak wspomniałem wcześniej, zmiany w stosunku do oryginalnego kodu zawierają się w dodaniu pola precyzji, uwzględnieniu tego w złączeniu oraz usunięcia z kodu CTE TypeRange i zastąpieniu go trwałą strukturą (lub tabelką w tempdb). A także małymi poprawkami dotyczącymi obliczeń procentowego wypełnienia typu dla właściwości IDENTITY z ujemnym przyrostem.

Wynik procedury będziemy przechowywać w tabelce IdentityCheck:

Po co nam ta tabelka? Otóż mając zrzut z ostatniego sprawdzania wypełnienia wartościami kolumn z własnością IDENTITY, będziemy mogli obliczyć, kiedy orientacyjnie zakres się skończy. Ponadto, aby jednoznacznie zidentyfikować kolumnę w tabelce poza opisem typów, będzie potrzebna nazwa bazy, schematu oraz tabeli, razem z datą wykonanego zrzutu. Jeszcze kolumna snap_id, która będzie identyfikatorem kolejnego zrzutu danych. Dzięki niej łatwiej będzie znaleźć właściwe, następujące po sobie, zestawy danych do porównania. A na samym końcu znajdziemy kolumnę z datą przewidywanego wypełnienia się zakresu.

Algorytm wykrywający wypełnienie zakresu jest bardzo prosty. Sprawdzamy czas w godzinach, jaki upłynął między kolejnymi punktami kontrolnymi, oraz różnicę IDENTITY na wskazanej kolumnie. Dzieląc tę różnicę przez ilość godzin, mamy szacunkowy przyrost danych na godzinę. Im więcej czasu upłynęło między tymi punktami, tym ten przyrost jest precyzyjniejszy, ponieważ w ciągu dnia mogą być okresy wzmożonej pracy, ale również przestojów (np. w nocy). Moim zdaniem doba to najkrótszy okres, który powinno się brać pod uwagę.

Mając zatem godzinny przyrost wartości na kolumnie IDENTITY, możemy łatwo obliczyć, kiedy zakres zostanie wypełniony. Musimy tylko uwzględnić kilka wyjątków, które przytaczam poniżej.

  1. Jeżeli różnica jest mniejsza niż jedna godzina pomiędzy kolejnymi punktami, zakładamy, że obliczenia będą nieprecyzyjne i ustawiamy maksymalną datę dla typu danych datetime2.
  2. Jeżeli zakres dat jest większy niż  jedna godzina, ale obliczona wartość przekracza maksymalną datę dla typu danych datetime2 (różnica godzin jest większa niż 60 mln.), to także ustawiamy ‚9999-12-31 23:59:59.9999999’.
  3. Jeżeli przyrost w polu IDENTITY wynosi 0, również zakładamy maksymalną wartość w polu expected_date_of_filling.

Raportowanie

Załóżmy, że tabelkę IdentityCheck mamy już wypełnioną. Chcielibyśmy wiedzieć bez zaglądania do niej, że na przykład: zakres na kolumnie IDENTITY został już wypełniony w ponad 90% albo spodziewane wypełnienie nastąpi za trzy miesiące.

Nic prostszego – zaczynamy od zbudowania odpowiedniego zapytania wyciągającego wskazany zakres danych:

Jeżeli znajdą się dane spełniające powyższe warunki, to za pomocą trybu RAW z FOR XML budujemy strukturę html, którą wyślemy pocztą elektroniczną (wcześniej rekordy spełniające warunki zostały przepisane do globalnej tabeli tymczasowej ##IdentityCheckReport – globalnej, ponieważ docelowo kod procedury będzie dynamicznie składany):

Wystarczy teraz zmienną @html_body wstawić do procedury sp_send_dbmail i wysłać do wskazanych odbiorców. Przykładowy raport powinien wyglądać tak jak poniżej:

Oczywiście, zawsze możemy podłączyć się przez SSMS i ręcznie sprawdzić, jaki jest stan naszych kolumn z własnością IDENTITY.

Procedura usp_CheckIdentityValue

Teraz stworzymy procedurę, która jako parametry będzie przyjmowała nazwę bazy i schemat, w których przechowamy tabelę zakresów i IdentityCheck. Domyślnie to baza tempdb i schemat identity. Kolejne dwa parametry to poziom wypełnienia wartości identity (domyślnie 95%) oraz czas w miesiącach (domyślnie trzy miesiące) do 100% wypełnienia zakresu, po przekroczeniu których wyjdzie e-mail jak na powyższym zrzucie (rozdział Raportowanie). Ostatnie dwa parametry to profil dbmail, przez który wysyłamy tę wiadomość oraz lista odbiorców raportu. Niepodanie tych parametrów spowoduje tylko sprawdzenie wszystkich kolumn bez wysyłki raportu.

Przykład nr 1:

Zakładamy, że mamy założony profil DBMail o nazwie mail_profile i użytkownik miros@poczta.fm otrzyma raport, jeżeli przynajmniej jedna z kolumn z własnością IDENTITY będzie wypełniona w 95% lub do 100%, wypełnienia zostało mniej niż trzy miesiące. Dane obu tabel będą przechowywane w bazie tempdb.

Przykład nr 2:

Użytkownik miros@poczta.fm otrzyma raport, jeżeli przynajmniej jedna z kolumn z własnością IDENTITY będzie wypełniona już w 80% lub do 100% wypełnienia zostało mniej niż pół roku. Natomiast jako miejsce do składowania danych została wybrana baza MyDB i schemat RANGE (jeżeli nie ma takiego schematu to zostanie założony).

Przykład nr 3 (wywołanie bez parametrów):

Raport nie zostanie wysłany, natomiast stan kolumn będzie można podejrzeć bezpośrednio zapytaniem:

Przykładowe wyniki z systemu produkcyjnego:

Ciało reszty procedury to dynamicznie składany kod realizujący opisane w tym artykule zadania. Cały kod można podejrzeć na githubie:

https://github.com/BruceSpruce/IdentityCheck/blob/master/Identity_RANGE.sql

Automatyzacja

Jak wspomniałem wcześniej, administrator potrzebuje narzędzi, które będą działały w tle i tylko, jeżeli coś jest nie tak, ma być o tym informowany. Dlatego zamiast ręcznego uruchamiania procedury na naszych systemach powinniśmy zaprząc do pracy SQL Agenta, który maksymalnie raz na dobę przejrzy i przeliczy kolumny IDENTITY, a w razie potrzeby wyśle odpowiedni raport.

Pod poniższym adresem znajdziemy przygotowany skrypt zakładający wspomnianego joba:

https://github.com/BruceSpruce/IdentityCheck/blob/master/Identity_RANGE_CREATE_JOB.sql                

Zmieniamy tylko parametry konfiguracji joba, takie jak właściciel joba i nazwę operatora (dla ewentualnych notyfikacji dotyczących błędów w procedurze):

Parametry dotyczące procedury zmieniamy w zakresie profilu mailowego w Database Mail, adresu email, na który będą wysyłane raporty, oraz docelowej bazy dla naszych dwóch pomocniczych tabel. Reszta może pozostać bez zmian.

Uprawnienia

Dobrze jest wiedzieć, jakich uprawnień potrzebujemy, aby zainstalować i uruchomić naszą „sprawdzajkę”. Do instalacji tej procedury wymagane są uprawnienia ALTER na schemacie dbo. Aby ją uruchomić, musimy mieć uprawnienia EXECUTE, uprawnienia do tworzenia schematu w bazie docelowej oraz musimy być podłączeni do roli public na każdej ze sprawdzanych baz i dla każdej sprawdzanej tabeli musimy mieć uprawnienia SELECT. Gdy chcemy nadać uprawnienia dla wszystkich tabel w bazie, należy użytkownikowi nadać rolę db_datareader. To wystarczy, by wykonać przebieg po kolumnach z własnością IDENTITY. Jednak aby wygenerować raport, musimy jeszcze wskazanemu użytkownikowi pozwolić go wysłać, nadając uprawnienia access do profilu w Database Mail.

Czy ta procedura się przyda?

Powiem tak: przed publikacją zainstalowałem ją na kilkunastu testowych systemach i w ciągu kilku miesięcy testów otrzymałem tylko jedno powiadomienie o zbliżającym się końcu zakresu dla typu danych INT (w ciągu najbliższych trzech miesięcy). Czyli szału nie ma. Ale też nie ma co się oszukiwać: prawdopodobieństwo wyczerpania wolnych identyfikatorów jest naprawdę bardzo małe, jednak rośnie ono z czasem i dla produkcyjnych instancji może być bardzo bolesne, gdy „dobrze” trafi w czasie i przestrzeni…

Ryzyko możemy zniwelować prawie do zera, instalując opisaną procedurę, o której z biegiem czasu zapomnimy, aż któregoś pięknego dnia dostaniemy raport, że w naszym głównym systemie, za jakieś trzy miesiące skończą się identyfikatory polis, numerów umów lub innych ważnych dokumentów. I wtedy dotrze do nas, że przecież mogliśmy się o tym dowiedzieć po fakcie, za te trzy miesiące, w godzinach największego przerobu, na kilka godzin przed zamknięciem miesiąca…

Strona projektu:

https://github.com/BruceSpruce/IdentityCheck

Linki:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

https://msdn.microsoft.com/pl-pl/library/ms186775%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *