W ostatnim w poście pokazałem jak odczytać współrzędne geograficzne. Kolejne możliwe pytanie, to jak je przechowywać w bazie danych. Najprostsze podejście to stworzenie dwóch kolumn typu float, dla szerokości i długości geograficznej.

W SQL Server istnieje jednak lepsze rozwiązanie, a mianowicie typ “geography”. Służy on do przechowywania lokalizacji i nie koniecznie  wyłącznie pojedynczego punktu. Możliwe jest zapisywanie całych poligonów. Utworzenie tabeli z taką kolumną, nie różni się niczym od pozostałych typów:

CREATE TABLE Locations   
    ( Id int IDENTITY (1,1),  
    Location geography );  
GO  

Następnie dodanie lokalizacji, opisującej poligon wygląda następująco:

INSERT INTO Locations (Location)  
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));  

W celu dodania lokalizacji w formie pojedynczego punktu, należy stworzyć obiekt POINT zamiast powyższego POLYGON:

INSERT INTO Locations (Location)  
VALUES (geography::STGeomFromText('geography::Point(47.65100, -122.34900, 4326))', 4326));

Zaletą korzystania z geography jest, że wszelkie operacje takie jak znajdowanie odległości między dwoma punktami, są już zaimplementowane. Jeśli chcemy znaleźć części wspólne dwóch lokalizacji wtedy:

DECLARE @g geometry;  
DECLARE @h geometry;  
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);  
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);  
SELECT @g.STIntersection(@h).ToString();  

Zwracanie odległości między punktami wygląda następująco:

DECLARE @g geography;  
DECLARE @h geography;  
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);  
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);  
SELECT @g.STDistance(@h);  

Ponadto, SQL Server management studio, ma pewne wsparcie dla typu geography. Wykonując prosty SELECT zobaczymy wyłącznie binarne wartości:

Przechodząc jednak do zakładki “Spatial Results”, zobaczymy całą mapkę:

W przyszłym wpisie, pokażę jak korzystać z tego typu w C# (a konkretnie w Dapper).

To jest nie blog o SQL Server, ale myślę, że podstawowe informacje o bazach danych są przydatne dla każdego programisty.

Wszyscy używamy indeksów i wiemy, że dzielą się one m.in. na clustered index oraz non-clustered index. Odpowiednie stworzenie ich może okazać się dość trudnym zadaniem. Niestety nasza rola nie ogranicza się wyłącznie do ich utworzenia ponieważ wcześniej czy później będziemy mieli do czynienia z fragmentacją indeksów.

Co to jest fragmentacja? W informatyce jest to znane pojęcie i oznacza, że dane nie są rozmieszczone równomiernie w pamięci. Indeks jest reprezentowany jako drzewo. Jeśli jego węzły nie są rozmieszczone równomiernie (w spójnych blokach), wtedy odczytywanie danych jest trudniejsze ponieważ musimy skakać z jednego adresu na drugi.

Tak na prawdę w bazach danych musimy być świadomi dwóch typów fragmentacji. Pierwszy z nich to fragmentacja fizyczna dysku. Jeśli dane na dysku nie są spójne, to naturalnie może zdarzyć się, że plik bazy danych również zostanie podzielony na różne bloki w pamięci. Ten typ fragmentacji nie ma nic wspólnego z SQL Server, ale jeśli ma miejsce, spowoduje spowolnienie wszystkich zapytań. Wyobraźmy sobie, że plik bazy danych jest umieszczony na tym samym dysku, gdzie umieszczone są jakieś inne pliki. Jeśli tylko jest tam dużo operacji Input\Output, wtedy fragmentacja może mieć miejsce dosyć często. Rozwiązaniem jest zwykła defragmentacja dysku wykonywana przez Windows. Dodatkowo warto umieszczać plik bazodanowy na osobnym, niezależnym dysku.

Drugi typ fragmentacji, to fragmentacja indeksu i jest ściśle związana z silnikiem baz danych. Wchodząc szczegóły, wyróżnia się fragmentację wewnętrzną oraz zewnętrzna. Jeśli usuniemy dane ze środka tabeli, wtedy pojawią się puste fragmenty na stronach pamięci. Wykonując DELETE wielokrotnie, możemy doprowadzić zatem do fragmentacji wewnętrznej, czyli pustych fragmentów (“dziur”) na stronach pamięci.

Z kolei zewnętrzna fragmentacja ma miejsce, gdy logiczna kolejność stron nie odpowiada fizycznej kolejności. Przytrafia się to, gdy wiersze są dodawane do środkowych stron, w których nie ma już miejsca. Załóżmy, że mamy strony Page1, Page2, Page3, które odpowiadają fizycznej kolejności Page1->Page2->Page3. Załóżmy, że wykonujemy INSERT, który powinien dodać dane na końcu strony Page1. Jeśli nie będzie tam wystarczającao miejsca, wtedy zostanie dodana nowa strona (nazwijmy ją Page1A) i fizyczny porządek będzie wyglądał Page1->Page2->Page3->Page1A, a logiczny z kolei Page1->Page1a->Page2->Page3. Innymi słowy, zewnętrzna fragmentacja to “dziury” pomiędzy stronami, a wewnętrzna to “dziury” w obrębie pojedynczych stron.

