Co nowego w silniku bazodanowym SQL Server 2008 June CTP

Autor: Marcin Guzowski

Opublikowano: 21 czerwca 2007

Zawartość strony

	Wstęp
	Nowe funkcjonalności
	Polecenie MERGE
	Szybka inicjalizacja zmiennych
	Wielowierszowa konstrukcja polecenia INSERT
	GROUPING SETS – rozszerzenie klauzuli GROUP BY
	Tablicowy typ danych
	Optymalizacja zapytań zawierających złączenia w schemacie gwiazdy
	Hint FORCESEEK
	Mechanizm Change Data Capture (CDC)
	Mechanizm Declarative Management Framework (DMF)
	Nowe systemowe widoki dynamiczne (DMV)
	Podsumowanie

Wstęp

W dniu 4 czerwca 2007 w Orlando, na konferencji TechEd, koncern Microsoft ogłosił, że nowa wersja popularnej platformy bazodanowej SQL Server będzie nosiła nazwę SQL Server 2008. Dotychczas wersja ta określana była nazwą kodową „Katmai”, która to nazwa będzie stosowana równolegle, do czasu dostarczenia na rynek produktu w finalnej postaci, co nastąpić ma w roku 2008. Z empirycznym poznawaniem funkcjonalności nowego wydania SQL Server nie musimy jednak czekać do przyszłego roku – tego samego dnia upubliczniona została wersja CTP (ang. Community Technology Preview) SQL Server code name Katmai. Artykuł opisuje nowości, jakie można znaleźć w silniku bazodanowym tej wersji.

Informacja:

Firma Microsoft oraz autor artykułu ostrzegają, że funkcjonalności omawiane w niniejszym artykule mogą zostać usunięte lub ich działanie może ulec nawet zasadniczym zmianom w następnych wersjach systemu.

Pierwsze oficjalne oblicze Katmai – SQL Server 2008 June CTP (build 10.0.1019) naturalnie nie zawiera pełnego zestawu funkcjonalności, jakie dostarczone zostaną w wersji finalnej. Firma Microsoft zapowiedziała, że co kilka miesięcy (około 60 dni) udostępni kolejne CTP, w których sukcesywnie dodawane będą docelowe mechanizmy. Ma to służyć stopniowemu zapoznawaniu się społeczności z oferowanymi nowościami na możliwie wczesnym etapie ich istnienia. Warto od razu zaznaczyć, że główne kierunki rozwoju SQL Server 2008 nie ulegają istotnym zmianom w stosunku do wersji SQL Server 2005. Nadal będzie to zintegrowany z CLR rozbudowany system zarządzania bazami danych, stanowiący jednocześnie platformę business intelligence. Jeżeli zaś mowa o różnicach i ulepszeniach, to nowe wydanie cechować będzie m.in. większe bezpieczeństwo, większą skalowalność, nowe funkcjonalności wysokiej dostępności (ang. high availability) oraz lepsza wydajność. Katmai ma być urzeczywistnieniem wizji platformy danych, która kładzie szczególny nacisk na 4 kluczowe obszary:

• krytyczne znaczenie danych (platforma typu mission-critical),
• dynamiczne tworzenie aplikacji i rozwiązań bazodanowych,
• przekraczanie granic danych relacyjnych,
• skalowalne i efektywne rozwiązanie typu business intelligence.

 Do początku strony

Nowe funkcjonalności

Główne nowości zaimplementowane w czerwcowym CTP to:

• rozszerzenia języka Transact-SQL:
  • polecenie MERGE,
  • szybka inicjalizacja zmiennych,
  • wielowierszowa konstrukcja polecenia INSERT,
  • GROUPING SETS – rozszerzenie klauzuli GROUP BY,
• tablicowy typ danych,
• zmiany w optymalizatorze kwerend:
  • optymalizacja zapytań zawierających złączenia w schemacie gwiazdy (hurtownie danych),
  • hint FORCESEEK,
• mechanizm Change Data Capture (CDC),
• mechanizm Declarative Management Framework (DMF),
• nowe systemowe widoki dynamiczne (DMV).

Rys. 1. Kluczowe nowe funkcjonalności w SQL Server 2008 June CTP.

 Do początku strony

Polecenie MERGE

