10 złych praktyk i niebezpiecznych nawyków w SQL Server

W swojej karierze miałem okazję oglądać już całkiem dużą liczbę mniejszych lub większych implementacji z wykorzystaniem SQL Server. Zdarza mi się również oglądać rozwiązania pisane według myśli technicznej innych deweloperów. Bardzo często spotykam się z kodem TSQL, który jest nie tylko bardzo dobrze napisany pod kątem składni, ale również poukładany według pewnych konwencji nazewniczych. Niestety często oglądam również małe “potworki” gdzie deweloperzy nie trzymają się absolutnie żadnych dobrych praktyk, a to co mieli na myśli pisząc taki, a nie inny kod jest zagadką tak ciężką do rozwiązania, że same próby przyprawiają o ból głowy. Dlatego też postanowiłem, że kilka pomniejszych złych nawyków i złych praktyk które nie są zrozumiane opiszę w ramach artykułu. Od razu ostrzegam, że niniejsza lista jest mocno subiektywna i z całą pewnością można by ją rozszerzyć (być może nawet to zrobię – zobaczymy). W tym artykule pomagać będą nam Andrzej oraz Janek czyli nietypowi deweloperzy z ciągotami administracyjnymi (zbieżność imion przypadkowa). Zapraszam Was serdecznie do lektury!

Błędne dopasowanie typów daty i czasu do rzeczywistych potrzeb

Andrzej: Janek do tej kolumny DataUrodzenia to jaki typ myslisz?

Janek: DateTime!

Andrzej: Myslisz, ze beda tam wstawiac godzine?

Janek: Pewnie nie ale moze w przyszlosci beda chcieli

Złe dopasowanie typów danych to już jest plaga. Tyle razy słyszy się w ogromnej ilości miejsc, żeby dobierać jak najmniejszy typ danych do potrzeb. Wpływ struktury obiektów na wydajność całego rozwiązania jest ogromny, jednakże deweloperzy/architekci często siląc się na utworzenie obiektu, który ma być maksymalnie szeroki “na wszelki wypadek” robią krzywdę sobie i odbiorcy danego rozwiązania. Zaszczytne miejsce w tym gronie zachowuje dobór typu danych związanego z datą i czasem. Mogę Was zapewnić, że implementacje typu DATE lub TIME widziałem dosłownie kilka razy, za to królem na salonach był i (niestety) będzie DATETIME. Przypomnijmy tylko, że chodzi tu przede wszystkim o rozmiar danych – żeby przytoczyć te najpopularniejsze typy:

• datetime – 8 bajtów
• date – 3 bajty
• time – w zależności od precyzji od 3 do 5 bajtów
• datetime2 – od 6 do 8 bajtów

Tak więc jeśli potrzebujemy daty używajmy DATE, potrzebujemy czasu używajmy TIME, a jeśli potrzebujemy daty i czasu użyjmy datetime2(0). W większości przypadków nie potrzebujemy nawet milisekund, a rozgraniczamy się do dużo mniejszych jednostek. Nie zapominajmy również o typie smalldatetime, który pozwala na przechowanie daty i czasu z zakresu od 1900-01-01 do 2079-06-06 który w wielu przypadkach powinien wystarczyć.

Wiele systemów musi rejestrować czas, a data nie jest istotna. Standardowy wybór powinien paść na wspomniany typ TIME ale kreatywność nie zna tu granic, możemy spotkać pełny zapis data czas (gdzie data jest “wyrzucana” w aplikacji/raporcie) jak i sztywną datę (np. 2000-01-01), która i tak jest ignorowana “na front endzie” poprzez ustawienie odpowiedniego typu wyświetlania. W niektórych przypadkach, aż trudno znaleźć schemat myślowy tego kto to wymyślił…

Podzapytania w SELECT

Janek: Słuchaj potrzebuje wyświetlić date ostatniego zamówienia przy każdym kliencie – masz pomysł?

Andrzej:Widziałem kiedyś w oraklu jak gościu napisał SELECT MAX w SELECT sprawdź czy w sikuelu to zadziała

Nie wiem skąd się wzięło to przyzwyczajenie, ale stosunkowo często spotyka się zapis podobny do poniższego:

