Kao što je poznato, indeksi igraju važnu ulogu u DBMS-u, omogućujući brzo pretraživanje potrebnih zapisa. Zbog toga je važno da ih pravodobno održavamo. O analizi i optimizaciji napisano je mnogo materijala, uključujući i na Internetu. Na primjer, nedavno je napravljena retrospektiva ove teme u .
Postoji mnoštvo kako plaćenih, tako i besplatnih rješenja za to. Na primjer, postoji gotovo , zasnovano na adaptivnoj metodi optimizacije indeksa.
Dalje ćemo razmotriti besplatnu alatku , čiji je autor .
Osnovna tehnička razlika između SQLIndexManager i niza drugih analoga dolazi iz same autorove perspektive. i .
U ovom članku ćemo pogledati projekt i mogućnosti korištenja ovog softverskog rješenja.
Ova alatka se raspravlja o .
S vremenom su najveći dio komentara i bugova ispravljeni.
Dakle, prijeđimo sada na sam alat SQLIndexManager.
Aplikacija je napisana u jeziku C# .NET Framework 4.5 u Visual Studio 2017 i koristi DevExpress za obrasce:
i izgleda ovako:
Svi upiti se formiraju u sljedećim datotekama:
- Index
- Upit
- QueryEngine
- ServerInfo
Prilikom povezivanja s bazom podataka i slanja upita DBMS-u, aplikacija se prijavljuje na sljedeći način:
ApplicationName="SQLIndexManager" Prilikom pokretanja aplikacije otvorit će se modalni prozor za dodavanje veze:
Ovdje još uvijek ne radi učitavanje cijelog popisa svih instanci MS SQL Server-a dostupnih putem lokalnih mreža.
Vezu također možete dodati pomoću krajnje lijeve tipke na glavnom izborniku:
Nakon toga pokrenut će se sljedeći upiti prema DBMS-u:
- Dobivanje informacija o DBMS-u
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Dobivanje popisa dostupnih baza podataka s njihovim kratkim svojstvima
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 gorenavedenih skripti, otvorit će se prozor s kratkim informacijama o bazama podataka odabrane instance MS SQL Server-a:
Važno je napomenuti da se proširene informacije prikazuju prema pravima. Ako postoji , možete odabrati podatke iz prikaza . Ako takva prava ne postoje, vraća se manje podataka kako ne bi usporilo upit.
Ovdje je potrebno odabrati zanimljive baze podataka i pritisnuti gumb "OK".
Sljedeći skript bit će izvršen 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 zahtjeva, privremene tablice se često koriste. To je učinjeno kako bi se izbjegle recompilacije i, u slučaju velike sheme, plan bi se mogao generirati paralelno prilikom umetanja podataka, jer je umetanje s tabličnim varijablama moguće samo u jednom toku.
Nakon izvršavanja gornjeg skripta pojavit će se prozor s tablicom indeksa:
Također ovdje možete prikazati i druge detaljne informacije, poput:
- baze podataka
- broj sekcija
- datum i vreme zadnjeg pristupa
- kompresija
- datotečna grupa
itd.
Stupce je moguće konfigurirati:
U ćelijama kolone Fix možete odabrati koja će akcija biti izvršena prilikom optimizacije. Također, nakon završetka skeniranja akcija po defaultu odabire se na temelju odabranih postavki:
Potrebno je odabrati potrebne indekse za obradu.
Pomoću glavnog izbornika možete sačuvati skriptu (ova tipka također pokreće proces optimizacije indeksa):
kao i sačuvati tablicu u različitim formatima (ova tipka omogućuje otvaranje detaljnih postavki za analizu i optimizaciju indeksa):
Također, informacije možete ažurirati pritiskom na treću tipku s lijeve strane u glavnom izborniku pored povećala.
Tipka s povećalom omogućuje odabir potrebnih baza podataka za razmatranje.
Trenutno ne postoji potpuna referentna pomoć. Stoga, pritisak na tipku “?” jednostavno će otvoriti modalni prozor koji sadrži osnovne informacije o proizvodu:
Osim svega gore opisanog, u glavnom izborniku nalazi se linija za pretraživanje:
Kada se pokrene proces optimizacije indeksa:
Također, na dnu prozora možete pregledati log izvršenih radnji:
U prozoru detaljnih postavki analize i optimizacije indeksa moguće je postaviti finije opcije:
Želje za aplikacijom:
- omogućiti selektivno ažuriranje statistika ne samo za indekse, već i na različite načine (puni ili djelomični ažuriranje)
- omogućiti ne samo odabir BDP-a, već i različitih poslužitelja (to je vrlo zgodno kada postoji mnogo instanci MS SQL Servera)
- za veću fleksibilnost u korištenju preporučuje se omotati naredbe u biblioteke i izvesti u PowerShell naredbe, kao što je to učinjeno, na primjer, ovdje:
- omogućiti pohranu i promjenu osobnih postavki, kako za cijelu aplikaciju, tako i, po potrebi, za svaku instancu MS SQL Servera i svaku bazu podataka
- iz točke 2 i 4 proizlazi želja da se naprave grupe po bazama podataka i grupe po instancama MS SQL Servera za koje su postavke jednake
- provesti pretraživanje duplikata indeksa (potpunih i nepotpunih, koji se ili neznatno razlikuju ili se razlikuju samo po uključenim stupcima)
- budući da se SQLIndexManager koristi samo za DBMS MS SQL Server, to treba odraziti u nazivu, na primjer, na sljedeći način: SQLIndexManager za MS SQL Server
- sve dijelove aplikacije koji nisu GUI premjestiti u zasebne module i preraditi na .NET Core 2.1
U vrijeme pisanja ovog članka, točka 6 iz želja je aktivno u razvoju i već postoji podrška u obliku pretraživanja punih i sličnih duplikata:
Izvori
Izvor: habr.com
