Akademia SQL - Część 14: Studio monitoringu     Akademia SQL     Akademia SQL - Część 16: Partycjonowanie tabel – informacje podstawowe

Akademia SQL - Część 15: Optymalizator zapytań - nowości Udostępnij na: Facebook

Autor: Damian Widera

Opublikowano: 5 lutego 2009

Zawartość strony
 Nowości związane z optymalizatorem zapytań – informacje podstawowe   Nowości związane z optymalizatorem zapytań – informacje podstawowe
 Nowości związane z optymalizatorem zapytań – laboratorium   Nowości związane z optymalizatorem zapytań – laboratorium
 Zadanie 1   Zadanie 1
 Zadanie 2   Zadanie 2
 Zadanie 3   Zadanie 3
 Nowości związane z optymalizatorem zapytań – referencje   Nowości związane z optymalizatorem zapytań – referencje

Nowości związane z optymalizatorem zapytań – informacje podstawowe

Jednym z najważniejszych elementów wchodzących w skład SQL Server jest optymalizator zapytań. Jego głównym zadaniem jest odnalezienie optymalnego, czyli najmniej kosztownego, planu zapytania. Najnowsza wersja SQL Server 2008 posiada ulepszony (w stosunku do poprzednich wydań systemu) optymalizator zapytań. Dodano w nim między innymi możliwość korzystania z nowych wskazówek (ang. hints) podczas konstruowania zapytań:

  • Wskazówkę OPTIMIZE FOR UNKNOWN,
  • Wskazówkę FORCESEEK.

Znana z wcześniejszych wersji systemu wskazówka OPTIMIZE FOR w SQL Server 2008 została rozszerzona o nową klauzulę UNKNOWN, która pozwala użyć optymalizatorowi zapytań metod statystycznych zamiast aktualnej wartości parametru podczas optymalizacji zapytania. Wskazówka OPTIMIZE FOR UNKNOWN może być użyta dla wszystkich lokalnych zmiennych w zapytaniu.

Administratorzy SQL Server od dłuższego czasu mieli mozliwość proponowania konkretnego indeksu, który miał być użyty podczas realizacji konkretnego zapytania. Optymalizator nie gwarantował jednak, że taki indeks zostanie w praktyce użyty. Obecnie, dzięki nowej wskazówce FORCESEEK można także wymusić użycie operacji przeszukiwania indeksu podczas pobierania danych z tabeli. W praktyce zdarzało się, że w planie zapytania wygenerowanym przez optymalizator można było odnaleźć operację skanowania indeksu, a doświadczenie podpowiadało, że bardziej wydajna byłaby operacja przeszukiwania indeksu. Należy jednak pamiętać, że wskazówka FORCESEEK powinna być użyta przez administrtora po dokładnym przeanalizowaniu zapytania. Wynika to z faktu, że dane w tablicach zmieniają się i po pewnym czasie wymuszone na optymalizatorze zachowanie, czyli przeszukiwanie indeksu, może być operacją dużo bardziej kosztowną niż plan wygenerowany przez optymalizator.

W SQL Server 2008 zmienione także zostało zachowanie optymalizatora dla zapytań parametryzowanych. Pozwala to uprościć plany zapytań dla takich kwerend, a zwłaszcza zmniejszyć ich ilość w buforze planów. Administrator baz danych może wskazać optymalizatorowi sugerowany plan wykonania zapytania (ang. plan guide), który powinien być uwzględniony przy okazji wykonywania wybranej kwerendy lub wzorca kwerendy. Podczas wykonywania zapytania SQL Server 2008 odnajduje odpowiedni plan i stosuje go do pobierania danych.

Serwer SQL 2008 pozwala wskazać plan zapytania zapisany w pliku XML (za pomocą procedury sp_create_plan_guide, znanej z wcześniejszej wersji systemu) oraz używając nowej procedury składowanej sp_create_plan_guide_from_handle. Za jej pomocą można utworzyć jeden lub więcej sugerowanych planów wykonań zapytań w buforze planów. Procedura sp_create_plan_guide_from_handle wymaga podania trzech parametrów:

  1. @name, który określa nazwę planu wykonania zapytania,
  2. @plan\_handle, który jest identyfikatorem planu zapytania.
  3. @statement\_start\_offset, który wskazuje na pozycję zapytania w paczce, na przykład w procedurze składowanej, w której występuje kilka zapytań.

Oprócz procedury składowanej sp_create_plan_guide_from_handle administrator ma do dyspozycji tablicową funkcję sys.fn_validate_plan_guide, której zadaniem jest zweryfikować poprawność sugerowanego planu wykonania zapytania.

 Do początku strony Do początku strony

Nowości związane z optymalizatorem zapytań – laboratorium

Celem laboratorium jest praktyczne zaprezentowanie nowości związanych z optymalizatorem zapytań. Laboratorium składa się z trzech zadań, w których pokazane będzie:

  1. Użycie wskazówki OPTIMIZE FOR UNKNOWN
  2. Użycie wskazówki FORCESEEK
  3. Użycie sugerowanego planu zapytania.