Wniosek taki, że indeksy trzeba przebudowywać od czasu do czasu. W  SQL Management Studio można wybrać z menu kontekstowego opcję Rebuild:

Na ekranie pojawi się informacja jak bardzo dany indeks jest sfragmentowany:

Oczywiście w praktyce automatyzuje się takie zadania. Administratorzy baz danych zwykle definiują skrypty, które wykonają się, gdy poziom fragmentacji osiągnie dany próg.

Na przykład, podany skrypt wyświetli informacje o wszystkich indeksach wraz z ich fragmentacją (źródło):

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Kilka ostatnich wpisów poświęciłem na temat SQL Server. Ostatnio pisałem o SQL Statistics, które wpływa na wygenerowanie planu wykonania. Jak wiemy, plan jest zawsze buforowany, co wpływa zwykle pozytywnie na wydajność. Analiza zapytania i wygenerowanie planu są dość czasochłonne, dlatego zwykle plan jest zapisywany.

Problem w tym, że nie zawsze plan przechowywany w cache jest najbardziej optymalny. Zwykle pierwsze wykonanie danego zapytania powoduje zapis planu w pamięci. Co jeśli kolejne zapytania będą używały innych parametrów, a co za tym idzie, inny plan może być skuteczniejszy?

Problem pokażę, na przykładzie bazy AdventureWorks, którą można ściągnąć z stąd.
Do dyspozycji mamy tam tabelę Address.

Zapytanie:

SELECT* FROM [AdventureWorks2014].[Person].[Address]

Wynik:

Jeden z indeksów pokrywa kolumnę StateProvinceID. Wykonajmy poniższe zapytanie, aby dowiedzieć się trochę więcej o dystrybucji danych:

SELECT StateProvinceID,count(*)
  FROM [AdventureWorks2014].[Person].[Address] group by StateProvinceID order by count(*) desc

Wynik:

Widzimy, że bardzo dużo wierszy ma StateProvinceId równy 9. Z kolei 119 ma tylko jedno wystąpienie. Wykonajmy zatem  poniższe zapytania na tej kolumnie, aby zobaczyć wygenerowane plany wykonania:

 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=9
 GO
 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=119

Plany wykonania:

Widzimy, że pierwsze zapytanie nie korzysta z indeksu, ponieważ jest mało selektywne i LookUp kosztował by więcej niż skanowanie. Drugie zapytanie, które zwraca wyłącznie jeden wiersz jest dużo lepszym kandydatem na przeszukiwanie indeksu.
Plan nie został zbuforowany bo to fizycznie dwa różne zapytanie.

Stwórzmy teraz procedurę, która wykonuje analogiczne zapytanie:

CREATE PROCEDURE GetAddressByStateId
	@stateId int
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Wykonajmy również procedurę przekazując dwa różne identyfikatory stanów:

  exec GetAddressByStateId 9
  GO
  exec GetAddressByStateId 119

Plany wykonania:

Co się stało? Pierwsze wykonanie zbuforowało plan wykonania. Drugie wywołanie zatem nie będzie próbowało znaleźć optymalnego planu, a skorzysta z tego zawartego w cache. Problem w tym, że dla skomplikowanych procedur nie zawsze pierwszy plan wykonania jest najlepszy.

Jednym z “obejść” jest rekompilacja procedury za każdym razem za pomocą WITH RECOMPILE:

ALTER PROCEDURE GetAddressByStateId
	@stateId int  WITH RECOMPILE
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Innym rozwiązaniem może być skorzystanie z Query Hints. Za pomocą wskazówek można ułatwić SQL Server wygenerowanie najlepszego planu. W praktyce jednak, jeśli mamy problemy z parameter sniffing oznacza to, że procedura jest zbyt skomplikowana i część logiki powinna być wykonywana np. w osobnej funkcji.

W poprzednim poście, wyjaśniłem do czego służą statystyki w SQL Server. Do dyspozycji MSSQL ma gęstość oraz dystrybucję danych. Dzisiaj pokażę, jak przeglądać te dane, aby przekonać się samemu, że faktycznie powyższe metryki istnieją.

Załóżmy, że mamy tabele Articles z dwoma kolumnami, ID oraz Price. Następnie tworzymy indeks, czyli zarazem statystykę na kolumnie Price. W Sql Server studio będzie to wyglądać następująco:

Dodajmy również kilka wierszy z tymi samymi cenami. W celu wyświetlenia statystyk należy wywołać DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS('dbo.Articles',PRICE_INDEX)

Wynik będzie wyglądać następująco:

