Microsoft SQL Server 2008     Co nowego w silniku bazodanowym SQL Server – February CTP, cz. II

Co nowego w silniku bazodanowym SQL Server – February CTP, cz. I Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 15 kwietnia 2008

Zawartość strony
 Wstęp   Wstęp
 Sparse Columns oraz Columns Sets   Sparse Columns oraz Columns Sets
 Integracja z PowerShell   Integracja z PowerShell
 Kompresja w aparacie składowania danych (dla tablic oraz indeksów)   Kompresja w aparacie składowania danych (dla tablic oraz indeksów)
 Filtrowane indeksy oraz statystyki   Filtrowane indeksy oraz statystyki

Wstęp

Kolejna wersja testowa systemu SQL Server 2008 (oznaczona jako CTP6) ukazała się w lutym. Nowych oraz ulepszonych funkcjonalności jest co najmniej tyle, ile otrzymaliśmy w poprzedniej wersji systemu (w listopadzie 2007 r.). Najciekawszymi nowościami są zmiany w aparacie składowania danych, integracja z PowerShell oraz nowy, ulepszony audyt baz danych.

Uwaga

Microsoft SQL Server 2008 February CTP jest rozwojową wersją systemu. Firma Microsoft nie gwarantuje, że opisane w artykule funkcjonalności znajdą się w wersji finalnej systemu w takiej postaci, jak to przedstawiono w tekście. Nie ma również gwarancji, że funkcjonalności te w ogóle znajdą się w wersji finalnej.

 Do początku strony Do początku strony

Sparse Columns oraz Columns Sets

Sparse Columns są to zwykłe kolumny, dla których zoptymalizowano sposób przechowywania wartości NULL. Taka kolumna redukuje wymagania składowania dla wartości NULL kosztem niewielkiego narzutu miejsca na wartości nienullowe. Kolumna powinna zostać zadeklarowana jako sparse, jeżeli zaoszczędzi to od 20 do 40% miejsca na stronie danych. Kolumny typu sparse oraz tzw. zestawy kolumn (columns sets) są określane w poleceniach CREATE TABLE lub ALTER TABLE.

CREATE TABLE Tablica

(

i int SPARSE NULL

,b char(100) SPARSE NULL 

,kol_cs xml column_set FOR ALL_SPARSE_COLUMNS

)

GO

Cechą charakterystyczną sparse columns jest to, iż przechowanie wartości NULL nic nie kosztuje (nie konsumuje żadnego miejsca na stronie). Dla pewnych typów danych, takich jak:

  1. geography
  2. geometry
  3. image
  4. ntext
  5. text
  6. timestamp
  7. typów definiowanych przez użytkownika (UDT)

nie można definiować kolumn z atrybutem sparse.

Kolumny typu sparse wymagaja jednak nieco więcej miejsca dla przechowania wartości nienullowych w porównaniu z sytuacją, gdy kolumna jest zdefiniowana bez tego atrybutu. Narzut ten jest zależny od bezpośrednio od typu danych, jak pokazano w poniższej tabeli. Dodatkowo, w kolumnie ‘% wartości NULL’ zawarto informację, ile musi być wierszy z wartościami NULL, aby zaoszczędzić 40% miejsca.

Typ danych Liczba bajtów dla definicji bez atrybutu sparse Liczba bajtów dla kolumny typu sparse %wartości NULL Uwagi
bit 0.125 4.125 98 Stały rozmiar
int 4 8 64 Stały rozmiar
money 8 12 52 Stały rozmiar
datetime2(0) 10 6 57 Zmienny rozmiar
Datetime2(7) 12 8 52 Zmienny rozmiar
xml 4 + dane 2 + dane 60 Zależy od danych
varchar lub char 4 + dane 2 + dane 60 Zależy od danych

Z ciekawszych informacji na temat tego typu kolumn należy zapamiętać, iż nie mogą posiadać wartości domyślnej (DEFAULT) oraz nie mogą być częścią indeksu zgrupowanego lub opartego na kluczu głównym.