Polecenie MERGE umożliwia jednoczesną realizację odmiennych operacji (INSERT, UPDATE, DELETE) dla różnych wierszy, w zależności od określonego warunku, za pomocą jednej konstrukcji składniowej. MERGE zostało wprowadzone przez standard SQL:2003 jako piąte polecenie DML, aby efektywnie połączyć kilkuetapowy proces manipulacji danych w jedną spójną operację. Implementacja MERGE w SQL Server 2008 June CTP jest nawet bardziej rozbudowana, niż przewiduje sam standard. Potencjalnie dostarcza więc zupełnie nowych możliwości kreowania wydajnego i przejrzystego kodu Transact-SQL. Proste polecenie MERGE może wyglądać np. tak:

MERGE INTO dbo.Quantities Q

USING dbo.Transactions T ON T.ProductID = Q.ProductID

WHEN MATCHED 

    THEN UPDATE SET Q.Quantity = Q.Quantity + T.Quantity

WHEN NOT MATCHED 

    THEN INSERT (ProductID, Quantity) VALUES (T.ProductID, T.Quantity);

Alternatywą dla powyższego zapytania byłyby dwie operacje: UPDATE dla produktów (ProductID) już istniejących w tabeli z ilościami (Quantities) oraz INSERT dla produktów nieistniejących w tabeli Quantities. Siłą rzeczy do zrealizowania dwuoperacyjnego scenariusza potrzebne będą dwie oddzielne operacje złączenia tabel (w przypadku UPDATE – INNER JOIN, a przypadku INSERT – OUTER JOIN). Polecenie MERGE będzie w tym przypadku wydajniejsze, gdyż tabele będą łączone tylko jeden raz. W omawianym scenariuszu różnica szybkości działania między MERGE a dwuetapowym UPDATE+INSERT wynosić może nawet kilkadziesiąt procent na korzyść MERGE – nie mówiąc już o minimalizacji ilości operacji IO.

 Do początku strony

Szybka inicjalizacja zmiennych

Język Transact-SQL od swoich początków zakładał niepołączalność operacji deklaracji i inicjalizacji zmiennych. Programiści musieli kodować dwa polecenia, aby nowo zadeklarowanej zmiennej przypisać jakąś wartość – jedynie w prototypach procedur składowanych możliwe było natychmiastowe nadanie parametrom ich wartości domyślnych. Katmai umożliwia tzw. szybką inicjalizację zmiennych, czyli połączenie deklaracji i inicjalizacji (przypisania) w jednej operacji, co zostało zademonstrowane w poniższym przykładzie:

DECLARE @variable int = 2;

Możliwość ta z pozoru nie wydaje się szczególnie istotna, ma jednak bardzo duże znaczenie praktyczne - prowadzi do zmniejszenia ilości niepotrzebnego kodu przy jednoczesnym zwiększeniu jego przejrzystości.

 Do początku strony

Wielowierszowa konstrukcja polecenia INSERT

Do tej pory w Transact-SQL brakowało konstrukcji składniowej znanej z innych silników bazodanowych, która pozwalała za pomocą jednej instrukcji INSERT wstawić do tabeli kilka wierszy o wartościach kolumn enumeratywnie zapisanych w kodzie. Oczywiście istniały mniej lub bardziej sprytne sposoby (m.in. z użyciem poleceń SELECT w unii), jednak przy większej liczbie unii (około 1000) procesor kwerend zaczynał się zachowywać mało wydajnie. W omawianym wariancie konstrukcji INSERT wartości definiujące poszczególne wiersze należy umieścić po klauzuli VALUES w nawiasach oddzielonych przecinkiem, np.:

INSERT INTO table (colA, colB, colC) VALUES (1, 2, 3), (4, 5, 6);

 Do początku strony

GROUPING SETS – rozszerzenie klauzuli GROUP BY

Opcja GROUPING SETS pozwala na zdefiniowanie w jednym zapytaniu kilku zestawów kolumn, po których następuje grupowanie. Stanowi ekwiwalent dla szeregu zapytań z różnym grupowaniem, połączonych operatorem UNION ALL. Zgodnie z zasadą, że przykład wart jest tysiąca słów, zasada działania GROUPING SETS zostanie poniżej zilustrowana w hipotetycznym scenariuszu. Dana jest tabela Cities o następującej zawartości:

GROUPING SETS będzie doskonałym wyborem do realizacji zapytania, które ma zwrócić liczbę miast na każdym poziomie geograficznym (kraju, kontynentu i dla pewności - planety). Wspomniane zapytanie wygląda następująco:

SELECT Planet, Continent, Country, Count(*) as CitiesCount

FROM Cities

GROUP BY GROUPING SETS (Planet, Continent, Country)

Powyższa kwerenda zwróci następujący rezultat:

Trzeba przyznać, że przy zastosowaniu GROUPING SETS wynikowy zbiór rekordów nie powstaje zasadniczo wydajniej, niż w metodzie z zastosowaniem kilku zapytań połączonych przez UNION ALL. Omawiane rozszerzenie klauzuli GROUP BY znacznie upraszcza jednak składnię, zmniejszając kilkukrotnie ilość kodu niezbędnego do zrealizowania zadania.

 Do początku strony

Tablicowy typ danych

Jedną z ciekawych nowości w June CTP jest obsługa tablicowego typu danych. Jest to złożony typ danych o zdefiniowanej strukturze, będący analogią tabeli. Nie dość więc, że może zawierać kilka pól, może także przechowywać kilka wierszy. Trzeba jednak pamiętać, że tablicowe typy danych są typami definiowanymi przez użytkownika i jako takie nie mogą być użyte do tworzenia kolumn w tabeli. Typ tablicowy świetnie nadaje się natomiast do wszelkiego rodzaju parametrów. Dzięki niemu łatwo przekazać w parametrze do procedury dane o strukturze wielowymiarowej, czyli mówiąc wprost – tabelę. Przykładowe użycie typu tablicowego demonstruje poniższy fragment kodu Transact-SQL:

CREATE TYPE Person AS TABLE

(

    FirstName nvarchar(50) NOT NULL,

    LastName nvarchar(50) NOT NULL,

    YearOfBrith int NULL

);

GO



CREATE PROCEDURE tableval_proc

@person Person READONLY

AS

SELECT * FROM @person;

GO



DECLARE @people AS Person; -- "AS" jest opcjonalne

INSERT INTO @people 

VALUES ('John', 'Dow', 1950), ('Jan', 'Kowalski', 1960);



EXEC tableval_proc @people;

Zmienna typu tablicowego w rzeczywistości jest obiektem w bazie tempdb. Różnica między zmiennymi o typie tablicowym, a zmiennymi tabelarycznymi, polega zasadniczo wyłącznie na tym, że każdorazowo podczas deklaracji zmiennej tabelarycznej trzeba zdefiniować strukturę danych, natomiast w zmiennych typu tablicowych wskazuje się jedynie na stworzony wcześniej typ.

 Do początku strony

Optymalizacja zapytań zawierających złączenia w schemacie gwiazdy

Schemat gwiazdy jest bardzo często stosowany w klasycznych hurtowniach danych. Zakłada on istnienie tabeli faktów (dane analizowane) otoczonej tabelami wymiarów (zawierających dane klasyfikacyjne umożliwiające agregację faktów). Dla powstania wartościowej informacji konieczne jest więc połączenie danych z tabeli faktów i najczęściej z kilku tabel wymiarów. Biorąc pod uwagę, że w tabeli faktów mogą znajdować się setki milionów rekordów, JOIN między tabelą faktów a kilkoma tabelami wymiarów jest dla silnika bazodanowego sporym wyzwaniem. Aby zoptymalizować tego typu połączenia, optymalizator kwerend w Katmai może zastosować technikę filtrowania bitmapowego (ang. bitmap filtering) podczas realizacji kosztownych złączeń.

Filtrowanie bitmapowe nie jest pomysłem nowym - w ograniczonym zakresie było wykorzystywane wewnętrznie przez procesor kwerend już w wersji SQL Server 7.0. SQL Server 2008 znacznie ulepsza wykorzystanie tej techniki umożliwiając jej automatyczne zastosowanie do optymalizacji kosztownych zapytań w schemacie gwiazdy. Filtrowanie bitmapowe ma doprowadzić do sytuacji, że operacji łączenia od strony tabeli faktów podlegać będą wyłącznie rekordy, które do takiej operacji się kwalifikują. Chodzi więc o usunięcie rekordów niepotrzebnych na jak najwcześniejszym etapie, czyli już na etapie odczytu rekordów z tabeli faktów. Bez filtrowania bitmapowego z tabeli faktów do operacji łączenia zwrócone zostaną wszystkie wiersze i dopiero na podstawie warunku złączenia (INNER JOIN ON ..) odrzucone zostaną fakty niepotrzebne. Filtrowanie bitmapowe opiera się na tworzeniu w pamięci dynamicznych struktur, które przechowują reprezentację wierszy tabel faktów i tabel wymiarów (tzw. wektory bitowe) z punktu widzenia wartości kolumn uczestniczących w złączeniu. Operacja tworzenia filtra bitmapowego odbywa się w bardzo szybki sposób i nie stanowi istotnego kosztu zapytania.

 Do początku strony