SELECT 
 SIL.InvoiceLineID,
 SIL.InvoiceID,
 (SELECT IIF(PaymentDays=7,1,0) FROM Sales.Customers AS SC
 WHERE SC.CustomerID=SI.CustomerID
 )
FROM [Sales].[InvoiceLines] AS SIL
JOIN [Sales].[Invoices] AS SI
ON SI.InvoiceID=SIL.InvoiceID

Być może coś takiego z logiczne punktu widzenia ma sens, ale z punktu widzenia czytelności już sensu nie ma. Jeśli chodzi o wydajność to SQL Server w wielu przypadkach będzie potrafił to sobie odpowiednio zoptymalizować jednak z całą pewnością nie we wszystkich możliwych scenariuszach. Chyba wszyscy zdajemy sobie sprawę jak łatwo coś takiego przepisać do bardziej cywilizowanej formy, mamy całkiem dużo możliwości np. zwykły JOIN:

SELECT 
 SIL.InvoiceLineID,
 SIL.InvoiceID,
IIF(PaymentDays=7,1,0)
FROM [Sales].[InvoiceLines] AS SIL
JOIN [Sales].[Invoices] AS SI
ON SI.InvoiceID=SIL.InvoiceID
LEFT JOIN Sales.Customers AS SC
ON SC.CustomerID=SI.CustomerID

Część z Was zapewne powie, że pierwszy zapis jest bardziej czytelny jednakże dla mnie z całą pewnością nie jest i jak już wspomniałem we wstępie jest to lista w pełni subiektywna.

Powyższy przykład był prosty jednakże czasem możemy mieć naprawdę problemy z rozszyfrowaniem tego co się dzieje albo wydajność będzie bardzo mocno niezadowalająca. Z mojego doświadczenia powiem, że problemy z SELECTami inline niemal zawsze można rozwiązać używając w zależności od problemu:

• typowego złączenia JOIN
• EXISTS/ NOT EXISTS
• APPLY
• Funkcji okna

Wystarczy trochę dobrych chęci i samozaparcia, a zapytania będą działały wydajnie zapis będzie przyjemniejszy dla oka i wydajniejszy.

Odwoływania do obiektów

Andrzej: Powiedz mi dlaczego ty piszesz cały czas nazwę bazy danych schemat i kolumnę w zapytaniach? By na następnym razem pisać tylko nazwę kolumny?

Janek: Najpierw daje sikuelowi próbkę jako zbiór uczący resztę niech zgaduje sam – Data Science chłopie!

Odwoływać się do obiektów takich jak tabele, widoki, procedury czy cokolwiek innego może odbywać się na wiele sposobów. Przede wszystkim możemy użyć nazwy trójczłonowej:

[BazaDanych].[Schemat].[Obiekt]

Nazwy dwuczłonowej:

[Schemat].[Obiekt]

lub nazwy jednoczłonowej:

[Obiekt]

Występuje również nazwa czteroczłonowa najczęściej stosowana w przypadku Linked Servers:

[Serwer].[BazaDanych].[Schemat].[Obiekt]

Jak myślicie, która z nich jest najlepsza? Ktoś przezorny odpowie, że to zależy. I to jest właśnie prawidłowa odpowiedź! Jednakże w moim odczuciu najczęściej powinniśmy używać nazwy dwuczłonowej, rzadziej trójczłonowej (właściwie to powinniśmy jej używać tylko wtedy gdy mamy pewność, że bazy do których się odwołujemy zawsze będą znajdować się na tym samym serwerze. Jeśli któraś z baz zostanie przeniesiona na inny serwer to co wtedy?) i nigdy nazwy jednoczłonowej.

Widząc zapis  FROM Sales w skomplikowanym zapytaniu pomyślimy, że Sales to właściwie co? Ja osobiście na samym początku pomyślałbym, że to CTE lub np. podzapytanie jednakże często w praktyce to tabela lub widok… Poza tym zapis jednoczłonowy może prowadzić do problemów bo w zapytaniu w fazie rozpoznawania obiektów SQL Server weźmie pod uwagę domyślny schemat użytkownika, a to może prowadzić niekiedy do problemów. Po co zatem utrudniać sobie życie? Używajmy nazwy dwuczłonowej i w określonych przypadkach trój i czteroczłonowej… zapomnijmy o jednoczłonowych sierotach…