Tablica zawierająca kolumny typu sparse może zawierać specjalną kolumnę w formacie XML (columns set) i za jej pomoca zwracać wartości wszystkich kolumn zdefiniowanych jako sparse. Kolumnę columns sets można traktować jako kolumnę wyliczaną (calculated), ponieważ nie jest ona fizycznie przechowywana w tabeli. Z drugiej jednak strony można dokonywać na niej np. operacji UPDATE, co spowoduje wykonanie tych operacji na tworzących ją kolumnach typu sparse.

Użycie tej kolumy należy rozpatrzyć, gdy w danej tablicy uywanych jest wiele kolumn typu sparse i operowanie na nich pojedynczo nastręczałoby wiele kłopotu. W takim przypadku można zauważyć wzrost wydajności, jeżeli operacje INSERT oraz UPDATE będą przeprowadzone przez columns sets. Należy jednak pamiętać, iż w przypadku, gdy dana tabela ma zdefiniowanych wiele indeksów na kolumnach sparse to operacje z wykorzystaniem columns sets obniżą wydajność, ze względu na zapotrzebowanie na pamięć potrzebną do wykonania tych operacji.

Poniżej zaprezentowane zostanie kilka przykładów, które pozwolą zorientować się w omawianej funkcjonalności. W przykładach tych założyłem, iż istnieje tablica, której definicję podałem powyżej. Wobec tego, można dokonać operacji INSERT:

INSERT Tablica(kol_cs) 

VALUES ('<i>1</i><b>znaki</b>')

,('<i>2</i><b>wiersz 2</b>')

,('<i>3</i><b/>')

,('<i/><b>wiersz 4</b>')

GO

Z definicji, kolumna kol_cs przechowuje informacje o wszystkich kolumnach typu sparse dla zawartych w tablicy. Można sprawdzić, co zostało faktycznie zapisane w kolumnach i oraz b wykonująć zapytanie:

SELECT *,i,b FROM Tablica

GO

W wyniku uruchomienia powyższego fragmentu kodu otrzymamy:

Rysunek 1 Wynik wykonania operacji INSERT na kolumnie typu column set.

Wartości w kolumnach typu sparse mogą być aktualizowane na dwa sposoby – w sposób tradycyjny:

UPDATE Tablica

SET b=’a’

WHERE i=1

oraz wykorzystując zdefiniowany Columns sets

UPDATE Tablica

SET kol_cs=’<i>100</i>’

WHERE b=’wiersz 2’

Proponuję czytelnikowi, aby samodzielne sprawdził, jakie różnice powoduje wykonanie tej operacji DML w obydwu przypadkach.

Podsumowując wątek sparse columns można stwierdzić, iż łącząc tę funkcjonalność z omawianymi columns sets oraz fltered indexes, o których będzie mowa w następnej części artykułu, można w takich aplikacjach jak np. Windows Sharepoint Services lepiej przechowywać oraz szybciej uzyskiwać dostęp do danych za pomocą SQL Server 2008.

 Do początku strony Do początku strony

Integracja z PowerShell

SQL Server 2008 February CTP umożliwia korzystanie z konsoli PowerShell, która pozwala administatorom i deweloperom zautomatyzować administrację serwera oraz instalowanie (deployment) aplikacji. Składnia języka PowerShell jest dużo bardziej rozbudowana i umożliwia wykonywanie znacznie bardziej skomplikowanych logicznie poleceń aniżeli pozwala na to język T-SQL [1]. Skrypty PowerShell mogą być także używane do administrowania innymi serwerami firmy Microsoft, co daje administartorom (wreszcie) jeden wspólny język skryptowania. Konsola PowerShell jest instalowana wraz ze wszystkimi składnikami podczas instalowania serwera baz danych (o ile oczywiście nie została zainstalowana wcześniej). Po zakończeniu instalacji SQL Server 2008 umożliwia pracę z konsolą PowerShell na dwa sposoby:

  1. za pomocą providera SQL Server, który umożliwia prosty mechanizm nawigacji podobny do ścieżek w systemie plików. Można także używać takich komend jak cd, dir, ren lub del do nawigacji lub wykonywania innych akcji na obiektach SQL Server tak, jak to czyni się w oknie komend.
  2. poprzez tzw. cmdlets, które są komendami używanymi w skryptach PowerShell do określania akcji SQL Server. Cmdlets wspierają takie akcje jak uruchamianie narzędzia sqlcmd zawierającego komendy T-SQL lub XQuery

