Jak wygenerować skrypt statystyki, aby utworzyć bazę danych tylko do statystyk w SQL Server
W tym artykule dowiesz się, jak wygenerować skrypt statystyki przy użyciu metadanych bazy danych do tworzenia bazy danych tylko do statystyk w SQL Server.
Oryginalna wersja produktu: SQL Server 2014, SQL Server 2012, SQL Server 2008
Oryginalny numer KB: 914288
Wprowadzenie
DBCC CLONEDATABASE jest preferowaną metodą generowania klonu bazy danych tylko do schematu w celu zbadania problemów z wydajnością. Procedura opisana w tym artykule jest używana tylko wtedy, gdy nie możesz użyć polecenia DBCC CLONEDATABASE.
Optymalizator zapytań w firmie Microsoft SQL Server używa następujących typów informacji do określenia optymalnego planu zapytania:
- metadane bazy danych
- środowisko sprzętowe
- stan sesji bazy danych
Zazwyczaj należy symulować wszystkie te same typy informacji, aby odtworzyć zachowanie optymalizatora zapytań w systemie testowym.
Dział obsługi klienta firmy Microsoft może poprosić o wygenerowanie skryptu metadanych bazy danych w celu zbadania problemu optymalizatora zapytań. W tym artykule opisano kroki generowania skryptu statystyki, a także opisano sposób, w jaki optymalizator zapytań używa tych informacji.
Uwaga
Klucze zapisane w tych danych mogą zawierać informacje o identyfikatorze PII. Jeśli na przykład tabela zawiera kolumnę Numer telefonu ze statystyką, wysoka wartość klucza każdego kroku będzie znajdować się w wygenerowanym skryptze statystyk.
Skrypt całej bazy danych
Podczas generowania bazy danych klonowania tylko do statystyk łatwiej i bardziej niezawodnie skryptować całą bazę danych zamiast skryptować poszczególne obiekty. Podczas wykonywania skryptu całej bazy danych otrzymujesz następujące korzyści:
- Unikasz problemów z brakującymi obiektami zależnymi, które są wymagane do odtworzenia problemu.
- Aby wybrać niezbędne obiekty, należy wykonać mniej kroków.
Należy pamiętać, że jeśli wygenerujesz skrypt dla bazy danych, a metadane bazy danych zawierają tysiące obiektów, proces skryptów zużywa znaczne zasoby procesora CPU. Zaleca się wygenerowanie skryptu poza godzinami szczytu lub użycie drugiej opcji Script Individual Objects do wygenerowania skryptu dla poszczególnych obiektów.
Aby wykonać skrypt dla każdej bazy danych, do których odwołuje się zapytanie, wykonaj następujące kroki:
Otwórz SQL Server Management Studio.
W Eksplorator obiektów rozwiń węzeł Bazy danych, a następnie znajdź bazę danych, dla których chcesz wykonać skrypt.
Kliknij prawym przyciskiem myszy bazę danych, wskaż pozycję Zadania, a następnie wybierz pozycję Generuj skrypty.
W kreatorze skryptów sprawdź, czy wybrano poprawną bazę danych. Kliknij, aby wybrać całą bazę danych skryptu i wszystkie obiekty bazy danych, a następnie wybierz przycisk Dalej.
W oknie dialogowym Wybieranie opcji skryptu wybierz przycisk Zaawansowane , aby zmienić następujące ustawienia z wartości domyślnej na wartość wymienioną w poniższej tabeli.
Opcja Skrypty Wartość do wybrania Dopełnienie ansi True Kontynuuj wykonywanie skryptów po błędzie True Generowanie skryptu dla obiektów zależnych True Uwzględnij nazwy ograniczeń systemu True Sortowanie skryptów True Identyfikatory logowania skryptu True Uprawnienia na poziomie obiektu skryptu True Statystyki skryptów Statystyki skryptów i histogramy Indeksy skryptów True Wyzwalacze skryptu True Uwaga
Pamiętaj, że opcja Logowania skryptu i opcja Uprawnienia na poziomie obiektu skryptu mogą nie być wymagane, chyba że schemat zawiera obiekty, które są własnością identyfikatorów logowania innych niż dbo.
Wybierz przycisk OK , aby zapisać zmiany, i zamknij stronę Zaawansowane opcje skryptów .
Wybierz pozycję Zapisz w pliku i wybierz opcję Pojedynczy plik .
Przejrzyj wybrane opcje i wybierz pozycję Dalej.
Wybierz Zakończ.
Skrypt poszczególnych obiektów
Zamiast skryptów pełnej bazy danych można wykonywać skrypty tylko dla pojedynczych obiektów, do których odwołuje się określone zapytanie. Jednak jeśli wszystkie obiekty bazy danych nie zostały utworzone przy użyciu WITH SCHEMABINDING klauzuli, informacje o zależnościach w tabeli systemowej sys.depends mogą nie być zawsze dokładne. Ta niedokładność może spowodować jeden z następujących problemów:
Proces skryptowania nie wykonuje skryptu obiektu zależnego.
Proces tworzenia skryptów może tworzyć skrypty obiektów w nieprawidłowej kolejności. Aby pomyślnie uruchomić skrypt, należy ręcznie edytować wygenerowany skrypt.
W związku z tym nie zaleca się tworzenia skryptów poszczególnych obiektów, chyba że baza danych zawiera wiele obiektów, a wykonywanie skryptów w przeciwnym razie trwałoby zbyt długo. Jeśli musisz używać poszczególnych obiektów skryptu, wykonaj następujące kroki:
W SQL Server Management Studio rozwiń węzeł Bazy danych, a następnie znajdź bazę danych, dla których chcesz wykonać skrypt.
Kliknij prawym przyciskiem myszy bazę danych, wskaż polecenie Script Database As, a następnie wskaż polecenie CREATE To, a następnie wybierz pozycję Plik.
Wprowadź nazwę pliku, a następnie wybierz pozycję Zapisz.
Podstawowy kontener bazy danych zostanie skryptowany. Ten kontener zawiera pliki, grupy plików, bazę danych i właściwości.
Kliknij prawym przyciskiem myszy bazę danych, wskaż pozycję Zadania, a następnie wybierz pozycję Generuj skrypty.
Upewnij się, że wybrano poprawną bazę danych, a następnie wybierz pozycję Dalej.
W oknie dialogowym Wybieranie typów obiektów wybierz pozycję Wybierz określone obiekty bazy danych i wybierz wszystkie typy obiektów bazy danych, do których odwołuje się problematyczne zapytanie.
Jeśli na przykład zapytanie odwołuje się tylko do tabel, wybierz pozycję Tabele. Jeśli zapytanie odwołuje się do widoku, wybierz pozycję Widoki i tabele. Jeśli problematyczne zapytanie używa funkcji zdefiniowanej przez użytkownika, wybierz pozycję Funkcje.
Po wybraniu wszystkich typów obiektów, do których odwołuje się zapytanie, wybierz przycisk Dalej.
W oknie dialogowym Ustawianie opcji skryptów wybierz przycisk Zaawansowane i zmień następujące ustawienia z wartości domyślnej na wartość wymienioną w poniższej tabeli na stronie Zaawansowane opcje skryptów .
Opcja Skrypty Wartość do wybrania Dopełnienie ansi True Kontynuuj wykonywanie skryptów po błędzie True Uwzględnij nazwy ograniczeń systemu True Generowanie skryptu dla obiektów zależnych True Sortowanie skryptów True Identyfikatory logowania skryptu True Uprawnienia na poziomie obiektu skryptu True Statystyki skryptów Statystyki skryptów i histogramy UŻYCIE SKRYPTU BAZY DANYCH True Indeksy skryptów True Wyzwalacze skryptu True Uwaga
Pamiętaj, że opcje Logowania skryptu i Uprawnienia na poziomie obiektu skryptu mogą nie być wymagane, chyba że schemat zawiera obiekty, które są własnością identyfikatorów logowania innych niż dbo.
Wybierz przycisk OK , aby zapisać i zamknąć stronę Zaawansowane opcje skryptów .
Zostanie wyświetlone okno dialogowe dla każdego typu obiektu bazy danych wybranego w kroku 7.
W każdym oknie dialogowym wybierz określone tabele, widoki, funkcje lub inne obiekty bazy danych, a następnie wybierz pozycję Dalej.
Wybierz opcję Skrypt do pliku , a następnie określ tę samą nazwę pliku, którą wprowadzono w kroku 3.
Wybierz pozycję Zakończ , aby rozpocząć wykonywanie skryptów.
Po zakończeniu wykonywania skryptu wyślij plik skryptu do inżyniera pomoc techniczna firmy Microsoft. Inżynier pomoc techniczna firmy Microsoft może również zażądać następujących informacji:
Konfiguracja sprzętu, w tym liczba procesorów i ilość pamięci fizycznej.
USTAW opcje, które były aktywne podczas uruchamiania zapytania.
Pamiętaj, że te informacje mogły już zostać podane przez wysłanie raportu SQLDiag lub śledzenia programu SQL Profiler. Być może użyto również innej metody, aby podać te informacje.
Sposób użycia informacji
Poniższe tabele pomagają wyjaśnić, w jaki sposób optymalizator zapytań używa tych informacji do wybierania planu zapytania.
Metadanych
| Opcja | Objaśnienie |
|---|---|
| Ograniczenia | Optymalizator zapytań często używa ograniczeń do wykrywania sprzeczności między zapytaniem a podstawowym schematem. Jeśli na przykład zapytanie zawiera WHERE col = 5 klauzulę CHECK (col < 5) i istnieje ograniczenie w tabeli bazowej, optymalizator zapytań wie, że żadne wiersze nie będą zgodne. Optymalizator zapytań dokonuje podobnych typów odliczeń dotyczących wartości null. Na przykład klauzula WHERE col IS NULL jest znana jako true lub false w zależności od wartości null kolumny i tego, czy kolumna pochodzi z zewnętrznej tabeli sprzężenia zewnętrznego. Obecność ograniczeń KLUCZA OBCEGO jest przydatna do określenia kardynalności i odpowiedniej kolejności sprzężenia. Optymalizator zapytań może używać informacji o ograniczeniach, aby wyeliminować sprzężenia lub uprościć predykaty. Te zmiany mogą usunąć wymaganie dostępu do tabel podstawowych. |
| Statystyki | Informacje statystyczne zawierają gęstość i histogram pokazujący rozkład kolumny wiodącej klucza indeksu i statystyki. W zależności od charakteru predykatu optymalizator zapytań może użyć gęstości, histogramu lub obu tych elementów do oszacowania kardynalności predykatu. Aktualne statystyki są wymagane do dokładnego oszacowania kardynalności. Oszacowania kardynalności są używane jako dane wejściowe w szacowaniu kosztów operatora. W związku z tym musisz mieć dobre szacunki kardynalności, aby uzyskać optymalne plany zapytań. |
| Rozmiar tabeli (liczba wierszy i stron) | Optymalizator zapytań używa histogramów i gęstości, aby obliczyć prawdopodobieństwo, że dany predykat ma wartość true lub false. Ostateczne oszacowanie kardynalności jest obliczane przez pomnożenie prawdopodobieństwa przez liczbę wierszy zwracanych przez operator podrzędny. Liczba stron w tabeli lub indeksie jest czynnikiem oceniającym koszt operacji we/wy. Rozmiar tabeli służy do obliczania kosztu skanowania i jest przydatny podczas szacowania liczby stron, do których będzie uzyskiwany dostęp podczas wyszukiwania indeksu. |
| Opcje bazy danych | Kilka opcji bazy danych może mieć wpływ na optymalizację. Opcje AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS mają wpływ na to, czy optymalizator zapytań utworzy nowe statystyki lub zaktualizuje nieaktualne statystyki. Poziom parametryzacji ma wpływ na sposób parametryzacji zapytania wejściowego przed przekazaniem zapytania wejściowego do optymalizatora zapytań. Parametryzacja może mieć wpływ na szacowanie kardynalności i może również uniemożliwić dopasowywanie do indeksowanych widoków i innych typów optymalizacji. To DATE_CORRELATION_OPTIMIZATION ustawienie powoduje, że optymalizator szuka korelacji między kolumnami. To ustawienie ma wpływ na kardynalność i szacowanie kosztów. |
Środowiska
| Opcja | Objaśnienie |
|---|---|
| Opcje zestawu sesji | Ustawienie ANSI_NULLS ma wpływ na to, NULL = NULL czy wyrażenie ma wartość true. Szacowanie kardynalności sprzężeń zewnętrznych może ulec zmianie w zależności od bieżącego ustawienia. Ponadto mogą również ulec zmianie niejednoznaczne wyrażenia. Na przykład col = NULL wyrażenie jest obliczane inaczej na podstawie ustawienia. Jednak col IS NULL wyrażenie zawsze jest obliczana w ten sam sposób. |
| Zasoby sprzętowe | Koszt operatorów sortowania i skrótów zależy od względnej ilości pamięci dostępnej dla SQL Server. Jeśli na przykład rozmiar danych jest większy niż pamięć podręczna, optymalizator zapytań wie, że dane muszą być zawsze buforowane na dysku. Jeśli jednak rozmiar danych jest znacznie mniejszy niż pamięć podręczna, operacja prawdopodobnie zostanie wykonana w pamięci. SQL Server uwzględnia również różne optymalizacje, jeśli serwer ma więcej niż jeden procesor i jeśli równoległość nie została wyłączona za pomocą MAXDOP wskazówek lub maksymalnego stopnia konfiguracji równoległości. |
Zobacz też
Opinia
Prześlij i wyświetl opinię dla