On teada, et indeksid mängivad andmebaasi haldust süsteemides olulist rolli, pakkudes kiiret juurdepääsu vajalikele kirjadele. Seetõttu on nende õigeaegne hooldus nii tähtis. Analüüsist ja optimeerimisest on kirjutatud palju, sealhulgas ka Internetis. Näiteks hiljuti tehti selle teema ülevaade .
Selle jaoks on olemas palju nii tasulisi kui ka tasuta lahendusi. Näiteks on olemas valmis , mis põhineb kohandataval indeksi optimeerimise meetodil.
Järgmine arutleme tasuta utiliidi üle , mille autor on .
Peamine tehniline erinevus SQLIndexManageri ja teiste sarnaste vahel toob esile autor ise ja .
Käesolevas artiklis vaatame projekti ja selle tarkvara lahenduse kasutusvõimalusi.
Arutatakse seda utiliiti .
Aja jooksul on enamus märkusi ja vigu parandatud.
Nii et liikume nüüd SQLIndexManageri utiliidi juurde.
Rakendus on kirjutatud C# .NET Framework 4.5 keeles Visual Studio 2017-s ja kasutab DevExpressi vormide jaoks:
ja näeb välja järgmiselt:
Kõik päringud koostatakse järgnevates failides:
- Index
- Query
- QueryEngine
- ServerInfo
Andmebaasile ühendudes ja päringute saatmisel andmebaasi haldust süsteemile on rakendus registreeritud järgmiselt:
ApplicationName="SQLIndexManager" Rakenduse käivitamisel avaneb modaalne aken ühenduse lisamiseks:
Siin ei tööta veel täieliku nimekirja laadimine kõigist MS SQL Serveri instantsidest, mis on kergesti kättesaadavad kohalikes võrkudes.
Lisaks saab ühendust lisada ka vasakpoolseima nupu kaudu peamenüüs:
Seejärel käivitatakse järgmised päringud andmebaasi haldust süsteemile:
- Andmebaasi haldust süsteemi teabe saamine
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Saama nimekirja kergesti kättesaadavatest andmebaasidest koos nende lühikeste omadustega
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
Pärast ülaltoodud skriptide täitmist ilmub aken, mis sisaldab lühikest teavet valitud MS SQL Serveri instantsi andmebaasidest:
On oluline märkida, et piiratud teave kuvatakse vastavalt õigustele. Kui on , siis saab valida andmeid vaates . Kui selliseid õigusi pole, tagastatakse lihtsalt vähem andmeid, et päringu aeglustumist vältida.
Siin tuleb valida huvipakkuvad andmebaasid ja vajutada nuppu "OK".
Seejärel käivitatakse järgmine skript iga valitud andmebaasi jaoks indeksite oleku analüüsimiseks:
Indeksite oleku analüüs
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)
)
Nagu näha on, kasutatakse ajutisi tabeleid üsna sageli. See on tehtud selleks, et vältida rekompileerimist, ja kui skeem on suur, võib plaan andmete sisestamisel genereerida paralleelselt, kuna tabelimuutujatega sisestamine on võimalik ainult ühes voos.
Pärast ülaltoodud skripti täitmist ilmub aken, kus on tabel indeksitest:
Siin on võimalik kuvada ka muid üksikasjalikke andmeid, nagu:
- andmebaas
- jaotuste arv
- viimane juurdepääs kuupäev ja kellaaeg
- kompressioon
- failigrupp
jne.
Veerge saab seadistada:
Veergude Fix rakkudes saab valida, milline toiming toimub optimeerimise käigus. Samuti valitakse skaneerimise lõpetamisel vaikeoperatsioon valitud seadete põhjal:
On vajalik valida vajalikud indeksid töötlemiseks.
Peamenüü kaudu saab kui salvestada skripti (see nupp käivitab ka indekseerimise optimeerimise protsessi):
ning salvestada tabel erinevatesse formaatidesse (see nupp avab ka üksikasjalikud seadistused indeksite analüüsiks ja optimeerimiseks):
Samuti saab teavet värskendada, vajutades peamenüüs kolmandat nuppu vasakul, mis asub luubi kõrval.
Luubi nupp võimaldab valida vajalikud andmebaasid vaatamiseks.
Hetkel ei ole täielikku abisüsteemi. Seetõttu nupp "?" kutsub lihtsalt esile modaalakna, mis sisaldab põhiteavet tarkvaratootest:
Lisaks ülaltoodule on peamenüüs ka otsinguriba:
Indekseerimise optimeerimise protsessi käivitamisel:
Samuti saab akna allosas näha teostatavate toimingute logi:
Indeksite analüüsi ja optimeerimise detailsete seadete aknas saab seadistada täiendavad valikud:
Soovid rakendusele:
- teha võimalikuks valikulise statistika värskendamine mitte ainult indeksite jaoks, vaid ka erinevatel viisidel (täielikult või osaliselt värskendada)
- teha võimalikuks mitte ainult andmebaaside, vaid ka erinevate serverite valik (see on väga mugav, kui on palju MS SQL Serveri koopiaid)
- suurema paindlikkuse saavutamiseks soovitatakse käsud teeke pakendada ja välja tuua PowerShelli käskudesse, nagu on tehtud näiteks siin:
- võimaldada salvestada ja muuta isiklikke seadeid nii kogu rakenduse kui ka vajadusel igas MS SQL Serveri eksemplaris ja igas andmebaasis.
- punktist 2 ja 4 tuleneb soov luua andmebaaside grupid ja MS SQL Serveri eksemplaride grupid, millel on sama seade.
- teha indeksite (täiskomplektsete ja mittetäiskomplektsete, mis erinevad veidi või erinevad ainult kaasatud veergude poolest) topeltotsing.
- kuna SQLIndexManager on mõeldud ainult MS SQL Serveri andmebaasidele, tuleks seda kajastada nimes, näiteks järgmiselt: SQLIndexManager for MS SQL Server.
- kõik rakenduse osad, mis ei ole GUI, tuleks välja viia eraldi moodulitesse ja ümber kirjutada .NET Core 2.1 peal.
Käesoleva artikli kirjutamise ajal on punkt 6 soove aktiivselt arendamisel ja toetust on juba olemas täiskomplektsete ja sarnaste topeltotsingute näol:
Allikad
Allikas: habr.com