SQL Server 2008 pozwala na uruchomienie sesji PowerShell wprost z drzewa eksploratora obiektów (Object Explorer) w SQL Server Management Studio (SSMS):

Rysunek 2 Uruchomienie sesji PowerShell z eksploratora obiektów w SQL Server Management Studio.

SQL Server Management Studio uruchamia konsolę PowerShell w kontekście obiektu, który został wskazany w oknie Object Explorer:

Rysunek 3 Konsola PowerShell

Podobnie ma się sprawa dla usługi SQL Server Agent, ponieważ istnieje możliwość uruchomienia skryptów PowerShell dla wybranych kroków tworzących joby. Każdy krok zawarty w jobie jest skojarzony z pewnym podsystemem, który implementuje określone środowisko, jak np. agent replikacji. W podobny sposób zaimplementowano możliwość uruchamiana skryptów PowerShell.

SQL Server 2008 dostarcza także narzędzie (sqlps), które jest odpowiedzialne za utworzenie środowiska oraz zarejestrowanie i uruchomienie konsoli PowerShell w SQL Server. Za pomocą sqlps można uruchamiać skrypty PowerShell, skrypty cmdlets oraz nawigować po obiektach instancji serwera baz danych. Dokładne informacje jak uruchamiać skrypty PowerShell za pomocą narzędzia sqlps można znaleźć w Books Online oraz na stronach internetowych.

Pozostaje do odszyfrowania jeszcze jeden termin, dla którego trudno znaleźć adekwatne tłumaczenie na język polski – cmdlet. Jest to komenda w formacie <czasownik-rzeczownik> , a jej przykładami mogą być:

  1. Get-Hep
  2. Set-MachineName
  3. Encode-SqlName

Poniżej znajduje się kilka przykładów zastosowania konsoli PowerShell z SSMS:

Rysunek 4 Użycie komendy dir do pobrania informacji o obiektach wchodzących w skład tablicy Person.Contact.

Rysunek 5 Wyświetlenie informacji o strukturze tablicy Person.Contacts.

Rysunek 6 Wyświetlenie informacji o indeksach dla tablicy Person.Contact (górny zapis) oraz wyświetlenie informacji o kolumnach indeksu zgrupowanego dla tej tablicy.

 Do początku strony Do początku strony

Kompresja w aparacie składowania danych (dla tablic oraz indeksów)

SQL Server CTP6 wspiera zarówno kompresję wierszy jak i stron danych, zarówno dla tabel jaki i dla indeksów. Zgodnie z dostarczoną dokumentacją kompresja danych może być skonfigurowana dla następujących obiektów:

  1. tablicy, która jest przechowywana jako sterta
  2. tablicy, która jest przechowywana jako indeks zgrupowany
  3. indeksu niezgrupowanego
  4. widoku indeksowanego
  5. partycjonowanej tablicy lub indeksu – w tym przypadku opcje kompresji mogą być skonfigurowane dla każdej partycji osobno i nie wszystkie partycje muszą zostać poddane kompresji.

Kompresją w aparacie składowania danych rządzi kilka prostych reguł, które warto zapamiętać:

  1. ustawienia opcji kompresji na tablicy nie powodują przeniesienia tej opcji na indeksy niezgrupowane związane z daną tablicą
  2. nie można kompresować tablic systemowych
  3. tablice i indeksy mogą być kompresowane już na etapie ich tworzenia – odpowiednią opcję wybiera się w poleceniu CREATE TABLE lub CREATE INDEX
  4. zmiana typu kompresji dla tablicy lub indeksu odbywa się za pomocą polecenia ALTER TABLE lub ALTER INDEX
  5. stosowanie kompresji pozwala na przechowanie większej liczby wierszy na stronie danych, ale nie zmienia rozmiaru strony.

Jak wspomniałem na wstępie, kompresja może być implementowana dwojako – jako kompresja wiersza lub strony danych. Domyślnym trybem jest brak kompresji. Poniżej przedstawione fragmenty kodu pokazują, w jaki sposób można zadeklarować odpowiedni model kompresji:

     1.  dla kompresji wierszy w tablicy