Nadużywanie DISTINCT

Andrzej: Janek ten JOIN to chyba nie jest dobry bo duplikaty są..

Janek: Wrzuć tam DISTINCTa i będzie dobrze

Spotkaliście się kiedyś z taką sytuacją? A może sami byliście Jankiem lub Andrzejem? Taka sytuacja zdarza się nader często tj. problem ze złączeniem jest “ukrywany” poprzez DISTINCT. Co ciekawe często zdarza się również to, że deweloperzy prewencyjnie wrzucą DISTINCT  “na wszelki wypadek”. SQL Server nie lubi takich “na wszelki wypadek” szczególnie w tym przypadku. Jak wiecie DISTINCT w SQL Server jest wykonywany jako operator SORT, który z kolei potrzebuje grantu pamięci aby wartości posortować i odrzucić duplikaty. Grant pamięci całkowicie zależy od estymacji ilości wierszy, a estymacja tego typu w dużych zapytaniach zazwyczaj im “dalej” tym mniej prawidłowa. Nieprawidłowa estymacja może prowadzić do zbyt dużego lub zbyt małego grantu co z kolei może skutkować zrzutem do tempdb lub może niepotrzebnie alokować pamięć. Jeżeli masz pewność, że tego potrzebujesz to użyj ale nie w innym przypadku.

Jakiekolwiek użycie ISNUMERIC

Janek: Ta twoja procedura to jakaś dziwna jest.. Jako cenę zakupu podałem “-.,$++–” i zwróciła mi, że jestem w TOP 10 klientów i należy mi się $$$$… zwrotu

Andrzej: Może te dane są obfuskowane? Pewnie włączyli Always Encrypted

Funkcja ta ma na celu sprawdzić czy podany argument jest typu numerycznego, w rezultacie nie działa ona w pełni prawidłowo bo niektóre znaki interpretowane są przez nią jako liczba. Poniżej kilka przykładów:

SELECT ISNUMERIC('-') AS Example1
SELECT ISNUMERIC('.') AS Example2
SELECT ISNUMERIC(',') AS Example3
SELECT ISNUMERIC('$') AS Example4
SELECT ISNUMERIC('+.') AS Example5

Jak można zauważyć zwrócona została 1 w przypadkach gdzie podane argumenty z całą pewnością nie są liczbami. Jeśli coś nie działa tak jak tego oczekujemy nie powinniśmy tego używać. Jak w takim razie jak sprawdzić czy podany argument jest liczbą? Można np. użyć TRY_CAST:

SELECT TRY_CAST('-' AS DECIMAL(18,6)) AS Example1
SELECT TRY_CAST('.' AS DECIMAL(18,6)) AS Example2
SELECT TRY_CAST(',' AS DECIMAL(18,6)) AS Example3
SELECT TRY_CAST('$' AS DECIMAL(18,6)) AS Example4
SELECT TRY_CAST('+.' AS DECIMAL(18,6)) AS Example5
SELECT TRY_CAST('66' AS DECIMAL(18,6)) AS Example6
SELECT TRY_CAST('16.22222222' AS DECIMAL(18,6)) AS Example7

Zagnieżdżanie widoków

Janek: Andrzej bo ja muszę wyświetlić tylko nazwę produktu, a to zapytanie mieli się już 20 minut

Andrzej: Też tak miałem, poszukaj na 16 poziomie zagnieżdżenia odnośnika do widoku vCustomer. On tam na 3 poziomie ma dwupoziomowy widok gdzie jakiś głupek wpisał LEFTa w warunku złączenia

 