Hint FORCESEEK

Stosowany na poziomie tabeli hint optymalizacyjny FORCESEEK powoduje, że jako ścieżka dostępu do danych wykorzystywana jest wyłącznie operacja wyszukiwania w indeksie (tzw. Index Seek). Hint ten może być używany w poleceniach SELECT, a także w klauzuli FROM poleceń UPDATE i DELETE. Jeżeli na tabeli oznaczonej hintem FORCESEEK nie istnieje żaden indeks, przy użyciu którego można dotrzeć do wartości żądanych kolumn, zapytanie zwróci błąd 8622 („Procesor kwerend nie był w stanie wygenerować planu wykonania z powodu hintów zdefiniowanych w zapytaniu”). Trzeba pamiętać, że wyszukiwanie w indeksie nie zawsze stanowi najwydajniejszą metodę realizacji zapytania. W niektórych przypadkach może generować liczbę operacji IO o rząd wielkości większą niż skanowanie tabeli (tzw. Table Scan). W SQL Server 2008 nadal prawdziwe jest twierdzenie, że hintów optymalizacyjnych należy używać z umiarem i tylko w sytuacjach, w których standardowa optymalizacja nie daje zadowalających efektów.

 Do początku strony

Mechanizm Change Data Capture (CDC)

Kolejną ciekawą nowością w June CTP jest mechanizm Change Data Capture. CDC pozwala śledzić zmiany w tabelach wywoływane operacjami DML (INSERT, UPDATE, DELETE). Po włączeniu przechwytywania zmian danych dla określonych tabel specjalny proces przechwytujący (tzw. CDC Collection Agent) automatycznie odczyta z logu transakcyjnego stosowne informacje i umieści je w odpowiedniej formie w uprzednio utworzonej tabeli zmian. Użytkownicy czytają informacje o zmianach bezpośrednio z tabeli zmian lub przy użyciu specjalnych funkcji systemowych. Wszystkie obiekty związane z mechanizmem CDC znajdują się w schemacie cdc w bazie danych, dla której omawiany mechanizm został włączony (domyślnie CDC jest dla wszystkich baz wyłączone). Change Data Capture działa w sposób analogiczny jak replikacja transakcyjna – specjalne zadanie (ang. job) pobiera zmiany z loga transakcyjnego i umieszcza je w zdefiniowanej strukturze.

Najprostsza konfiguracja CDC dla tabeli Cities (tabela z przykładu dotyczącego GROUPING SETS) wygląda następująco:

USE some_database;

GO



-- włączenie CDC dla bieżącej bazy

EXEC sys.sp_cdc_enable_db_change_data_capture;



-- włączenie przechwytywania zmian dla tabeli dbo.Cities

EXECUTE sys.sp_cdc_enable_table_change_data_capture 

    @source_schema = N'dbo',

    @source_name = N'Cities',

    @role_name = N'cdcAdmins';

Powyższy kod utworzy dwa joby odpowiedzialne za przechwytywanie zmian – od tego momentu mechanizm jest gotowy do użycia. W celach demonstracyjnych na tabeli zostały wykonane 3 operacje: INSERT nowego rekordu, UPDATE tegoż rekordu oraz jego usunięcie przy wykorzystaniu polecenia DELETE:

INSERT INTO Cities (City, Country, Continent, Planet)

VALUES ('Paris', 'Spain', 'Europe', 'Earch');



UPDATE Cities SET Country = 'France' WHERE City = 'Paris';



DELETE FROM Cities WHERE City = 'Paris';

Powyższe operacje zostały zapisane w tabeli zmian (cdc.dbo_Cities_CT) w następujący sposób:

