Category Archives: SQL Server

SQL Server

Funkcje skalarne a zmienne użytkownika w SQL Server

Jakiś czas temu brałem udział w spotkaniu na którym padło następujące pytanie: „Czy użycie funkcji skalarnej zamiast zwykłej zmiennej może mieć wpływ na wydajność? ”. Pierwsza moja odpowiedź: „Raczej nie …”. Potem jednak zacząłem się zastanawiać nad znalezieniem przypadku, w którym taka zmiana mogłaby coś popsuć i jak już się pewnie domyślacie – znalazłem 😀

Zanim jednak zacznę, to chciałbym podkreślić, że poniższy post nie jest wpisem na temat wyższości danej konstrukcji T-SQL nad inną. Po prostu chciałbym podzielić się z wami moimi obserwacjami 🙂

Przygotowanie do testów

Do testów użyłem SQL Server 2012 z zainstalowanym SP1 oraz przykładowej bazy AdventureWorks2012. Baza ta powinna posiadać tabelę Sales.SalesOrderDetail, na której został stworzony następujący indeks:

USE [AdventureWorks2012]
GO
CREATE INDEX IX_SalesOrderDetail_ProductID
ON [Sales].[SalesOrderDetail]
(
 ProductID ASC
)

Dodatkowo powinniśmy stworzyć prostą funkcję skalarną:

CREATE FUNCTION dbo.test(@i int)
RETURNS int
AS
BEGIN
 RETURN @i
END

Pierwsze porównanie i pierwszy problem 🙂

Na początek do porównania wydajnościowego weźmy następujący kod:

-- zapytanie 1
DECLARE @i int = 707
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = @i
-- zapytanie 2
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = dbo.Test(707)

Porównanie planów zapytań oraz wydajności pokazuje, że koszt dla obu instrukcji SELECT jest identyczny:

ZMIENNE_A_FUNKCJE_01

Jednak jest pewne  „małe ale” …

Czy optymalizator wybrał odpowiedni plan wykonania?

Niestety okazuje się, że NIE. W obu przypadkach zastosowana została estymacja wierszy na poziomie około 456 rekordów, zaś wierszy było o kilkukrotnie więcej:

ZMIENNE_A_FUNKCJE_02

Jedną z technik optymalizacji w tego typu przypadkach jest wymuszenie rekompilacji zapytania:

SET STATISTICS IO ON
-- zapytanie 1
DECLARE @i int
SET @i = 707
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = @i 
OPTION (RECOMPILE)
-- zapytanie 2
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = dbo.Test(707)
OPTION (RECOMPILE)

ZMIENNE_A_FUNKCJE_03

Jak widać tym razem plan wykonania jest różny dla obu przypadków. Mimo tego, że Management Studio pokazuje, że druga instrukcja jest wydajniejsza, to porównanie odczytanych stron nie daje złudzeń – pierwsze z zapytań jest kilkukrotnie szybsze 🙂

ZMIENNE_A_FUNKCJE_04

Sprawą, o którą mi chodziło w tym poście nie jest jednak tylko i wyłącznie wydajność. Kluczowym aspektem jest fakt, że w drugim przypadku SQL Server nadal estymował niepoprawną ilość wierszy. Mimo rekompilacji na poziomie całej instrukcji T-SQL przewidywana ilość wierszy pozostała taka jak wcześniej:

ZMIENNE_A_FUNKCJE_05

Niestety nie mamy możliwości poprawy tego typu sytuacji … nawet jeżeli rekompilacja funkcji by pomogła, to zgodnie z SQL Server Books Online funkcje w odróżnieniu od procedur nie posiadają słówka „RECOMPILE” w dostępnych opcjach:

Function Options
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}

Podsumowanie

Zastosowanie funkcji skalarnej zamiast zmiennej użytkownika może mieć wpływ na wydajność. W analizowanym przypadku błędna estymacja rekordów zwracanych przez instrukcję SELECT stwarzała nieodpowiedni plan zapytania. Dla zmiennej użytkownika mogliśmy zastosować opcję RECOMPILE  i przyśpieszyć wykonanie. Z funkcją skalarną nie udało się tego efektu uzyskać – mimo rekompilacji  plan pozostał ten sam.

Reklamy

Statystyki w SQL Server: GEEK LEVEL BONUS

Jak zapewne pamiętacie kilka dni temu prowadziłem prelekcję na spotkaniu Trójmiejskiej Zawodowej Grupy .Net. W ramach tego spotkania pokazałem demo, które jest odpowiedzią na bardzo specyficzny problem 🙂

Definicja problemu

Wyobraźmy sobie sytuację, w której jesteście osobami z działu developmentu i dostajecie zgłoszenie odnośnie problemów wydajnościowych związanych z działaniem waszej aplikacji. Standardowym sposobem byłoby podłączenie się do instancji i stworzenie odpowiedniego trace-a. Tym razem jest niestety nieco inaczej – dane klienta są tajne i nie możecie w żaden sposób ich podejrzeć. Oczywiście możecie prosić o różnego rodzaju zrzuty wydajnościowe, jednak działanie „po omacku” i przy pomocy osób trzecich jest czasem problematyczne …