SQL Server umożliwia nam zagnieżdżanie widoków i funkcjonalność ta jest oczywiście niezwykle użyteczna powiedziałbym nawet niezbędna. Jednakże jak to często bywa jest to aż nazbyt często wykorzystywane bardzo często w absurdalny sposób. Pamiętam, że widziałem jedną sesję na spotkaniu społecznościowym gdzie prelegent był wielkim zwolennikiem zagnieżdżania i nawet chciałby aby Microsoft zwiększył maksymalny poziom zagnieżdżania który o ile się nie mylę wynosi 32… Szaleństwo…  Według moich obserwacji wynika, że problem ten wynika przede wszystkim z lenistwa komuś po prostu nie chce się pisać czegoś drugi raz. Z drugiej zaś strony wynika to z braku wiedzy biznesowej bo skoro widok zwraca prawidłowe dane to użyjmy ich i miejmy pewność, że jest w porządku. Takie wyjście nie jest oczywiście kategorycznie złe jednakże pamiętajmy o tym, że SQL Server nie zawsze będzie w stanie wyeliminować wszystkich obliczeń zawartych w widokach, a my potrzebując określonego podzbioru narażamy cały system na wykonywanie ogromnej pracy. Poza tym już kilkukrotne zagnieżdżenie powoduje problemy ze zrozumieniem logiki i tuningiem wydajnościowym. Używajmy dobrodziejstwa zagnieżdżania ale z umiarem.

“Nie wiadomo jakie uprawnienia nadać to dam sysadmina albo dbo”

Andrzej: Ty, żeby user mógł odczytać dane z tej tabeli to mam dać db_datareader czy co?

Janek: Tam sobie kliknij sysadmin albo poczekaj 5 minut to ci dam hasełko do sa… a nie sorry mam to hasło… Wpisz “Admin” z dużej litery

Myślałem, że w dzisiejszych czasach nadawanie uprawnień to proces do którego przywiązuje się niezwykle dużą wagę… Myliłem się! Ile to razy słyszałem, że do serwera użytkownik dostaje admina bo “nie mógł się połączyć do bazy” albo raporty łączą się z bazą po użytkowniku będącym administratorem. Ponadto bardzo często na pytanie “co jeżeli <tutaj dowolny argument>” otrzymuje zdawkową odpowiedź “inaczej nie działa” lub “kto u nas by się bawił w jakiś indżekszyn”. Hierarchia uprawnień w SQL Server może i jest rozbudowana, ale jej znajomość jest kluczem dla każdego administratora baz danych i w żadnym wypadku nie jest to wymówka do dawania za wysokich uprawnień. Zrobienie wszystkiego w ten właściwy sposób zawsze będzie trudniejsze niż zrobienie czegoś “na szybko” ale chyba każdy się zgodzi, że gra jest warta świeczki. Sam osobiście doświadczyłem tego jak jedna osoba testowała skrypt i usunęła obiekt ze środowiska produkcyjnego bo myślała, że okienko w Management Studio jest podłączone do “deva”… Wystarczyło, żeby nie miała tak wysokich uprawnień na produkcji…

Kult seeka

Andrzej: Chyba nie przyjmą tego raportu.. Odświeża się już drugą godzinę

Janek: Przecież mamy już seeka – więcej nie mogę nic zrobić – trzeba kupić SSD i zwiększyć ilość rdzeni z tych marnych 32 na 64…

Zdaję sobie sprawę, że tytuł tego podpunktu brzmi jak jakaś książka fantasy na temat starożytnego Egiptu jednakże z całą pewnością kult seeka trwa i ma się dobrze. Bardzo wiele osób gdzieś usłyszało/ przeczytało, że jak mamy “seek” to jest dobrze a jak mamy “scan” to jest źle. Czy jest to prawda? Odpowiedź jak zawsze jest niejednoznaczna i zależy od sytuacji. Metody dostępu do danych w SQL Server zostały zaimplementowane w taki, a nie inny sposób i każda z nich ma swoje zastosowanie. Seek świetnie się sprawdzi dla tzw. Point Lookupów, a Scan wkracza na scenę wtedy gdy Seek będzie zbyt kosztowny.. Właściwie to wszystko! Oczywiście jest cała masa kwestii związanych z tym “dlaczego” SQL Server wybrał Scan jak mógł wybrać Seek jak np.:

• Tipping Point
• Statystyki i ich aktualność
• Występowanie właściwego indeksu
• Zapytanie napisane w sposób sprzyjający operacji Seek
• Resiudal Predicate Pushdown

Myślę, że właśnie dlatego powstał mit o tym, że Seek jest tym dobrym, a Scan tym złym. Według mnie chodzi po prostu o to, że jest wiele problemów związanych z tym, że optymalizator wybrał Scan zamiast Seek, a w drugą stronę jest ich już zdecydowanie mniej. Tak więc jest to absolutnie błędne przekonanie, które łatwo wybić komuś z głowy pokazując hint FORCE SEEK:

SET STATISTICS IO ON

SELECT 
 SIL.InvoiceLineID,
 SIL.InvoiceID
FROM [Sales].[InvoiceLines] AS SIL
WHERE SIL.InvoiceLineID>0

SELECT 
 SIL.InvoiceLineID,
 SIL.InvoiceID
FROM [Sales].[InvoiceLines] AS SIL WITH (FORCESEEK)
WHERE SIL.InvoiceLineID>0

Z kosztowego punktu widzenia:

Dla nieprzekonanych z punktu widzenia IO:

Z logicznego punktu widzenia:

Po co zaimplementowano by dwa operatory dostępu do danych skoro jeden miałby być we wszystkim lepszy? No właśnie…

Missing Index Warning

Janek: Ty bo ta tabela ma 8 kolumn i 20 indeksów? Myślisz, że to dobrze?

Andrzej: Przecież zakładamy indeksy tylko jak podpowiada je sikuel… Poguglaj za jakąś flagą albo service packiem

SQL Server zapisuje dla nas informację o tym, że nasze zapytanie jakby miało indeks o takiej a nie innej strukturze który miał wpływ N% na nasze zapytanie. Nawet nie spodziewałem się, że wyrażenie “wpływ N%” może aż tak bardzo wpływać na wyobraźnię! Widziałem nawet implementację rozwiązania “samooptymalizującego”, które na podstawie rzeczonych “Missing index warnings” tworzył automatycznie indeksy… W świecie baz danych bardzo mało jest złotych zasad, które sprawdzają się świetnie w każdych możliwych warunkach i nie inaczej jest w tym przypadku. Bohater tego podpunktu to nic innego jak sugestia, która nie bierze pod uwagę w żadnym wypadku całego workloadu na bazie, a jedynie to określone zapytanie dla którego to ostrzeżenie powstało. Ponadto SQL Server nie posiada wbudowanego ludzkiego zdrowego rozsądku co naprawdę dla niektórych może być szokiem. Jaki z tego wniosek?

Rozważmy poniższy przypadek:

SELECT 
 *
FROM [Sales].[InvoiceLines] AS SIL 
WHERE LastEditedWhen='2013-01-01 12:00:00.0000000'

Zapytanie raczej trywialne, które wybiera wszystkie wiersze bazując na kolumnie technicznej LastEditedWhen. Tak wygląda plan wykonania takiego zapytania:

Nasz Missing Index Details zasugerował następujący indeks:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] 
ON [Sales].[InvoiceLines]([LastEditedWhen]) 
INCLUDE(
[InvoiceLineID], 
[InvoiceID], 
[StockItemID], 
[Description], 
[PackageTypeID], 
[Quantity], 
[UnitPrice], 
[TaxRate], 
[TaxAmount], 
[LineProfit], 
[ExtendedPrice], 
[LastEditedBy]);
GO

Powyższa definicja powinna dać wszystkim wiele do myślenia – po pierwsze jest to pełna kopia całej tabeli posortowana według kolumny technicznej. Zalet takiego indeksu brak ale należy powiedzieć, że dla naszego zapytania jest dopasowany idealnie. Tak więc do tego typu podpowiedzi zawsze należy podchodzić z pewną dozą zdrowego rozsądku. Takiego rozsądku z całą pewnością brak wielu specjalistom, którzy nie tylko tworzą te indeksy hurtowo ale czasem nawet z nazwą [<Name of Missing Index, sysname,>] – polecam sprawdzić w zastanej bazie czy nie ma tam takiego indeksu 🙂 Może to powiedzieć więcej o bazie niż Life Page Expectancy czy Batch requests per Second.

Bezcelowa zmiana COLLATION

Andrzej: Odróżnienie małych od dużych znaków to na pewno dobry pomysł?

Janek: Niech użytkownicy myślą co wpisują – to nie zabawka tylko baza danych!