Pierwsza tabela przedstawia nazwę statystyki wraz z podsumowaniem. Widzimy m.in. datę ostatniej aktualizacji czy liczbę wierszy, które brały udział w obliczaniu metryk.

Druga tabela to gęstość. Wartość 1 oznacza, że nie ma unikalnych wierwszy. 0.125 to z kolei gęstość klucza.Mamy osiem wierszy i klucz główny zawsze ma unikalną wartość, stąd 1/8 równa się 0.125.

Ostatnia tabela to dystrybucja danych w formie histogramu. Histogram opisany jest za pomocą kilku kolumn tzn.:

RANGE_HI_KEY
RANGE_ROWS
EQ_ROWS
DISTINCT_RANGE_ROWS
AVG_RANGE_ROWS

RANGE_HI_KEY to wartość pojedynczego progu w hisogramie. Kolejne metryki opisują ile wartości przypada na ten próg (m.in. włączając granice progu, przypadające dokładnie w miejsce progu, liczba unikalnych wartość).

Szczegóły można znaleźć w dokumentacji m.in. tutaj. W praktyce oczywiście nie musimy analizować tych danych. Bardzo ważne jednak jest, aby mieć świadomość o SQL Statistics ponieważ statystyki mają ogromny wpływ podczas generowania planu wykonania.

SQL Server buduje statystyki opisujące przechowywane dane. Dzięki tym statystkom łatwiej jest wygenerować optymalny “execution plan”.  Jak wiemy,  plan wykonania zależy m.in. od stworzonych indeksów oraz przekazanych parametrów do danego zapytania\procedury.  Problem w tym, że bez wiedzy o konkretnych danych w bazie, trudno wygenerować optymalny plan. Jeśli wiemy, że zapytanie zwróci wszystkie wiersze, wtedy nie ma sensu korzystać z żadnych indeksów, ponieważ skanowanie będzie i tak szybsze.

Dzięki statystykom, SQL Server wie m.in. o dystrybucji i gęstości konkretnych danych\kolumn. Jeśli kolumna B ma jedną wartość we wszystkich wierszach, wtedy filtrowanie po niej nie jest zbyt selektywne. Takie informacje są bardzo przydatne w czasie tworzenia planu. W momencie, gdy wykonujemy jakiekolwiek zapytanie czy procedurę następuje kilka ważnych etapów:

1. Zapytanie jest przekazane do SQL Server query optimizer.
2. Jeśli plan zapytania istnieje w cache, wtedy po prostu jest zwracany.
3. Tworzony jest nowy plan na podstawie dostępnych indeksów oraz typu przechowywanych danych.  To tutaj SQL Optimizer decyduje czy warto użyć indeksu czy lepsze może okazać się standardowe skanowanie. Jeśli optymizer zdecyduje się na użycie indeksu, wtedy musi również wybrać ten najbardziej optymalny.
4. Nowo stworzony plan jest umieszczany w cache, wiec następne zapytania nie będą musiały być ponownie analizowane.

Do dyspozycji SQL Server ma dwie metryki, zagęszczenie danych oraz ich dystrybucję.

• Gęstość  danych(Density)

Pierwszą zbieraną metryką jest zagęszczenie danych, czyli współczynnik pokazujący jak wiele unikalnych wartości jest w danej kolumnie. Można to opisać następującym wzorem:

Density of X = 1 / liczba unikalnych wartości w kolumnie X

Wysoka gęstość oznacza zatem, że jest tam mało unikalnych wartości, a co za tym idzie, zapytanie na takiej kolumnie będzie mało selektywne i nie ma sensu korzystać z indeksu.

• Dystrybucja danych

Dystrybucja danych również opisuje zagęszczenie danych, ale pokazuje jakie konkretne dane występują najczęściej. Innymi słowy, dystrybucja danych to histogram. Dane są zatem dzielone na odpowiednie przedziały (oś X), a potem na osi Y mamy liczbę wystąpień znajdujących się w danym przedziale.   Jeśli w jakimś zapytaniu mamy “WHERE X> 200 AND X < 500, wtedy za pomocą dystrybucji można określić ile w przybliżeniu zapytanie zwróci wierszy. Jeśli histogram w tych przedziałach ma wysokie wartości, wtedy zapytanie jest bardzo selektywne, a co za tym idzie, indeks jest dobrym rozwiązaniem.

W SQL Server występuje 200 wspomnianych przedziałów. Trochę upraszczam tutaj, bo SQL Server przechowuje kilka innych wartości histogramu, ale główną ideą jest posiadanie opisu gęstości konkretnych przedziałów danych.

Kiedy są zatem tworzone statystyki? Domyślnie są tworzone dla indeksów, w tym klucza głównego (clustered index). Jeśli AUTO_CREATE_STATISTICS jest uaktywnione na bazie, wtedy również będą tworzone dla kolumn w zapytaniach, które używane są do filtrowania danych (klauzula WHERE, JOIN).

W następnym wpisie przyjrzyjmy się jak obejrzeć powyższe statystyki w SQL Server.