Co możemy w takim razie zrobić?

I tutaj do akcji wchodzi mój GEEK LEVEL BONUS :). Może by oszukać optymalizator i sprawić by „myślał”, że jest u naszego klienta? Tylko jak to zrobić …

Na początek imitacja bazy klienta.

Załóżmy, że naszą bazą klienta jest baza AdventureWorks2012, którą można pobrać z witryny Codeplex, a problematyczna instrukcja SELECT wygląda następująco:

SELECT *
fROM AdventureWorks2012.Sales.SalesOrderDetail sod
JOIN AdventureWorks2012.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

A teraz nasza baza developerska

Jako bazę developerską użyjemy również bazy AdventureWorks2012. Jedyne co, to odzyskamy ją z kopii zapasowej pod inną nazwą (np. AdventureGeek) i usuniemy informacje z tabel , tak aby zasymulować brak danych na serwerze developerskim:

DELETE AdventureGeek.Sales.SalesOrderDetail

DELETE AdventureGeek.Sales.SalesOrderHeader

Gdy odpalimy nasz selekt na bazie developerskiej zobaczymy następujący plan zapytania:

SELECT *
fROM AdventureGeek.Sales.SalesOrderDetail sod
JOIN AdventureGeek.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

GEEK_LEVEL_BONUS_01

No tak … ale co w tym planie jest nie tak? Oczywiście mamy pewne ostrzeżenie optymailizatora, ale mimo wszystko plan zapytania na pierwszy rzut oka wygląda w miarę OK (no, może cześć z was zauważy niuans naprowadzający na rozwiązanie). Dlaczego tak się stało – po prostu mamy inne dane niż nasz klient (a dokładnie nie mamy danych). Diagnostyka, co może być nie tak przeprowadzona w ten sposób może okazać się dość kłopotliwa.

No dobrze, ale wspomniałem coś o oszukiwaniu optymalizatora 🙂

Na początek użyjemy instrukcji DBCC SHOW_STATISTICS, która służy do pokazania informacji na temat rozkładu danych w tabeli. Instrukcja ta zawiera nie do końca udokumentowaną opcję o nazwie STATS_STREAM:

DBCC SHOW_STATISTICS ('AdventureWorks2012.Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID')
WITH STATS_STREAM

GEEK_LEVEL_BONUS_02

Gdy zajrzymy w SQL Server Books Online możemy o tej opcji przeczytać następujące zdanie:

STATS_STREAM is Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Jeżeli namówimy osobę po stronie klienta by skopiowała i udostępniła nam jej wyniki, to może być tylko lepiej 🙂

W drugim kroku użyjemy instrukcji UPDATE STATISTICS (już na naszej bazie), przepisując wartości ROWCOUNT, PAGECOUNT I STATS_STREAM na zgodne z wypisanymi we wcześniejszym kroku:

UPDATE STATISTICS AdventureGeek.Sales.SalesOrderDetail PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
WITH ROWCOUNT = 121317, PAGECOUNT = 1237,
STATS_STREAM = -- tutaj wklejamy dane STATS_STREAM z poprzedniego kroku :)

No i oczywiście odpalmy nasze zapytanie w bazie AdventureGeek. Dla łatwiejszego porównania wyników zamieszczam obie instrukcje SELECT:

-- baza developerska
SELECT *
FROM AdventureGeek.Sales.SalesOrderDetail sod
JOIN AdventureGeek.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

-- baza klienta
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail sod
JOIN AdventureWorks2012.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

GEEK_LEVEL_BONUS_03

Jak widać podpowiedź optymalizatora w obu przypadkach jest taka sama – utworzyć indeks na kolumnie CarrierTrackingNumber. Oczywiście oba zaprezentowane plany są nieco odmienne. Na bazie developerskiej obie tabele nie są wypełnione wartościami, a my podmieniliśmy tylko jedną ze statystyk. Pozostałe kolumny nadal pozostają dla SQL Server-a puste.  

Mimo wszystko, na powyższym przykładzie widać, że nasza idea jak działa. Podmieniając kolejne statystyki dla obu tabel w bazie AdventureGeek moglibyśmy doprowadzić do wygenerowania identycznych planów.

Podsumowanie

Mam nadzieję, że moje rozwiązanie związane z podmianą statystyk wam się podoba. Jedynie co, to proszę – nie róbcie tego typu „sztuczek” na serwerach „u klienta”. Oczywiście chcielibyśmy, aby działały ładnie jak developerskie u nas, lecz nie jestem przekonany, czy ta sztuczka będzie działać w obie strony 🙂

PS. Pamiętajcie, że te rozwiązanie jest totalnie niewspierane !!!! (nawet przez mnie :))

Mój pierwszy artykuł na technecie już dostępny !!! :)

Wczoraj na stronach polskiego technetu ukazał się artykuł mojego autorstwa. Tematem artykułu jest wykorzystanie dysków SSD jako rozszerzenia puli buforów w SQL Server 2014. Mam nadzieję, że artykuł się wam spodoba, gdyż na temat nowości w kolejnej platformie bazodanowej firmy Microsoft mam zamiar jeszcze co nieco napisać 🙂

