Come è noto, gli indici svolgono un ruolo importante nei database, fornendo ricerche rapide ai record necessari. È quindi fondamentale mantenerli correttamente. Ci sono molte risorse, anche online, dedicate all'analisi e ottimizzazione. Ad esempio, di recente è stata pubblicata un'analisi su questo argomento in .
Esistono molte soluzioni, sia a pagamento che gratuite. Ad esempio, c'è un'applicazione pronta , basata su un metodo adattivo per l'ottimizzazione degli indici.
Successivamente, analizzeremo l'utility gratuita , scritta da .
La principale differenza tecnica tra SQLIndexManager e altri simili è spiegata dallo stesso autore. e .
In questo articolo, daremo uno sguardo al progetto e alle possibilità di utilizzo della soluzione software.
Si discute di quest'utility .
Nel tempo, la maggior parte dei problemi e dei bug è stata risolta.
Passiamo ora all'utility SQLIndexManager.
L'applicazione è scritta in C# .NET Framework 4.5 utilizzando Visual Studio 2017 e utilizza DevExpress per i moduli:
e appare nel seguente modo:
Tutte le query vengono generate nei seguenti file:
- Index
- Query
- QueryEngine
- ServerInfo
Quando ci si connette a un database e si inviano query al DBMS, l'applicazione si registra come segue:
ApplicationName="SQLIndexManager" All'avvio dell'applicazione si aprirà una finestra modale per l'aggiunta di una connessione:
Attualmente non funziona il caricamento completo dell'elenco di tutte le istanze di MS SQL Server disponibili nelle reti locali.
È possibile aggiungere una connessione anche utilizzando il primo pulsante a sinistra nel menu principale:
Successivamente verranno eseguite le seguenti query al DBMS:
- Recupero delle informazioni sul DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Recupero dell'elenco dei database disponibili con le loro proprietà brevi
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
Dopo l'esecuzione degli script sopra indicati, verrà visualizzata una finestra contenente informazioni sintetiche sui database dell'istanza di MS SQL Server selezionata:
È importante notare che le informazioni dettagliate vengono mostrate in base ai diritti. Se si dispone di , è possibile selezionare i dati dalla vista . Se non si dispone di tali diritti, verranno restituiti solo i dati minimi per non rallentare la query.
Qui è necessario selezionare i database di interesse e premere il pulsante "OK".
Successivamente verrà eseguito il seguente script per ciascun database selezionato per analizzare lo stato degli indici:
Analisi dello stato degli indici
dichiarare @Fragmentation float=15;
dichiarare @MinIndexSize bigint=768;
dichiarare @MaxIndexSize bigint=1048576;
dichiarare @PreDescribeSize bigint=32768;
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SE OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
ELIMINA TABELLA #AllocationUnits
CREA TABELLA #AllocationUnits (
ContainerID BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages BIGINT NOT NULL
)
INSERISCI IN #AllocationUnits (ContainerID, ReservedPages, UsedPages)
SELEZIONA [container_id]
, SOMMA([total_pages])
, SOMMA([used_pages])
DA sys.allocation_units CON NOLOCK
RAGGRUPPA PER [container_id]
HAVING SOMMA([total_pages]) TRA @MinIndexSize E @MaxIndexSize
SE OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
ELIMINA TABELLA #ExcludeList
CREA TABELLA #ExcludeList (ID INT PRIMARY KEY)
INSERISCI IN #ExcludeList
SELEZIONA [object_id]
DA sys.objects CON NOLOCK
DOVE [type] IN ('V', 'U')
E ( [is_ms_shipped] = 1 )
SE OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
ELIMINA TABELLA #Partitions
SELEZIONA [object_id]
, [index_id]
, [partition_id]
, [partition_number]
, [rows]
, [data_compression]
IN #Partitions
DA sys.partitions CON NOLOCK
DOVE [object_id] > 255
E [rows] > 0
E [object_id] NOT IN (SELEZIONA * DA #ExcludeList)
SE OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
ELIMINA TABELLA #Indexes
CREA TABELLA #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)
)
INSERISCI IN #Indexes
SELEZIONA ObjectID = i.[object_id]
, IndexID = i.index_id
, IndexName = i.[name]
, PagesCount = a.ReservedPages
, UnusedPagesCount = CASE WHEN ASSOLUTO(a.ReservedPages - a.UsedPages) > 32 ALLORA a.ReservedPages - a.UsedPages ELSE 0 FINE
, 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]
DA #AllocationUnits a
UNISCI #Partitions p SU a.ContainerID = p.[partition_id]
UNISCI sys.indexes i CON NOLOCK SU i.[object_id] = p.[object_id] E p.[index_id] = i.[index_id]
DOVE i.[type] IN (0, 1, 2, 5, 6)
E i.[object_id] > 255
DICHIARARE @files TABELLA (ID INT PRIMARY KEY)
INSERISCI IN @files
SELEZIONA DISTINTIVO [data_space_id]
DA sys.database_files CON NOLOCK
DOVE [state] != 0
E [type] = 0
SE @@ROWCOUNT > 0 INIZIA
ELIMINA DA i
DA #Indexes i
SINISTRA UNISCI sys.destination_data_spaces dds CON NOLOCK SU i.DataSpaceID = dds.[partition_scheme_id] E i.PartitionNumber = dds.[destination_id]
DOVE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELEZIONA * DA @files)
FINE
DICHIARARE @DBID INT
, @DBNAME SYSNAME
SET @DBNAME = DB_NAME()
SELEZIONA @DBID = [database_id]
DA sys.databases CON NOLOCK
DOVE [name] = @DBNAME
SE OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
ELIMINA TABELLA #Fragmentation
CREA TABELLA #Fragmentation (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, PartitionNumber INT NOT NULL
, Fragmentation FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
INSERISCI IN #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
SELEZIONA i.ObjectID
, i.IndexID
, i.PartitionNumber
, r.[avg_fragmentation_in_percent]
DA #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
DOVE i.PagesCount = @Fragmentation
OPPURE
i.PagesCount > @PreDescribeSize
OPPURE
i.IndexType IN (5, 6)
)
Come si può vedere dalle richieste stesse, le tabelle temporanee sono utilizzate abbastanza frequentemente. Questo è stato fatto per evitare le ricompilazioni e, nel caso di schemi ampi, il piano può essere generato in parallelo durante l'inserimento dei dati, poiché l'inserimento con variabili di tabella è possibile solo in un flusso.
Dopo aver eseguito lo script sopra indicato, apparirà una finestra con la tabella degli indici:
Inoltre, qui è possibile visualizzare anche altre informazioni dettagliate, come:
- database
- numero di sezioni
- data e ora dell'ultimo accesso
- compressione
- gruppo file
ecc.
Le colonne stesse possono essere configurate:
Nelle celle della colonna Fix è possibile scegliere quale azione verrà eseguita durante l'ottimizzazione. Inoltre, al termine della scansione, l'azione predefinita è selezionata in base alle impostazioni scelte:
È necessario selezionare gli indici da elaborare.
Tramite il menu principale è possibile sia salvare lo script (questo stesso pulsante avvia il processo di ottimizzazione degli indici):
sia salvare la tabella in vari formati (questo stesso pulsante consente di aprire le impostazioni dettagliate per l'analisi e l'ottimizzazione degli indici):
Inoltre, le informazioni possono essere aggiornate premendo il terzo pulsante a sinistra nel menu principale accanto alla lente di ingrandimento.
Il pulsante con la lente di ingrandimento consente di selezionare i database desiderati per la revisione.
Attualmente non esiste un sistema di aiuto completo. Pertanto, premendo il pulsante “?” si aprirà semplicemente una finestra modale contenente le informazioni principali sul prodotto software:
Oltre a quanto sopra, nel menu principale è presente una barra di ricerca:
All'avvio del processo di ottimizzazione degli indici:
Inoltre, nella parte inferiore della finestra è possibile visualizzare il registro delle azioni eseguite:
Nella finestra delle impostazioni dettagliate per l'analisi e l'ottimizzazione degli indici, è possibile configurare opzioni più specifiche:
Suggerimenti per l'applicazione:
- rendere possibile l'aggiornamento selettivo delle statistiche non solo per gli indici, e anche in diversi modi (aggiornare completamente o parzialmente)
- rendere possibile non solo la selezione dei database, ma anche di server diversi (questo è molto comodo quando ci sono molte istanze di MS SQL Server)
- per una maggiore flessibilità nell'utilizzo, si propone di racchiudere i comandi in librerie e di esportarli nei comandi PowerShell, come fatto qui, ad esempio:
- abilitare la possibilità di salvare e modificare le impostazioni personali sia per l'intera applicazione che, se necessario, per ogni istanza di MS SQL Server e ogni database
- dai punti 2 e 4 deriva il desiderio di creare gruppi per database e gruppi per istanze di MS SQL Server, per i quali le impostazioni sono identiche
- realizzare la ricerca di duplicati degli indici (completi e parziali, che differiscono leggermente o solo per colonne incluse)
- poiché SQLIndexManager è utilizzato solo per i DBMS MS SQL Server, è necessario riflettere questo nel nome, ad esempio, nel seguente modo: SQLIndexManager for MS SQL Server
- esportare tutte le parti dell'applicazione non GUI in moduli separati e riscriverli su .NET Core 2.1
Al momento della scrittura dell'articolo, il punto 6 delle richieste è attivamente in fase di sviluppo e c'è già supporto per la ricerca di duplicati completi e simili:
Fonti
Fonte: habr.com
