SQLIndexManager тегін құралына шолу

Өздеріңіз білетіндей, индекстер қажетті жазбаларды жылдам іздеуді қамтамасыз ететін ДҚБЖ маңызды рөл атқарады. Сондықтан оларға дер кезінде қызмет көрсету өте маңызды. Талдау және оңтайландыру туралы көптеген материалдар, соның ішінде Интернетте жазылған. Мысалы, бұл тақырып жақында қаралды осы жарияланымның.

Бұл үшін көптеген ақылы және ақысыз шешімдер бар. Мысалы, дайын бар шешім, бейімделген индексті оңтайландыру әдісіне негізделген.

Әрі қарай, тегін қызметтік бағдарламаны қарастырайық SQLIndexManager, авторы АланДентон.

SQLIndexManager мен бірқатар басқа аналогтар арасындағы негізгі техникалық айырмашылықты автордың өзі береді осында и осында.

Бұл мақалада біз жобаны және осы бағдарламалық шешімнің операциялық мүмкіндіктерін сырттай қарастырамыз.

Бұл қызметтік бағдарламаны талқылау осында.
Уақыт өте келе көптеген пікірлер мен қателер түзетілді.

Сонымен, енді SQLIndexManager утилитасының өзіне көшейік.

Қолданба Visual Studio 4.5 бағдарламасында C# .NET Framework 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 серверінің таңдалған данасының дерекқорлары туралы қысқаша ақпаратты қамтитын терезе пайда болады:

Айта кету керек, кеңейтілген ақпарат құқықтар негізінде көрсетіледі. Бар болса сисадмин, содан кейін көріністен деректерді таңдауға болады 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. файлдар тобы

i t. d.
Динамиктердің өзін теңшеуге болады:

Түзету бағанының ұяшықтарында оңтайландыру кезінде қандай әрекет орындалатынын таңдауға болады. Сондай-ақ, сканерлеу аяқталған кезде таңдалған параметрлер негізінде әдепкі әрекет таңдалады:

Өңдеу үшін қажетті индекстерді таңдау керек.

Негізгі мәзірді пайдаланып сценарийді сақтауға болады (сол түйме индексті оңтайландыру процесін өзі бастайды):

және кестені әртүрлі пішімдерде сақтаңыз (бір түйме индекстерді талдау және оңтайландыру үшін егжей-тегжейлі параметрлерді ашуға мүмкіндік береді):

Сондай-ақ, лупа жанындағы негізгі мәзірдің сол жағындағы үшінші түймені басу арқылы ақпаратты жаңартуға болады.

Үлкейткіш әйнегімен түйме қарастыру үшін қажетті мәліметтер қорын таңдауға мүмкіндік береді.

Қазіргі уақытта толық көмек жүйесі жоқ. Сондықтан «?» түймесін басу бағдарламалық өнім туралы негізгі ақпаратты қамтитын модальды терезенің пайда болуына себеп болады:

Жоғарыда сипатталған барлық нәрселерден басқа, негізгі мәзірде іздеу жолағы бар:

Индексті оңтайландыру процесін бастағанда:

Сондай-ақ терезенің төменгі жағында орындалған әрекеттер журналын көруге болады:

Индексті талдау және оңтайландыру үшін егжей-тегжейлі параметрлер терезесінде неғұрлым нәзік опцияларды конфигурациялауға болады:

Өтінімге арналған сұраулар:

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 ДҚБЖ үшін пайдаланылғандықтан, оны атауда көрсету қажет, мысалы, келесідей: MS SQL серверіне арналған SQLIndexManager
9. Қолданбаның барлық GUI емес бөліктерін бөлек модульдерге жылжытыңыз және оларды .NET Core 2.1 жүйесінде қайта жазыңыз

Жазу кезінде тілектердің 6-тармағы белсенді түрде әзірленуде және толық және ұқсас көшірмелерді іздеу түрінде қолдау бар:

Ақпарат көздері

• адаптивті индексті оңтайландыру әдісі
• SQLIndexManager утилитасы
• SQLIndexManager утилитасына қысқаша техникалық шолу
• SQLIndexManager утилитасында талқылау
• SQL Index Manager – SQL Server және Azure жүйесінде индекстерге қызмет көрсетуге арналған тегін GUI құралы
• SQL серверінің индексін талдау және оңтайландыру

Ақпарат көзі: www.habr.com