Zapraszam do lektury:

Nowości w SQL Server 2014 CTP1 – Buffer Pool Extension

Jak użyć tabeli tymczasowej wewnątrz kolejnych zadań SSIS

Dzisiejszym wpisem chciałbym wam pokazać w jaki sposób możemy używać tabel tymczasowych wewnątrz kolejnych zadań pakietu SSIS.

Przykładowe zadanie

Mój przykład będzie polegał na stworzeniu paczki SSIS zawierającej dwa zadania. Pierwsze z nich stworzy tabelę tymczasową, a drugie skorzysta z niej.

Przygotowanie paczki

Na początku przechodzimy do SQL Server Data Tools, tworzymy standardowy projekt SSIS a w nim paczkę. Pierwszą sprawą, o jaką powinniśmy zadbać jest stworzenie managera połączeń. Aby to zrobić klikamy prawym przyciskiem w sekcji Connection Manager i wybieramy odpowiedniego dostawcę (ja wybrałem „OLE DB”):

SSIS_TABELE_TYMCZASOWE_01

Kolejnym krokiem jest wykreowanie w paczce zadania tworzącego tabelę tymczasową. Użyjemy do tego zadania „Execute SQL Task”. Przeciągamy je do zakładki „Control Flow” i klikamy dwa razy w celu edycji właściwości:

SSIS_TABELE_TYMCZASOWE_03

Wewnątrz tego zadania w sekcji SQLStatement umieszczamy kod  kreujący tabelę tymczasową:

CREATE TABLE #t(
 [BusinessEntityID] int PRIMARY KEY,
 [LastName] nvarchar(50)
)

Drugie z zadań w paczce ma polegać na dowolnym użyciu tabeli tymczasowej. W tym celu ponownie przeciągamy zadanie „Execute SQL Task”, tym razem jednak w sekcji SQLStatement wpisujemy kod zliczający ilość wierszy:

SELECT COUNT(*) FROM #t

SSIS_TABELE_TYMCZASOWE_04

Łączymy oba zadania tak aby najpierw wykonał się kod tworzący tabelę oraz uruchamiamy paczkę za pomocą klawisz F5:

SSIS_TABELE_TYMCZASOWE_05

Niestety kod drugiego z zadań nie wykonał się poprawnie !!!

Przeglądając zakładkę „Execution Results” możemy zauważyć, że przyczyną problemu jest brak (wcześniej stworzonej) tabeli tymczasowej:

[Execute SQL Task] Error: Executing the query „SELECT COUNT(*) FROM #t” failed with the following error: „Invalid object name ‚#t’.”. Possible failure reasons: Problems with the query, „ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Dlaczego tak się stało?

Domyślnie Usługi Integracyjne SQL Server nawiązują i kończą połączenia do bazy w ramach istniejących zadań. W naszym przypadku mieliśmy dwa zadania, a więc po stworzeniu tabeli tymczasowej w ramach pierwszego z nich zamknęliśmy połączenie i straciliśmy ją. Dowodem na to może być poniższy zrzut ekranu, który przedstawia ślad SQL Server Profiler-a wygenerowany podczas działania naszej paczki:

SSIS_TABELE_TYMCZASOWE_06

Jak w takim razie pozbyć się tego problemu?

Rozwiązaniem jest użycie opcji „RetainSameConnection” naszego connection managera. Klikamy na niego prawym przyciskiem i z menu kontekstowego wybieramy opcję „Properties”. Po prawej stronie ekranu powinna pojawić się zakładka z jego właściwościami, gdzie możemy zmienić domyślne zachowanie:

SSIS_TABELE_TYMCZASOWE_07

W momencie ustawienia opcji RetainSameConnection na „True” SSIS nie będzie zamykał połączeń po każdym z zadań, a nasza paczka zacznie działać:

SSIS_TABELE_TYMCZASOWE_08

Podsumowanie

Usługi Integracyjne SQL Server nawiązują oraz kończą połączenia z bazą danych w ramach zadań zdefiniowanych przez użytkownika podczas tworzenia paczki (co jest zachowaniem domyślnym). W przypadku, gdy  kilka zadań używa tych samych obiektów tymczasowych to powinniśmy zmienić opcję RetainSameConnection naszego managera połączeń, tak aby połączenia nie były zamykane i otwierane na nowo.

SQL Server 2014 CTP1 gotowy do pobrania !!!

Kilka dni temu dostałem całkiem interesującą wiadomość mailową. Pewnie większość z was widząc tytuł dzisiejszego posta już wie czego ta wiadomość dotyczyła 🙂

SQL Server 2014 CTP1 jest już gotowy do pobrania na stronach technetu !!!

SQL_2014CTP1_do_pobrania_01

Miłego testowania w takim razie oraz (mam nadzieję) czytania kolejnych wpisów na moim blogu. Ostatnio mam niewiele czasu na pisanie, ale na pewno postaram się „udokumentować” cześć swoich przemyśleń na temat przyszłej wersji SQL Server-a 🙂