Widząc słowo COLLATION niektórym może pojawić się na twarzy uśmiech jednakże mnie nie było do śmiechu gdy u jednego u klientów w ramach migracji musiałem dostosować wcześniej zmienione przez kogoś COLLATION. Ustawienia porządkowania i sortowania znaków to jedne z tych ustawień, które są kluczowe dla działania całej bazy danych. W przytoczonym przeze mnie przypadku COLLATION było niestandardowe i rozróżniało duże i małe litery, a ja w ramach migracji musiałem zmienić to ustawienie na case insensitive czyli najgorszy możliwy scenariusz… Co kierowało kimś kto ustawił COLLATION na CS? Ciężko z całą pewnością mi to stwierdzić ale zakładam, że ten ktoś chciał się dostosować do mocno w tym projekcie wykorzystywanego Integration Services, którego niektóre transformacje z zasady są CASE SENSITIVE.  Ogólnie zmiana tego ustawienia do najprzyjemniejszych nie należy bo nie sprowadza się do uruchomienia dwóch – trzech komend języka TSQL. Jako przykład przedstawię Wam następujący scenariusz:

use master
go

DROP DATABASE TEST

CREATE DATABASE TEST
COLLATE SQL_Latin1_General_CP1_CS_AS

USE TEST
GO

CREATE TABLE dbo.t1
(
c1 VARCHAR(50) NULL,
c2 VARCHAR(55) NULL
)
GO

INSERT INTO dbo.t1 
(c1,c2)
VALUES
('a','b')
GO 2

SELECT 'a' c1 INTO #tmp

Czyli mamy bazę TEST w której mamy tabelę t1. Ze względu na fakt, że przy tworzeniu tabeli t1 nie podaliśmy jawnie COLLATION to została ona odziedziczona z poziomu bazy danych. Następnie stworzyliśmy sobie bazę tymczasową z jedną kolumną tekstową. Spróbujmy teraz połączyć obie tabele:

SELECT * FROM #tmp
JOIN dbo.t1
ON #tmp.c1=dbo.t1.c1

Zamiast rezultatu otrzymaliśmy błąd niezgodnych COLLATION ponieważ baza #tmp odziedziczyła to ustawienie z poziomu swojej bazy danych czyli tempdb:

Msg 468, Level 16, State 9, Line 29
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" 
and "Latin1_General_CS_AS" in the equal to operation.

Nie możemy zmienić COLLATION tempdb (nie wiadomo jaki byłby wpływ na inne bazy danych na tym samym serwerze) więc pozostaje nam zmiana na poziomie naszej bazy danych lub jawne wskazanie na poziomie zapytania:

SELECT * FROM #tmp
JOIN dbo.t1
ON #tmp.c1=dbo.t1.c1 COLLATE SQL_Latin1_General_CP1_CS_AS

Chyba łatwo sobie wyobrazić, że definiowanie w każdym możliwym zapytaniu jest po prostu niemożliwe. Co się stanie gdy zmienimy COLLATION na poziomie bazy danych? Sprawdźmy to używając poniższego zapytania:

ALTER DATABASE TEST
COLLATE Latin1_General_CS_AS

W rezultacie próba ponownego uruchomienia JOINa z tabelą tymczasową zakończy się tym samym błędem co wcześniej – dlaczego? Wystarczy odpytać sys.columns i wszystko staje się jasne:

Oczywiście zmiana na poziomie bazy danych ustawia po prostu domyślne COLLATION, które będzie:

• dziedziczone przez kolumny tekstowe nowych obiektów
• zwracane domyślnie dla kolumn tekstowych przez obiekty takie jak np. procedury
• zmienione w tabelach systemowych
• Zmienione dla typów użytkownika bazujących na typach systemowych

Ale istniejące obiekty pozostaną nietknięte… Dlatego też zmiana COLLATION to ciężka droga i naprawdę trzeba się napracować i bardzo uważać aby nie wpaść w tarapaty.

Problemów związanych z nieprzestrzeganiem określonych zasad czy też dobrych praktyk jest oczywiście dużo więcej, jednakże postanowiłem wylistować tylko kilka z nich. Mam nadzieję, że czytelnicy tego artykułu znają opisane kwestie i unikają czegoś co da się uniknąć w bardzo prosty sposób znając te parę prostych zasad. W przyszłości być może pokuszę się o kolejną część, która rozszerzy powyższą listę. Pomysłów nie brak bo każdy nowy projekt czy też konsultacja pokazuje, że nie ma rzeczy niemożliwych i zasad których nie dałoby się złamać.