Kao što je poznato, indeksi igraju važnu ulogu u DBMS-u, omogućavajući brzu pretragu potrebnih zapisa. Zbog toga je važno da ih redovno održavate. O analizi i optimizaciji napisano je mnogo materijala, uključujući i na internetu. Na primer, nedavno je urađena analiza ove teme u .
Postoji mnogo rešenja, i plaćenih i besplatnih, za ovo. Na primer, postoji pripremljeno , zasnovano na adaptivnoj metodi optimizacije indeksa.
Dalje ćemo razmotriti besplatnu alatku , čiji je autor .
Glavna tehnička razlika između SQLIndexManager-a i nekih drugih sličnih alata dolazi od samog autora. i .
U ovom članku ćemo pogledati projekat i mogućnosti korišćenja ovog softverskog rešenja.
Ova alatka se raspravlja .
Vremenom su ispravljene većina zapažanja i grešaka.
Dakle, pređimo sada na sam alat SQLIndexManager.
Aplikacija je napisana u C# .NET Framework 4.5 u Visual Studio 2017 i koristi DevExpress za forme:
i izgleda ovako:
Svi upiti se kreiraju u sledećim datotekama:
- Index
- Query
- QueryEngine
- ServerInfo
Priključivanjem na bazu podataka i slanjem upita prema DBMS-u, aplikacija se registrova na sledeći način:
ApplicationName="SQLIndexManager" Kada pokrenete aplikaciju, otvoriće se modalni prozor za dodavanje veze:
Trenutno ne funkcioniše učitavanje celokupnog spiska svih instanci MS SQL Server-a dostupnih putem lokalnih mreža.
Takođe, vezu možete dodati putem krajnje leve dugmadi u glavnom meniju:
Dalje će se izvršiti sledeći upiti prema DBMS-u:
- Dobijanje informacija o DBMS-u
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Dobijanje liste dostupnih baza podataka sa njihovim kratkim karakteristikama
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
Nakon izvršavanja gore navedenih skripti, pojaviće se prozor koji sadrži kratke informacije o bazama podataka izabranog instancu MS SQL Server-a:
Važno je napomenuti da se dodatne informacije prikazuju na osnovu prava. Ako postoje , možete birati podatke iz prikaza . Ako takva prava ne postoje, vraća se manje podataka kako se ne bi usporavao zahtev.
Ovde je potrebno odabrati interesantne baze podataka i pritisnuti dugme "OK".
Zatim će se izvesti sledeći skript za svaku odabranu bazu podataka radi analize stanja indeksa:
Analiza stanja indeksa
declare @Fragmentation float=15;
declare @MinIndexSize bigint=768;
declare @MaxIndexSize bigint=1048576;
declare @PreDescribeSize bigint=32768;
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
IF OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
DROP TABLE #AllocationUnits
CREATE TABLE #AllocationUnits (
ContainerID BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages BIGINT NOT NULL
)
INSERT INTO #AllocationUnits (ContainerID, ReservedPages, UsedPages)
SELECT [container_id]
, SUM([total_pages])
, SUM([used_pages])
FROM sys.allocation_units WITH(NOLOCK)
GROUP BY [container_id]
HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize
IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
DROP TABLE #ExcludeList
CREATE TABLE #ExcludeList (ID INT PRIMARY KEY)
INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
AND ( [is_ms_shipped] = 1 )
IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
DROP TABLE #Partitions
SELECT [object_id]
, [index_id]
, [partition_id]
, [partition_number]
, [rows]
, [data_compression]
INTO #Partitions
FROM sys.partitions WITH(NOLOCK)
WHERE [object_id] > 255
AND [rows] > 0
AND [object_id] NOT IN (SELECT * FROM #ExcludeList)
IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #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)
)
INSERT INTO #Indexes
SELECT 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]
FROM #AllocationUnits a
JOIN #Partitions p ON a.ContainerID = p.[partition_id]
JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.[object_id] AND p.[index_id] = i.[index_id]
WHERE i.[type] IN (0, 1, 2, 5, 6)
AND i.[object_id] > 255
DECLARE @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
AND [type] = 0
IF @@ROWCOUNT > 0 BEGIN
DELETE FROM i
FROM #Indexes i
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)
END
DECLARE @DBID INT
, @DBNAME SYSNAME
SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME
IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
DROP TABLE #Fragmentation
CREATE TABLE #Fragmentation (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, PartitionNumber INT NOT NULL
, Fragmentation FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
SELECT i.ObjectID
, i.IndexID
, i.PartitionNumber
, r.[avg_fragmentation_in_percent]
FROM #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
WHERE i.PagesCount = @Fragmentation
OR
i.PagesCount > @PreDescribeSize
OR
i.IndexType IN (5, 6)
)
Kao što se može vidjeti iz samih upita, privremene tablice se često koriste. To se radi da ne bi došlo do rekompilacija i kako bi se, u slučaju velike sheme, plan mogao generirati paralelno prilikom umetanja podataka, jer umetanje s tabličnim varijablama može biti moguće samo u jednom toku.
Nakon izvršavanja gornjeg skripta, pojavit će se prozor s tablicom indeksa:
Također, ovdje se može prikazati i druge detaljne informacije, kao što su:
- baza podataka
- broj sekcija
- datum i vrijeme posljednjeg pristupa
- kompresija
- fajl grupa
itd.
Same kolone se mogu prilagoditi:
U ćelijama kolone Fix možete odabrati koja će radnja biti izvršena prilikom optimizacije. Također, na kraju skeniranja radnja po defaultu se bira na osnovu odabranih postavki:
Potrebno je odabrati potrebne indekse za obradu.
Pomoću glavnog menija možete sačuvati skript (ova ista dugmad pokreće proces optimizacije indeksa):
kao i sačuvati tabelu u različitim formatima (ova ista dugmad omogućava otvaranje detaljnih postavki za analizu i optimizaciju indeksa):
Također, informacije se mogu ažurirati pritiskom na treće dugme lijevo u glavnom meniju pored lupe.
Dugme s lupom omogućava odabir potrebnih baza podataka za razmatranje.
Trenutno ne postoji potpuna referentna sistem. Stoga će pritiskom na dugme “?” jednostavno doći do modnog prozora koji sadrži osnovne informacije o programskom proizvodu:
Osim svega gore navedenog, u glavnom meniju postoji pretraživačka traka:
Prilikom pokretanja procesa optimizacije indeksa:
Također, na dnu prozora možete pregledati log izvršenih radnji:
U prozoru detaljnih postavki analize i optimizacije indeksa možete prilagoditi finije opcije:
Želje za aplikacijom:
- omogućiti selektivno ažuriranje statistike ne samo za indekse već i na različite načine (potpuno ažurirati ili delimično)
- omogućiti ne samo izbor Baza podataka, već i različitih servera (to je vrlo korisno kada postoji mnogo instanci MS SQL Servera)
- za veću fleksibilnost u korištenju predlaže se obavijanje komandi u biblioteke i izlaženje u PowerShell komande, kao što je to urađeno, na primjer, ovdje:
- omogućiti čuvanje i promenu ličnih podešavanja kako za celu aplikaciju, tako i za svaki primerak MS SQL Server-a i svaku bazu podataka pojedinačno
- iz tačaka 2 i 4 proizlazi želja da se naprave grupe po bazama podataka i grupe po primerima MS SQL Server-a, za koje su podešavanja identična
- napraviti pretragu duplikata indeksa (potpunih i nepotpunih, koji se samo malo razlikuju ili se razlikuju samo po uključenim kolonama)
- pošto se SQLIndexManager koristi samo za SGBD MS SQL Server, to se mora odraziti u nazivu, na primer, ovako: SQLIndexManager za MS SQL Server
- sve delove aplikacije koji nisu GUI izdvojiti u posebne module i prepisati na .NET Core 2.1
u trenutku pisanja ovog članka, tačka 6 iz želja aktivno se razvija i već postoji podrška u obliku pretrage potpunih i sličnih duplikata:
Izvori
Izvor: habr.com
