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