Ilma veebivahendita SQLIndexManageri ülevaade

Nagu teada, mängivad indeksid andmebaasis olulist rolli, pakkudes kiiret juurdepääsu vajalikele kirjadele. Seetõttu on oluline neid õigeaegselt hooldada. Analüüsist ja optimeerimisest on kirjutatud piisavalt materjali, sealhulgas ka Internetis. Näiteks tehti hiljuti selle teema ülevaade selles publikatsioonis.

On olemas palju nii tasulisi kui tasuta lahendusi selle jaoks. Näiteks on valmis lahendus, mis põhineb dünaamilisel indeksite optimeerimise meetodil.

Järgmine, vaatame tasuta tööriista SQLIndexManager, mille autoriks on AlanDenton.

SQLIndexManageri ja mõne teiste sarnase tööriista vaheline põhitehniline erinevus on välja toodud autori enda poolt. siit ja siit.

Selles artiklis vaatame projekti ja selle tarkvaralahenduse kasutamise võimalusi.

Arutatakse selle tööriista üle siit.
Aja jooksul on suurem osa märkustest ja vigadest parandatud.

Nii, liikume nüüd tööriista SQLIndexManager juurde.

Rakendus on kirjutatud C# .NET Framework 4.5 keeles Visual Studio 2017-s ning kasutab DevExpressi vormide jaoks:

ja see näeb välja järgmiselt:

Kõik päringud koostatakse järgmistes failides:

1. Indeks
2. Päring
3. PäringuMootor
4. ServeriTeave

Andmebaasi ühendamisel ja päringute saatmisel DBMS-ile, registreerib rakendus end järgmiselt:

ApplicationName="SQLIndexManager"

Rakenduse käivitamisel avatakse modaalne aken uue ühenduse lisamiseks:

Siin ei tööta veel kõigi MS SQL Serveri eksemplaride täieliku nimekirja laadimine, mis on saadaval kohalikes võrkudes.

Samuti saab ühenduse lisada, kasutades peamenüü vasakpoolset nuppu:

Seejärel käivitatakse järgmised päringud DBMS-ile:

1. Teabe saamine DBMS-ist

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

2. Saadaval olevate andmebaaside nimekirja saamine koos nende lühikirjeldustega

   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
   

Ülaltoodud skriptide täitmisel kuvatakse aken, mis sisaldab valitud MS SQL Serveri eksemplari andmebaaside lühikest teavet:

Oluline on märkida, et põhjalik teave kuvatakse vastavalt õigustele. Kui on olemas sysadmin, saab andmeid valida vaate kaudu sys.master_files. Kui selliseid õigusi ei ole, tagastatakse lihtsalt vähem andmeid, et päringut ei aeglustada.

Siin tuleb valida huvipakkuvad andmebaasid ja vajutada nuppu "OK".

Seejärel käivitatakse iga valitud andmebaasi jaoks järgmine skript indeksi oleku analüüsimiseks:

Indeksi 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, kasutatakse ajutisi tabeleid suhteliselt tihti. See on tehtud selleks, et vältida ümberkompileerimist, ja suure skeemi puhul saab plaan genereeruda paralleelseltandmete sisestamisel, kuna tabelimuutujatega sisestamine on võimalik ainult ühes voos.

Pärast eespool nimetatud skripti täitmist ilmub aken indeksitabeliga:

Samuti saab kuvada ka muud üksikasjalikku teavet, näiteks:

1. andmebaas
2. jaotiste arv
3. viimane juurdepääsu kuupäev ja kellaaeg
4. kompressioon
5. failigrupp

jne.
Isegi veerge saab kohandada:

Veeru Fix rakkudes saab valida, milline tegevus toimub optimeerimise ajal. Samuti, skaneerimise lõpus valitakse vaikimisi tegevus põhinedes valitud seadistustele:

Vajalik on valida vajalikud indeksid töötlemiseks.

Peamenüü abil saab nii skripti salvestada (see sama nupp käivitab indeksi optimeerimise protsessi):

kui ka salvestada tabel erinevatesse formaatidesse (see sama nupp võimaldab avada üksikasjalikud seaded indeksite analüüsimiseks ja optimeerimiseks):

Samuti saab teavet värskendada, vajutades peamenüü kolmandat nuppu vasakul, mis asub suurendusklaasi kõrval.

Suurendusklaasiga nupp võimaldab valida vajalikud andmebaasid vaatamiseks.

Praegu pole täielikku abisüsteemi olemas. Seetõttu põhjustab nupu “?” vajutamine lihtsalt modaalaken, mis sisaldab põhiinformatsiooni tarkvara kohta:

Lisaks eelnevale on peamenüüs otsinguriba:

Indeksite optimeerimise protsessi käivitamisel:

Samuti saab akna allosas vaadata teostatavate toimingute logi:

Indeksite analüüsi ja optimeerimise üksikasjalike seadete aknas saab seadistada peenemaid valikuid:

Soovid rakendusele:

1. teha võimalikuks valikuliselt värskendada statistikat mitte ainult indeksite jaoks ning ka erinevate viisidega (täielik või osaline värskendus)
2. teha võimalikuks mitte ainult andmebaaside, vaid ka erinevate serverite valimine (see on väga mugav, kui on palju MS SQL Serveri eksemplare)
3. suurem paindlikkus kasutamisel, pakutakse käskude pakendamist raamatukogudesse ja väljundit PowerShelli käskudesse, nagu on näiteks tehtud siin:
4. dbatools.io/commands
5. võimaldada salvestada ja muuta isiklikke seadeid nii kogu rakendusele kui ka vajadusel iga MS SQL Serveri eksemplari ja iga andmebaasi jaoks
6. punktidest 2 ja 4 tuleneb soov luua rühmad andmebaaside ja MS SQL Serveri eksemplaride jaoks, mille seaded on samad
7. teha indeksteerimise topeltkopeerimise otsing (täielike ja mittetäielike, mis võivad olla vähem erinevad või erinevad vaid lisatud veergude järgi)
8. kuna SQLIndexManagerit kasutatakse ainult MS SQL Serveri andmebaaside jaoks, tuleb see pealkirjas kajastada, näiteks kujul: SQLIndexManager for MS SQL Server
9. kõik rakenduse osad, mis ei ole GUI, tuleb viia eraldi moodulitesse ja ümber kirjutada .NET Core 2.1-le

Käesoleva artikli kirjutamise ajal on punkt 6 soovidest aktiivselt arendamisel ja toetust on juba olemas, näiteks täisteksti ja sarnaste topeltkopeerimiste otsimine:

Allikad

• kohandatav indeksteerimise optimeerimise meetod
• SQLIndexManager utiliit
• SQLIndexManager utiliidi lühike tehniline ülevaade
• arutelu SQLIndexManager utiliidi üle
• SQL Index Manager – tasuta GUI tööriist indeksteerimise haldamiseks SQL Serveris ja Azure'is
• SQL Serveri indeksi analüüs ja optimeerimine

Allikas: habr.com