Преглед на безплатния инструмент SQLIndexManager

Както е известно, индексите играят важна роля в СУБД, предоставяйки бързо търсене на нужните записи. Затова е важно да се поддържат навреме. За анализа и оптимизацията са написани доста материали, включително в интернет. Например, скоро беше направен преглед на тази тема в този публикация.

Съществуват множество платени и безплатни решения за това. Например, има готово решение, основано на адаптивен метод за оптимизация на индексите.

По-нататък ще разгледаме безплатната утилита SQLIndexManager, чийто автор е Alan Denton.

Основното техническо различие между SQLIndexManager и редица други аналози посочва самият автор. тук и тук.

В тази статия ще разгледаме проекта и възможностите за експлоатация на това софтуерно решение.

Обсъждат тази утилита тук.
С времето голяма част от забележките и бъговете бяха отстранени.

И така, да преминем към самата утилита SQLIndexManager.

Приложението е написано на C# .NET Framework 4.5 в Visual Studio 2017 и използва DevExpress за формите:

и изглежда по следния начин:

Всички заявки се генерират в следните файлове:

1. Index
2. Query
3. QueryEngine
4. ServerInfo

При свързване с базата данни и изпращане на заявки към СУБД, приложението се подписва по следния начин:

ApplicationName=”SQLIndexManager”

При стартиране на приложението ще се отвори модален прозорец за добавяне на свързване:

Към момента подгрузката на целия списък с екземпляри на MS SQL Server, достъпни по локалните мрежи, не работи.

Също така може да добавите свързване с помощта на крайната лява бутонка в основното меню:

След това ще бъдат изпълнени следните заявки към СУБД:

1. Получаване на информация за СУБД

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

2. Получаване на списък с налични бази данни с техните кратки свойства

   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
   

След изпълнението на горепосочените скриптове ще се появи прозорец, съдържащ кратка информация за базите данни на избрания екземпляр на MS SQL Server:

Следва да се отбележи, че разширената информация се показва в зависимост от правата. Ако има sysadmin, може да се избират данни от представянето sys.master_files. Ако такива права няма, просто ще се върне по-малко информация, за да не се забави заявката.

Тук е необходимо да се изберат интересуващите бази данни и да се натисне бутона “ОК”.

Следва да бъде изпълнен следния скрипт за всяка избрана база данни за анализ на състоянието на индексите:

Анализ на състоянието на индексите

обяви @Fragmentation float=15;
обяви @MinIndexSize bigint=768;
обяви @MaxIndexSize bigint=1048576;
обяви @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

СЪЗДАЙ ТАБЛИЦА #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

СЪЗДАЙ ТАБЛИЦА #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

СЪЗДАЙ ТАБЛИЦА #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

СЪЗДАЙ ТАБЛИЦА #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)
    )

Както се вижда от самите заявки, временни таблици се използват достатъчно често. Това е направено, за да не се извършват ре-компилации, и в случай на голяма схема, планът може да бъде генериран паралелно при вмъкване на данни, тъй като вмъкването с таблицни променливи е възможно само в един поток.

След изпълнението на горепосочения скрипт ще се появи прозорец с таблица на индексите:

Тук също може да се покаже друга подробна информация, като:

1. база данни
2. брой секции
3. дата и час на последното посещение
4. сжатие
5. файлова група

и т.н.
Самите колони могат да бъдат настроени:

В клетките на колоната Fix може да се избере какво действие ще бъде извършено при оптимизация. Освен това, след приключването на сканирането, действието по подразбиране се избира въз основа на избраните настройки:

Необходимо е да се изберат нужните индекси за обработка.

С помощта на главното меню можете както да запазите скрипта ( тази същата кнопка стартира самия процес на оптимизация на индексите):

така и да запазите таблицата в различни формати (тази съща кнопка позволява да се отворят детайлни настройки за анализ и оптимизация на индексите):

Също така информацията може да бъде обновена, като се натисне третата кнопка отляво в главното меню до лупата.

Бутона с лупата позволява да изберете нужните бази данни за разглеждане.

В момента няма пълна справочна система. Следователно натискането на бутона “?” просто ще предизвика появата на модален прозорец, съдържащ основна информация за софтуерния продукт:

Освен всичко по-горе в главното меню има и лента за търсене:

При стартиране на процеса на оптимизация на индексите:

Също така в долната част на прозореца може да се прегледа лог на извършените действия:

В прозореца с детайлни настройки на анализа и оптимизацията на индексите могат да се настроят по-фини опции:

Предложения към приложението:

1. да се направи възможно селективното обновяване на статистиките не само за индексите и също по различни начини (пълно обновяване или частично)
2. да се направи възможно не само избор на БД, но и различни сървъри (това е много удобно, когато има много екземпляри на MS SQL Server)
3. за по-голяма гъвкавост при използването се предлага да се обгръщат командите в библиотеки и да се извеждат в команди PowerShell, както е направено, например, тук:
4. dbatools.io/commands
5. да се направи възможно запазването и промяната на лични настройки както за цялото приложение, така и, при необходимост, за всеки екземпляр на MS SQL Server и всяка база данни
6. от п.2 и 4 следва желанието да се направят групи по бази данни и групи по екземпляри на MS SQL Server, за които настройките са идентични
7. да се направи търсене на дублиращи индекси (пълни и непълни, които или не се различават съществено, или се различават само по включените колони)
8. тъй като SQLIndexManager се използва само за СУБД MS SQL Server, е необходимо да отразим това в името, например, по следния начин: SQLIndexManager for MS SQL Server
9. всички части на приложението, които не са GUI, да бъдат изнесени в отделни модули и пренаписани на .NET Core 2.1

Към момента на писане на статията, п.6 от желанията е активно в разработка и вече има поддръжка под формата на търсене на пълни и подобни дубликати:

Източници

• адаптивен метод за оптимизация на индексите
• утилита SQLIndexManager
• кратък технически преглед на утилитата SQLIndexManager
• дискусия относно утилитата SQLIndexManager
• SQL Index Manager – безплатен GUI инструмент за поддръжка на индекси в SQL Server и Azure
• Анализ и оптимизация на индексите в SQL Server

Източник: habr.com