Najpierw identyfikator: jest to najbardziej selektywne (tj. Najbardziej unikalne) pole. Ale ponieważ jest to pole automatycznego przyrostu (lub losowe, jeśli nadal używa GUID), dane każdego klienta są rozłożone w każdej tabeli. Oznacza to, że zdarza się, że klient potrzebuje 100 wierszy, a to wymaga prawie 100 stron danych odczytanych z dysku (nie szybko) do puli buforów (zajmuje więcej miejsca niż 10 stron danych). Zwiększa także rywalizację na stronach danych, ponieważ częściej klienci będą musieli aktualizować tę samą stronę danych.
Zazwyczaj jednak nie występuje tak wiele problemów z wąchaniem parametrów / złym buforowaniem planu, ponieważ statystyki dla różnych wartości identyfikatora są dość spójne. Możesz nie uzyskać najbardziej optymalnych planów, ale rzadziej będziesz mieć okropne. Ta metoda zasadniczo poświęca wydajność (nieznacznie) wszystkim klientom, aby czerpać korzyści z rzadszych problemów.
TenantID najpierw:Nie jest to wcale wybiórcze. Różnice między milionami wierszy mogą być bardzo małe, jeśli masz tylko 100 TenantID. Ale statystyki dla tych zapytań są dokładniejsze, ponieważ SQL Server będzie wiedział, że zapytanie dla Najemcy A spowoduje wycofanie 500 000 wierszy, ale to samo zapytanie dla Najemcy B ma tylko 50 wierszy. To jest główny punkt bólu. Ta metoda znacznie zwiększa szanse na problemy z wąchaniem parametrów, gdy pierwszy przebieg procedury składowanej jest dla dzierżawcy A i działa odpowiednio w oparciu o Optymalizator zapytań, który widzi te statystyki i wie, że musi być wydajny, uzyskując 500 tys. Wierszy. Ale gdy działa Dzierżawa B, która ma tylko 50 wierszy, ten plan wykonania nie jest już odpowiedni, a wręcz niewłaściwy. ORAZ, ponieważ dane nie są wstawiane w kolejności pola wiodącego,
Jednak, aby pierwszy TenantID uruchomił procedurę przechowywaną, wydajność powinna być lepsza niż w drugim podejściu, ponieważ dane (przynajmniej po przeprowadzeniu konserwacji indeksu) będą zorganizowane fizycznie i logicznie, tak że potrzeba będzie znacznie mniej stron danych, aby spełnić zapytania. Oznacza to mniej fizycznych operacji we / wy, mniej logicznych odczytów, mniej rywalizacji między dzierżawcami o te same strony danych, mniej zmarnowanego miejsca zajmowanego w puli buforów (a zatem poprawiona oczekiwana długość życia strony) itp.
Ulepszenie wydajności wiąże się z dwoma głównymi kosztami. Pierwszy nie jest tak trudne: Państwo musi zrobić regularnej konserwacji indeksu, aby przeciwdziałać zwiększoną fragmentację. Drugi jest nieco mniej zabawny.
Aby przeciwdziałać zwiększonym problemom z wąchaniem parametrów, musisz rozdzielić plany wykonania między najemcami. Uproszczone podejście polega na użyciu WITH RECOMPILEprocs lub OPTION (RECOMPILE)podpowiedzi do zapytania, ale jest to uderzenie w wydajność, które może zniszczyć wszystkie zyski osiągnięte przez stawianie na TenantIDpierwszym miejscu. Metodą, która według mnie działała najlepiej, jest użycie sparametryzowanego Dynamicznego SQL poprzez sp_executesql. Powodem potrzeby użycia dynamicznego SQL jest umożliwienie konkatenacji TenantID z tekstem zapytania, podczas gdy wszystkie inne predykaty, które normalnie byłyby parametrami, są nadal parametrami. Na przykład, jeśli szukasz konkretnego Zakonu, zrobiłbyś coś takiego:
DECLARE @GetOrderSQL NVARCHAR(MAX);
SET @GetOrderSQL = N'
SELECT ord.field1, ord.field2, etc.
FROM dbo.Orders ord
WHERE ord.TenantID = ' + CONVERT(NVARCHAR(10), @TenantID) + N'
AND ord.OrderID = @OrderID_dyn;
';
EXEC sp_executesql
@GetOrderSQL,
N'@OrderID_dyn INT',
@OrderID_dyn = @OrderID;
Efektem tego jest utworzenie planu zapytań wielokrotnego użytku tylko dla tego TenantID, który będzie pasował do ilości danych tego konkretnego Najemcy. Jeśli ten sam dzierżawca A ponownie uruchomi procedurę przechowywaną dla innej, @OrderIDwówczas ponownie użyje buforowanego planu zapytań. Inny dzierżawca uruchamiający tę samą procedurę składowaną wygenerowałby tekst zapytania, który byłby inny tylko pod względem wartości TenantID, ale każda różnica w tekście zapytania wystarczy, aby wygenerować inny plan. A plan wygenerowany dla dzierżawcy B będzie nie tylko pasował do wielkości danych dla dzierżawcy B, ale będzie również można go użyć ponownie dla dzierżawcy B dla różnych wartości @OrderID(ponieważ ten predykat jest nadal sparametryzowany).
Wadami tego podejścia są:
- To trochę więcej pracy niż pisanie prostego zapytania (ale nie wszystkie zapytania muszą być dynamiczne SQL, tylko te, które mają problem z wąchaniem parametrów).
- W zależności od liczby dzierżawców w systemie, zwiększa to rozmiar pamięci podręcznej planu, ponieważ każde zapytanie wymaga teraz 1 planu na identyfikator TenantID, który go wywołuje. To może nie być problem, ale przynajmniej należy o tym pamiętać.
Dynamiczny SQL przerywa łańcuch własności, co oznacza, że nie można zakładać, że dostęp do tabel będzie możliwy dzięki EXECUTEzezwoleniu na procedurę przechowywaną. Łatwym, ale mniej bezpiecznym rozwiązaniem jest zapewnienie Użytkownikowi bezpośredniego dostępu do tabel. To z pewnością nie jest idealne, ale zwykle jest to kompromis dla szybkiego i łatwego. Bardziej bezpiecznym podejściem jest korzystanie z zabezpieczeń opartych na certyfikatach. Oznacza to, że utwórz certyfikat, a następnie utwórz użytkownika na podstawie tego certyfikatu, nadaj temu użytkownikowi żądane uprawnienia (użytkownik oparty na certyfikacie lub login nie może połączyć się z programem SQL Server samodzielnie), a następnie podpisuj za pomocą niego procedury składowane korzystające z dynamicznego SQL ten sam certyfikat przez DODAJ PODPIS .
Aby uzyskać więcej informacji na temat podpisywania modułów i certyfikatów, zobacz: ModuleSigning.Info