CREATE TABLE Tablica1

(

i int

,c char(10)

)

WITH (DATA_COMPRESSION=ROW)

     2.  dla kompresji strony danych w tablicy

CREATE TABLE Tablica2

(

i int

,c char(10)

)

WITH (DATA_COMPRESSION=PAGE)

     3.  dla kompresji indeksu niezgrupowanego (porównaj z punktem a powyżej – indeks posiada inną opcję kompresji zawartych w nim danych aniżeli tablica źródłowa)

CREATE NONCLUSTERED INDEX IX_NCL_TAB1_C

ON Tablica1 ( C )

WITH (DATA_COMPRESSION=PAGE)

     4.  dla tablicy partycjonowanej – każda partycja może miec wybraną inna opcję kompresji danych. Zakładając, że tablica jest partycjonowana zgodnie ze schematem schPart i posiada 4 partycje, to jej definicję można napisać następująco:

CREATE TABLE partTable

(

i int

,c char(10)

)

ON schPart (i)

WITH 

(

DATA_COMPRESSION = ROW ON PARTITIONS (1),

DATA_COMPRESSION = PAGE ON PARTITIONS (2 ,4),

DATA_COMPRESSION = NONE ON PARTITIONS(3)    

);

W ostatnim przypadku kompresja na poziomie wierszy zostanie zastosowana dla partycji nr 1 i odpowiednio – kompresja stron na partycjach 2 oraz 4; brak kompresji będzie obowiązywał dla partycji nr 3.

Na zakończenie wstępnego omawiania tematu związanego z kompresją tablic i indeksów warto wspomnieć o jeszcze jednej rzeczy. Jeżeli istniejące dane są pofragmentowane to redukcja ich rozmiaru powinna być dokonana przez przebudowanie indeksu a nie użycie kompresji.

Ostatnią ciekawą nowością ściśle związaną z kompresją danych jest procedura sp_estimate_data_compression_savings. Za pomoca tej procedury można dowiedzieć się, jaki jest aktualny rozmiar tabeli oraz jaki będzie jej przewidywalny rozmiar po zastosowaniu innej opcji kompresji danych. W przypadku, gdy partycja jest już skompresowana to wynikiem uruchomienia procedury będzie informacja o rozmiarze tablicy bez kompresji.

Dla estymacji rozmiaru obiektu wykorzystuje się bazę danych tempdb.

Po wybraniu opcji kompresji dane ładowane są z obiektu źródłowego do bazy danych tempdb i tam kompresowane.

Przykład uruchomienia procedury sp_estimate_data_compression_savings oraz uzyskane za jej pomoca wyniki przedstawiono poniżej:

use AdventureWorks

GO

EXEC  sp_estimate_data_compression_savings ‘Sales’, ‘SalesOrderDetail’, NULL, NULL, ‘PAGE’



[object_name]       [index_id]  [size with          [size with

                    current compression [kB]]   requested compression [kB]]

SalesOrderDetail       1        9904                4856

SalesOrderDetail       2        3280                3936

SalesOrderDetail       3        1824                1464

Analizując otrzymane wyniki mozna zauwazyć, iż indeks zgrupowany (1) zostanie skompresowany o ponad 50%, zmniejszy się również objętość jednego z indeksów niezgrupowanych (3), natomiast drugi z nich powiekszy się o nieco ponad 20%. Wynika stąd, iż kompresja danych rzeczywiście jest wydajna, ale nie może być stosowana automatycznie dla wszystkich obiektów (indeksów) danej tablicy.

 Do początku strony Do początku strony

Filtrowane indeksy oraz statystyki

