Ingyenes SQLIndexManager eszköz áttekintése

Ahogy ismert, az indexek fontos szerepet játszanak az adatbázis-kezelő rendszerekben, lehetővé téve a gyors keresést a szükséges rekordok között. Ezért elengedhetetlen, hogy időben karbantartsuk őket. Az indexek elemzéséről és optimalizálásáról rengeteg anyag készült, beleértve az internetet is. Például nemrégiben egy áttekintést készítettek erről a témáról cikkünkben.

Számos fizetős és ingyenes megoldás létezik erre. Például van kész döntés, amely az indexek optimalizálásának adaptív módszerén alapul.

Ezután egy ingyenes segédprogramot fogunk megvizsgálni SQLIndexManager, amelynek szerzője AlanDenton.

A SQLIndexManager és más hasonló eszközök közötti fő technikai különbség maga a szerző itt és itt.

Ebben a cikkben egy kicsit más szögből nézzük meg a projektet és a program megoldás használati lehetőségeit.

Ezt az eszközt itt.
Idővel a legtöbb megjegyzést és hibát javították.

Most pedig térjünk át magára az SQLIndexManager segédprogramra.

Az alkalmazás C# .NET Framework 4.5 nyelven készült a Visual Studio 2017-ben, és a DevExpress-t használja a űrlapokhoz:

és a következőképpen néz ki:

Minden lekérdezés a következő fájlokban készül:

1. Index
2. SQL lekérdezés továbbítására.
3. QueryEngine
4. ServerInfo

Amikor csatlakozunk az adatbázishoz és lekérdezéseket küldünk az adatbázis-kezelő rendszernek, az alkalmazás a következő módon írja alá magát:

ApplicationName="SQLIndexManager"

Az alkalmazás indításakor egy modal ablak fog megjelenni a kapcsolat hozzáadására:

Jelenleg nem működik a helyi hálózatokban elérhető összes MS SQL Server példány teljes listájának betöltése.

Kapcsolatot a bal szélen található gombbal is hozzáadhatunk a főmenüben:

Ezután a következő lekérdezések indulnak az adatbázis-kezelő rendszeren:

1. Információk lekérése az adatbázis-kezelő rendszerről

   SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
        , Edition       = SERVERPROPERTY('Edition')
        , ServerVersion = SERVERPROPERTY('ProductVersion')
        , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)
   

2. A rendelkezésre álló adatbázisok listájának lekérése rövid tulajdonságaikkal

   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
   

Az említett szkriptek végrehajtása után egy ablak fog megjelenni, amely tartalmazza a kiválasztott MS SQL Server példány adatbázisainak rövid információit:

Fontos megjegyezni, hogy a kiterjesztett információ a jogosultságok alapján jelenik meg. Ha van sysadmin, akkor az adatokat a sys.master_filesból lehet választani. Ha ilyen jogok nincsenek, akkor csak kevesebb adat tér vissza, hogy a lekérdezés ne lassuljon.

Itt ki kell választani a kívánt adatbázisokat és megnyomni az 'OK' gombot.

Ezután a következő szkript fut le minden kiválasztott adatbázisra az indexek állapotának elemzésére:

Indexek állapotának elemzése

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)
    )

Ahogy a lekérdezések maguk is mutatják, a ideiglenes táblák gyakran használatosak. Ez azért van, hogy elkerülhetőek legyenek a rekompilációk, és nagy séma esetén a terv párhuzamosan generálható legyen az adatok beszúrásakor, mivel a táblázati változókkal való beszúrás csak egy szálon lehetséges.

A fenti szkript végrehajtása után egy ablak jelenik meg az indexek táblázatával:

Itt más részletes információkat is meg lehet jeleníteni, mint például:

1. adatbázis
2. szekciók száma
3. az utolsó hozzáférés dátuma és ideje
4. tömörítés
5. fájlcsoport

stb.
A saját oszlopokat lehet konfigurálni:

A Fix oszlop celláiban kiválasztható, hogy milyen műveletet hajtsanak végre az optimalizálás során. A beolvasás befejezésekor az alapértelmezett művelet a kiválasztott beállítások alapján kerül kiválasztásra:

Ki kell választani a megfelelő indexeket a feldolgozáshoz.

A főmenü segítségével el lehet menteni a szkriptet (ez a gomb indítja el az indexek optimalizálási folyamatát is):

ugyancsak a táblázatokat különféle formátumokba menteni (ez a gomb lehetővé teszi a részletes beállítások megnyitását az indexek elemzéséhez és optimalizálásához):

Az információ frissítéséhez a főmenü bal oldalán található harmadik gombra kell kattintani a nagyító mellett.

A nagyítós gomb lehetővé teszi a szükséges adatbázisok kiválasztását.

A jelenlegi pillanatban nincs teljes körű súgórendszer. Ezért a „?” gombra kattintva csak egy modal ablak jelentkezik meg, amely az alapinformációkat tartalmazza a szoftvertermékről:

Minden fent említett dolgon kívül a főmenüben található egy keresősor:

Az indexek optimalizálási folyamatának indításakor:

Az ablak alján a végrehajtott műveletek naplója is megtekinthető:

Az indexek elemzéséhez és optimalizálásához tartozó részletes beállítások ablakában finomabb opciókat lehet beállítani:

Kérések az alkalmazáshoz:

1. lehetővé tenni a statisztikák szelektív frissítését nemcsak az indexek esetén, hanem különböző módokon (teljesen frissíteni vagy részben)
2. lehetővé tenni nemcsak az adatbázisok, hanem különböző szerverek kiválasztását is (ez nagyon kényelmes, ha sok MS SQL Serverpéldány van)
3. nagyobb rugalmasság érdekében javasoljuk, hogy a parancsokat könyvtárakba csomagolják, és PowerShell parancsként jelenítsék meg, ahogyan például itt is:
4. dbatools.io/commands
5. Lehetővé kell tenni a személyes beállítások mentését és módosítását az alkalmazás egészére, valamint szükség esetén minden egyes MS SQL Server példányra és adatbázisra vonatkozóan.
6. A 2. és 4. pontból következik, hogy javasolt adatbázis- és MS SQL Server példány csoportokat létrehozni, amelyeknek azonosak a beállításaik.
7. Keresni kell a duplikált indexeket (teljes és részleges, amelyek vagy csak kissé eltérnek, vagy csak a beillesztett oszlopokban különböznek).
8. Mivel a SQLIndexManager csak az MS SQL Server adatbázis-kezelő rendszerhez használható, ezt tükrözni kell a névben, például a következőképpen: SQLIndexManager for MS SQL Server.
9. Az alkalmazás összes GUI-n kívüli részét külön modulokba kell szervezni és .NET Core 2.1-re kell átírni.

A cikk írásának időpontjában a 6. pontjában javasolt funkciók aktívan fejlesztés alatt állnak, és már létezik a teljes és hasonló duplikátumok keresésére vonatkozó támogatás.

Források

• Adaptív indexoptimalizáló módszer.
• SQLIndexManager eszköz.
• A SQLIndexManager eszköz rövid technikai áttekintése.
• Beszélgetés a SQLIndexManager eszközről.
• SQL Index Manager – Ingyenes GUI eszköz az SQL Server és Azure index karbantartásához.
• SQL Server indexelemzés és optimalizálás.

Forrás: habr.com