Operacja INSERT spowodowała dodanie do tabeli zmian jednego rekordu z wartościami kolumn podanymi w klauzuli VALUES. Operacja UPDATE wywołała z kolei umieszczenie w cdc.dbo_Cities_CT dwóch rekordów – jednego z wartościami kolumn sprzed zmiany oraz jednego z wartościami kolumn po zmianie. Usunięcie rekordu powoduje oczywiście dodanie do tabeli zmian jednego rekordu z wartościami kolumn z momentu usunięcia. Rodzaj operacji, która spowodowała dodanie rekordu do tabeli zmian, można rozróżnić za pomocą wartości w kolumnie __$operation. Dzięki specjalnym funkcjom możliwe jest odpytywanie tabel zmian w oparciu o przedział czasowy.

 Do początku strony

Mechanizm Declarative Management Framework (DMF)

Declarative Management Framework to kolejna nowość zaimplementowana w June CTP, z której DBA powinni być szczególnie zadowoleni. DMF jest systemem zarządzania instancjami SQL Server 2008, opartym na definiowaniu polityk. Polityki pozwalają na zarządzanie serwerem, bazami danych, tabelami, a także obiektami typu indeks czy login poprzez wybór zestawu reguł modelujących zachowanie lub charakterystykę określonego obiektu (tzw. DMF management facet) oraz określenie warunków determinujących dopuszczalne stany danego obiektu (tzw. DMF condition). Dana polityka definiuje także tryb działania w sytuacji przekroczenia ustawionych warunków – może wyłącznie dokonywać ich sprawdzenia (okresowo lub zdarzeniowo) lub uniemożliwić określone działanie. Polityki przechowywane są w bazie msdb, a zarządzanie nimi odbywa się przez SQL Server Management Studio. Declarative Management Framework to potężne narzędzie w rękach administratorów baz danych, które pozwala im na zarządzanie całą platformą SQL Server na wyższym niż dotychczas poziomie abstrakcji. DBA może przykładowo stworzyć politykę wymuszającą określoną konwencję nazewniczą obiektów we wszystkich bazach danych, może też uniemożliwić modyfikację określonych elementów konfiguracji serwera.

 Do początku strony

Nowe systemowe widoki dynamiczne (DMV)

SQL Server 2008 wprowadza pięć nowych widoków DMV:

• sys.dm_os_memory_brokers,
• sys.dm_os_memory_nodes,
• sys.dm_os_nodes,
• sys.dm_os_process_memory,
• sys.dm_os_sys_memory.

Wszystkie wyżej wymienione widoki systemowe informują o sposobie wykorzystania i zarządzania pamięcią przez SQL Server i system operacyjny. W określonych sytuacjach mogą być wykorzystane do badania problemów wydajnościowych.

 Do początku strony

Podsumowanie

Czerwcowa wersja CTP to dopiero pierwszy krok do docelowego zestawu funkcjonalności, jakie mają się pojawić w SQL Server 2008. Doskonale pokazuje jednak kierunki, w których podążają deweloperzy z SQL Server Team’u. W artykule zaprezentowałem przegląd nowych mechanizmów, które znalazły się w pierwszej publicznej wersji CTP SQL Server Katmai. Należą do nich zarówno rozszerzenia języka Transact-SQL – np. nowe polecenie MERGE, rozszerzenia klauzuli GROUP BY, czy szybka inicjalizacja zmiennych, jak również nowe własności optymalizatora kwerend, czy nie występujące we wcześniejszych wersjach typy danych i mechanizmy audytowe. Na zakończenie chciałbym podziękować Pawłowi Potasińskiemu, który zaproponował temat tego artykułu i poczynił cenne sugestie co do jego merytorycznej treści.

Marcin Guzowski Zawodowo zajmuje się inżynierią oprogramowania, w szczególności rozwiązaniami data-centric. Obecnie jest szefem zespołu pracującego nad usługami inteligentnej automatyzacji danych (m.in. deduplikacji, standaryzacji, kojarzenia danych podobnych) w Dziale Badawczo-Rozwojowym w firmie Anica System S.A. Praktycznie od początku swojej przygody z bazami danych intensywnie wykorzystuje SQL Server jako podstawową platformę bazodanową, także do zastosowań stosunkowo nietypowych i wymagających maksymalizacji wydajności działania.

Do początku strony