Indeks filtrowany jest zoptymalizowanym indeksem niezgrupowanym, który najlepiej sprawdza się dla zapytań pokrywających, dla których dobrze zdefiniowano podzbiór danych. Dobrze skonstruowany indeks filtrowany może znacząco poprawić wydajność zapytań oraz zredukować koszty związane zarówno z jego obsługą (rozumianą w szerszym zakresie, nie sprowadzającą się tylko do sprawdzania poziomu fragmentacji indeksu, ale także operacji nakładanych na silnik baz danych i związanych np. z aktualizacją statystyk) oraz zajmowanym miejscem na dysku. Poprawa wydajności zapytań związana jest faktem korzystania przez filtrowany indeks także z tzw. filtrowanych statystyk, które z samej natury są bardziej dokładne od statystyk bazujących na informacji zawartej w całej tablicy, bowiem zawierają one tylko najbardziej nas interesujący wycinek danych. Zmniejszony koszt obsługi indeksu jest najbardziej zauważalny w momencie, kiedy liczba wierszy indeksu jest mała w porównaniu z liczbą wierszy zawartą w indeksie zgrupowanym. W przypadku, gdy indeks filtrowany zawiera większość wierszy z danej tablicy to koszt zarządzania indeksem jest większy od kosztu zarządzania indeksem zgrupowanym.

Utworzenie indeksu filtrowanego dla tablicy:

CREATE TABLE Pracownik

(

    ID              int

    ,imie           varchar(50)

    ,nazwisko           varchar(100)

    ,email          varchar(50)

    ,departamentID      int 

    ,miasto         varchar(50) SPARSE NULL

    ,ulica          varchar(50) SPARSE NULL

    ,kod_pocztowy       varchar(50) SPARSE NULL

)

Wygląda następująco:

CREATE NONCLUSTERED INDEX IX_NCL_FI_DepartamentHR

ON Pracownik (Nazwisko,Imie)

WHERE departamentID = 4

Wtedy dla zapytania jak poniżej optymalizator może rozważyć użycie indeksu filtrowanego.

SELECT nazwisko , imie  FROM Pracownik

WHERE departamentID=4

Indeks filtrowany nie może zostać utworzony na widokach (chociaż przyglądając się definicji indeksu filtrowanego trudno w pierwszym momencie się temu oprzeć), jednakże optymalizator zapytań będzie nadal brał go pod uwagę w sytuacji, gdy taki indeks będzie utworzony na tabeli, z której dany widok korzysta. Indeksy filtrowane doskonale nadają się do współpracy z inną omawianą wcześniej w artykule nowością – sparse columns, zwłaszcza w przypadku, gdy w takiej kolumnie jest tylko kilka nienullowych wartości.

Filtrowane statystyki są zoptymalizowanymi statystykami, które poprawiają dokładność statystyk dla zapytań skonstruowanych na dobrze zdefiniowanym podzbiorze danych. Filtrowane statystyki używają – podobnie jak filtrowane indeksy - predykatu do określenia podzbioru danych, który będzie zawarty w tej statystyce. Filtrowane statystyki mogą poprawić jakość planu zapytań w następujących sytuacjach:

  1. dla zapytań korzystających z indeksu filtrowanego – dla takiego indeksu aparat SQL Server 2008 automatycznie tworzy i zarządza odpowiednimi statystykami z właściwym predykatem. Tego typu statystyki są zdecydowanie bardziej dokładne od statystyk opartych na danych z całej tabeli,
  2. dla zapytań na podzbiorze danych zawierających kolumny nieindeksowane – dla przypadku, gdy do określonej kolumny nie odwołuje się żaden indeks a kolumna jest wywoływana w zapytaniu, to silnik baz danych może utworzyć dla niej pełnotablicowe statystyki. Gdyby taka kolumna zawierała podzbiór danych o unikalnym rozkładzie to z powodzeniem, poprawiając wydajność wykonywania zapytań, można utworzyć na niej filtrowane statystyki,
  3. dla zapytań pobierających dane ze skorelowanych kolumn – w przypadku, gdy zapytanie wybiera dane z nieindeksowanej kolumny, która jest skorelowana z wartościami w innej kolumnie, a liczba odwołań do nieindeksowanych kolumn jest mała, to wydajność filtrowana indeksu nie będzie zadowalająca, z kolei filtrowane statystyki będą bardzo przydatne.

Przykład utworzonej statystyki filtrowanej znajduje się poniżej:

CREATE STATISTICS IX_NCL_FI_DepartamentHR(Filtered)

ON Pracownik (Nazwisko,Imie)

WHERE departamentID = 4

 Do początku strony Do początku strony


  Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.
 Do początku strony Do początku strony

Microsoft SQL Server 2008     Co nowego w silniku bazodanowym SQL Server – February CTP, cz. II