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

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

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

Сега ще разгледаме безплатната утилита SQLIndexManager, чийто автор е AlanDenton.

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

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

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

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

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

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

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

1. Индекс
2. Заявка
3. Двигател за заявки
4. Информация за сървъра

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

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. При липса на такива права се връща по-малко информация, за да не се забавя запитването.

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

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

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

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

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

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

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

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