Jak wiadomo, indeksy odgrywają ważną rolę w systemach zarządzania bazami danych, umożliwiając szybkie wyszukiwanie potrzebnych rekordów. Dlatego tak ważne jest ich regularne utrzymywanie. O analizie i optymalizacji napisano wiele materiałów, w tym w Internecie. Na przykład ostatnio zrobiono przegląd tego tematu w .
Istnieje wiele zarówno płatnych, jak i darmowych rozwiązań dla tego. Na przykład, dostępne jest gotowe , oparte na adaptacyjnej metodzie optymalizacji indeksów.
Następnie przyjrzymy się darmowemu narzędziu , którego autorem jest .
Główna różnica techniczna między SQLIndexManager a innymi podobnymi narzędziami wskazuje sam autor. i .
W tym artykule przyjrzymy się projektowi oraz możliwościom eksploatacji tego rozwiązania programowego.
Narzędzie to jest omawiane .
Z czasem większość uwag i błędów została naprawiona.
Przejdźmy teraz do samego narzędzia SQLIndexManager.
Aplikacja jest napisana w języku C# .NET Framework 4.5 w Visual Studio 2017 i używa DevExpress do formularzy:
i wygląda następująco:
Wszystkie zapytania są generowane w następujących plikach:
- Index
- Query
- QueryEngine
- ServerInfo
Podczas łączenia z bazą danych i wysyłania zapytań do systemu zarządzania bazą danych aplikacja jest podpisywana w następujący sposób:
ApplicationName="SQLIndexManager" Po uruchomieniu aplikacji otworzy się okno modalne do dodawania połączenia:
Na razie opcja załadowania pełnej listy wszystkich instancji MS SQL Server dostępnych w lokalnych sieciach nie działa.
Można również dodać połączenie za pomocą skrajnie lewej przycisku w głównym menu:
Następnie uruchomią się następujące zapytania do systemu zarządzania bazą danych:
- Uzyskiwanie informacji o systemie zarządzania bazą danych
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Uzyskanie listy dostępnych baz danych z ich krótkimi właściwościami
SELECT DatabaseName = t.[name] , d.DataSize , DataUsedSize = CAST(NULL AS BIGINT) , d.LogSize , LogUsedSize = CAST(NULL AS BIGINT) , RecoveryModel = t.recovery_model_desc , LogReuseWait = t.log_reuse_wait_desc FROM sys.databases t WITH(NOLOCK) LEFT JOIN ( SELECT [database_id] , DataSize = SUM(CASE WHEN [type] = 0 THEN CAST(size AS BIGINT) END) , LogSize = SUM(CASE WHEN [type] = 1 THEN CAST(size AS BIGINT) END) FROM sys.master_files WITH(NOLOCK) GROUP BY [database_id] ) d ON d.[database_id] = t.[database_id] WHERE t.[state] = 0 AND t.[database_id] != 2 AND ISNULL(HAS_DBACCESS(t.[name]), 1) = 1
Po wykonaniu powyższych skryptów pojawi się okno zawierające krótką informację o bazach danych wybranej instancji MS SQL Server:
Należy zauważyć, że rozszerzone informacje są wyświetlane w oparciu o uprawnienia. Jeśli są , to można wybierać dane z widoku . Jeśli takich uprawnień brakuje, po prostu zwracane jest mniej danych, aby nie spowalniać zapytania.
Tutaj należy wybrać interesujące bazy danych i kliknąć przycisk „OK”.
Następnie zostanie wykonany następujący skrypt dla każdej wybranej bazy danych w celu analizy stanu indeksów:
Analiza stanu indeksów
deklaruj @Fragmentation float=15;
deklaruj @MinIndexSize bigint=768;
deklaruj @MaxIndexSize bigint=1048576;
deklaruj @PreDescribeSize bigint=32768;
USTAW NOCOUNT ON
USTAW ARITHABORT ON
USTAW NUMERIC_ROUNDABORT OFF
JEŚLI OBJECT_ID('tempdb.dbo.#AllocationUnits') JEST NIE NULL
ZRZUĆ TABELĘ #AllocationUnits
UTWÓRZ TABELĘ #AllocationUnits (
ContainerID BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages BIGINT NOT NULL
)
WSTAW DO #AllocationUnits (ContainerID, ReservedPages, UsedPages)
WYBIERZ [container_id]
, SUM([total_pages])
, SUM([used_pages])
Z sys.allocation_units Z (NOLOCK)
GRUPUJ WYBIERZ [container_id]
HAVING SUM([total_pages]) POMIĘDZY @MinIndexSize A @MaxIndexSize
JEŚLI OBJECT_ID('tempdb.dbo.#ExcludeList') JEST NIE NULL
ZRZUĆ TABELĘ #ExcludeList
UTWÓRZ TABELĘ #ExcludeList (ID INT PRIMARY KEY)
WSTAW DO #ExcludeList
WYBIERZ [object_id]
Z sys.objects Z (NOLOCK)
GDZIE [type] W ('V', 'U')
I ( [is_ms_shipped] = 1 )
JEŚLI OBJECT_ID('tempdb.dbo.#Partitions') JEST NIE NULL
ZRZUĆ TABELĘ #Partitions
WYBIERZ [object_id]
, [index_id]
, [partition_id]
, [partition_number]
, [rows]
, [data_compression]
DO #Partitions
Z sys.partitions Z (NOLOCK)
GDZIE [object_id] > 255
I [rows] > 0
I [object_id] NIE W (WYBIERZ * Z #ExcludeList)
JEŚLI OBJECT_ID('tempdb.dbo.#Indexes') JEST NIE NULL
ZRZUĆ TABELĘ #Indexes
UTWÓRZ TABELĘ #Indexes (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, IndexName SYSNAME NULL
, PagesCount BIGINT NOT NULL
, UnusedPagesCount BIGINT NOT NULL
, PartitionNumber INT NOT NULL
, RowsCount BIGINT NOT NULL
, IndexType TINYINT NOT NULL
, IsAllowPageLocks BIT NOT NULL
, DataSpaceID INT NOT NULL
, DataCompression TINYINT NOT NULL
, IsUnique BIT NOT NULL
, IsPK BIT NOT NULL
, FillFactorValue INT NOT NULL
, IsFiltered BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
WSTAW DO #Indexes
WYBIERZ ObjectID = i.[object_id]
, IndexID = i.index_id
, IndexName = i.[name]
, PagesCount = a.ReservedPages
, UnusedPagesCount = CASE WHEN ABS(a.ReservedPages - a.UsedPages) > 32 THEN a.ReservedPages - a.UsedPages ELSE 0 END
, PartitionNumber = p.[partition_number]
, RowsCount = ISNULL(p.[rows], 0)
, IndexType = i.[type]
, IsAllowPageLocks = i.[allow_page_locks]
, DataSpaceID = i.[data_space_id]
, DataCompression = p.[data_compression]
, IsUnique = i.[is_unique]
, IsPK = i.[is_primary_key]
, FillFactorValue = i.[fill_factor]
, IsFiltered = i.[has_filter]
Z #AllocationUnits a
DOŁĄCZ #Partitions p ON a.ContainerID = p.[partition_id]
DOŁĄCZ sys.indexes i Z (NOLOCK) ON i.[object_id] = p.[object_id] I p.[index_id] = i.[index_id]
GDZIE i.[type] W (0, 1, 2, 5, 6)
I i.[object_id] > 255
DEKLARUJ @files TABELA (ID INT PRIMARY KEY)
WSTAW DO @files
WYBIERZ DISTINCT [data_space_id]
Z sys.database_files Z (NOLOCK)
GDZIE [state] != 0
I [type] = 0
JEŚLI @@ROWCOUNT > 0 ZACZNIJ
USUŃ Z i
Z #Indexes i
LEWY DOŁĄCZ sys.destination_data_spaces dds Z (NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] I i.PartitionNumber = dds.[destination_id]
GDZIE ISNULL(dds.[data_space_id], i.DataSpaceID) W (WYBIERZ * Z @files)
KONIEC
DEKLARUJ @DBID INT
, @DBNAME SYSNAME
USTAW @DBNAME = DB_NAME()
WYBIERZ @DBID = [database_id]
Z sys.databases Z (NOLOCK)
GDZIE [name] = @DBNAME
JEŚLI OBJECT_ID('tempdb.dbo.#Fragmentation') JEST NIE NULL
ZRZUĆ TABELĘ #Fragmentation
UTWÓRZ TABELĘ #Fragmentation (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, PartitionNumber INT NOT NULL
, Fragmentation FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
WSTAW DO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
WYBIERZ i.ObjectID
, i.IndexID
, i.PartitionNumber
, r.[avg_fragmentation_in_percent]
Z #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
GDZIE i.PagesCount = @Fragmentation
LUB
i.PagesCount > @PreDescribeSize
LUB
i.IndexType W (5, 6)
)
Jak widać z samego zapytania, często używane są tymczasowe tabele. Robi się to po to, aby uniknąć rekompilacji, a w przypadku dużej schemy, plan mógł być generowany równolegle podczas wstawiania danych, ponieważ wstawianie za pomocą zmiennych tabelowych jest możliwe tylko w jednym wątku.
Po wykonaniu powyższego skryptu pojawi się okno z tabelą indeksów:
Można tutaj również wyświetlić inne szczegółowe informacje, takie jak:
- baza danych
- liczba sekcji
- data i czas ostatniego dostępu
- kompresja
- grupa plików
itd.
Kolumny można dostosować:
W komórkach kolumny Fix można wybrać, jakie działanie zostanie wykonane podczas optymalizacji. Po zakończeniu skanowania działanie domyślne jest wybierane na podstawie wybranych ustawień:
Należy wybrać odpowiednie indeksy do przetworzenia.
Za pomocą głównego menu można zarówno zapisać skrypt (ten sam przycisk uruchamia też proces optymalizacji indeksów):
jak i zapisać tabelę w różnych formatach (ten sam przycisk pozwala otworzyć szczegółowe ustawienia analizy i optymalizacji indeksów):
Informacje można również zaktualizować, klikając trzeci przycisk z lewej w głównym menu obok lupy.
Przycisk z lupą pozwala wybrać odpowiednie bazy danych do przeglądania.
Obecnie nie ma pełnoprawnego systemu pomocy. Dlatego naciśnięcie przycisku „?” spowoduje jedynie pojawienie się modalnego okna z podstawowymi informacjami o produkcie:
Oprócz wszystkiego opisanego powyżej w głównym menu znajduje się pasek wyszukiwania:
Podczas uruchamiania procesu optymalizacji indeksów:
Na dole okna można również przeglądać log wykonywanych działań:
W oknie szczegółowych ustawień analizy i optymalizacji indeksów można dostosować bardziej szczegółowe opcje:
Życzenia dotyczące aplikacji:
- umożliwić wybiórcze aktualizowanie statystyk nie tylko dla indeksów i na różne sposoby (całkowicie aktualizować lub częściowo)
- umożliwić nie tylko wybór DB, ale i różnych serwerów (to bardzo wygodne, gdy jest wiele instancji MS SQL Server)
- dla większej elastyczności w użytkowaniu proponuje się owinąć komendy w biblioteki i wyprowadzić w polecenia PowerShell, jak to zrobiono np. tutaj:
- umożliwić zachowanie i modyfikację ustawień osobistych zarówno dla całej aplikacji, jak i w razie potrzeby dla każdego egzemplarza MS SQL Server i każdej bazy danych
- z punktu 2 i 4 wynika życzenie utworzenia grup baz danych oraz grup egzemplarzy MS SQL Server, dla których ustawienia są identyczne
- przeprowadzić wyszukiwanie duplikatów indeksów (pełnych i niepełnych, które albo nieznacznie się różnią, albo różnią się tylko pod względem dodanych kolumn)
- ponieważ SQLIndexManager jest używany tylko dla systemów MS SQL Server, należy to odzwierciedlić w nazwie, na przykład w ten sposób: SQLIndexManager dla MS SQL Server
- wszystkie części aplikacji nie-GUI przenieść do osobnych modułów i przepisać na .NET Core 2.1
W momencie pisania artykułu punkt 6 z życzeń jest aktywnie rozwijany i już ma wsparcie w postaci wyszukiwania pełnych i podobnych duplikatów:
Źródła
Źródło: habr.com
