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'

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

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'

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 :))

Materiały z sesji o statystykach już dostępne

Prezentacja z sesji „Statystyki w SQL Server: od zera do totalnego geeka :)”, którą poprowadziłem w ramach środowego spotkania Trójmiejskiej Zawodowej Grupy .NET jest już dostępna w materiałach do pobrania.

Jako, że GEEK LEVEL BONUS wyraźnie wam się spodobał, to w kolejnym wpisie wykonam go jeszcze raz, wyjaśniając krok po kroku co i jak 🙂

Statystyki w SQL Server – od zera do totalnego GEEKa na spotkaniu TZG.NET :)

13 listopada 2013 o godzinie 18:00 obędzie się kolejne spotkanie Trójmiejskiej Zawodowej Grupy .NET, podczas którego będę prelegentem. 

Tym razem opowiem na temat statystyk w SQL Server. Sesję rozpoczniemy łagodnym wprowadzeniem do tematu,  a następnie zaczniemy podkręcać level, aż do uzyskania poziomu totalnego GEEKa 🙂 

Agenda spotkania przedstawia się następująco:

18:00 – 19:00: Statystyki w SQL Server – od zera do totalnego GEEKa

Prelegent:  Cezary Ołtuszyk (BEST S.A.)

19:15 – 20:00: Wprowadzenie do knockout.js

Prelegent: Wojciech Gomoła (Atena Usługi Informatyczne i Finansowe S.A)

Link do rejestracji znajdziecie po tym adresem: rejestracja !!!

Materiały z wczorajszej sesji na temat planów zapytań

Slajdy z wczorajszej sesji na temat planów zapytań prowadzonej przeze mnie w ramach spotkania Trójmiejskiej Zawodowej Grupy .NET są już dostępne w zakładce materiały do pobrania mojego bloga.

Tych z was, którzy są zainteresowani tematem zapraszam do lektury 🙂

Kolejna prelekcja – tym razem w Gdańsku :)

Po powrocie z Torunia przyszedł czas na prelekcję w Gdańsku 🙂

12 marca 2013 o godzinie 18:00 obędzie się kolejne spotkanie Trójmiejskiej Zawodowej Grupy .NET, podczas którego będę prelegentem. Agenda spotkania przedstawia się nastepująco:

18:00 – 18:45: Dotacja nie tylko unijna, zastrzyk na założenie działalności lub rozwój firmy IT

Prelegent: Artur Rorzkowski (Europejskie Doradztwo Biznesowe)

18:45 – 19:45: SQL Server 2012 i nie tylko: Wprowadzenie do planów zapytań

Prelegent: Cezary Ołtuszyk (BEST S.A.)

19:45 – 19:45: ASP .NET MVC 4.0 – nowości

Prelegent: Rafał Gordon (Banqsoft)

Zachęcam do wzięcia udziału i rejestracji !!! 🙂