Агляд бясплатнай прылады SQLIndexManager

Як вядома індэксы гуляюць важную ролю ў СКБД, падаючы хуткі пошук да патрэбных запісаў. Таму так важна іх своечасова абслугоўваць. Аб аналізе і аптымізацыі напісана дастаткова шмат матэрыялу, у тым ліку і ў Інтэрнеце. Напрыклад, нядаўна рабіўся агляд дадзенай тэмы ў гэтай публікацыі.

Існуе мноства як платных, так і бясплатных рашэньняў для гэтага. Напрыклад, ёсць гатовае рашэнне, заснаванае на адаптыўнай метадзе аптымізацыі індэксаў.

Далей разгледзім бясплатную ўтыліту SQLIndexManager, аўтарам якой з'яўляецца AlanDenton.

Асноўнае тэхнічнае адрозненне паміж SQLIndexManager і шэрагу іншых аналагаў прыводзіць сам аўтар тут и тут.

У гэтым жа артыкуле са боку зірнем на праект і на магчымасці эксплуатацыі дадзенага праграмнага рашэння.

Абмяркоўваюць дадзеную ўтыліту тут.
З часам большая частка заўваг і багаў былі выпраўленыя.

Такім чынам, пяройдзем зараз да самай утыліты SQLIndexManager.

Прыкладанне напісана на мове C# .NET Framework 4.5 у Visual Studio 2017 і выкарыстоўвае DevExpress для формаў:

і выглядае наступным чынам:

Усе запыты фарміруюцца ў наступных файлах:

1. індэкс
2. запыт
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. Калі такіх правоў няма, то проста вяртаецца менш дадзеных, каб не запавольваць запыт.

Тут неабходна выбраць цікавыя базы дадзеных і націснуць на кнопку "ОК".

Далей будзе выкананы наступны скрыпт для кожнай абранай базы дадзеных для аналізу стану азначнікаў:

Аналіз стану індэксаў

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 <= @PreDescribeSize
AND r.[index_level] = 0
AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
AND i.IndexType IN (0, 1, 2)
IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL
DROP TABLE #Columns
CREATE TABLE #Columns (
ObjectID     INT NOT NULL
, ColumnID     INT NOT NULL
, ColumnName   SYSNAME NULL
, SystemTypeID TINYINT NULL
, IsSparse     BIT
, IsColumnSet  BIT
, MaxLen       INT
, PRIMARY KEY (ObjectID, ColumnID)
)
INSERT INTO #Columns
SELECT ObjectID     = [object_id]
, ColumnID     = [column_id]
, ColumnName   = [name]
, SystemTypeID = [system_type_id]
, IsSparse     = [is_sparse]
, IsColumnSet  = [is_column_set]
, MaxLen       = [max_length]
FROM sys.columns WITH(NOLOCK)
WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i)
IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL
DROP TABLE #IndexColumns
CREATE TABLE #IndexColumns (
ObjectID   INT NOT NULL
, IndexID    INT NOT NULL
, OrderID    INT NOT NULL
, ColumnID   INT NOT NULL
, IsIncluded BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, ColumnID)
)
INSERT INTO #IndexColumns
SELECT ObjectID   = [object_id]
, IndexID    = [index_id]
, OrderID    = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END
, ColumnID   = [column_id]
, IsIncluded = ISNULL([is_included_column], 0)
FROM sys.index_columns ic WITH(NOLOCK)
WHERE EXISTS(
SELECT *
FROM #Indexes i
WHERE i.ObjectID = ic.[object_id]
AND i.IndexID = ic.[index_id]
AND i.IndexType IN (1, 2)
)
IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL
DROP TABLE #Lob
CREATE TABLE #Lob (
ObjectID    INT NOT NULL
, IndexID     INT NOT NULL
, IsLobLegacy BIT
, IsLob       BIT
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
SELECT c.ObjectID
, IndexID     = ISNULL(i.IndexID, 1)
, IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END)
, IsLob       = 0
FROM #Columns c
LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID
WHERE c.SystemTypeID IN (34, 35, 99)
GROUP BY c.ObjectID
, i.IndexID
IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL
DROP TABLE #Sparse
CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY)
INSERT INTO #Sparse
SELECT DISTINCT ObjectID
FROM #Columns
WHERE IsSparse = 1
OR IsColumnSet = 1
IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL
DROP TABLE #AggColumns
CREATE TABLE #AggColumns (
ObjectID        INT NOT NULL
, IndexID         INT NOT NULL
, IndexColumns    NVARCHAR(MAX)
, IncludedColumns NVARCHAR(MAX)
, PRIMARY KEY (ObjectID, IndexID)
)
INSERT INTO #AggColumns
SELECT t.ObjectID
, t.IndexID
, IndexColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 0
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
, IncludedColumns = STUFF((
SELECT ', [' + c.ColumnName + ']'
FROM #IndexColumns i
JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
WHERE i.ObjectID = t.ObjectID
AND i.IndexID = t.IndexID
AND i.IsIncluded = 1
ORDER BY i.OrderID
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
SELECT DISTINCT ObjectID, IndexID
FROM #Indexes
WHERE IndexType IN (1, 2)
) t
SELECT i.ObjectID
, i.IndexID
, i.IndexName
, ObjectName       = o.[name]
, SchemaName       = s.[name]
, i.PagesCount
, i.UnusedPagesCount
, i.PartitionNumber
, i.RowsCount
, i.IndexType
, i.IsAllowPageLocks
, u.TotalWrites
, u.TotalReads
, u.TotalSeeks
, u.TotalScans
, u.TotalLookups
, u.LastUsage
, i.DataCompression
, f.Fragmentation
, IndexStats       = STATS_DATE(i.ObjectID, i.IndexID)
, IsLobLegacy      = ISNULL(lob.IsLobLegacy, 0)
, IsLob            = ISNULL(lob.IsLob, 0)
, IsSparse         = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT)
, IsPartitioned    = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT)
, FileGroupName    = fg.[name]
, i.IsUnique
, i.IsPK
, i.FillFactorValue
, i.IsFiltered
, a.IndexColumns
, a.IncludedColumns
FROM #Indexes i
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id]
LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID
LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID
LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber
LEFT JOIN (
SELECT ObjectID      = [object_id]
, IndexID       = [index_id]
, TotalWrites   = NULLIF([user_updates], 0)
, TotalReads    = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0)
, TotalSeeks    = NULLIF([user_seeks], 0)
, TotalScans    = NULLIF([user_scans], 0)
, TotalLookups  = NULLIF([user_lookups], 0)
, LastUsage     = (
SELECT MAX(dt)
FROM (
VALUES ([last_user_seek])
, ([last_user_scan])
, ([last_user_lookup])
, ([last_user_update])
) t(dt)
)
FROM sys.dm_db_index_usage_stats WITH(NOLOCK)
WHERE [database_id] = @DBID
) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID
LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND lob.IndexID = i.IndexID
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] 
WHERE o.[type] IN ('V', 'U')
AND (
f.Fragmentation >= @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 Tool for Index Maintenance on SQL Server and Azure
• SQL Server Index Analysis and Optimization

Крыніца: habr.com