Do wykonania przykładów potrzebna będzie baza danych AdventureWorks2008.

 Do początku strony Do początku strony

Zadanie 1

1. Otwórz SQL Server Mangement Studio (SSMS) i w nowym oknie zapytania wpisz następującą kwerendę, która będzie pozwalała wyświetlić dane osoby na podstawie jej nazwiska oraz inicjałów. Zwróć uwagę, że nie nakazujemy optymalizatorowi wykonać optymalizacji dla konkretnych wartości parametrów wejściowych procedury:

use AdventureWorks2008

GO

CREATE PROC DaneOsoby

(

@Nazwisko varchar(50)

,@I varchar(50)

)

AS BEGIN



SELECT



[LastName]

,[MiddleName]

,[FirstName]

FROM [Person].[Person]

WHERE LastName = @Nazwisko

AND MiddleName = @I

END;

GO

2. W nowym oknie zapytania wpisz druga wersję procedury, która zawiera wskazówkę OPTIMIZE FOR UNKNOWN, co pozwoli optymalizatorowi na zastosowanie metod statystycznych podczas realizacji zapytania. Nakazywane jest również optymalizatorowi, aby zoptymalizował wyszukiwanie dla kolumny MiddleName = ‘B’:

CREATE PROC DaneOsoby_OPTIMIZE_FOR_UNKNOWN

(



@Nazwisko varchar(50)



,@I varchar(50)

)

AS BEGIN



SELECT



[LastName]

,[MiddleName]

,[FirstName]

FROM [Person].[Person]

WHERE LastName = @Nazwisko

AND MiddleName = @I



OPTION (OPTIMIZE FOR (@I=’B’, @Nazwisko UNKNOWN) );



END;

GO

3. Wykonaj obydwie procedury dla następujących parametrów:

DECLARE @Nazwisko varchar(50) = ‘Black’;



DECLARE @I char(2) = ‘M’;

EXEC DaneOsoby @Nazwisko,@I;

EXEC DaneOsoby_OPTIMIZE_FOR_UNKNOWN @Nazwisko,@I;

4. Plany wykonań procedur są w tak prostym przypadku identyczne, ale wskaźnik Estimated Number of Rows wynosi 1 dla procedury DaneOsoby oraz 1.243 dla procedury DaneOsoby_OPTIMIZE_FOR_UNKNOWN. Oznacza to, że w drugim przypadku optymalizator wziął pod uwagę statystyczny rozkład danych w kolumnie LastName.

 Do początku strony Do początku strony

Zadanie 2

1. Celem zadania jest praktyczne sprawdzenie działania wskazówki FORCESEEK, która poleca optymalizatorowi użycie operacji przeszukiwania indeksu zamiast pełnozakresowego skanowania indeksu.

2. W nowym oknie zapytania wpisz następujący kod, który utworzy indeks niezgrupowany dla tabeli SalesPerson w schemacie Sales:

use AdventureWorks2008;

GO



CREATE NONCLUSTERED INDEX [IDX_NCL_Sales_SalesPerson_Ter] ON [Sales].[SalesPerson]





(

[TerritoryID] ASC

) ON [PRIMARY];

GO

3. Wykonaj poniższe zapytanie, które zostanie zrealizowane w oparciu o operację skanowania indeksu zgrupowanego z tabeli Sales.SalesPerson

SELECT PH.SalesQuota,PH.ModifiedDate,PH.QuotaDate,P.TerritoryID,P.SalesYTD

FROM [Sales].[SalesPerson] P JOIN

[Sales].[SalesPersonQuotaHistory] PH

ON P.BusinessEntityID = PH.BusinessEntityID

WHERE P.TerritoryID = 1;

4. Podejrzyj plan zapytania i sprawdź, że istotnie do realizacji tego zapytania optymalizator użył operacji skanowania indeksu zgrupowanego w tabeli Sales.SalesPerson

5. Zmodyfikuj zapytanie z punktu 3 tak, aby wymusić użycie indeksu niezgrupowanego, który utworzyłeś w punkcie 1 tego zadania:

SELECT PH.SalesQuota,PH.ModifiedDate,PH.QuotaDate,P.TerritoryID,P.SalesYTD



FROM [Sales].[SalesPerson] P WITH(FORCESEEK) JOIN



[Sales].[SalesPersonQuotaHistory] PH

ON P.BusinessEntityID = PH.BusinessEntityID

WHERE P.TerritoryID = 1;

6. Podejrzyj plan zapytania. Tym razem jest on inny i nie zawiera operacji skanowania indeksu zgrupowanego dla tabeli Sales.SalesPerson. Zwróć uwagę, że operacja ta została zamieniona na dwie operacje – przeszukania indeksu niezgrupowanego i doczytania brakujących informacji z indeksu zgrupowanego, co można stwierdzić po obecności operatora Key Lookup w planie zapytania:

7. Wymuszanie na optymalizatorze wzięcia pod uwagę konkretnej operacji przetwarzania indeksu za pomocą wskazówki FORCESEEK, podobnie jak używanie innych wskazówek, może mieć negatywny wpływ na szybkość uzyskanej informacji.

 Do początku strony Do początku strony

Zadanie 3

1. Celem zadania jest wykonanie planu zapytania i wskazanie tego planu optymalizatorowi. W ten sposób, mając lepszą i głebszą wiedzę na temat zapytania, można starać się wskazać optymalizatorowi zapytań, z którego planu należy skorzystać podczas realizacji zapytania

2. W nowym oknie zapytania wpisz poniższy kod, który pobiera informacje z tabeli Person.Person dla pracowników, których nazwisko rozpoczyna się na literę L:

USE AdventureWorks2008;

GO



SELECT



BusinessEntityID

,FirstName

,LastName

,PersonType



FROM Person.Person



WHERE LastName LIKE ‘L%’

GO

3. Plan zapytania można utworzyć na dwa sposoby. Pierwszym z nich jest zapisanie planu zapytania w zmiennej typu xml, a następnie wskazanie takiego planu dla realizacji zapytań określonej klasy. Skopiuj poniższy kod, który zachowuje w zmiennej typu xml plan zapytania

DECLARE @xml_showplan nvarchar(max);

SET @xml_showplan = (SELECT query_plan



FROM sys.dm_exec_query_stats AS qs



CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st



CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT,

DEFAULT) AS qp





WHERE st.text LIKE N’SELECT



BusinessEntityID

,FirstName

,LastName

,PersonType



FROM Person.Person



WHERE LastName LIKE „L%”;%’);

4. Plan zachowany w zmiennej @xml\_showplan należy teraz wskazać w procedurze sp_create_plan_guide jako parametr @hint. Zwróć uwagę, że optymalizator weźmie ten plan pod uwagę podczas realizacji konkretnej klasy zapytań, która jest wskazana w zmiennej @stmt.

EXEC sp_create_plan_guide



@name = N’Sugerowany plan wykonania zapytania z XML_showplan’,



@stmt = N’SELECT



BusinessEntityID

,FirstName

,LastName

,PersonType



FROM Person.Person





WHERE LastName LIKE „L%”;’,



@type = N’SQL’,



@module_or_batch = NULL,



@params = NULL,



@hints =@xml_showplan;

GO

5. Drugim sposobem na wskazanie optymalizatorowi planu wykonania zapytania jest użycie nowej procedury składowanej sp_create_plan_guide_from_handle. Procedura ta wymaga, aby najpierw uzyskać dwa parametry— @plan\_handle, który jest identyfikatorem planu zapytania oraz @statement\_start\_offset, który wskazuje na pozycję zapytania w paczce, na przykład w procedurze składowanej, w której występuje kilka zapytań:

DECLARE @plan_handle VARBINARY(64);

DECLARE @offset INT;

--uzyskanie informacji o identyfikatorze zapytania oraz pozycji zapytania z widoków

--dynamicznych sys.dm_exec_text_query_plan oraz sys.dm_exec_query_stats

SELECT @plan_handle = plan_handle

, @offset = qs.statement_start_offset

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st



CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset,

qs.statement_end_offset) AS qp





WHERE text LIKE N’SELECT



BusinessEntityID

,FirstName

,LastName

,PersonType



FROM Person.Person



WHERE LastName %’;

6. Zapisane w zmiennych @plan\_handle i @offset informacje należy przekazać do procedury sp_create_plan_guide_from_handle, jak pokazano poniżej:

EXECUTE sp_create_plan_guide_from_handle



@name = N’Sugerowany plan wykonania zapytania nr 2’,

@plan_handle = @plan_handle,

@statement_start_offset = @offset;

                GO

7. Administrator może zweryfikować poprawność planu zapytania wykorzystując funkcje tablicową fn_validate_plan_guide, która przyjmuje jako parametr identyfikator planu. W kolumnie msgnum można znaleźć informacje o błędach, które związane są z danym planem.

SELECT



plan_guide_id



, msgnum



, severity



, state



, message



FROM sys.plan_guides

CROSS APPLY fn_validate_plan_guide(plan_guide_id);

GO

 Do początku strony Do początku strony

Nowości związane z optymalizatorem zapytań – referencje

Dodatkowe informacje na temat nowości związanych z optymalizatorem zapytań można znaleźć w następujących miejscach:

[1] Witryna SQL Server 2008 Jumpstart

[2] Dokumentacja Books Online

[3] „Serwer SQL 2008. Administracja i programowanie”, Wydawnictwo Helion SA


  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

Akademia SQL - Część 14: Studio monitoringu     Akademia SQL     Akademia SQL - Część 16: Partycjonowanie tabel – informacje podstawowe